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 ;
  
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Popular Posts

Featured Post

Basic unix commands used in Administration

ls –ltr                Shows all the files and sub directories in the  current directory. ls –la                  Shows all the hidden...