Informatica Powercenter Repository Queries set-1

Overview:
The below SQL queries are given to Informatica Development or Administration teams to see the metadata from Power center repository database. Please suffix your schema name if required with the table names.

1: To get the source and target connection objects information

SELECT WF.SUBJECT_AREA AS FOLDER_NAME, WF.WORKFLOW_NAME AS WORKFLOW_NAME,
T.INSTANCE_NAME AS SESSION_NAME, T.TASK_TYPE_NAME,
C.CNX_NAME AS CONNECTION_NAME, V.CONNECTION_SUBTYPE, V.HOST_NAME,
V.USER_NAME, C.INSTANCE_NAME, C.READER_WRITER_TYPE,
C.SESS_EXTN_OBJECT_TYPE
FROM REP_TASK_INST T,
REP_SESS_WIDGET_CNXS C,
REP_WORKFLOWS WF,
V_IME_CONNECTION V
WHERE T.TASK_ID = C.SESSION_ID
AND WF.WORKFLOW_ID = T.WORKFLOW_ID
AND C.CNX_NAME = V.CONNECTION_NAME
--AND WF.SUBJECT_AREA = <FOLDER NAME>

2 : Check the master gateway node

select * from ISP_MASTER_ELECTION;

3: Check which Session has Target Table Truncate Option enabled:

select task_name,'Truncate Target Table' ATTR,
decode(attr_value,1,'Yes','No') Value
from OPB_EXTN_ATTR A, REP_ALL_TASKS B
where A.SESSION_ID=B.TASK_ID and attr_id=9

4: To Find the tracing levels of Powercenter Sessions:

select task_name,
decode (attr_value,
0,'None',
1,'Terse',
2,'Normal',
3,'Verbose Initialisation',
4,'Verbose Data','') Tracing_Level
from
REP_SESS_CONFIG_PARM A,
opb_task B
WHERE a.SESSION_ID=TSK.TASK_ID
and b.TASK_TYPE=68
and attr_id=204
and attr_type=6

5: Find all the Invalid workflows:

select subj_name, task_name
from opb_task a, opb_subject b
where task_type = 71
and is_valid = 0
and a.subj_id = b.subject_id
and UPPER(SUBJ_NAME) like UPPER('<FOLDER_NAME>')

6: List of Users and Groups having Folders Permissions:

SELECT b.subj_name folder_name, c.NAME "USER/GROUP NAME",
DECODE (a.user_type, 1, 'USER', 2, 'GROUP') TYPE,
CASE WHEN ((a.permissions - (a.user_id + 1)) IN (8, 16))THEN 'R--'
WHEN ((a.permissions - (a.user_id + 1)) IN (10, 20))THEN 'R-X'
WHEN ((a.permissions - (a.user_id + 1)) IN (12, 24))THEN 'RW-'
WHEN ((a.permissions - (a.user_id + 1)) IN (14, 28))THEN 'RWX'
ELSE 'NO PERMISSIONS'
END permissions,
CASE WHEN (a.user_id = b.owner_id and c.type = 1 ) THEN 'Y' ELSE 'N' END as Owner
FROM opb_object_access a, opb_subject b, opb_user_group c
WHERE a.object_type = 29
AND a.object_id = b.subj_id
AND a.user_id = c.ID
AND a.user_type = c.TYPE
order by 1,2,3

7: List of all Shared Folders:

SELECT SUBJ_NAME,SUBJ_DESC FROM OPB_SUBJECT WHERE IS_SHARED <>0 ORDER BY 1,2

8: List of all Repository Folders:

SELECT SUBJ_NAME,SUBJ_DESC FROM OPB_SUBJECT ORDER BY 1,2

9: List of Folder and their Owners with OS profiles:

SELECT SUBJ_NAME FOLDER_NAME, OS_USER OS_PROFILE, USER_NAME OWNER FROM OPB_SUBJECT A, REP_USERS B
WHERE A.OWNER_ID=B.USER_ID
ORDER BY 1

10: List of Workflows with NO Integration Service assigned:

SELECT SUBJECT_AREA as FOLDER_NAME, WORKFLOW_NAME, SERVER_NAME as INTEGRATION_SERVICE
FROM REP_WORKFLOWS
WHERE SERVER_NAME IS NULL