Abstract
Below are queries to retrieve
the data from Informatica metadata (Repository Tables). You need to have
access to the Repository schema to access the metadata. Please prefix with your
schema name to the tables and view in the below queries.
1. REPOSITORY
1.1
REPOSITORY INFO
SELECT DOMAIN_NAME,
REPOSITORY_NAME,
PCSF_DOMAIN AS DOMAIN,
DB_USER
FROM
OPB_REPOSIT_INFO;
1.2
LIST OF OBJECTS WHICH ARE NOT VALID
SELECT REPOSITORY,
FOLDER_NAME,
TASK_TYPE,
WORKLET_OR_SESSION,
IS_VALID
FROM (SELECT DISTINCT
OPB_REPOSIT_INFO.REPOSITORY_NAME AS REPOSITORY,
OPB_SUBJECT.SUBJ_NAME AS FOLDER_NAME,
OPB_TASK.TASK_NAME
ASWORKFLOW_OR_WORKLET,
DECODE (OPB_TASK_INST.TASK_TYPE,
58, 'COMMAND',
59, 'DECISION',
60, 'EVENT WAIT',
62, 'START',
65, 'EMAIL',
66, 'TIMER',
67, 'ASSIGNMENT',
68, 'SESSION',
70, 'WORKLET',
91, 'CONTROL',
NULL)
TASK_TYPE,
OPB_TASK_INST.INSTANCE_NAME
AS WORKLET_OR_SESSION,
DECODE (OPB_TASK_INST.IS_VALID, 1, 'VALID', 'NOT VALID')
AS IS_VALID
FROM OPB_TASK_INST,
OPB_OBJECT_TYPE,
OPB_TASK,
OPB_SUBJECT,
OPB_REPOSIT_INFO
WHERE OPB_TASK_INST.TASK_TYPE
!= 62
AND OPB_TASK_INST.TASK_TYPE = OPB_OBJECT_TYPE.OBJECT_TYPE_ID
AND OPB_TASK_INST.WORKFLOW_ID = OPB_TASK.TASK_ID
AND OPB_TASK_INST.VERSION_NUMBER = OPB_TASK.VERSION_NUMBER
AND OPB_TASK.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID
AND OPB_TASK.UTC_CHECKIN <> 0)
WHERE IS_VALID = 'NOT VALID'
ORDER BY 2, 3;
1.3 LIST OF OBJECTS WHICH ARE FAILED IN
LAST 10 DAYS
SELECT Subject_Area AS Folder,
Session_Name,
Last_Error AS Error_Message,
DECODE (Run_Status_Code, 3, 'Failed', 4, 'Stopped', 5, 'Aborted')
AS Status,
Actual_Start AS Start_Time,
Session_TimeStamp
FROM rep_sess_log
WHERE run_status_code <> 1
AND TRUNC (Actual_Start) BETWEEN TRUNC (SYSDATE - 10) AND TRUNC (SYSDATE)
ORDER BY 1, 2;
1.4 LIST WHERE ALL A TABLE IS USED
If you want to know the tables used
in SQL OVERRIDES of Source Qualifier or in Lookup Transformation. The Query
will give you the Type, Subject Area (Folder) and SQL as o/p.
SELECT DISTINCT REP_ALL_MAPPINGS.SUBJECT_AREA,
REP_ALL_MAPPINGS.MAPPING_NAME,
REP_WIDGET_ATTR.ATTR_NAME,
REP_WIDGET_ATTR.ATTR_VALUE
FROM REP_WIDGET_ATTR, REP_WIDGET_INST, REP_ALL_MAPPINGS
WHERE REP_WIDGET_ATTR.WIDGET_ID = REP_WIDGET_INST.WIDGET_ID
AND REP_WIDGET_INST.MAPPING_ID = REP_ALL_MAPPINGS.MAPPING_ID
AND REP_WIDGET_ATTR.WIDGET_TYPE IN (3, 11)
AND REP_WIDGET_ATTR.ATTR_ID = 1
AND REP_WIDGET_ATTR.ATTR_VALUE LIKE
'%' || REPLACE ('TABLE_NAME', '_', '/_') || '%' ESCAPE '/'
ORDER BY 1, 2, 3;
1.5 LIST
ALL SOURCE AND TARGET TABLES OF MAPPING
SELECT DISTINCT SUBJECT_AREA,
SOURCE_NAME,
TARGET_NAME,
MAPPING_NAME
FROM
REP_TBL_MAPPING ;
1.6 LIST COMMENTS OF ALL OBJECTS
SELECT REP_SUBJECT.SUBJECT_AREA
AS FOLDER_NAME,
REP_VERSION_PROPS.OBJECT_NAME,
REP_VERSION_PROPS.COMMENTS,
REP_VERSION_PROPS.VERSION_NUMBER
FROM REP_VERSION_PROPS, REP_SUBJECT
WHERE REP_SUBJECT.SUBJECT_ID = REP_VERSION_PROPS.SUBJECT_ID AND REP_VERSION_PROPS.COMMENTS IS NOT NULL
ORDER BY 1, 2;
1.7 All Folders in Repository
SELECT SUBJ_NAME, SUBJ_DESC
FROM OPB_SUBJECT
ORDER BY 1, 2;
1.8 All Shared Folders in the Repository
SELECT SUBJ_NAME, SUBJ_DESC
FROM OPB_SUBJECT
WHERE IS_SHARED <> 0
ORDER BY 1, 2;
1.9 User
or Groups having access to the folders in Repository
SELECT subj.subj_name folder_name,
user_group.NAME user_name,
DECODE (obj_access.user_type, 1, 'USER', 2, 'GROUP') TYPE,
CASE WHEN ( (obj_access.permissions - (obj_access.user_id + 1))IN(8, 16))
THEN 'READ'
WHEN( (obj_access.permissions - (obj_access.user_id + 1))IN (10, 20))
THEN 'READ & EXECUTE'
WHEN ( (obj_access.permissions - (obj_access.user_id + 1))IN(12, 24))
THEN 'READ & WRITE'
WHEN ( (obj_access.permissions - (obj_access.user_id + 1))IN(14, 28))
THEN 'READ, WRITE &
EXECUTE'
ELSE'NO PERMISSIONS'
END permissions
FROM opb_object_access obj_access,
opb_subject subj,
opb_user_group user_group
WHERE obj_access.object_type = 29
AND obj_access.object_id = subj.subj_id
AND obj_access.user_id = user_group.ID
AND obj_access.user_type = user_group.TYPE
ORDER BY 1, 2, 3;
