✍ Kashif M
Trends

Sunday, 6 September 2015


Query related to session in Informatica


By on September 06, 2015


8.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


  List save session log count

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


  List stop on errors count


 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


List hard coded 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


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


Informatica
Informatica


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


  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


 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


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 



  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


 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
  

   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
  

  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


   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


   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

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

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


   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



 List load type


 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


 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

 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 '\')

 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

Kashif
mkashu: Query related to session in Informatica
Review : Kashif | Kashif
Update: September 06, 2015 | Rating: 4.5

Comment for "Query related to session in Informatica"

0 comments

Post a Comment

Blog Archive