Informatica PowerCenter Primer: Default and Null Values

Following is some basic Info about the behavior of informatica PowerCenter regarding default and NULL values.

Starting with default values, there is a major distinction on how Informatica PowerCenter treats expression ports versus mapping variables/parameters.  Expression ports with no value are being assigned Null values during initialization, where mapping variables are being given a default value depending on their datatype.
More specifically, Ports per datatype

And default values assigned to variables per datatype

It might seem basic but you can avoid many well hidden bugs that more than often occur based on that trivial information.

Another caveat regarding Null values and PowerCenter is the difference in behavior between"DECODE" and "IIF" expressions when evaluating NULL operands.

"IIF" expressions return FALSE,  i.e: 
IIF(NULL=NULL, 1, 2)  
returns 2. 

On the other hand "DECODE" evaluates Null operands as equal,  i.e:
DECODE(NULL, NULL, 1, 2)
returns 1. 
That seems different to a common RDBMS's decode expression, since SQL returns false on nulls decode i.e:
SELECT CASE WHEN NULL = NULL THEN 1 ELSE 2 END 
Returns 2

The above behavior can be customized by fiddling with the TreatNullInComparisonOperatorsAs at the corresponding integration service properties. 

The aforementioned option change the behavior of sorters and aggregators as well. It is best advised to take care of this behavior on the mapping itself and not depend on server specific settings that can be different between environments. 

INFA: How to convert binary fields to series of 1s and 0s

There are some cases that require to convert a binary field to a sequence of bits. 
Lets say that we have a source system that stores a series of flags in just one field by using a binary, data type. This is a common way of storing flags in ERP systems since you can later add more fields without altering the DB schema, by just utilizing unused bits. During ETL we want to convert this binary datatype to a string so we can check the value that a position has.

e.g convert to 01000000 and then get the value at position 2, at this example 1 denotes that the customer is active.

An easy way to achieve this, is by using the ENC_BASE64 encoding function (introduced at Infa 8.x).
Base64 is an encoding scheme that represents binary data in ASCII characters. It is used mainly for binary data transfer through emails. Lets see some basics about base64 Encoding.

Base64 Primer

1. "=" at base 64 encoding stands for filler char that has zero value.
2. Encoding and decoding of base64 algorithm is based in sets of six bits thus the filler charachters when appropriate.
3. Every ASCII Char at the BASE64 sequence has a coresponding value that stands for (see Char Sequence table below.)

Building the reusable transformation

The whole process consists of two distinct steps:

1. Encode the incoming Bynary data to BASE64 format
2. Decode the emerged data to a string of bits

The above steps are going to be encapsulated at a reusable transformation. The aforementrioned expression transformation accepts as input a binary field (binary(4) at this example) and returns a varchar(32) that contains the bit series. As a plus i added an optional input port. That if feeded with 'Y' reverses the bit sequence.

Now lets start with the theory behind the implementation. You can download the transformation at the end of the post.

All the above steps are being encapsulated at a reusable transformation that you can download and try it Here.

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 ;

HOW TO: Change the domain database password

HOW TO: Change the domain database password

Solution
To change the domain database password, do the following:
1. Back up nodemeta.xml:
    cd <informatica--installation-folder>/server/config/
    cp nodemeta.xml nodemeta_backup.xml

2. Run the command infasetup UpdateGatewayNode to change the password. Run this command on each gateway node.

Example:
cd <informatica--installation-folder>/server
infasetup.sh updateGatewayNode -DatabaseAddress in <DB IP>:<DB PORT> -DatabaseUserName <DBUid> -DatabasePassword newpassword -DatabaseType oracle -DatabaseServiceName orcl -DomainName <Domain Name> -NodeName <node Name> -NodeAddress in<Node Ip>:<Infa Port> -LogServiceDirectory C:\Informatica\PowerCenter8.1.1\server\tomcat\logs

3. Start the Informatica services.

Note:
Set the parameters enclosed within angular brackets <> according to you environment.