PowerCenter Repository Queries

This article contains some of the repository queries I have worked with.

Note: All the queries given below are for repository in Oracle Database. Please check the syntax before running on other Database.

1. Get Mapping Level Lookup SQL Override, SQL Query of Lookup Procedure and Source Qualifier Transformations

SELECT DISTINCT

d.subject_area AS Folder, d.mapping_name, a.widget_type_name AS Transformation_Type,

a.instance_name as Transformation_Name, b.attr_name, b.attr_value, c.session_name 

FROM

REP_WIDGET_INST a, REP_WIDGET_ATTR b, REP_LOAD_SESSIONS c, REP_ALL_MAPPINGS d

WHERE b.widget_id = a. widget_id

AND b.widget_type = a. widget_type

AND b.widget_type in (3, 11)

AND c.mapping_id = a.mapping_id

AND d.mapping_id = a.mapping_id

AND b.attr_id= 1

AND b.attr_datatype=2 and b.attr_type=3

ORDER BY d.subject_area, d.mapping_name

2. Get list of tables used in Lookup transformation

SELECT

b.mapping_name,a.instance_name, c.attr_name, c.attr_value,

a.widget_type_name,b.parent_subject_area

FROM

REP_WIDGET_INST a INNER JOIN REP_ALL_MAPPINGS b ON a.mapping_id = b.mapping_id INNER JOIN

REP_WIDGET_ATTR c ON a.widget_id = c.widget_id

WHERE

(c.attr_description LIKE 'Lookup source table')

3. Get list of all sources along with mappings using the source

SELECT

b.source_database_name,b.source_name,a.mapping_name

FROM

REP_SRC_MAPPING a,REP_ALL_SOURCES b

WHERE a.source_id(+) =   b.source_id

AND   a.source_name(+) = b.parent_source_name

AND   a.subject_id(+) =  b.subject_id

4. Get Lookup connection information

SELECT

d.subject_area AS Folder, a.mapping_name AS Mapping, b.instance_name AS Transformation, c.attr_value as Connection

FROM

REP_ALL_MAPPINGS a, REP_WIDGET_INST b, REP_WIDGET_ATTR c, REP_SUBJECT d

WHERE d.SUBJECT_ID = a.SUBJECT_ID

AND a.MAPPING_ID = b.MAPPING_ID

AND b.WIDGET_TYPE = 11

AND b.WIDGET_ID = c.WIDGET_ID

AND c.ATTR_ID = 6

5. Get Stored Procedure connection information

SELECT

d.subject_area AS Folder, a.mapping_name AS Mapping, b.instance_name AS Transformation, c.attr_value as Connection

FROM

REP_ALL_MAPPINGS a, REP_WIDGET_INST b, REP_WIDGET_ATTR c, REP_SUBJECT d

WHERE d.SUBJECT_ID = a.SUBJECT_ID

AND a.MAPPING_ID = b.MAPPING_ID

AND b.WIDGET_TYPE = 11

AND b.WIDGET_ID = c.WIDGET_ID

AND c.ATTR_ID = 1

6. Get number of sessions executed on a specific day

SELECT

server_name AS Integration_Server,count(*) AS Total_Jobs

FROM

REP_TASK_INST_RUN

WHERE TO_CHAR(start_time,'mm/dd/yyyy') = '12/06/2010'

GROUP BY server_name

7. Get run details for a worklet in a specific workflow for a particular time period

SELECT DISTINCT

b.workflow_name,a.task_name, b.start_time,b.end_time

FROM

REP_ALL_TASKS a, REP_TASK_INST_RUN b

WHERE (a.task_id = b.task_id AND a.version_number = b.task_version_number )

AND (b.workflow_name = 'workflow_name' AND

b.start_time > TO_DATE('11/08/2010 06:00:00','MM/DD/YYYY HH24:MI:SS')

AND a.task_name like 'workletname%' )

ORDER BY

b.start_time

8. Get list of all the emails with attachment used as part of email task or post-session commands

SELECT DISTINCT

d.subj_name AS Folder_Name, c.workflow_name AS Workflow_Name,a.task_name AS Task_Name, b.attr_value AS Value

FROM

OPB_TASK a, OPB_TASK_ATTR b, REP_TASK_INST_RUN c, OPB_SUBJECT d

WHERE a.task_id = b.task_id

AND a.task_type = b.task_type

AND c.subject_id = a.subject_id

AND a.subject_id = d.subj_id

AND a.task_type = 65

AND b.attr_id in (2,3)

AND(b.attr_value LIKE '%\%a%' ESCAPE '\' OR b.attr_value LIKE '%\%g%' ESCAPE '\')

9. Get all the post session commands used inside each session

SELECT

a.task_name AS Task,c.subj_name AS Folder, b.PM_value AS Command

FROM

OPB_TASK a,OPB_TASK_VAL_LIST b, OPB_SUBJECT c

WHERE a.task_type=58 AND a.task_name='post_session_command'

AND b.task_id=a.task_id AND b.subject_id=c.subj_id

10. Get all the post session success commands used inside each session

SELECT

a.task_name AS Task,c.subj_name AS Folder, b.PM_value AS Command

FROM

OPB_TASK a,OPB_TASK_VAL_LIST b, OPB_SUBJECT c

WHERE a.task_type=58 AND a.task_name='post_session_success_command'

AND b.task_id=a.task_id AND b.subject_id=c.subj_id

11. Get all the post session failure commands used inside each session

SELECT

a.task_name AS Task,c.subj_name AS Folder, b.PM_value AS Command

FROM

OPB_TASK a,OPB_TASK_VAL_LIST b, OPB_SUBJECT c

WHERE a.task_type=58 AND a.task_name='post_session_failure_command'

AND b.task_id=a.task_id AND b.subject_id=c.subj_id

12. Get DTM buffer size, buffer block size and line sequential buffer length for each session

SELECT

a.SUBJECT_AREA AS Folder_Name, a.task_name AS Session_Name, b.ATTR_VALUE AS DTM_BUFFER_SIZE, c.ATTR_VALUE AS Buffer_Block_Size,

d.ATTR_VALUE AS Line_Sequential_Buffer_Length

FROM

REP_ALL_TASKS a, REP_TASK_ATTR b, REP_SESS_CONFIG_PARM c, REP_SESS_CONFIG_PARM d

WHERE a.TASK_ID = b.TASK_ID

AND a.TASK_ID = c.SESSION_ID

AND a.TASK_ID = d.SESSION_ID

AND b.ATTR_ID = 101

AND c.ATTR_ID = 5

AND d.ATTR_ID = 6

ORDER BY 1, 2

13. Get All Version Comments (For versioning enabled repositories)

SELECT

b.subject_area AS Folder_Name, b.subject_id, a.comments, a.object_name, a.version_number, a.object_type, a.user_id, a.saved_from

FROM

rep_version_props a, rep_subject b

WHERE b.SUBJECT_ID = a.subject_id

AND a.comments is not null;

14. List all workflows and their associated Integration Service

SELECT

workflow_name,server_name

FROM

REP_WORKFLOWS