Most Useful Informatica Repository Queries (Meta Queries)

Query 1 :  Query to get the list of users and groups.

Please fire this query on the Domain database to get the list of users and groups which the user belongs.

SELECT T1.GROUP_NAME,T2.USER_LOGIN,T4.POU_FULLNAME USER_NAME

from (select POS_NAME GROUP_NAME,PSI_CONTAINER from po_idobj where PSI_DISCRIMINATOR ='GROUP' and POS_NAME!='Everyone') T1,

(select POS_NAME USER_LOGIN,PSI_CONTAINER from po_idobj where PSI_DISCRIMINATOR ='USERREF' and PSI_CONTAINER!=36) T2,

(select POS_NAME USER_LOGIN,PSI_CONTAINER from po_idobj where PSI_DISCRIMINATOR ='USER') T3,

PO_USERINFO T4

where T1.PSI_CONTAINER(+)=T2.PSI_CONTAINER

and   T2.USER_LOGIN(+)=T3.USER_LOGIN

and   T3.PSI_CONTAINER=T4.PSU_RSID

order by 1,2

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query 2 : Query to get the scheduling details for the workflows.

Please fire this query on the Repository database to get the scheduling details for the worklows.

select 'Rep_Name' as repository

     , wkfl.subject_area

     , wkfl.workflow_name

     , wkfl.scheduler_name

     , DECODE( wkfl.run_options

             , 20, 'Run on Initialize'

             , 2 , NULL

             , 3 , 'Run on Demand'

             , 4 , NULL

             , 5 , 'Run on Demand'

             , 8 , NULL

             , 9 , 'Run on Demand'

             , wkfl.run_options ) as RUN_OPTIONS

     , DECODE( wkfl.run_options

             , 20, 'Custom'

             , 2 , 'Run Once'

             , 3 , 'Run Once'

             , 4 , 'Run Every'

             , 5 , 'Run Every'

             , 8 , 'Custom'

             , 9 , 'Custom'

             , wkfl.run_options ) as SCHEDULE_OPTIONS

     , DECODE( wkfl.end_options

             , 0 , 'End ON'

             , 1 , 'End AFTER'

             , 2 , 'Forever'

             , wkfl.end_options ) as END_OPTIONS

     , wkfl.run_options

     , wkfl.end_options

     , wkfl.start_time

     , wkfl.end_time

     , wkfl.delta_value

     , wkfl.run_count

  from rep_workflows wkfl

where wkfl.end_options = 2 -- FOREVER

order by 1,2,3

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query 3 : Query to get the workflows, sessions and associated mappings in a repository.

Please Fire this query on the Repository database to get the workflows, sessions and associated mappings in a repository.

select b.Folder_Name, x.workflow_name, c.Session_Name, o.MAPPING_NAME, d.Connection_name, d.user_name, d.connect_string   from

(select object_id, object_name Connection_name, user_name, connect_string from opb_cnx) d,

(select task_id, task_name Session_Name from opb_task) c,

(select subj_id, subj_name Folder_Name from opb_subject) b,

(select subject_id, mapping_name from opb_mapping) o,

(select b.session_id, a.subject_id, a.workflow_name, c.connection_id connection_id from

    (select distinct session_id, connection_id  from opb_sess_cnx_vals where connection_id != 0) c,

    (select  workflow_id, task_id session_id from opb_task_inst where task_type = 68 ) b, -- Workflow id , session id

    (select  subject_id,

         task_id Workflow_id,

         task_name Workflow_name

  from opb_task where task_type = 71 and

       subject_id in (select subj_id

                      from opb_subject

                    )

) a

where a.workflow_id = b.workflow_id

and c.session_id = b.session_id

) x

where x.subject_id = b.subj_id

and c.task_id = x.session_id

and o.SUBJECT_ID = x.subject_id

and d.object_id = x.connection_id

order by folder_name, workflow_name, session_name, o.MAPPING_NAME,connection_name

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query 4 : Query to get the list of relational connections based on the user name and the database instance .

select  distinct os.subj_name folder_name,oc.object_name,ot.TASK_NAME session_name

--ot.task_id, oc.object_id,ov.SESSION_ID,ov.WORKFLOW_ID ,ot.task_id,ot.subject_id

from opb_subject os,opb_task ot, opb_sess_cnx_vals ov ,opb_cnx oc

where ov.connection_id=oc.object_id AND ot.subject_id=os.subj_id AND ot.task_id=ov.SESSION_ID

AND oc.user_name in ('USER_NAME','user_name') AND connect_string in ('DB_NAME','db_name')

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query 5 : Query to get the sessions configured to enable “Configure Concurrent Execution” option.

Below query is to get the list of workflows configured as “Configure Concurrent Execution” enabled.

select distinct s.subj_name, a.task_name from opb_subject s, opb_task a ,opb_task_attr b where  a.task_id=b.task_id AND s.subj_id=a.subject_id AND b.attr_ID in (18,17) AND b.attr_value in (1) AND a.task_type=71;

If you want get the  workflow list which are not configured as “Configure Concurrent Execution” below query should work.

select s1.subj_name, a1.task_name from opb_subject s1,opb_task a1 where s1.subj_id=a1.subject_id AND a1.task_type=71 AND a1.task_name NOT IN ( select distinct a.task_name from opb_subject s, opb_task a ,opb_task_attr b where  a.task_id=b.task_id AND s.subj_id=a.subject_id AND b.attr_ID in (18,17) AND b.attr_value in (1) AND a.task_type=71);

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query 6 : Query to get the session and workflow statistics.

SELECT DISTINCT

subject_area,wfname, inst_name, run_hour,start_time,end_time,targ_success_rows,targ_failed_rows,first_error_msg

FROM (  SELECT a.subject_area,MAX (a.workflow_name) AS wfname,

                a.workflow_run_id,

                a.instance_id,

                b.targ_success_rows,

                b.targ_failed_rows,

                b.first_error_msg,

                a.start_time,

                a.end_time,

                MAX (a.instance_name) AS INST_Name,

                TRUNC (AVG (a.END_TIME-a.START_TIME) * (10 / 24 * 60), 2)

                   AS RUN_HOUR

           FROM REP_TASK_INST_RUN a,opb_sess_task_log b

          WHERE (a.END_TIME - a.START_TIME) IS NOT NULL

          AND a.TASK_TYPE=68

          and a.workflow_run_id = b.workflow_run_id

         -- AND TRUNC(A.START_TIME) >= TRUNC(SYSDATE-15)

          and start_time between  to_date('05-01-15 12:00:00 AM','mm-dd-yy hh:mi:ss AM')

and  to_date('05-15-15 12:00:00 AM','mm-dd-yy hh:mi:ss AM')

          --and a.subject_area like '%FOLDER_NAME%’

       GROUP BY a.subject_area,a.workflow_run_id,a.instance_id,targ_success_rows,targ_failed_rows,first_error_msg,a.end_time,a.start_time

       ORDER BY run_hour DESC);

--WHERE ROWNUM < 11

 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query 7 :  Query to get the list of users and groups.

Please fire this query on the Domain database to get the list of users and groups which the user belongs.

SELECT T1.GROUP_NAME,T2.USER_LOGIN,T4.POU_FULLNAME USER_NAME

from (select POS_NAME GROUP_NAME,PSI_CONTAINER from po_idobj where PSI_DISCRIMINATOR ='GROUP' and POS_NAME!='Everyone') T1,

(select POS_NAME USER_LOGIN,PSI_CONTAINER from po_idobj where PSI_DISCRIMINATOR ='USERREF' and PSI_CONTAINER!=36) T2,

(select POS_NAME USER_LOGIN,PSI_CONTAINER from po_idobj where PSI_DISCRIMINATOR ='USER') T3,

PO_USERINFO T4

where T1.PSI_CONTAINER(+)=T2.PSI_CONTAINER

and   T2.USER_LOGIN(+)=T3.USER_LOGIN

and   T3.PSI_CONTAINER=T4.PSU_RSID

order by 1,2

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 Query 7 : Query to get the sessions,workflows,mappings,along with source and targets used.

SELECT b.Folder_Name,

  x.workflow_name,

  c.Session_Name,

  o.MAPPING_NAME,

  rwi.SOURCE_TARGET_NAME,

  d.Connection_name,

  d.user_name,

  d.connect_string

FROM

  (SELECT object_id,

    object_name Connection_name,

    user_name,

    connect_string

  FROM opb_cnx

  ) d,

  (SELECT task_id, task_name Session_Name FROM opb_task

  ) c,

  (SELECT subj_id, subj_name Folder_Name FROM opb_subject

  ) b,

  (SELECT subject_id, mapping_name,mapping_id FROM opb_mapping

  ) o,

  (SELECT DECODE(WIDGET_TYPE, '1', 'SOURCE', '2', 'TARGET') SOURCE_TARGET,

    INSTANCE_NAME SOURCE_TARGET_NAME,

    MAPPING_ID

  FROM REP_WIDGET_INST

  WHERE WIDGET_TYPE IN (1,2)

  ) rwi,

  (SELECT b.session_id,

    a.subject_id,

    a.workflow_name,

    c.connection_id connection_id

  FROM

    (SELECT DISTINCT session_id,

      connection_id

    FROM opb_sess_cnx_vals

    WHERE connection_id != 0

    ) c,

    (SELECT workflow_id,

      task_id session_id

    FROM opb_task_inst

    WHERE task_type = 68

    ) b, -- Workflow id , session id

    (SELECT subject_id,

      task_id Workflow_id,

      task_name Workflow_name

    FROM opb_task

    WHERE task_type = 71

    AND subject_id IN

      (SELECT subj_id FROM opb_subject

      )

    ) a

  WHERE a.workflow_id = b.workflow_id

  AND c.session_id    = b.session_id

  ) x

WHERE x.subject_id = b.subj_id

AND c.task_id      = x.session_id

AND o.SUBJECT_ID   = x.subject_id

AND d.object_id    = x.connection_id

AND o.mapping_id   = rwi.MAPPING_ID

ORDER BY folder_name,

  workflow_name,

  session_name,

  o.MAPPING_NAME,

  connection_name ;

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
 

Query 8 :  Query to get the list of sessions that run longer than 5 Hours.

 SELECT REPOSIT_NAME AS REPOSITORY_NAME FROM OPB_REPOSIT WHERE RECID=1;

SELECT

--'INF_ESPROD_REP'AS REPOSITORY_NAME,

B.SUBJ_NAME AS SUBJECT_AREA,

A.TASK_NAME AS SESSION_NAME,

TO_CHAR(A.START_TIME,'DD/MM/YYYY HH24:MI:SS') AS START_TIME,

       --TO_CHAR(A.START_TIME,'DD.MM.YYYY:HH24:MI:SS') "ACTUAL_START",

       --TO_CHAR(SYSDATE,'DD.MM.YYYY:HH24:MI:SS') "SYSDATE",

       TRUNC((((86400*(SYSDATE-A.START_TIME))/60)/60)/24) AS  DAYS,

           TRUNC(((86400*(SYSDATE-A.START_TIME))/60)/60)-24*(TRUNC((((86400*(SYSDATE-A.START_TIME))/60)/60)/24)) AS HOURS,

       TRUNC((86400*(SYSDATE-A.START_TIME))/60)-60*(TRUNC(((86400*(SYSDATE-A.START_TIME))/60)/60)) AS MINS

FROM

OPB_TASK_INST_RUN A,OPB_SUBJECT B

WHERE

(A.END_TIME IS NULL or (a.end_time < a.start_time)) and

A.TASK_TYPE IN (68,58,65) AND

A.SUBJECT_ID=B.SUBJ_ID AND

((SYSDATE-A.START_TIME)*24) > 5 ;

  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------