Why should we know Active and Passive Transformations in informatica?

Why should we know Active and Passive Transformations in informatica?

Suppose the data split-ed and concatenated.

1. if all the branches are Passive transformations, for sure job execute normally.
2. if one of the branch contains at least one Active Transformation the job will fail. In this case we should use joiner with business logic to run the job.

What is Code Page Compatibility?

What is Code Page Compatibility?

When two code pages are compatible, the characters encoded in the two code pages are virtually identical.

Compatibility between code pages is used for accurate data movement when the Informatica Sever runs in the Unicode data movement mode. If the code pages are identical, then there will not be any data loss. One code page can be a subset or superset of another. For accurate data movement, the target code page must be a superset of the source code page.

What is code page in informatica

The code page in informatica is used to specify the character encoding. It is selected based on the source data. If the source data contains japanese characters, then the code page is selected to support japanese text. When a code page is chosen, the program or application for which the code page is set, refers to a specific set of data that describes the characters the application recognizes. This influences the way that application stores, receives, and sends character data.

Most commonly selected encoding systems are:
ASCII
UTF-8
UTF-32

Difference between static cache and dynamic cache

Difference between static cache and dynamic cache?

Property	Static Cache Lookup	Dynamic Cache Lookup
How it work	In Static Cache the Integration Service does not update the  cache while it processes the transformation. Cache is created, when lookup is called for the first time. After that it is never updated.	In Dynamic Cache ,the Integration Service dynamically inserts or updates data in the lookup cache and passes the data to the target
Scenarios	Static Cache is normally used while referring to reference Data or when Data source is not a target for the mapping.Example : Referring to Dept table to get Dept Name based on the DEPT no for Each employee.	Dynamic Cache is normally used when we refer to Target as the lookup source,  which actually change while running the mapping.Example : If there are 2 or more entries of the same customer on the same day in Source systems then while loading to the target we want the Lookup Cache to be refreshed dynamically(Insert/Update) and see the latest data in Cache
How to Enable	Lookup Can be marked as static, by unchecking the “Dynamic lookup  Cache” in Properties Tab of Lookup Transformation.Note: “Lookup caching Enabled” should be checked first to enable the cache.	Lookup Can be marked as dynamic , by checking the “Dynamic lookup  Cache” in Properties Tab of Lookup Transformation.Note: “Lookup caching Enabled” should be checked first to enable the cache.
Default Cache	By Default Cache lookup Transformation is static.	By Default Cache lookup Transformation is not dynamic .
Condition	In Statuc cache Lookup , we can use all type of operator like =. >, >=  etc while adding the condition in condition tab	In Dynamic Cache lookup , we can use only equal operator (=) while adding the condition in condition tab of lookup Transformation
Cache Type	Static Cache is read only	Dynamic Cache is Read/Write
Return Value	Informatica returns value when condition is true and if it is false it will return default value  in connected look up and Null value in unconnected look up	When condition is false , then we insert the new record in Target as well as in Lookup Cache.
ConnectedUnconnected	Can be configured as connected as well as unconnected lookup	Can be configured as connected only
Support	Supports lookup on flatfile	It does not support lookup on flat file

Why lookup is Active in informatica 9.x?

Why lookup is Active in informatica 9.x?

Active Transformation:
An active Transformation can change the number of rows that pass through it form source to target i.e it eliminates rows that do not meet the condition in transformation.

Passive Transformation:
A passive transformation does not change the number of rows that pass through it i.e it passes all rows through the transformation.

In Informatica 9.x Lookup can return multiple rows on Successful match.

Properties > Lookup Policy on Multiple match

deleted record from Target table

Same as previous scenario, here target records has to be deleted if corresponding record gets deleted in source

mapping:

Joiner Transformation:
1. Target table as Master
2. Join Type : Detail Outer Join
3. Condition : EMPNO_SRC = EMPNO_TGT

Update Strategy:
Update Strategy Expression:         IIF(ISNULL(EMPNO),DD_DELETE,DD_REJECT)

Delete Flag

Lets say a record is deleted in source which is already available in target,Requirement is to have a column DELETE_FLAG in target and stores 'Y' for the records
deleted in source else 'N'

Source table: SRC_EMP

Target Table: TGT_EMP

Add D_FLAG column to TGT_EMP

Import Source table to Informatica as Source.
Import Target table to Informatica as both Source and Target.

Use Joiner Transformations as below

Use Expression Transformation as below

Use Update Strategy as below

 Final mapping looks like below

Workflow Designer:
session >
Properties >Target Source Row as - Update
Mapping > SQ_SRC_EMP should be Source
Mapping > SQ_TGT_EMP should be Target
Mapping > TGT_EMP should be  Target

Result:

Please post your comments.