Informatica Repository Queries to get connection names, command tasks and event wait file names

Query1: 

Query to get folder name, workflow name, session names and all the connections used in sessions.

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>
 
   Note: If SESS_EXTN_OBJECT_TYPE=78 then its reader, If 79 then its writer
 
 Query2:
   
   Query to get commands used in command tasks including the commands used as pre/post session commands.
 
   SELECT DISTINCT T.SUBJECT_ID, F.SUBJECT_AREA AS FOLDER_NAME,
                W.TASK_NAME AS WORKFLOW_NAME, T.TASK_NAME AS CMD_TASK_NAME,
                CMD.PM_VALUE AS CMD_NAME, CMD.EXEC_ORDER,
                CMD.VAL_NAME AS CMD_NUMBER, T.TASK_ID, T.TASK_TYPE,
                T.RU_PARENT_ID
           FROM OPB_TASK_VAL_LIST CMD, OPB_TASK T, OPB_TASK W, REP_SUBJECT F
          WHERE T.TASK_ID = CMD.TASK_ID
            AND T.SUBJECT_ID = F.SUBJECT_ID
            AND T.TASK_TYPE = 58
            AND T.RU_PARENT_ID = W.TASK_ID
            AND F.SUBJECT_AREA = <FOLDER NAME>
       ORDER BY F.SUBJECT_AREA, W.TASK_NAME, T.TASK_NAME, CMD.EXEC_ORDER
 
Query3:

Query to get the event wait file names used in event wait tasks.

  SELECT    DISTINCT F.SUBJECT_AREA AS FOLDER_NAME, W.TASK_NAME AS WORKFLOW_NAME,
                T.TASK_ID, T.TASK_TYPE, T.TASK_NAME AS EVENTWAIT_NAME,
                T.RU_PARENT_ID, W.TASK_ID
           FROM OPB_TASK T, OPB_TASK W, REP_SUBJECT F
          WHERE T.RU_PARENT_ID = W.TASK_ID
            AND T.TASK_TYPE = 60
            AND T.SUBJECT_ID = F.SUBJECT_ID
            AND F.SUBJECT_AREA = <FOLDER NAME>

UTC columns in Infa Repository tables

You might have seen some UTC columns in Informatica repository tables. 


Eg: 'PARENT_SOURCE_UTC_LAST_SAVED' column in 'REP_ALL_SOURCES' view. Datatype for this column is 'Number'. Have you ever wondered about the value of this column?


This is one of the formats of date and this number denotes 'number of seconds from 1/1/1970' 

We can use the below query to find the time when an action was performed on the repository using the below logic on the UTC time columns that we have in Infa repository.

Will help us to get the time irrespective of the timezone (PST/PDT)

Just replace the number in bold in below query with the UTC value.

select

FROM_TZ (

cast ( (to_date( '01/01/1970 00:00:00', 'mm/dd/yyyy hh24:mi:ss') + 1300192052/86400) as timestamp(0) )

, '+00:00')

AT TIME ZONE 'US/Pacific'

from dual

Thanks Subbu for above query.

How to get relational connection details used in Informatica?

Below is the query to get relation connection details in Informatica 8series.

select * from V_PC8X_CONNECTION where CONNECTION_NAME IN
(select DISTINCT CONNECTION_NAME from REP_SESSION_CNXS where SUBJECT_AREA='FOLDER_NAME');


If you want to get connection details used in particular folder then you need to pass the FOLDER_NAME as input to the above query.
Above query will give details like Connection name, Host name, database type, user name, Connection string and more.

How to find out whether 'Push Down Optimization' option is enabled or not?

Below is query on repository database to check whether 'Push Down Optimization' option is enabled or not

Select T.* from OPB_TASK T, OPB_TASK_ATTR A where T.TASK_ID=A.TASK_ID AND A.ATTR_ID=107 AND ATTR_VALUE=1

How to find target load type(Whether it is 'Bulk' or 'Normal' ) in session properties?

SELECT E.SUBJ_NAME AS FOLDERNAME, 

D.TASK_NAME AS SESSIONNAME, 

A.SESSION_ID AS SESSIONID, 

B.INSTANCE_NAME AS INSTANCENAME, 

DECODE (C.ATTR_VALUE, '0', 'NORMAL', '1', 'BULK', NULL) 

AS TARGETLOADTYPE 

FROM INFA_REP.OPB_SESSION A, 

INFA_REP.OPB_SWIDGET_INST B, 

INFA_REP.OPB_EXTN_ATTR C, 

INFA_REP.OPB_TASK D, 

INFA_REP.OPB_SUBJECT E 

WHERE A.SESSION_ID = B.SESSION_ID 

AND A.SESSION_ID = C.SESSION_ID 

AND C.ATTR_ID = 3 

AND B.SESS_WIDG_INST_ID = C.SESS_WIDG_INST_ID 

AND A.SESSION_ID = D.TASK_ID 

AND D.SUBJECT_ID = E.SUBJ_ID 

AND E.SUBJ_NAME='FOLDER_NAME'

How to check whether 'Fail Parent if this Task fails' option is checked or not?

Below is the query to know whether 'Fail Parent if this task Fails' option is checked or not.
SELECT * FROM OPB_TASK_INST 
WHERE TO_NUMBER(BITAND(BIT_OPTIONS,16))<>0
use the below query to know parent task(workflow or worklet where session resides) .
Note: Check the version number. one which is having the max version number is the latest task.

SELECT T.SUBJECT_ID,T.TASK_ID,
T.TASK_TYPE,
T.TASK_NAME AS PARENT_TASK,
T.VERSION_NUMBER,I.WORKFLOW_ID,
I.INSTANCE_ID,I.TASK_ID,
I.TASK_TYPE,
I.INSTANCE_NAME AS SESSION_NAME,
I.VERSION_NUMBER
FROM 
OPB_TASK T,OPB_TASK_INST I
WHERE T.TASK_ID=I.WORKFLOW_ID 
AND T.IS_VISIBLE=1 
AND TO_NUMBER(BITAND(I.BIT_OPTIONS,16))<>0

To get all objects under Folder from Repository Tables

Query to get workflow, Session, Mapping, SRC instance, Target instance, SRC (Physical) and Target (Physical) names by passing folder name as input.

SELECT F.SUBJ_NAME        AS FOLDER_NAME,

       WF.TASK_NAME       AS WORKFLOW_NAME,

       SESS.INSTANCE_NAME AS SESSION_NAME,

       M.MAPPING_NAME     AS MAPPING_NAME,

       SRC.INSTANCE_NAME  AS SOURCE_NAME,

       TGT.INSTANCE_NAME  AS TARGET_NAME,

       PS.SOURCE_NAME     AS PHY_SRC_NAME,

       PT.TARGET_NAME     AS PHY_TARGET_NAME

FROM   OPB_SUBJECT F,

       (SELECT TASK_NAME,TASK_ID,

               SUBJECT_ID,IS_VISIBLE,

               TASK_TYPE,MAX(VERSION_NUMBER)

        FROM   OPB_TASK

        GROUP  BY TASK_NAME,TASK_ID,

                  SUBJECT_ID,IS_VISIBLE,

                  TASK_TYPE) WF,

       (SELECT WORKFLOW_ID,INSTANCE_ID,

               TASK_ID, INSTANCE_NAME

        FROM   OPB_TASK_INST

        WHERE  ( WORKFLOW_ID, INSTANCE_ID, TASK_ID, VERSION_NUMBER ) IN 

       (SELECT WORKFLOW_ID,INSTANCE_ID,

               TASK_ID,VERSION_NUMBER

                     FROM

               (SELECT WORKFLOW_ID,INSTANCE_ID,

                       TASK_ID,

                       MAX(VERSION_NUMBER) AS VERSION_NUMBER

               FROM    OPB_TASK_INST

                       GROUP  BY WORKFLOW_ID,INSTANCE_ID,

                                 TASK_ID))) WL,

       (SELECT WORKFLOW_ID,INSTANCE_ID,

               TASK_ID,INSTANCE_NAME,

               TASK_TYPE

        FROM   OPB_TASK_INST

        WHERE  ( WORKFLOW_ID, INSTANCE_ID, TASK_ID, VERSION_NUMBER ) IN 

       (SELECT WORKFLOW_ID,INSTANCE_ID,

               TASK_ID,VERSION_NUMBER

                          FROM

               (SELECT WORKFLOW_ID,INSTANCE_ID,

                       TASK_ID,

                       MAX(VERSION_NUMBER) AS VERSION_NUMBER

               FROM    OPB_TASK_INST

                       GROUP  BY WORKFLOW_ID,INSTANCE_ID,

                                 TASK_ID))) SESS,

       (SELECT SESSION_ID,MAPPING_ID,

               MAX(VERSION_NUMBER)

        FROM   OPB_SESSION

        GROUP  BY SESSION_ID,MAPPING_ID) S,

       (SELECT MAPPING_NAME,IS_VISIBLE,

               MAPPING_ID,MAX(VERSION_NUMBER)

        FROM   OPB_MAPPING

        GROUP  BY MAPPING_NAME,IS_VISIBLE,

                  MAPPING_ID) M,

       (SELECT MAPPING_ID,INSTANCE_NAME,

               WIDGET_TYPE,WIDGET_ID,

               MAX(VERSION_NUMBER)

        FROM   OPB_WIDGET_INST

        GROUP  BY MAPPING_ID,INSTANCE_NAME,

                  WIDGET_TYPE,WIDGET_ID) SRC,

       (SELECT MAPPING_ID,INSTANCE_NAME,

               WIDGET_TYPE,WIDGET_ID,

               MAX(VERSION_NUMBER)

        FROM   OPB_WIDGET_INST

        GROUP  BY MAPPING_ID,INSTANCE_NAME,

                  WIDGET_TYPE,WIDGET_ID) TGT,

       OPB_SRC PS,

       OPB_TARG PT

WHERE  WF.IS_VISIBLE = 1

       AND WF.SUBJECT_ID = F.SUBJ_ID

       AND WF.TASK_ID = WL.WORKFLOW_ID

       AND WF.TASK_TYPE = 71

       AND ( WL.TASK_ID = SESS.WORKFLOW_ID

              OR WL.WORKFLOW_ID = SESS.WORKFLOW_ID )

       AND WF.IS_VISIBLE = 1

       AND SESS.TASK_TYPE IN ( 58, 68 )

       AND SESS.TASK_ID = S.SESSION_ID

       AND S.MAPPING_ID = M.MAPPING_ID

       AND M.IS_VISIBLE = 1

       AND SRC.MAPPING_ID = M.MAPPING_ID

       AND SRC.WIDGET_TYPE = 1

       AND TGT.MAPPING_ID = M.MAPPING_ID

       AND TGT.WIDGET_TYPE = 2

       AND TGT.WIDGET_ID = PT.TARGET_ID

       AND SRC.WIDGET_ID = PS.SRC_ID

       AND F.SUBJ_NAME = 'FOLDER_NAME'      

To know Connected Column names in target from Repository Tables

Say a target having 10 columns might be having only 3 columns available in the source and hence only these 3 will be mapped in the target. Use the below query to know these connected column names.

In the below query give your folder name to SUBJECT_AREA and your mapping name to MAPPING_NAME.

SELECT TO_OBJECT_FIELD_NAME 

FROM REP_MAPPING_CONN_PORTS

WHERE TO_OBJECT_TYPE=2

AND MAPPING_NAME ='M_STG_DATA_LOAD_FOR_TESTING'

AND SUBJECT_AREA='USR_KOTHAKOP'

Workflow, Session, Mapping Names from Repository Tables

Following is the query to know workflow, session, mapping names in a particular folder by passing folder name as an input from repository tables in informatica.

SELECT WF.TASK_NAME       AS WORKFLOW_NAME,
       SESS.INSTANCE_NAME AS SESSION_NAME,
       M.MAPPING_NAME     AS MAPPING_NAME
FROM   OPB_SUBJECT F,
       (SELECT TASK_NAME,TASK_ID,
               SUBJECT_ID,IS_VISIBLE,
               TASK_TYPE,MAX(VERSION_NUMBER)
        FROM   OPB_TASK
        GROUP  BY TASK_NAME,TASK_ID,SUBJECT_ID,IS_VISIBLE,
                  TASK_TYPE) WF,
       (SELECT WORKFLOW_ID,INSTANCE_ID,
               TASK_ID,INSTANCE_NAME
        FROM   OPB_TASK_INST
        WHERE  ( WORKFLOW_ID, INSTANCE_ID, TASK_ID, VERSION_NUMBER ) IN
        (SELECT   WORKFLOW_ID,INSTANCE_ID,
               TASK_ID,VERSION_NUMBER
                             FROM
               (SELECT WORKFLOW_ID,INSTANCE_ID,
                       TASK_ID,
                       MAX(VERSION_NUMBER)
                          AS VERSION_NUMBER
               FROM  OPB_TASK_INST
                       GROUP  BY WORKFLOW_ID,INSTANCE_ID,
                                 TASK_ID))) WL,
       (SELECT WORKFLOW_ID,INSTANCE_ID,
               TASK_ID,INSTANCE_NAME,
               TASK_TYPE
        FROM   OPB_TASK_INST
        WHERE  ( WORKFLOW_ID, INSTANCE_ID, TASK_ID, VERSION_NUMBER ) IN
        (SELECT   WORKFLOW_ID,INSTANCE_ID,
               TASK_ID,VERSION_NUMBER
                         FROM
               (SELECT  WORKFLOW_ID,INSTANCE_ID,
                       TASK_ID,MAX(VERSION_NUMBER)
                         AS VERSION_NUMBER
               FROM   OPB_TASK_INST
                      GROUP  BY WORKFLOW_ID,INSTANCE_ID,TASK_ID))) SESS,
       (SELECT SESSION_ID,MAPPING_ID,
               MAX(VERSION_NUMBER)
        FROM   OPB_SESSION
        GROUP  BY SESSION_ID,MAPPING_ID) S,
       (SELECT MAPPING_NAME,IS_VISIBLE,
               MAPPING_ID,MAX(VERSION_NUMBER)
        FROM   OPB_MAPPING
        GROUP  BY MAPPING_NAME,IS_VISIBLE,MAPPING_ID) M
WHERE  WF.IS_VISIBLE = 1
       AND WF.SUBJECT_ID = F.SUBJ_ID
       AND WF.TASK_ID = WL.WORKFLOW_ID
       AND WF.TASK_TYPE = 71
       AND ( WL.TASK_ID = SESS.WORKFLOW_ID
              OR WL.WORKFLOW_ID = SESS.WORKFLOW_ID )
       AND WF.IS_VISIBLE = 1
       AND SESS.TASK_TYPE IN ( 58, 68 )
       AND SESS.TASK_ID = S.SESSION_ID
       AND S.MAPPING_ID = M.MAPPING_ID
       AND M.IS_VISIBLE = 1
       AND F.SUBJ_NAME = 'FOLDER_NAME'