Friday, January 20, 2017

Informatica Metadata Queries - 9 SESSION



9.1         List session names


SELECT   SUBJECT_AREA, TASK_TYPE_NAME, TASK_NAME
    FROM REP_ALL_TASKS
   WHERE TASK_TYPE IN (68) 
         --AND SUBJECT_AREA= 'ABC'
ORDER BY 1, 2, 3

9.2         List “save session log” runs


select distinct cc.subject_area,cc.task_name as session_name,bb.attr_value as Savesessionlog
from
(select  a.session_id,min(a.config_id) as config_id,a.attr_id from  rep_sess_config_parm a
where a.attr_id = '103' group by a.session_id,a.attr_id) aa,
(select session_id,config_id,attr_value from rep_sess_config_parm
where attr_id = '103') bb,
(select subject_area,task_name,task_id from rep_all_tasks ) cc
where aa.session_id = bb.session_id
and aa.config_id=bb.config_id
and bb.session_id = cc.task_id
and bb.attr_value not in (8,4)
order by 1,2,3
    

9.3         List “stop on errors” count value


SELECT DISTINCT
A.SUBJECT_AREA,
A.TASK_NAME AS SESSION_NAME,
B.ATTR_VALUE AS STOPONERRORS
FROM
REP_ALL_TASKS A ,
REP_SESS_CONFIG_PARM  B
WHERE
A.TASK_ID = B.SESSION_ID
AND TASK_TYPE_NAME = 'Session' AND B.ATTR_ID = '202'
--AND B.ATTR_VALUE NOT IN (1) --AND A.SUBJECT_AREA in ('ABC')
ORDER BY 1,2

9.4         List hardcoded paths


SELECT DISTINCT
A.SUBJECT_AREA,
A.TASK_NAME AS SESSION_NAME,
B.FILE_NAME,
DIR_NAME
FROM
REP_ALL_TASKS A , 
OPB_SESS_FILE_VALS B
WHERE  
         A.TASK_TYPE_NAME = 'Session'
         AND A.TASK_ID = B.SESSION_ID
         --AND A.SUBJECT_AREA IN ('ABC')
ORDER BY 1,2

9.5         List parameter file paths


SELECT DISTINCT
                B.SUBJECT_AREA,
                B.TASK_NAME AS SES_WF_NAME,
                A.ATTR_VALUE AS PRM_FILE_PATH
FROM
                 OPB_TASK_ATTR A,
                 REP_ALL_TASKS B
WHERE
                  A.ATTR_ID IN (1,4)
                  AND A.TASK_ID = B.TASK_ID
                  AND A.ATTR_VALUE LIKE '%.prm%'
ORDER BY 1,2 ASC

9.6         List session log names 


SELECT DISTINCT
                A.SUBJECT_AREA,
                A.WORKFLOW_NAME,
                A.SESSION_NAME,
                A.SESSION_INSTANCE_NAME,
                SUBSTR(A.SESSION_LOG_FILE,25,300) AS EXISTING_SESSLOGNAME
FROM
                 REP_SESS_LOG A
WHERE
                  SUBSTR(A.SESSION_LOG_FILE,25,300) != CONCAT(LOWER(A.SESSION_INSTANCE_NAME),'.log')
                ORDER BY 1,2,3

9.7         List commit intervals 


SELECT
                B.SUBJECT_AREA,
                B.TASK_NAME AS SESS_NAME,
                A.ATTR_VALUE AS COMMITINTERVEL
FROM
                OPB_TASK_ATTR A ,
                REP_ALL_TASKS B
WHERE
                A.ATTR_ID IN (14)
                AND A.ATTR_VALUE <> 10000
                AND A.TASK_ID = B.TASK_ID
                AND TASK_TYPE_NAME IN ('Session')
ORDER BY 1,2 ASC


9.8         List total source partitions


SELECT
                B.SUBJECT_AREA,
                B.TASK_NAME AS SESS_NAME,
                A.ATTR_VALUE AS TOTAL_SOURCE_PARTITIONS
FROM
                OPB_TASK_ATTR A ,
                REP_ALL_TASKS B
WHERE
                A.ATTR_ID IN (12)
                AND A.TASK_ID = B.TASK_ID
                AND TASK_TYPE_NAME IN ('Session')
ORDER BY 1,2 ASC

9.9         List total target partitions


SELECT
                B.SUBJECT_AREA,
                B.TASK_NAME AS SESS_NAME,
                A.ATTR_VALUE AS TOTAL_TARGET_PARTITIONS
FROM
                OPB_TASK_ATTR A ,
                REP_ALL_TASKS B
WHERE
                A.ATTR_ID IN (11)
                AND A.TASK_ID = B.TASK_ID
                AND TASK_TYPE_NAME IN ('Session')
ORDER BY 1,2 ASC

             

9.10      List DTM Buffer Size


SELECT
                B.SUBJECT_AREA,
                B.TASK_NAME AS SESS_NAME,
                A.ATTR_VALUE AS DTM_BUFFER_SIZE
FROM
                OPB_TASK_ATTR A ,
                REP_ALL_TASKS B
WHERE
                A.ATTR_ID IN (101)
                AND A.TASK_ID = B.TASK_ID
                AND TASK_TYPE_NAME IN ('Session')
ORDER BY 1,2 ASC

9.11      List collect performance data


SELECT
                B.SUBJECT_AREA,
                B.TASK_NAME AS SESS_NAME,
                A.ATTR_VALUE AS COLLECT_PERFORMANCE_DATA
FROM
                OPB_TASK_ATTR A ,
                REP_ALL_TASKS B
WHERE
                A.ATTR_ID IN (102)
                AND A.TASK_ID = B.TASK_ID
                AND TASK_TYPE_NAME IN ('Session')
ORDER BY 1,2 ASC

9.12      List Incremental Aggregation


SELECT
                B.SUBJECT_AREA,
                B.TASK_NAME AS SESS_NAME,
                A.ATTR_VALUE AS INCREMENTAL_AGGREGATION
FROM
                OPB_TASK_ATTR A ,
                REP_ALL_TASKS B
WHERE
                A.ATTR_ID IN (103)
                AND A.TASK_ID = B.TASK_ID
                AND TASK_TYPE_NAME IN ('Session')
ORDER BY 1,2 ASC

9.13      List Reinitialize Aggregate Cache


SELECT
                B.SUBJECT_AREA,
                B.TASK_NAME AS SESS_NAME,
                A.ATTR_VALUE AS REINITIALIZE_AGGREGATE_CACHE
FROM
                OPB_TASK_ATTR A ,
                REP_ALL_TASKS B
WHERE
                A.ATTR_ID IN (104)
                AND A.TASK_ID = B.TASK_ID
                AND TASK_TYPE_NAME IN ('Session')
ORDER BY 1,2 ASC

9.14      List Enable high precision

SELECT
                B.SUBJECT_AREA,
                B.TASK_NAME AS SESS_NAME,
                A.ATTR_VALUE AS ENABLE_HIGH_PRECISION
FROM
                OPB_TASK_ATTR A ,
                REP_ALL_TASKS B
WHERE
                A.ATTR_ID IN (105)
                AND A.TASK_ID = B.TASK_ID
                AND TASK_TYPE_NAME IN ('Session')
ORDER BY 1,2 ASC

9.15      List Session retry on deadlock


SELECT
                B.SUBJECT_AREA,
                B.TASK_NAME AS SESS_NAME,
                A.ATTR_VALUE AS SESSION_RETRYON_DEADLOCK
FROM
                OPB_TASK_ATTR A ,
                REP_ALL_TASKS B
WHERE
                A.ATTR_ID IN (106)
                AND A.TASK_ID = B.TASK_ID
                AND TASK_TYPE_NAME IN ('Session')
ORDER BY 1,2 ASC

9.16      List write backward compatible check


SELECT DISTINCT
                A.SUBJECT_AREA,A.TASK_NAME,
                DECODE (B.ATTR_VALUE,0,'TO BE CHECKED',1,'CHECKED') WRITEBACKWARDCOMPATIBLE
FROM
                REP_ALL_TASKS A ,
                OPB_TASK_ATTR B
WHERE
                A.TASK_ID = B.TASK_ID
                AND B.TASK_TYPE IN 68
                AND B.ATTR_ID = 17
                --AND B.ATTR_VALUE <> 1
ORDER BY 1,2,3

9.17      List over ride tracing


SELECT REP_REPOSIT_INFO.REPOSITORY_NAME,
       REP_ALL_TASKS.SUBJECT_AREA AS FOLDER_NAME,
       REP_ALL_TASKS.TASK_NAME AS SESSION_NAME,
       CASE
          WHEN REP_SESS_CONFIG_PARM.ATTR_VALUE = 0 THEN 'NONE'
          WHEN REP_SESS_CONFIG_PARM.ATTR_VALUE = 1 THEN 'TERSE'
          WHEN REP_SESS_CONFIG_PARM.ATTR_VALUE = 2 THEN 'NORMAL'
       WHEN REP_SESS_CONFIG_PARM.ATTR_VALUE = 3 THEN 'VERBOSE INITIALIZATION'
          WHEN REP_SESS_CONFIG_PARM.ATTR_VALUE = 4 THEN 'VERBOSE DATA'
       END AS OVERRIDE_TRACING
  FROM
      REP_REPOSIT_INFO,
    REP_ALL_TASKS,
    REP_SESS_CONFIG_PARM
 WHERE
     REP_ALL_TASKS.TASK_ID = REP_SESS_CONFIG_PARM.SESSION_ID
     AND REP_SESS_CONFIG_PARM.ATTR_ID = 204
     --AND REP_SESS_CONFIG_PARM.ATTR_VALUE NOT IN (0,2)
ORDER BY 1,2,3

9.18      List save session log by


             SELECT
                     A.SUBJECT_AREA,
                     A.TASK_NAME AS SESSION_NAME,
                     B.ATTR_NAME,
    DECODE(B.ATTR_VALUE,1,'TIME STAMP','BY RUNS') AS SAVE_SESSION_LOG_BY
           FROM
REP_ALL_TASKS A ,
REP_SESS_CONFIG_PARM  B
         WHERE
 A.TASK_ID = B.SESSION_ID
 AND TASK_TYPE_NAME = 'Session'
 AND B.ATTR_ID IN ('102')
 --AND B.ATTR_VALUE <> 0
           ORDER BY 1,2

9.19      List load type Normal or bulk


SELECT DISTINCT
      REP_LOAD_SESSIONS.SUBJECT_AREA AS FOLDER, REP_LOAD_SESSIONS.SESSION_NAME,
     --REP_SESS_WIDGET_CNXS.CNX_NAME AS CONNECTION_NAME,
     CASE WHEN OPB_EXTN_ATTR.ATTR_VALUE ='0' THEN 'NORMAL'
               WHEN OPB_EXTN_ATTR.ATTR_VALUE ='1' THEN 'BULK'
     END AS TARGET_LOAD_TYPE
FROM
     REP_LOAD_SESSIONS,
     REP_SESS_WIDGET_CNXS,
     OPB_EXTN_ATTR
WHERE REP_LOAD_SESSIONS.SESSION_ID=REP_SESS_WIDGET_CNXS.SESSION_ID
     AND REP_LOAD_SESSIONS.SESSION_ID=OPB_EXTN_ATTR.SESSION_ID
     AND OPB_EXTN_ATTR.ATTR_ID=3
     AND OPB_EXTN_ATTR.ATTR_VALUE BETWEEN '0' AND '1'
     AND REP_SESS_WIDGET_CNXS.READER_WRITER_TYPE='Relational Writer'
     --AND OPB_EXTN_ATTR.ATTR_VALUE ='1'
ORDER BY 1,2

9.20      List 'post_session_success_command' in session


SELECT DISTINCT C.SUBJ_NAME AS FOLDER ,A.TASK_NAME AS TASK, B.PM_VALUE AS COMMAND
FROM OPB_TASK A,OPB_TASK_VAL_LIST B, OPB_SUBJECT C
WHERE A.TASK_TYPE=58 AND A.TASK_NAME='post_session_success_command'
AND B.TASK_ID=A.TASK_ID AND B.SUBJECT_ID=C.SUBJ_ID
ORDER BY 1

9.21      List of all the emails with attachment 


SELECT DISTINCT
D.SUBJ_NAME AS FOLDER_NAME, C.WORKFLOW_NAME AS WORKFLOW_NAME,A.TASK_NAME AS TASK_NAME, B.ATTR_VALUE AS VALUE
FROM
OPB_TASK A, OPB_TASK_ATTR B, REP_TASK_INST_RUN C, OPB_SUBJECT D
WHERE A.TASK_ID = B.TASK_ID
AND A.TASK_TYPE = B.TASK_TYPE
AND C.SUBJECT_ID = A.SUBJECT_ID
AND A.SUBJECT_ID = D.SUBJ_ID
AND A.TASK_TYPE = 65
AND B.ATTR_ID IN (2,3)
AND (B.ATTR_VALUE LIKE '%\%a%' ESCAPE '\' OR B.ATTR_VALUE LIKE '%\%g%' ESCAPE '\')

9.22      Invalid Sessions and Workflows


select opb_subject.subj_name, opb_task.task_name
from  opb_task,   opb_subject
where task_type in (68,71)
and is_valid = 0
and opb_subject.subj_id = opb_task.subject_id
order by 1,2

SELECT SUBJECT_AREA AS FOLDER_NAME,
DECODE(IS_REUSABLE,1,'Reusable',' ') || ' ' ||TASK_TYPE_NAME AS TASK_TYPE,
TASK_NAME AS OBJECT_NAME,
DECODE(IS_VALID,0,'INVALID OBJECT','VALID OBJECT') STATUS,
LAST_SAVED
FROM REP_ALL_TASKS
WHERE IS_VALID=0
AND IS_ENABLED=1
--AND CHECKOUT_USER_ID = 0 -- Comment out for V6
--AND is_visible=1 -- Comment out for V6
ORDER BY 1,2

9.23      List of session run history – Long running sessions


SELECT   subject_area, workflow_name, instance_name,
         TO_CHAR (start_time, 'Dy MM/DD/YYYY HH:MI:SS AM') start_time,
         TO_CHAR (end_time, 'Dy MM/DD/YYYY HH:MI:SS AM') end_time,
         ROUND (((end_time - start_time) * 24) * 60) elapsed
    FROM rep_task_inst_run
   WHERE task_type = 68
     AND start_time > TO_DATE ('01/01/2012', 'mm/dd/yyyy')
--     AND subject_area = 'ABC' AND workflow_name='wfc_dly'
ORDER BY 1, 2

9.24      List Bad file dir name of a session


SELECT subject_area,session_name,session_instance_name,bad_file_location FROM REP_SESS_TBL_LOG
--where bad_file_location not like '$PMBadFileDir\%'
  order by 1,2,3

9.25      List Throughput of the session


SELECT subject_area,session_name,session_instance_name,start_time,end_time,throughput
FROM REP_SESS_TBL_LOG
where throughput > 600
     order by 1,2,3

9.26      List the current memory settings (buffer block size, etc.) 



SELECT a.SUBJECT_AREA AS Folder_Name, a.task_name AS Session_Name, b.ATTR_VALUE AS DTM_BUFFER_SIZE, c.ATTR_VALUE AS Buffer_Block_Size,
d.ATTR_VALUE AS Line_Sequential_Buffer_Length
FROM REP_ALL_TASKS a, REP_TASK_ATTR b, REP_SESS_CONFIG_PARM c, REP_SESS_CONFIG_PARM d
WHERE a.TASK_ID = b.TASK_ID
AND a.TASK_ID = c.SESSION_ID
AND a.TASK_ID = d.SESSION_ID
AND b.ATTR_ID = 101
AND c.ATTR_ID = 5
AND d.ATTR_ID = 6
and a.SUBJECT_AREA like '%EBI_NACO_%'
ORDER BY 1, 2


9.27      Pushdown Optimization


SELECT T.*,SUBJECT_AREA B,WORKFLOW_NAME B FROM OPB_TASK T, OPB_TASK_ATTR A,REP_WORKFLOWS B  WHERE T.TASK_ID=A.TASK_ID AND
A.ATTR_ID=107 AND ATTR_VALUE=1 AND T.SUBJECT_ID=B.SUBJECT_ID;

SELECT T.*,SUBJECT_AREA B,WORKFLOW_NAME B FROM OPB_TASK T, OPB_TASK_ATTR A,REP_WORKFLOWS B  WHERE T.TASK_ID=A.TASK_ID AND
A.ATTR_ID=107 AND ATTR_VALUE=2 AND T.SUBJECT_ID=B.SUBJECT_ID;

SELECT T.*,SUBJECT_AREA B,WORKFLOW_NAME B FROM OPB_TASK T, OPB_TASK_ATTR A,REP_WORKFLOWS B  WHERE T.TASK_ID=A.TASK_ID AND
A.ATTR_ID=107 AND ATTR_VALUE=3 AND T.SUBJECT_ID=B.SUBJECT_ID;


9.28      Persistent Cache


SELECT DISTINCT c.PARENT_SUBJECT_AREA, c.widget_name, d.attr_name
  FROM OPB_WIDGET_ATTR b, rep_all_transforms C, OPB_ATTR d
 WHERE     d.attr_id = b.attr_id
       AND d.attr_type = 5
       AND b.attr_id = 12
       AND b.widget_type = 11
       AND c.widget_id = b.widget_id
       AND b.attr_value = 1                  -- 1 is enabled and 0 id disabled