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
