Informatica PowerCenter Repository Queries

Here are some repository queries that may come handy:


How to find all the mappings and sessions which have PARALLEL hints in the SQL Override

SELECT S.SUBJ_NAME, M.MAPPING_NAME, W.WIDGET_NAME, A.WIDGET_ID, W.VERSION_NUMBER,

SUBSTR(A.ATTR_VALUE, 1, 60) ATTR_VALUE

FROM OPB_WIDGET_ATTR A, OPB_WIDGET W, OPB_SUBJECT S, OPB_WIDGET_INST I, OPB_MAPPING M

WHERE A.WIDGET_ID = W.WIDGET_ID

AND W.IS_VISIBLE = 1

AND A.VERSION_NUMBER = W.VERSION_NUMBER

AND A.WIDGET_TYPE IN(2, 3, 11) --Limit to Src/Tgt/Lkp Transformations

AND W.WIDGET_ID = I.WIDGET_ID

AND W.VERSION_NUMBER = I.VERSION_NUMBER

AND I.MAPPING_ID = M.MAPPING_ID

AND I.VERSION_NUMBER = M.VERSION_NUMBER

AND W.SUBJECT_ID = S.SUBJ_ID

AND UPPER(A.ATTR_VALUE) LIKE '%PARALLEL%'

UNION

SELECT S.SUBJ_NAME, W.TASK_NAME||'.'||TI.INSTANCE_NAME TASK_NAME, 'SQL Override' WIDGET_NAME,

A.SESS_WIDG_INST_ID, TI.VERSION_NUMBER, SUBSTR(A.ATTR_VALUE, 1, 60) ATTR_VALUE

FROM OPB_SWIDGET_ATTR A, OPB_TASK_INST TI, OPB_TASK W, OPB_SUBJECT S

WHERE A.SESSION_ID = TI.TASK_ID

AND A.VERSION_NUMBER = TI.VERSION_NUMBER

AND TI.WORKFLOW_ID = W.TASK_ID

AND TI.VERSION_NUMBER = W.VERSION_NUMBER

AND W.IS_VISIBLE = 1

AND W.SUBJECT_ID = S.SUBJ_ID

AND UPPER(A.ATTR_VALUE) LIKE '%PARALLEL%'

ORDER BY 1, 2, 3, 4;

How to turn on Write Backward Compatible Session Log File for all session tasks in a folder

UPDATE OPB_TASK_ATTR A SET A.ATTR_VALUE=1

WHERE A.ATTR_ID=17 -- WBCSLF

-- 102 Write performance data to repository

-- 108 Collect performance data

-- 105 Enable high precision

AND EXISTS (

SELECT 0 FROM OPB_TASK T, OPB_SUBJECT S

WHERE T.TASK_ID = A.TASK_ID

AND T.VERSION_NUMBER = A.VERSION_NUMBER

AND T.IS_VISIBLE = 1

AND T.SUBJECT_ID = S.SUBJ_ID

AND S.SUBJ_NAME LIKE '%Sample%'

)

-- AND A.INSTANCE_ID = 0 -- Reusable Session Only ;

What are today's long-running tasks

select -- the SRC_ROWS may look big if joiner is used

T.SUBJECT_AREA, T.INSTANCE_NAME, TRUNC(AVG(END_TIME-START_TIME)*24, 2) RUN_HOUR,

MIN(T.START_TIME) START_TIME,

SUM(L.SRC_SUCCESS_ROWS) SRC_ROWS, SUM(L.TARG_SUCCESS_ROWS) TGT_ROWS

from REP_TASK_INST_RUN T, OPB_SESS_TASK_LOG L

where T.run_err_code=0 and (T.END_TIME-T.START_TIME)>= 1/24

and T.START_TIME >= TRUNC(SYSDATE)-2/24

and T.INSTANCE_ID = L.INSTANCE_ID

GROUP BY T.SUBJECT_AREA, T.INSTANCE_NAME

Order By RUN_HOUR desc;

Inside the Expression Transformation

select S.SUBJ_NAME, W.WIDGET_NAME, F.WIDGET_ID, F.FIELD_NAME, F.FIELD_ID, E.EXPR_ID, E.VERSION_NUMBER, E.EXPRESSION from OPB_WIDGET W, OPB_SUBJECT S, OPB_WIDGET_FIELD F, OPB_WIDGET_EXPR R, OPB_EXPRESSION E

where W.SUBJECT_ID=S.SUBJ_ID and W.WIDGET_ID=F.WIDGET_ID

and W.WIDGET_ID=R.WIDGET_ID AND F.FIELD_ID=R.OUTPUT_FIELD_ID

and W.WIDGET_ID=E.WIDGET_ID AND R.EXPR_ID=E.EXPR_ID

and W.VERSION_NUMBER = F.VERSION_NUMBER

and F.VERSION_NUMBER = R.VERSION_NUMBER

and R.VERSION_NUMBER = E.VERSION_NUMBER

and W.IS_VISIBLE = 1

and w.WIDGET_NAME like 'EXP_SAMPLE%'

order by S.SUBJ_ID, W.WIDGET_ID, F.FIELD_ID;

Which session populates the specific target table

select SUBJECT_AREA, SESSION_NAME,TARGET_NAME,

MAX(SUCC_ROWS) AS ROWS#, TYPE_ID, COUNT(1) SAMPLE#

from (

select SUBJECT_AREA, SESSION_INSTANCE_NAME SESSION_NAME,

TYPE_ID, SUCCESSFUL_AFFECTED_ROWS SUCC_ROWS,

TABLE_NAME TARGET_NAME

from PM_REPO.REP_SESS_TBL_LOG

WHERE TYPE_ID in (2) -- Target Only

and END_TIME >= TRUNC(SYSDATE-40)

and SUCCESSFUL_AFFECTED_ROWS > 0

and TABLE_NAME like :TGT_NAME||'%' ESCAPE '\'

)

Group By SUBJECT_AREA, SESSION_NAME, TARGET_NAME, TYPE_ID

Order By SUBJECT_AREA, TARGET_NAME, SESSION_NAME ;

How to avoid re-generating & re-transporting ABAP program after slightly changing the mapping

CR 29233 and 63134 have been opened to have fix this problem.

-- Find the current LAST_SAVED and UTC_LAST_SAVED

select m.Mapping_ID, m.VERSION_NUMBER, m.Last_Saved, m.UTC_Last_Saved,

v.User_ID, v.LAST_SAVED, v.UTC_LAST_SAVED, v.COMMENTS,

p.PROGRAM_NAME, p.INSTALL_TIME, p.VERSION_NUMBER ABAP_VERSION_NUM

from opb_mapping m, OPB_VERSION_PROPS v, opb_program_info p

where m.MAPPING_ID = v.OBJECT_ID

and v.OBJECT_TYPE = 21

and m.SUBJECT_ID = v.SUBJECT_ID

and m.VERSION_NUMBER = v.VERSION_NUMBER

and m.MAPPING_ID = p.MAPPING_ID(+)

and m.SUBJECT_ID = p.SUBJECT_ID(+)

nd m.VERSION_NUMBER = p.VERSION_NUMBER(+)

and m.MAPPING_NAME = :MAP_NAME o

rder by m.version_number;

Then modify the LAST_SAVED, UTC_LAST_SAVED in OPB_VERSION_PROPS and OPB_MAPPING first; then modify the VERSION_NUMBER in OPB_PROGRAM_INFO if necessary.

--The timsstamp below is just a sample

update OPB_VERSION_PROPS v set last_saved='12/05/2008 09:10:11', UTC_LAST_SAVED=1228363499

where v.OBJECT_ID = :MAP_ID and

version_number = :VER_NUM and

object_type = 21;

update opb_mapping m set last_saved='12/05/2008 09:10:11', UTC_LAST_SAVED=1228363499

where MAPPING_ID = :MAP_ID and version_number = :VER_NUM;

update opb_program_info set VERSION_NUMBER = :VER_NUM

where mapping_id = :MAP_ID and subject_id = :SUB_ID;

Link from EXP_FROM.PORT_NAME to ???

select S.SUBJ_NAME, WF.INSTANCE_NAME ||'.'|| F.FIELD_NAME

FROM_NAME, F.WIDGET_ID, F.FIELD_ORDER, F.FIELD_ID,

WT.INSTANCE_NAME ||'.'|| T.FIELD_NAME TO_NAME, T.WIDGET_ID, T.FIELD_ORDER, T.FIELD_ID

from OPB_WIDGET Z, OPB_WIDGET_INST WF, OPB_WIDGET_INST WT,

OPB_WIDGET_FIELD F, OPB_WIDGET_FIELD T, OPB_WIDGET_DEP D, OPB_SUBJECT S

where Z.SUBJECT_ID = S.SUBJ_ID

and Z.IS_VISIBLE = 1

and Z.WIDGET_ID = F.WIDGET_ID

and Z.WIDGET_ID = WF.WIDGET_ID

and Z.RU_VERSION_NUMBER = WF.VERSION_NUMBER

and WF.REF_VERSION_NUMBER = F.VERSION_NUMBER

and WF.VERSION_NUMBER = D.VERSION_NUMBER

and WF.MAPPING_ID = D.MAPPING_ID

and WF.INSTANCE_ID = D.FROM_INSTANCE_ID

and F.FIELD_ID = D.FROM_FIELD_ID

and D.TO_INSTANCE_ID = WT.INSTANCE_ID

and D.TO_FIELD_ID = T.FIELD_ID

and D.MAPPING_ID = WT.MAPPING_ID

and D.VERSION_NUMBER = WT.VERSION_NUMBER

and WT.WIDGET_ID = T.WIDGET_ID

and WT.REF_VERSION_NUMBER = T.VERSION_NUMBER

and Z.WIDGET_NAME like 'EXP_FROM%'

and F.FIELD_NAME like 'PORT_NAME%'

order by F.FIELD_ID;

How the connection values is set at session level

select x.WORKFLOW_ID , t.TASK_ID, t.TASK_NAME, b.INSTANCE_ID, b.INSTANCE_NAME,

b.SESS_WIDG_INST_ID, x.CONNECTION_NUM, x.CONNECTION_TYPE, x.CONNECTION_ID,

x.CONNECTION_VALUE, c.OBJECT_NAME

from opb_session s, opb_task t, OPB_SWIDGET_INST b, OPB_SESS_CNX_VALS x, opb_cnx c

where c.OBJECT_ID(+) = x.CONNECTION_ID and s.MAPPING_ID = b.MAPPING_ID and

s.SESSION_ID = b.SESSION_ID and s.SESSION_ID = t.TASK_ID and

s.SESSION_ID = x.SESSION_ID and b.SESS_WIDG_INST_ID = x.SESS_WIDG_INST_ID and

t.SUBJECT_ID = :SUBJ_ID and b.INSTANCE_NAME like :WIDGET_NAME -- Source/Target

and t.TASK_NAME like :SESS_NAME

order by t.task_name, b.SESS_WIDG_INST_ID;

Find Transformation Attribute Override at Session Level

select f.SUBJ_NAME Folder, t.task_name SESSION_NAME, i.INSTANCE_NAME,

o.OBJECT_TYPE_NAME, a.*

from opb_swidget_attr a, OPB_SWIDGET_INST i,

OPB_WIDGET w, OPB_OBJECT_TYPE o,

opb_task t, opb_subject f

where a.attr_value in (:VALUE1, :VALUE2, :VALUE3)

and i.SESSION_ID = a.SESSION_ID

and i.SESS_WIDG_INST_ID = a.SESS_WIDG_INST_ID

and i.WIDGET_ID = w.WIDGET_ID

and i.WIDGET_TYPE = w.WIDGET_TYPE

and i.WIDGET_TYPE = o.OBJECT_TYPE_ID

and i.SESSION_ID= t.task_id

and t.SUBJECT_ID= f.subj_id;

List all the Transformations in a given Mapping

Including the transformations within the mapplet

WITH M AS (

select M.SUBJECT_ID, M.MAPPING_ID

from OPB_MAPPING M, OPB_SUBJECT S

where S.SUBJ_ID = M.SUBJECT_ID

and S.SUBJ_NAME LIKE :Folder_Name

and M.MAPPING_NAME like :Mapping_Name

)

select O.OBJECT_TYPE_NAME, W.INSTANCE_NAME, W.COMMENTS

from OPB_WIDGET_INST W, OPB_OBJECT_TYPE O, M

where O.OBJECT_TYPE_ID = W.WIDGET_TYPE

and O.OBJECT_TYPE_NAME NOT IN ('Mapplet')

and W.MAPPING_ID = M.MAPPING_ID

union

select O.OBJECT_TYPE_NAME, W.INSTANCE_NAME, W.COMMENTS

from OPB_WIDGET_INST W, OPB_OBJECT_TYPE O, M

where O.OBJECT_TYPE_ID = W.WIDGET_TYPE

and O.OBJECT_TYPE_NAME NOT IN ('Mapplet', 'Output Transformation', 'Input Transformation')

and W.MAPPING_ID in (

select X.MAPPING_ID

from OPB_WIDGET_INST W, OPB_OBJECT_TYPE O, M, OPB_MAPPING X

where W.MAPPING_ID = M.MAPPING_ID

and O.OBJECT_TYPE_ID = W.WIDGET_TYPE

and O.OBJECT_TYPE_NAME = 'Mapplet'

and X.REF_WIDGET_ID = W.WIDGET_ID

)

order by 1,2 ;