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 ;

SQL Server : When you restore a database backup to another server, you may experience a problem with orphaned users.

SYMPTOMS When you restore a database backup to another server, you may experience a problem with orphaned users. The Troubleshooting Orphaned Users' topic in SQL Server Books Online does not outline the exact steps for troubleshooting this problem.
Troubleshooting Orphaned Users When you restore a database backup to another server, you may experience a problem with orphaned users. The following scenario illustrates the problem and shows how to resolve it. A. Showing a report of the current user to login mappings
The following example produces a report of the users in the current database and their security identifiers (SIDs).
Copy
EXEC sp_change_users_login 'Report';
B. Mapping a database user to a new SQL Server login
In the following example, a database user is associated with a new SQL Server login. Database user MB-Sales, which at first is mapped to another login, is remapped
USE AdventureWorks2012;
GO
EXEC sp_change_users_login 'Update_One', 'MB-Sales', 'MaryB';
GO
Use Northwind
go
sp_change_users_login 'update_one', (restore users id)'test',(current database user id) 'test'
sp_change_users_login :
@Action
@UserNamePattern
@LoginName
@Password

Your session has expired due to inactivity. You will be redirected to the login page where you can enter your username and password" on Internet Explorer while logging into Administration Console

Cause
This issue occurs due to underscores in the hostname.
Solution
To resolve this issue, do any of the following:
•Use the physical IP address.
•Use a different browser, like Mozilla Firefox, Chrome, or Safari.
•Make DNS entry changes on the server side.

Row error occured while fetching data from database.

I am using 8.6 version and In our sql override there are couple of columns which are more than 4000 i.e string 4000 , we are taking a substruing and sending them as 1000 in sql override but still the data is getting rejected at source level. when i was going through the informatica potral i came across this infor will this help in resolving this issue... RR_4053 : Row error occurred while fetching data from database. This error occurs when no column value is greater than 4000 characters in this case. Cause This occurs when Informatica Integration Service is fetching an array.

Cause: This occurs when Informatica Integration Service is fetching an array.
Solution : Steps
1. In Administration Console , click on Integration Service
2. Expand Custom Properties
3. Add a property called OptimizeODBCRead with value No
4. Bounce Integration Service
5. With this flag the Integration Service uses the single row fetch instead of doing array fetch.

Informatica PowerCenter Migration Checklist 7/28/2003

__ 1. Have all naming standards been used?

__ 2. Have the mappings/workflows been documented? Including:
- descriptions and text within the PowerCenter mappings and workflows;
- restart instructions; and
- special handling instructions.

__ 3. Have the mappings/workflows been sufficiently tested? Have they run in Test with no errors at least twice, preferably longer?

__ 4. Have DDL scripts moved to production? Are there additional objects/DDL to move to production for source or lookup tables?

__ 5. Optional, but critical: Have the business users reviewed the test data and approved the results?

__ 6. Has a folder comparison been done between the source and target repositories? Have all folder differences been explained as being necessary?

__ 7. Have reasonable error checks been coded for in the error handling routines?

__ 8. Has the default session configuration been used/altered for this folder? If there have been changes, list the changes.

__ 9. Has the fail parent option been checked?

__ 10. Have all test settings such as “collect performance data” been disabled?

__ 11. Are there sequence generators that need the current values adjusted? What should the current value be?

__ 12. Have any reusable objects been used? Have they been documented?

__ 13. Have post-session emails been set up and are the proper email addresses used?
(Indicate whether a failure is critical or not.)

__ 14. Have relational connections been reviewed and are they correct?

__ 15. Have mapping and session parameters and variables been reviewed, values set?

__ 16. Has a code review been completed? Check for compliance with standards? Check performance efficiency?

__ 17. Have the session failure variables been reviewed?