informatica cheat sheet

-- FILE: InformaticaCheatSheet.sql v.1.1.0 docs at the end 

-- DEFINE A NICE DATETIME FORMAT

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY.MM.DD HH24:MI:SS'

;

-- FIND FIRST THE NAME OF THE TABLE 

SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE '%OBJ%'

; 

-- FIND A COLUMN_NAME 

SELECT OWNER , TABLE_NAME , COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE 1=1 

AND COLUMN_NAME LIKE '%OBJECT%' 

AND OWNER = 'INFAPROD'

AND TABLE_NAME NOT LIKE '$'

AND COLUMN_NAME LIKE '%OBJECT%'

ORDER BY OWNER , TABLE_NAME

; 

-- AND THAN CHECK THE DATA OF THE TABLE

SELECT DISTINCT (SERVER_NAME) FROM OPB_WFLOW_RUN ; 

SELECT * FROM OPB_SESS_CNX_REFS

; 

-- GENERATE A COLUMN SEPARATED LIST FOR SELECTS FOR A TABLE 

SELECT  CASE WHEN ROWNUM = 1 THEN '   ' ELSE ' , ' END || COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'OPB_WFLOW_RUN'

;

-- GENERATE A TABLE_NAME.COLUMN_NAME SEPARATED LIST FOR SELECTS FOR A TABLE 

SELECT  CASE WHEN ROWNUM = 1 THEN '   ' ELSE ' , ' END || TABLE_NAME || '.' || COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'OPB_SUBJECT'

;

-- HOW-TO list Informatica folder

select * from OPB_SUBJECT 

WHERE 1=1 

-- and SUBJ_NAME LIKE '%MAN%'

AND SUBJ_NAME = 'FOLDER_NAME'

; 

-- HOW-TO FIND A FOLDER ID BY ITS NAME

select * from OPB_SUBJECT 

WHERE 1=1 

-- and SUBJ_NAME LIKE '%MAN%'

AND SUBJ_NAME = 'FOLDER_NAME'

; 

-- GET THE LIST OF ALL THE SESSIONS FOR A FOLDER

SELECT   * FROM OPB_TASK 

WHERE 1=1 

AND SUBJECT_ID = 999 

-- GET ONLY THE VALID OBJECTS

AND IS_VALID=1

 -- AND TASK_TYPE = 68 

AND (UTC_LAST_SAVED, TASK_NAME) 

IN 

( 

  SELECT MAX(UTC_LAST_SAVED) , TASK_NAME FROM OPB_TASK 

  WHERE 1=1 

  AND SUBJECT_ID = 999 

  GROUP BY TASK_NAME

)

-- sessions 

-- AND TASK_TYPE = 68 

-- workflows

AND TASK_TYPE = 71 

-- Command Task

--AND TASK_TYPE=58

ORDER BY TASK_NAME ASC

; 

select OWNER , TABLE_NAME , COLUMN_NAME FROM ALL_TAB_COLUMNS 

WHERE 1=1

AND TABLE_NAME LIKE '%CNXS%'

-- AND COLUMN_NAME LIKE '%OBJECT_TYPE%' 

AND OWNER = 'INFAPROD'

;  

select * from INFAPROD.REP_SESSION_CNXS 

WHERE 1=1 

AND CONNECTION_NAME like ('%SAP%')

-- AND CONNECTION_NAME LIKE '%SAP%'

; 

SELECT  * FROM INFAPROD.REP_SESS_WIDGET_CNXS

WHERE 1=1

order by instance_name

-- AND CNX_NAME LIKE 'SAPELI_TEST'

-- AND INSTANCE_NAME = 'FACT_SAP'

; 

desc INFAPROD.REP_SESSION_CNXS

; 

select * from REP_SESS_WIDGET_CNXS

; 

--and object_type=74; 

-- LIST THE OBJECT TYPES

SELECT * FROM OPB_MMD_EXTN_ATTR

;

-- LIST REPOSITORIES

SELECT * FROM OPB_SERVER_INFO 

; 

-- PowerCenter folders table

SELECT * FROM OPB_SUBJECT 

; 

-- Tasks table like sessions, workflow etc

SELECT * FROM OPB_TASK 

;

SELECT * FROM OPB_TASK_INST

; 

-- Mappings table

SELECT * FROM OPB_MAPPING 

;  

-- Session & Mapping linkage table

SELECT * FROM OPB_SESSION 

; 

-- Task attributes tables

SELECT * FROM OPB_TASK_ATTR 

;

-- connectoin strings

SELECT * FROM OPB_CNX 

; 

-- Transformations table

SELECT * FROM OPB_WIDGET 

;

-- Transformation ports table

SELECT * OPB_WIDGET_FIELD 

;

-- Transformation properties table

SELECT * OPB_WIDGET_ATTR 

;

-- Expressions table

SELECT * OPB_EXPRESSION

;

-- Session Configuration Attributes

SELECT * FROM OPB_CFG_ATTR 

WHERE 1=1 

-- AND ATTR_VALUE IN '%SAP%'

;

-- HOW-TO FIND A FOLDER ID BY ITS NAME

select * from OPB_SUBJECT 

WHERE 1=1 

-- and SUBJ_NAME LIKE '%MAN%'

AND SUBJ_NAME = 'FOLDER_NAME'

; 

-- GET THE LIST OF SESSION FOR A FOLDER

SELECT  *  FROM OPB_TASK 

WHERE 1=1 

AND SUBJECT_ID = 999 

AND IS_VALID=0

 AND TASK_TYPE = 68 

AND (VERSION_NUMBER, TASK_NAME) IN 

(SELECT MAX(VERSION_NUMBER), TASK_NAME FROM OPB_TASK 

  WHERE 1=1 

  AND SUBJECT_ID = 999 

  AND TASK_TYPE = 68 

GROUP BY TASK_NAME)

ORDER BY LAST_SAVED ASC

; 

select distinct m.mapping_name 

from opb_widget_inst w, opb_mapping m, opb_targ t

where m.mapping_id = w.mapping_id

and t.target_id = w.widget_id

-- and t.target_name = 'FOLDER_NAME'

; 

-- OBJECT_TYPE 73,74

-- GET THE CONNECTIONS STRINGS BY NAME

select * from OPB_CNX

WHERE 1=1 

AND 

( OBJECT_NAME LIKE '%SAP%'

OR 

USER_NAME LIKE '%sap%'

)

; 

SELECT * FROM OPB_CNX_ATTR

WHERE 1=1 

AND OBJECT_TYPE=74

-- ATTR_ID IS NOT NULL 

-- AND ATTR_VALUE LIKE '%SAP%'

; 

-- the session 

SELECT * FROM OPB_CFG_ATTR

WHERE ATTR_VALUE LIKE '%$%'

; 

-- LIST WORFKLOWS,SESSIONS,MAPPINGS,SOURCES,TARGETS

SELECT

  F.SUBJ_NAME AS FOLDER_NAME

, WF.TASK_NAME AS WORKFLOW_NAME 

, SE.INSTANCE_NAME AS SESSION_NAME

, M.MAPPING_NAME  AS MAPPING_NAME

, SRC.INSTANCE_NAME AS SOURCE_NAME

, TGT.INSTANCE_NAME AS TARGET_NAME

 FROM 

OPB_SUBJECT F,

OPB_TASK WF,

( 

  SELECT 

  WORKFLOW_ID,

INSTANCE_ID,

TASK_ID,

TASK_TYPE,

INSTANCE_NAME,

MAX(VERSION_NUMBER)

FROM OPB_TASK_INST SESS

WHERE 

SESS.TASK_TYPE=68 

GROUP BY

WORKFLOW_ID,INSTANCE_ID,TASK_ID,TASK_TYPE,INSTANCE_NAME) SE,

(SELECT SESSION_ID,MAPPING_ID,MAX(VERSION_NUMBER) FROM OPB_SESSION GROUP BY SESSION_ID,MAPPING_ID) S,

opb_mapping m,

(SELECT MAPPING_ID,INSTANCE_NAME ,WIDGET_TYPE,MAX(VERSION_NUMBER) from opb_widget_inst GROUP BY MAPPING_ID,INSTANCE_NAME,WIDGET_TYPE) SRC,

(SELECT MAPPING_ID,INSTANCE_NAME ,WIDGET_TYPE,MAX(VERSION_NUMBER) from opb_widget_inst GROUP BY MAPPING_ID,INSTANCE_NAME,WIDGET_TYPE) TGT

where WF.IS_VISIBLE = 1

AND WF.SUBJECT_ID = F.SUBJ_ID

AND SE.WORKFLOW_ID = WF.TASK_ID

AND WF.TASK_TYPE = 71

AND se.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

ORDER BY 

-- AND F.SUBJ_NAME = 'FOLDER_NAME'

   F.SUBJ_NAME 

 , WF.TASK_NAME 

 , SE.INSTANCE_NAME 

 , M.MAPPING_NAME 

 , SRC.INSTANCE_NAME 

 , TGT.INSTANCE_NAME 

; 

SELECT * FROM OPB_ATTR

WHERE 1=1

-- AND OBJECT_TYPE_ID IN (73)

; 

-- GET THE VALUES OF ALL SQL OVERRIDES 

select folder, wf_name, 

       sess_name, mapping_name, 

       transformation_name, attr_name, 

       line_no, sql_value

from (select f.subj_name folder, 

             wf.task_name wf_name, 

             sess.instance_name sess_name, 

             m.mapping_name mapping_name, 

             w_inst.instance_name transformation_name, 

             attr.line_no, attr.attr_value sql_value, 

             attr_type.attr_name attr_name,

             row_number() over (partition by wf.task_name, 

                                             sess.instance_name, 

                                             m.mapping_name, 

                                             w_inst.instance_name, 

                                             attr.line_no, 

                                             attr.attr_value

                                order by attr.session_task_id desc

                               ) rn

      from opb_task_inst wf_inst

           ,opb_task_inst sess

           ,opb_session s

           ,opb_mapping m

           ,opb_subject f

           ,opb_widget_attr attr

           ,opb_widget_inst w_inst

           ,opb_task wf

           ,(select o.object_type_id object_type_id, 

                    o_attr.attr_id attr_id, 

                    o.object_type_name||': '||o_attr.attr_name attr_name

             from opb_attr o_attr, 

                  opb_object_type o

             where o.object_type_id = o_attr.object_type_id

             and o_attr.attr_datatype = 2

             and o_attr.attr_value is null

             and upper(o_attr.attr_name) like '%SQL%'

            ) attr_type

      where wf_inst.task_id = sess.task_id

      and sess.task_type = 68

      and sess.task_id = s.session_id

      and wf.subject_id = f.subj_id

      and s.mapping_id = m.mapping_id

      and attr.widget_id = w_inst.widget_id

      and w_inst.mapping_id = m.mapping_id

      and w_inst.widget_type = attr_type.object_type_id

      and wf_inst.workflow_id = wf.task_id

      and wf.task_type = 71

      and (attr.session_task_id = s.session_id

           or attr.session_task_id = 0)

      and attr.attr_id = attr_type.attr_id

      and attr.attr_value is not null

      and attr.attr_value <> '0'

  --    and wf.task_name = 'WORKFLOW_NAME'

    --  and f.subj_name = 'FOLDER_NAME'

      )

where rn = 1

order by 1, 2, 3, 4, 5, 6, 7

; 

-- PURPOSE: 

-- Contain all the needed informatica repository sql in one place 

-- VersionHistory: 

-- 1.1.0 --- ysg --- Added repositories, improved formatting

-- 1.0.0 --- ysg --- Initial creation 

/pre