Dig Into Informatica PowerCenter Repository

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(+)
and m.VERSION_NUMBER = p.VERSION_NUMBER(+)
and m.MAPPING_NAME = :MAP_NAME o
order 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 ;