Informatica sql 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 '%RUN%'

; 

-- FIND A COLUMN_NAME 

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

AND COLUMN_NAME LIKE '%SERVER%' 

AND OWNER = 'INFAPROD'

AND TABLE_NAME NOT LIKE '$'

AND COLUMN_NAME = 'SERVER_NAME'

ORDER BY OWNER , TABLE_NAME

; 

-- AND THAN CHECK THE DATA OF THE TABLE

SELECT DISTINCT (SERVER_NAME) FROM OPB_WFLOW_RUN ; 

-- 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 = 93 

-- 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 = 93 

  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

; 

-- LIST REPOSITORIES

SELECT * FROM OPB_SERVER_INFO 

; 

-- PowerCenter folders table

SELECT * FROM OPB_SUBJECT 

; 

-- Mappings table

SELECT * FROM OPB_MAPPING 

;  

-- Tasks table like sessions, workflow etc

SELECT * FROM OPB_TASK 

; 

-- 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 * OPB_CFG_ATTR 

;

-- 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 = 93 

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 = 93 

  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'

; 

-- GET THE CONNECTIONS STRINGS BY NAME

select * from OPB_CNX

WHERE 1=1 

AND 

( OBJECT_NAME LIKE '%FOLDER_NAME%'

OR 

USER_NAME LIKE '%FOLDER_NAME%'

)

; 

SELECT * FROM OPB_CNX_ATTR

WHERE ATTR_ID IS NOT NULL 

; 

-- the session 

SELECT * FROM OPB_CFG_ATTR

WHERE ATTR_VALUE LIKE '%$%'

; 

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

span class=