ETL Informatica Repository Queries

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;