INFORMATICA ALL TRANSFORMATIONS

Informatica TransformationTypes

A transformation is a repository object that generates, modifies, or passes data. The Designer provides a set of transformations that performspecific functions. For example, an Aggregator transformation performs calculations on groups of data

Transformations can be of two types:

Active Transformation: An active transformation can change the number of rows that pass through the transformation, change the transaction boundary, can change the rowtype. For example, Filter, Transaction Control and Update Strategy are active transformations.

The key point is to note that Designer does not allowyou to connect multiple active transformations or an active and a passive transformation to the same downstreamtransformation or transformation input group because the Integration Service may not be able to concatenate the rows passed by active transformations. However, Sequence Generator transformation(SGT) is an exception to this rule. A SGT does not receive data. It generates unique numeric values. As a result, the Integration

Service does not encounter problems concatenating rows passed by a

SGT and an active transformation.

Passive Transformation: A passive transformation does not change the number of rows that pass through it, maintains the transaction boundary, and maintains the rowtype.

The key point is to note that Designer allows you to connect multiple transformations to the same downstreamtransformation or transformation input group only if all transformations in the upstreambranches are passive. The transformation that originates the branch can be active or passive.

Transformations can be Connected or UnConnected to the data flow.

Connected Transformation: Connected transformation is connected to other transformations or directly to target table in the mapping.

UnConnected Transformation: An unconnected transformation is not connected to other transformations in the mapping. It is called within another transformation, and returns a value to that transformation.

FILTER TRANSFORMATION IN INFORMATICA

Filter transformation is an active, connected transformation. The filter transformation is used to filter out rows in a mapping.it may change the number of rows passed through it. You have to specify a filter condition in the filter transformation. The rows that meet the specified filter condition are passed to other transformations. The rows that do not meet the filter condition are dropped.

Creating Filter Transformation

Follow the below steps to create a filter transformation

1. In the mapping designer, open a mapping or create a new mapping.

2. Go to the toolbar->click on Transformation->Create->Select the filter transformation

3. Enter a name->Click on create and then click on done.

4. You can add ports either by dragging from other transformations or manually creating the ports within the transformation.

Specifying Filter Condition

To configure the filter condition, go to the properties tab and in the filter condition section open the expression editor. Enter the filter condition you want to apply. Click on validate button to verify the syntax and then click OK.

Components of Filter Transformation

The filter transformation has the following components.

· Transformation: You can enter the name and description of the transformation.

· Ports: Create new ports and configure them

· Properties: You can specify the filter condition to filter the rows. You can also configure the tracing levels.

· Metadata Extensions: Specify the metadata details like name, datatype etc.

Configuring Filter Transformation

The following properties needs to be configured on the ports tab in filter transformation

· Port name: Enter the name of the ports created.

· Datatype, precision, and scale: Configure the data type and set the precision and scale for each port.

· Port type: All the ports in filter transformation are input/output.

Performance Tuning Tips

· Use the filter transformation as close as possible to the sources in the mapping. This will reduce the number of rows to be processed in the downstream transformations.

· In case of relational sources, if possible use the source qualifier transformation to filter the rows. This will reduce the number of rows to be read from the source.

Note: The input ports to the filter transformation mush come from a single transformation. You cannot connect ports from more than one transformation to the filter.

Filter Transformation examples

Specify the filter conditions for the following examples

1. Create a mapping to load the employees from department 50 into the target?

department_id=50

2. Create a mapping to load the employees whose salary is in the range of 10000 to 50000?

salary >=10000 AND salary <= 50000

3. Create a mapping to load the employees who earn commission (commission should not be null)?

IIF(ISNULL(commission),FALSE,TRUE)

SORTER TRANSFORMATION IN INFORMATICA

Sorter transformation is an active and connected transformation used to sort the data. The data can be sorted in ascending or descending order by specifying the sort key. You can specify one or more ports as a sort key and configure each sort key port to sort in ascending or descending order. You can also configure the order of the ports in which the integration service applies to sort the data.

The sorter transformation is used to sort the data from relational or flat file sources. The sorter transformation can also be used for case-sensitive sorting and can be used to specify whether the output rows should be distinct or not.

Creating Sorter Transformation

Follow the below steps to create a sorter transformation

1. In the mapping designer, create a new mapping or open an existing mapping

2. Go the toolbar->Click on Transformation->Create

3. Select the Sorter Transformation, enter the name, click on create and then click on Done.

4. Select the ports from the upstream transformation and drag them to the sorter transformation. You can also create input ports manually on the ports tab.

5. Now edit the transformation by double clicking on the title bar of the transformation.

6. Select the ports you want to use as the sort key. For each selected port, specify whether you want the integration service to sort data in ascending or descending order.

Configuring Sorter Transformation

Configure the below properties of sorter transformation

· Case Sensitive: The integration service considers the string case when sorting the data. The integration service sorts the uppercase characters higher than the lowercase characters.

· Work Directory: The integration service creates temporary files in the work directory when it is sorting the data. After the integration service sorts the data, it deletes the temporary files.

· Distinct Output Rows: The integration service produces distinct rows in the output when this option is configured.

· Tracing Level: Configure the amount of data needs to be logged in the session log file.

· Null Treated Low: Enable the property, to treat null values as lower when performing the sort operation. When disabled, the integration service treats the null values as higher than any other value.

· Sorter Cache Size: The integration service uses the sorter cache size property to determine the amount of memory it can allocate to perform sort operation

Performance improvement Tip

Use the sorter transformation before the aggregator and joiner transformation and sort the data for better performance.

Sorter Transformation Examples

1. Create a mapping to sort the data of employees on salary in descending order?

2. Create a mapping to load distinct departments into the target table?

SOURCE QUALIFIER TRANSFORMATION IN INFORMATICA

The source qualifier transformation is an active,connected transformation used to represent the rows that the integrations service reads when it runs a session. You need to connect the source qualifier transformation to the relational or flat file definition in a mapping. The source qualifier transformation converts the source data types to the Informatica native data types. So, you should not alter the data types of the ports in the source qualifier transformation.

The source qualifier transformation is used to do the following tasks:

· Joins: You can join two or more tables from the same source database. By default the sources are joined based on the primary key-foreign key relationships. This can be changed by explicitly specifying the join condition in the "user-defined join" property.

· Filter rows: You can filter the rows from the source database. The integration service adds a WHERE clause to the default query.

· Sorting input: You can sort the source data by specifying the number for sorted ports. The Integration Service adds an ORDER BY clause to the default SQL query

· Distinct rows: You can get distinct rows from the source by choosing the "Select Distinct" property. The Integration Service adds a SELECT DISTINCT statement to the default SQL query.

· Custom SQL Query: You can write your own SQL query to do calculations.

Creating Source Qualifier Transformation:

The easiest method to create a source qualifier transformation is to drag the source definition in to a mapping. This will create the source qualifier transformation automatically.

Follow the below steps to create the source qualifier transformation manually.

· Click Transformation -> Create.

· Select the Source Qualifier transformation.

· Enter a name for the transformation

· Click on create.

· Select a source, click OK and then click Done.

Now you can see in the below image how the source qualifier transformation is connected to the source definition.

Source Qualifier Transformation Properties:

We can configure the following source qualifier transformation properties on the properties tab. To go to the properties tab, open the source qualifier transformation by double clicking on it and then click on the properties tab.

Property	Description
SQL Query	To specify a custom query which replaces the default query.
User-Defined Join	Condition used for joining multiple sources.
Source Filter	Specifies the filter condition the Integration Service applies when querying rows.
Number of Sorted Ports	Used for sorting the source data
Tracing Level	Sets the amount of detail included in the session log when you run a session containing this transformation.
Select Distinct	To select only unique rows from the source.
Pre-SQL	Pre-session SQL commands to run against the source database before the Integration Service reads the source.
Post-SQL	Post-session SQL commands to run against the source database after the Integration Service writes to the target.
Output is Deterministic	Specify only when the source output does not change between session runs.
Output is Repeatable	Specify only when the order of the source output is same between the session runs.

Note: For flat file source definitions, all the properties except the Tracing level will be disabled.

To Understand the following, Please create the employees and departments tables in the source and emp_dept table in the target database.

create table DEPARTMENTS

  DEPARTMENT_ID   NUMBER(4) not null,

  DEPARTMENT_NAME VARCHAR2(15) not null,

  MANAGER_ID      NUMBER(6)

);

create table EMPLOYEES

  EMPLOYEE_ID   NUMBER(6) not null,

  NAME          VARCHAR2(10),

  SALARY        NUMBER(10,2),

  MANAGER_ID    NUMBER(6),

  DEPARTMENT_ID NUMBER(4)

);

create table EMP_DEPT

  EMPLOYEE_ID   NUMBER(6) not null,

  NAME          VARCHAR2(10),

  SALARY        NUMBER(10,2),

  MANAGER_ID    NUMBER(6),

  DEPARTMENT_ID NUMBER(4),

  DEPARTMENT_NAME VARCHAR2(15) not null

);

Viewing the Default Query or Generating the SQL query:

For relational sources, the Integration Service generates a query for each Source Qualifier transformation when it runs a session. To view the default query generated, just follow the below steps:

· Go to the Properties tab, select "SQL Query" property. Then open the SQL Editor, select the "ODBC data source" and enter the username, password.

· Click Generate SQL.

· Click Cancel to exit.

The default query generated in this case is

SELECT employees.employee_id,

 employees.name,

 employees.salary,

 employees.manager_id,

 employees.department_id

FROM  employees

You can write your own SQL query rather than relaying the default query for performing calculations.

Note: You can generate the SQL query only if the output ports of source qualifier transformation is connected to any other transformation in the mapping. The SQL query generated contains only the columns or ports which are connected to the downstream transformations.

Specifying the "Source Filter, Number Of Sorted Ports and Select Distinct" properties:

Follow the below steps for specifying the filter condition, sorting the source data and for selecting the distinct rows.

· Go to the properties tab.

· Select "Source Filter" property, open the editor and enter the filter condition (Example: employees.department_id=100) and click OK.

· Go to the "Number Of Sorted Ports" property and enter a value (Example: 2). This value (2) means to sort the data on the first two ports in the source qualifier transformation.

· Tick the check box for the "Select Distinct" property.

Now follow the steps for "Generating the SQL query" and generate the SQL query. The SQL query generated is

SELECT  DISTINCT employees.employee_id,

 employees.name,

 employees.salary,

 employees.manager_id,

 employees.department_id

FROM  employees

WHERE  employees.department_id=100

ORDER BY employees.employee_id, employees.name

Observe the DISTINCT, WHERE and ORDER BY clauses in the SQL query generated. The order by clause contains the first two ports in the source qualifier transformation. If you want to sort the data on department_id, salary ports; simply move these ports to top position in the source qualifier transformationa and specify the "Number Of Sorted Ports" property as 2

Joins:

The SQL transformation can be used to join sources from the same database. By default it joins the sources based on the primary-key, foreign-key relationships. To join heterogeneous sources, use Joiner Transformation.
A foreign-key is created on the department_id column of the employees table, which references the primary-key column, department_id, of the departments table.
Follow the below steps to see the default join

Create only one source qualifier transformation for both the employees and departments.
Go to the properties tab of the source qualifier transformation, select the "SQL QUERY" property and generate the SQL query.

The Generated SQL query is

SELECT employees.employee_id,

 employees.name,

 employees.salary,

 employees.manager_id,

 employees.department_id,

 departments.department_name

FROM  employees,

 departments

WHERE  departments.department_id=employees.department_id

You can see the employees and departments tables are joined on the department_id column in the WHERE clause.

There might be case where there won't be any relationship between the sources. In that case, we need to override the default join. To do this we have to specify the join condition in the "User Defined Join" Property. Using this property we can specify outer joins also. The join conditions entered here are database specific.
As an example, if we want to join the employees and departments table on the manager_id column, then in the "User Defined Join" property specify the join condition as "departments.manager_id=employees.manager_id". Now generate the SQL and observe the WHERE clause.

Pre and Post SQL:

You can add the Pre-SQL and Post-SQL commands. The integration service runs the Pre-SQL and Post-SQL before and after reading the source data respectively.

ROUTER TRANSFORMATION IN INFORMATICA

Router transformation is an active and connected transformation. It is similar to the filter transformation used to test a condition and filter the data. In a filter transformation, you can specify only one condition and drops the rows that do not satisfy the condition. Where as in a router transformation, you can specify more than one condition and provides the ability for route the data that meet the test condition. Use router transformation if you need to test the same input data on multiple conditions.

Creating Router Transformation

Follow the below steps to create a router transformation

1. In the mapping designer, create a new mapping or open an existing mapping

2. Go the toolbar->Click on Transformation->Create

3. Select the Router Transformation, enter the name, click on create and then click on Done.

4. Select the ports from the upstream transformation and drag them to the router transformation. You can also create input ports manually on the ports tab.

Configuring Router Transformation

The router transformation has input and output groups. You need to configure these groups.

· Input groups: The designer copies the input ports properties to create a set of output ports for each output group.

· Output groups: Router transformation has two output groups. They are user-defined groups and default group.

User-defined groups: Create a user-defined group to test a condition based on the incoming data. Each user-defined group consists of output ports and a group filter condition. You can create or modify the user-defined groups on the groups tab. Create one user-defined group for each condition you want to specify.

Default group: The designer creates only one default group when you create one new user-defined group. You cannot edit or delete the default group. The default group does not have a group filter condition. If all the conditions evaluate to FALSE, the integration service passes the row to the default group.

Specifying Group Filter Condition

Specify the group filter condition on the groups tab using the expression editor. You can enter any expression that returns a single value. The group filter condition returns TRUE or FALSE for each row that passes through the transformation.

Advantages of Using Router over Filter Transformation

Use router transformation to test multiple conditions on the same input data. If you use more than one filter transformation, the integration service needs to process the input for each filter transformation. In case of router transformation, the integration service processes the input data only once and thereby improving the performance.

Router Transformation Examples

1. Create the employees data into two target tables. The first target table should contain employees with department_id 10 and second target table should contain employees with department_id 20?

Solution: connect the source qualifier transformation to the router transformation.
In the router transformation, create two output groups. Enter the below filter conditions.

In the first group filter condition,

department_id=10

In the second group filter condition,

department_id=20

Now connect the output groups of router transformation to the targets

2. The router transformation has the following group filter conditions.

In the first group filter condition,

department_id=30

In the second group filter condition,

department_id<=30

What data will be loaded into the first and second target tables?

Solution: The first target table will have employees from department 30. The second table will have employees whose department ids are less than or equal to 30.

EXPRESSION TRANSFORMATION IN INFORMATICA

Expression transformation is a connected, passive transformation used to calculate values on a single row. Examples of calculations are concatenating the first and last name, adjusting the employee salaries, converting strings to date etc. Expression transformation can also be used to test conditional statements before passing the data to other transformations.

Creating an Expression Transformation

Just follow the below steps to create an expression transformation

1. In the mapping designer, create a new mapping or open an existing mapping.

2. Go to Toolbar->click Transformation -> Create. Select the expression transformation.

3. Enter a name, click on Create and then click on Done.

4. You can add ports to expression transformation either by selecting and dragging ports from other transformations or by opening the expression transformation and create ports manually.

Adding Expressions

Once you created an expression transformation, you can add the expressions either in a variable port or output port. Create a variable or output port in the expression transformation. Open the Expression Editor in the expression section of the variable or output port. Enter an expression and then click on Validate to verify the expression syntax. Now Click OK.

Expression Transformation Components or Tabs

The expression transformation has the following tabs

· Transformation: You can enter the name and description of the transformation. You can also make the expression transformation reusable.

· Ports: Create new ports and configuring the ports.

· Properties: Configure the tracing level to set the amount of transaction detail to be logged in session log file.

· Metadata Extensions: You can specify extension name, data type, precision, value and can also create reusable metadata extensions.

Configuring Ports:

You can configure the following components on the ports tab

· Port name: Enter a name for the port.

· Datatype: Select the data type

· Precision and scale: set the precision and scale for each port.

· Port type: A port can be input, input/output, output or variable.

· Expression: Enter the expressions in the expression editor.

Expression transformation examples

1. Create a mapping to increase the salary of an employee by 10 percent?

Solution:
In the expression transformation, create a new output port (call it as adj_sal) and enter the expression as salary+salary*(10/100)

The expression can be simplified as salary*(110/100)

2. Create a mapping to concatenate the first and last names of the employee? Include space between the names

Solution:
Just create a new port in the expression transformation and enter the expression as CONCAT(CONCAT(first_name,' '),last_name)

The above expression can be simplified as first_name||' '||last_name

SEQUENCE GENERATOR TRANSFORMATION IN INFOTMATICA

Sequence generator transformation is a passive and connected transformation. The sequence generator transformation is used for

· Generating unique primary key values.

· Replace missing primary keys

· Generate surrogate keys for dimension tables in SCDs.

· Cycle through a sequential range of numbers.

Creating Sequence Generator Transformation:

Follow the below steps to create a sequence generator transformation:

· Go to the mapping designer tab in power center designer.

· Click on the transformation in the toolbar and then on create.

· Select the sequence generator transformation. Enter the name and then click on Create. Click Done.

· Edit the sequence generator transformation, go to the properties tab and configure the options.

· To generate sequence numbers, connect the NEXTVAL port to the transformations or target in the mapping.

Configuring Sequence Generator Transformation:

Configure the following properties of sequence generator transformation:

· Start Value: Specify the start value of the generated sequence that you want the integration service to use the cycle option. If you select cycle, the integration service cycles back to this value when it reaches the end value.

· Increment By: Difference between two consecutive values from the NEXTVAL port. Default value is 1. Maximum value you can specify is 2,147,483,647.

· End Value: Maximum sequence value the integration service generates. If the integration service reaches this value during the session and the sequence is not configured to cycle, the session fails. Maximum value is 9,223,372,036,854,775,807.

· Current Value: Current Value of the sequence. This value is used as the first value in the sequence. If cycle option is configured, then this value must be greater than or equal to start value and less than end value.

· Cycle: The integration service cycles through the sequence range.

· Number of Cached Values: Number of sequential values the integration service caches at a time. Use this option when multiple sessions use the same reusable generator. Default value for non-reusable sequence generator is 0 and reusable sequence generator is 1000. Maximum value is ,223,372,036,854,775,807.

· Reset: The integration service generate values based on the original current value for each session. Otherwise, the integration service updates the current value to reflect the last-generated value for the session plus one.

· Tracing level: The level of detail to be logged in the session log file.

Sequence Generator Transformation Ports:

The sequence generator transformation contains only two output ports. They are CURRVAL and NEXTVAL output ports.

NEXTVAL Port:

You can connect the NEXTVAL port to multiple transformations to generate the unique values for each row in the transformation. The NEXTVAL port generates the sequence numbers base on the Current Value and Increment By properties. If the sequence generator is not configure to Cycle, then the NEXTVAL port generates the sequence numbers up to the configured End Value.

The sequence generator transformation generates a block of numbers at a time. Once the block of numbers is used then it generates the next block of sequence numbers. As an example, let say you connected the nextval port to two targets in a mapping, the integration service generates a block of numbers (eg:1 to 10) for the first target and then another block of numbers (eg:11 to 20) for the second target.

If you want the same sequence values to be generated for more than one target, then connect the sequence generator to an expression transformation and connect the expression transformation port to the targets. Another option is create sequence generator transformation for each target.

CURRVAL Port:

The CURRVAL is the NEXTVAL plus the Increment By value. You rarely connect the CURRVAL port to other transformations. When a row enters a transformation connected to the CURRVAL port, the integration service passes the NEXTVAL value plus the Increment By value. For example, when you configure the Current Value=1 and Increment By=1, then the integration service generates the following values for NEXTVAL and CURRVAL ports.

NEXTVAL CURRVAL

---------------

1       2

2       3

3       4

4       5

5       6

If you connect only the CURRVAL port without connecting the NEXTVAL port, then the integration service passes a constant value for each row.

RANK TRANSFORMATION IN INFORMATICA

Rank transformation is an active and connected transformation. The rank transformation is used to select the top or bottom rank of data. The rank transformation is used to select the smallest or largest numeric/string values. The integration service caches the input data and then performs the rank calculations.

Creating Rank Transformation

Follow the below steps to create an expression transformation

· In the mapping designer, create a new mapping or open an existing mapping.

· Go to Toolbar->click Transformation -> Create. Select the Rank transformation.

· Enter a name, click on Create and then click on Done.

· By default, the rank transformation creates a RANKINDEX port. The RankIndex port is used to store the ranking position of each row in the group.

· You can add additional ports to the rank transformation either by selecting and dragging ports from other transformations or by adding the ports manually in the ports tab.

· In the ports tab, check the Rank (R) option for the port which you want to do ranking. You can check the Rank (R) option for only one port. Optionally you can create the groups for ranked rows. select the Group By option for the ports that define the groups.

Configuring the Rank Transformation

Configure the following properties of Rank transformation

· Cache Directory: Directory where the integration service creates the index and data cache files.

· Top/Bottom: Specify whether you want to select the top or bottom rank of data.

· Number of Ranks: specify the number of rows you want to rank.

· Case-Sensitive String Comparison: Used to sort the strings using case sensitive or not.

· Tracing Level: Amount of logging to be tracked in the session log file.

· Rank Data Cache Size: The data cache size default value is 2,000,000 bytes. You can set a numeric value, or Auto for the data cache size. In case of Auto, the Integration Service determines the cache size at runtime.

· Rank Index Cache Size: The index cache size default value is 1,000,000 bytes. You can set a numeric value, or Auto for the index cache size. In case of Auto, the Integration Service determines the cache size at runtime.

Rank Transformation Examples:

Q) Create a mapping to load the target table with top 2 earners (employees) in each department using the rank transformation.

Solution:

· Create a new mapping, Drag the source definition into the mapping.

· Create a rank transformation and drag the ports of source qualifier transformation into the rank transformation.

· Now go to the ports tab of the rank transformation. Check the rank (R) option for the salary port and Group By option for the Dept_Id port.

· Go to the properties tab, select the Top/Bottom value as Top and the Number of Ranks property as 2.

· Now connect the ports of rank transformation to the target definition.

Rank Transformations

1. It is a active and connected transformation
2. Use a Rank transformation to return the largest or smallest numeric value in a port or group.
3. During the session, the Integration Service caches input data until it can perform the rank calculations.
4. You connect all ports representing the same row set to the transformation. Only the rows that fall within that rank, based on some measure you set when you configure the transformation, pass through the Rank transformation. You can also write expressions to transform data or perform calculations.
5. You can connect ports from only one transformation to the Rank transformation. You can also create local variables and write non-aggregate expressions.
6. When the Integration Service runs in the ASCII data movement mode, it sorts session data using a binary sort order.
7. During a session, the Integration Service compares an input row with rows in the data cache. If the input row out-ranks a cached row, the Integration Service replaces the cached row with the input row. If you configure the Rank transformation to rank across multiple groups, the Integration Service ranks incrementally for each group it finds.
8. The Integration Service stores group information in an index cache and row data in a data cache. If you create multiple partitions in a pipeline, the Integration Service creates separate caches for each partition.
9. If two rank values match, they receive the same value in the rank index and the transformation skips the next value.
10. The Designer automatically creates a RANKINDEX port for each Rank transformation. The Power Center Server uses the Rank Index port to store the ranking position for each row in a group.
11. We can specify to rank the data based on only one port. In the ports tab, you have to check the R option for designating the port as a rank port and this option can be checked only on one port.
12. When you create a Rank transformation, you can configure the following properties:
a. Enter a cache directory.
b. Select the top or bottom rank.
c. Select the number of rows falling within a rank.
d. Transformation Scope- Transaction/All Input (Transaction means one level of commit interval)
13. Rank TF have following ports:
a. Input port. Create an input port to receive data from another transformation.
b. Output port. Create an output port for each port you want to link to another transformation. You can designate input ports as output ports.
c. Variable port. Can use to store values or calculations to use in an expression. Variable ports cannot be input or output ports. They pass data within the transformation only.
d. Rank port. Use to designate the column for which you want to rank values. You can designate only one Rank port in a Rank transformation. The Rank port is an input/
e. Output port. You may or may not link the Rank port to another transformation.
14. It computes tank on a group or whole data set.
15. We cannot Group By on port which is also Rank Port.

JOINER TRANSFORMATION IN INFORMATICA

The joiner transformation is an active and connected transformation used to join two heterogeneous sources. The joiner transformation joins sources based on a condition that matches one or more pairs of columns between the two sources. The two input pipelines include a master and a detail pipeline or branch. To join more than two sources, you need to join the output of the joiner transformation with another source. To join n number of sources in a mapping, you need n-1 joiner transformations.

CREATING JOINER TRANSFORMATION

Follow the below steps to create a joiner transformation in informatica

· Go to the mapping designer, click on the Transformation->Create.

· Select the joiner transformation, enter a name and click on OK.

· Drag the ports from the first source into the joiner transformation. By default the designer creates the input/output ports for the source fields in the joiner transformation as detail fields.

· Now drag the ports from the second source into the joiner transformation. By default the designer configures the second source ports as master fields.

· Edit the joiner transformation, go the ports tab and check on any box in the M column to switch the master/detail relationship for the sources.

· Go to the condition tab, click on the Add button to add a condition. You can add multiple conditions.

· Go to the properties tab and configure the properties of the joiner transformation.

CONFIGURING JOINER TRANSFORMATION

Configure the following properties of joiner transformation:

· Case-Sensitive String Comparison: When performing joins on string columns, the integration service uses this option. By default the case sensitive string comparison option is checked.

· Cache Directory: Directory used to cache the master or detail rows. The default directory path is $PMCacheDir. You can override this value.

· Join Type: The type of join to be performed. Normal Join, Master Outer Join, Detail Outer Join or Full Outer Join.

· Tracing Level: Level of tracing to be tracked in the session log file.

· Joiner Data Cache Size: Size of the data cache. The default value is Auto.

· Joiner Index Cache Size: Size of the index cache. The default value is Auto.

· Sorted Input: If the input data is in sorted order, then check this option for better performance.

· Master Sort Order: Sort order of the master source data. Choose Ascending if the master source data is sorted in ascending order. You have to enable Sorted Input option if you choose Ascending. The default value for this option is Auto.

· Transformation Scope: You can choose the transformation scope as All Input or Row.

JOIN CONDITION

The integration service joins both the input sources based on the join condition. The join condition contains ports from both the input sources that must match. You can specify only the equal (=) operator between the join columns. Other operators are not allowed in the join condition. As an example, if you want to join the employees and departments table then you have to specify the join condition as department_id1= department_id. Here department_id1 is the port of departments source and department_id is the port of employees source.

JOIN TYPE

The joiner transformation supports the following four types of joins.

· Normal Join

· Master Outer Join

· Details Outer Join

· Full Outer Join

We will learn about each join type with an example. Let say i have the following students and subjects tables as the source.

Table Name: Subjects

Subject_Id subject_Name

-----------------------

1          Maths

2          Chemistry

3          Physics

Table Name: Students

Student_Id  Subject_Id

---------------------

10          1

20          2

30          NULL

Assume that subjects source is the master and students source is the detail and we will join these sources on the subject_id port.

Normal Join:

The joiner transformation outputs only the records that match the join condition and discards all the rows that do not match the join condition. The output of the normal join is

Master Ports       |   Detail Ports

---------------------------------------------

Subject_Id Subject_Name Student_Id Subject_Id

---------------------------------------------

1          Maths        10         1

2          Chemistry    20         2

Master Outer Join:

In a master outer join, the joiner transformation keeps all the records from the detail source and only the matching rows from the master source. It discards the unmatched rows from the master source. The output of master outer join is

Master Ports       |   Detail Ports

---------------------------------------------

Subject_Id Subject_Name Student_Id Subject_Id

---------------------------------------------

1          Maths        10         1

2          Chemistry    20         2

NULL       NULL         30         NULL

Detail Outer Join:

In a detail outer join, the joiner transformation keeps all the records from the master source and only the matching rows from the detail source. It discards the unmatched rows from the detail source. The output of detail outer join is

Master Ports       |   Detail Ports

---------------------------------------------

Subject_Id Subject_Name Student_Id Subject_Id

---------------------------------------------

1          Maths        10         1

2          Chemistry    20         2

3          Physics      NULL       NULL

Full Outer Join:

The full outer join first brings the matching rows from both the sources and then it also keeps the non-matched records from both the master and detail sources. The output of full outer join is

Master Ports       |   Detail Ports

---------------------------------------------

Subject_Id Subject_Name Student_Id Subject_Id

---------------------------------------------

1          Maths        10         1

2          Chemistry    20         2

3          Physics      NULL       NULL

NULL       NULL         30         NULL

SORTED INPUT

Use the sorted input option in the joiner properties tab when both the master and detail are sorted on the ports specified in the join condition. You can improve the performance by using the sorted input option as the integration service performs the join by minimizing the number of disk IOs. you can see good performance when worked with large data sets.

Steps to follow for configuring the sorted input option

· Sort the master and detail source either by using the source qualifier transformation or sorter transformation.

· Sort both the source on the ports to be used in join condition either in ascending or descending order.

· Specify the Sorted Input option in the joiner transformation properties tab.

WHY JOINER TRANSFORMATION IS CALLED AS BLOCKING TRANSFORMATION

The integration service blocks and unblocks the source data depending on whether the joiner transformation is configured for sorted input or not.

Unsorted Joiner Transformation

In case of unsorted joiner transformation, the integration service first reads all the master rows before it reads the detail rows. The integration service blocks the detail source while it caches the all the master rows. Once it reads all the master rows, then it unblocks the detail source and reads the details rows.

Sorted Joiner Transformation

Blocking logic may or may not possible in case of sorted joiner transformation. The integration service uses blocking logic if it can do so without blocking all sources in the target load order group. Otherwise, it does not use blocking logic.

JOINER TRANSFORMATION PERFORMANCE IMPROVE TIPS

To improve the performance of a joiner transformation follow the below tips

· If possible, perform joins in a database. Performing joins in a database is faster than performing joins in a session.

· You can improve the session performance by configuring the Sorted Input option in the joiner transformation properties tab.

· Specify the source with fewer rows and with fewer duplicate keys as the master and the other source as detail.

LIMITATIONS OF JOINER TRANSFORMATION

The limitations of joiner transformation are

· You cannot use joiner transformation when the input pipeline contains an update strategy transformation.

· You cannot connect a sequence generator transformation directly to the joiner transformation.

LOOKUP TRANSFORMATION IN INFORMATICA

Lookup transformation is used to look up data in a flat file, relational table, view or synonym. Lookup is a passive/active transformation and can be used in both connected/unconnected modes. From informatica version 9 onwards lookup is an active transformation. The lookup transformation can return a single row or multiple rows.

You can import the definition of lookup from any flat file or relational database or even from a source qualifier. The integration service queries the lookup source based on the ports, lookup condition and returns the result to other transformations or target in the mapping.

The lookup transformation is used to perform the following tasks:

· Get a Related Value: You can get a value from the lookup table based on the source value. As an example, we can get the related value like city name for the zip code value.

· Get Multiple Values: You can get multiple rows from a lookup table. As an example, get all the states in a country.

· Perform Calculation. We can use the value from the lookup table and use it in calculations.

· Update Slowly Changing Dimension tables: Lookup transformation can be used to determine whether a row exists in the target or not.

You can configure the lookup transformation in the following types of lookup:

· Flat File or Relational lookup: You can perform the lookup on the flat file or relational database. When you create a lookup using flat file as lookup source, the designer invokes flat file wizard. If you used relational table as lookup source, then you can connect to the lookup source using ODBC and import the table definition.

· Pipeline Lookup: You can perform lookup on application sources such as JMS, MSMQ or SAP. You have to drag the source into the mapping and associate the lookup transformation with the source qualifier. Improve the performance by configuring partitions to retrieve source data for the lookup cache.

· Connected or Unconnected lookup: A connected lookup receives source data, performs a lookup and returns data to the pipeline. An unconnected lookup is not connected to source or target or any other transformation. A transformation in the pipeline calls the lookup transformation with the :LKP expression. The unconnected lookup returns one column to the calling transformation.

· Cached or Uncached Lookup: You can improve the performance of the lookup by caching the lookup source. If you cache the lookup source, you can use a dynamic or static cache. By default, the lookup cache is static and the cache does not change during the session. If you use a dynamic cache, the integratiion service inserts or updates row in the cache. You can lookup values in the cache to determine if the values exist in the target, then you can mark the row for insert or update in the target.

AGGREGATOR TRANSFORMATION IN INFORMATICA

Aggregator transformation is an active transformation used to perform calculations such as sums, averages, counts on groups of data. The integration service stores the data group and row data in aggregate cache. The Aggregator Transformation provides more advantages than the SQL, you can use conditional clauses to filter rows.

Creating an Aggregator Transformation:

Follow the below steps to create an aggregator transformation

· Go to the Mapping Designer, click on transformation in the toolbar -> create.

· Select the Aggregator transformation, enter the name and click create. Then click Done. This will create an aggregator transformation without ports.

· To create ports, you can either drag the ports to the aggregator transformation or create in the ports tab of the aggregator.

Configuring the aggregator transformation:

You can configure the following components in aggregator transformation

· Aggregate Cache: The integration service stores the group values in the index cache and row data in the data cache.

· Aggregate Expression: You can enter expressions in the output port or variable port.

· Group by Port: This tells the integration service how to create groups. You can configure input, input/output or variable ports for the group.

· Sorted Input: This option can be used to improve the session performance. You can use this option only when the input to the aggregator transformation in sorted on group by ports.

Properties of Aggregator Transformation:

The below table illustrates the properties of aggregator transformation

Property	Description
Cache Directory	The Integration Service creates the index and data cache files.
Tracing Level	Amount of detail displayed in the session log for this transformation.
Sorted Input	Indicates input data is already sorted by groups. Select this option only if the input to the Aggregator transformation is sorted.
Aggregator Data Cache Size	Default cache size is 2,000,000 bytes. Data cache stores row data.
Aggregator Index Cache Size	Default cache size is 1,000,000 bytes. Index cache stores group by ports data
Transformation Scope	Specifies how the Integration Service applies the transformation logic to incoming data

Group By Ports:

The integration service performs aggregate calculations and produces one row for each group. If you do not specify any group by ports, the integration service returns one row for all input rows. By default, the integration service returns the last row received for each group along with the result of aggregation. By using the FIRST function, you can specify the integration service to return the first row of the group.

Aggregate Expressions:

You can create the aggregate expressions only in the Aggregator transformation. An aggregate expression can include conditional clauses and non-aggregate functions. You can use the following aggregate functions in the Aggregator transformation,

AVG

COUNT

FIRST

LAST

MAX

MEDIAN

MIN

PERCENTILE

STDDEV

SUM

VARIANCE

Examples: SUM(sales), AVG(salary)

Nested Aggregate Functions:

You can nest one aggregate function within another aggregate function. You can either use single-level aggregate functions or multiple nested functions in an aggregate transformation. You cannot use both single-level and nested aggregate functions in an aggregator transformation. The Mapping designer marks the mapping as invalid if an aggregator transformation contains both single-level and nested aggregate functions. If you want to create both single-level and nested aggregate functions, create separate aggregate transformations.

Examples: MAX(SUM(sales))

Conditional clauses:

You can reduce the number of rows processed in the aggregation by specifying a conditional clause.

Example: SUM(salary, slaray>1000)

This will include only the salaries which are greater than 1000 in the SUM calculation.

Non Conditional clauses:

You can also use non-aggregate functions in aggregator transformation.

Example: IIF( SUM(sales) <20000, SUM(sales),0)

Note: By default, the Integration Service treats null values as NULL in aggregate functions. You can change this by configuring the integration service.

Incremental Aggregation:

After you create a session that includes an Aggregator transformation, you can enable the session option, Incremental Aggregation. When the Integration Service performs incremental aggregation, it passes source data through the mapping and uses historical cache data to perform aggregation calculations incrementally.

Sorted Input:

You can improve the performance of aggregator transformation by specifying the sorted input. The Integration Service assumes all the data is sorted by group and it performs aggregate calculations as it reads rows for a group. If you specify the sorted input option without actually sorting the data, then integration service fails the session.

UPDATE STRATEGY TRANSFORMATION IN INFORMATICA

Update strategy transformation is an active and connected transformation. Update strategy transformation is used to insert, update, and delete records in the target table. It can also reject the records without reaching the target table. When you design a target table, you need to decide what data should be stored in the target.

When you want to maintain a history or source in the target table, then for every change in the source record you want to insert a new record in the target table.
When you want an exact copy of source data to be maintained in the target table, then if the source data changes you have to update the corresponding records in the target.

The design of the target table decides how to handle the changes to existing rows. In the informatica, you can set the update strategy at two different levels:

· Session Level: Configuring at session level instructs the integration service to either treat all rows in the same way (Insert or update or delete) or use instructions coded in the session mapping to flag for different database operations.

· Mapping Level: Use update strategy transformation to flag rows for inert, update, delete or reject.

Flagging Rows in Mapping with Update Strategy:

You have to flag each row for inserting, updating, deleting or rejecting. The constants and their numeric equivalents for each database operation are listed below.

· DD_INSERT: Numeric value is 0. Used for flagging the row as Insert.

· DD_UPDATE: Numeric value is 1. Used for flagging the row as Update.

· DD_DELETE: Numeric value is 2. Used for flagging the row as Delete.

· DD_REJECT: Numeric value is 3. Used for flagging the row as Reject.

The integration service treats any other numeric value as an insert.

Update Strategy Expression:

You have to flag rows by assigning the constant numeric values using the update strategy expression. The update strategy expression property is available in the properties tab of the update strategy transformation.

Each row is tested against the condition specified in the update strategy expression and a constant value is assigned to it. A sample expression is show below:

IIF(department_id=10, DD_UPDATE, DD_INSERT)

Mostly IIF and DECODE functions are used to test for a condition in update strategy transformation.

Update Strategy and Lookup Transformations:

Update strategy transformation is used mostly with lookup transformation. The row from the source qualifier is compared with row from lookup transformation to determine whether it is already exists or a new record. Based on this comparison, the row is flagged to insert or update using the update strategy transformation.

Update Strategy and Aggregator Transformations:

If you place an update strategy before an aggregator transformation, the way the aggregator transformation performs aggregate calculations depends on the flagging of the row. For example, if you flag a row for delete and then later use the row to calculate the sum, then the integration service subtracts the value appearing in this row. If it’s flagged for insert, then the aggregator adds its value to the sum.

Important Note:

Update strategy works only when we have a primary key on the target table. If there is no primary key available on the target table, then you have to specify a primary key in the target definition in the mapping for update strategy transformation to work.

SQL TRANSFORMATION IN INFORMATICA WITH EXAMPLES

SQL Transformation is a connected transformation used to process SQL queries in the midstream of a pipeline. We can insert, update, delete and retrieve rows from the database at run time using the SQL transformation.

The SQL transformation processes external SQL scripts or SQL queries created in the SQL editor. You can also pass the database connection information to the SQL transformation as an input data at run time.

The following SQL statements can be used in the SQL transformation.

· Data Definition Statements (CREATE, ALTER, DROP, TRUNCATE, RENAME)

· DATA MANIPULATION statements (INSERT, UPDATE, DELETE, MERGE)

· DATA Retrieval Statement (SELECT)

· DATA Control Language Statements (GRANT, REVOKE)

· Transaction Control Statements (COMMIT, ROLLBACK)

CONFIGURING SQL TRANSFORMATION

The following options can be used to configure an SQL transformation

· Mode: SQL transformation runs either in script mode or query mode.

· Active/Passive: By default, SQL transformation is an active transformation. You can configure it as passive transformation.

· Database Type: The type of database that the SQL transformation connects to.

· Connection type: You can pass database connection information or you can use a connection object.

We will see how to create an SQL transformation in script mode, query mode and passing the dynamic database connection with examples.

CREATING SQL TRANSFORMATION IN QUERY MODE

Query Mode: The SQL transformation executes a query that defined in the query editor. You can pass parameters to the query to define dynamic queries. The SQL transformation can output multiple rows when the query has a select statement. In query mode, the SQL transformation acts as an active transformation.

You can create the following types of SQL queries

Static SQL query: The SQL query statement does not change, however you can pass parameters to the sql query. The integration service runs the query once and runs the same query for all the input rows.
Dynamic SQL query: The SQL query statement and the data can change. The integration service prepares the query for each input row and then runs the query.

SQL Transformation Example Using Static SQL query

Q1) Let’s say we have the products and Sales table with the below data.

Table Name: Products

PRODUCT

-------

SAMSUNG

LG

IPhone

Table Name: Sales

PRODUCT QUANTITY PRICE

----------------------

SAMSUNG 2        100

LG      3        80

IPhone  5        200

SAMSUNG 5        500

Create a mapping to join the products and sales table on product column using the SQL Transformation? The output will be

PRODUCT QUANTITY PRICE

----------------------

SAMSUNG 2        100

SAMSUNG 5        500

IPhone  5        200

LG      3        80

Solution:

Just follow the below steps for creating the SQL transformation to solve the example

· Create a new mapping, drag the products source definition to the mapping.

· Go to the toolbar -> Transformation -> Create -> Select the SQL transformation. Enter a name and then click create.

· Select the execution mode as query mode, DB type as Oracle, connection type as static. This is shown in the below image.Then click OK.

· Edit the sql transformation, go to the "SQL Ports" tab and add the input and output ports as shown in the below image. Here for all the ports, you have to define Data Type (informatica specific data types) and Native Type (Database specific data types).

· In the same "SQL Ports" Tab, go to the SQL query and enter the below sql in the SQL editor.

select product, quantity, price from sales where product = ?product?

· Here ?product? is the parameter binding variable which takes its values from the input port. Now connect the source qualifier transformation ports to the input ports of SQL transformation and target input ports to the SQL transformation output ports. The complete mapping flow is shown below.

· Create the workflow, session and enter the connections for source, target. For SQL transformation also enter the source connection.

After you run the workflow, the integration service generates the following queries for sql transformation

select product, quantity, price from sales where product ='SAMSUNG'

select product, quantity, price from sales where product ='LG'

select product, quantity, price from sales where product ='IPhone'

Dynamic SQL query: A dynamic SQL query can execute different query statements for each input row. You can pass a full query or a partial query to the sql transformation input ports to execute the dynamic sql queries.

SQL Transformation Example Using Full Dynamic query

Q2) I have the below source table which contains the below data.

Table Name: Del_Tab

Del_statement

------------------------------------------

Delete FROM Sales WHERE Product = 'LG'

Delete FROM products WHERE Product = 'LG'

Solution:

Just follow the same steps for creating the sql transformation in the example 1.

· Now go to the "SQL Ports" tab of SQL transformation and create the input port as "Query_Port". Connect this input port to the Source Qualifier Transformation.

· In the "SQL Ports" tab, enter the sql query as ~Query_Port~. The tilt indicates a variable substitution for the queries.

· As we don’t need any output, just connect the SQLError port to the target.

· Now create workflow and run the workflow.

SQL Transformation Example Using Partial Dynamic query

Q3) In the example 2, you can see the delete statements are similar except Athe table name. Now we will pass only the table name to the sql transformation. The source table contains the below data.

Table Name: Del_Tab

Tab_Names

----------

sales

products

Solution:

Create the input port in the sql transformation as Table_Name and enter the below query in the SQL Query window.

Delete FROM ~Table_Name WHERE Product = 'LG'

STORED PROCEDURE TRANSFORMATION IN INFORMATICA

Stored Procedure Transformation is a passive transformation. Stored procedure transformation can be used in both connected and unconnected mode.

Stored procedures are stored and run within the database. Stored procedures contain a pre-compiled collection of PL-SQL statements. The stored procedures in the database are executed using the Execute or Call statements. Informatica provides the stored procedure transformation which is used to run the stored procedures in the database.

Some of the tasks you can do with stored procedures are listed below:

· Check the status of a target database before loading data into it.

· Determine if enough space exists in a database.

· Perform a specialized calculation.

· Dropping and recreating indexes.

The stored procedure must exist in the database before creating a Stored Procedure transformation, and the stored procedure can exist in a source, target, or any database with a valid connection to the Integration Service.

STORED PROCEDURE TRANSFORMATION OVERVIEW:

One of the important features of stored procedure is that you can send data to the stored procedure and receive data from the stored procedure. There are three types of data which pass between the integration service and the stored procedure:

· Input / Output Parameters: Used to send and receive data from the stored procedure.

· Return Values: After running a stored procedure, most databases returns a value. This value can either be user-definable, which means that it can act similar to a single output parameter, or it may only return an integer value. If a stored procedure returns a result set rather than a single return value, the Stored Procedure transformation takes only the first value returned from the procedure.

· Status Codes: Status codes provide error handling for the Integration Service during a workflow. Stored procedure issues a status code that notifies whether or not the stored procedure completed successfully. You cannot see this value.

Connected and Unconnected Stored Procedure Transformation:

· Connected Stored Procedure Transformation: The stored procedure transformation is connected to other transformations in the flow of the mapping. Use connected Stored Procedure transformation when you need data from an input port sent as an input parameter to the stored procedure, or the results of a stored procedure sent as an output parameter to another transformation.

· Unconnected Stored Procedure Transformation: The stored procedure transformation is not connected directly to the flow of the mapping. It either runs before or after the session, or is called by an expression in another transformation in the mapping.

Specifying when the Stored Procedure Runs:

The property, "Stored Procedure Type" is used to specify when the stored procedure runs. The different values of this property are shown below:

· Normal: The stored procedure transformation runs for each row passed in the mapping. This is useful when running a calculation against an input port. Connected stored procedures run only in normal mode.

· Pre-load of the Source: Runs before the session reads data from the source. Useful for verifying the existence of tables or performing joins of data in a temporary table.

· Post-load of the Source: Runs after reading data from the source. Useful for removing temporary tables.

· Pre-load of the Target: Runs before the session sends data to the target. This is useful for verifying target tables or disk space on the target system.

· Post-load of the Target: Runs after loading data into the target. This is useful for re-creating indexes on the database

UNION TRANSFORMATION IN INFORMATICA

Union transformation is an active and connected transformation. It is multi input group transformation used to merge the data from multiple pipelines into a single pipeline. Basically it merges data from multiples sources just like the UNION ALL set operator in SQL. The union transformation does not remove any duplicate rows.

Union Transformation Guidelines

The following rules and guidelines should be used when using a union transformation in a mapping

· Union transformation contains only one output group and can have multiple input groups.

· The input groups and output groups should have matching ports. The datatype, precision and scale must be same.

· Union transformation does not remove duplicates. To remove the duplicate rows use sorter transformation with "select distinct" option after the union transformation.

· The union transformation does not generate transactions.

· You cannot connect a sequence generator transformation to the union transformation.

· Union transformation does not generate transactions.

Creating union transformation

Follow the below steps to create a union transformation

1. Go the mapping designer, create a new mapping or open an existing mapping

2. Go to the toolbar-> click on Transformations->Create

3. Select the union transformation and enter the name. Now click on Done and then click on OK.

4. Go to the Groups Tab and then add a group for each source you want to merge.

5. Go to the Group Ports Tab and add the ports.

Components of union transformation

Configure the following tabs of union transformation

· Transformation: You can enter name and description of the transformation

· Properties: Specify the amount of tracing level to be tracked in the session log.

· Groups Tab: You can create new input groups or delete existing input groups.

· Group Ports Tab: You can create and delete ports for the input groups.

Note: The ports tab displays the groups and ports you create. You cannot edit the port or group information in the ports tab. To do changes use the groups tab and group ports tab.

Why union transformation is active

Union is an active transformation because it combines two or more data streams into one. Though the total number of rows passing into the Union is the same as the total number of rows passing out of it, and the sequence of rows from any given input stream is preserved in the output, the positions of the rows are not preserved, i.e. row number 1 from input stream 1 might not be row number 1 in the output stream. Union does not even guarantee that the output is repeatable.

Union Transformation Example

1. There are two tables in the source. The table names are employees_US and employees_UK and have the structure. Create a mapping to load the data of these two tables into single target table employees?

TRANSACTION CONTROL TRANSFORMATION IN INFORMATICA

Transaction Control is an active and connected transformation. The transaction control transformation is used to control the commit and rollback of transactions. You can define a transaction based on varying number of input rows. As an example, you can define a transaction on a group rows in the employees data using the department Id as a key.

In the informatica power center, you can define the transaction at the following levels:

· Mapping level: Use the transaction control transformation to define the transactions.

· Session level: You can specify the "Commit Type" option in the session properties tab. The different options of "Commit Type" are Target, Source and User Defined. If you have used the transaction control transformation in the mapping, then the "Commit Type" will always be "User Defined"

When you run a session, the integration service evaluates the expression for each row in the transaction control transformation. When it evaluates the expression as commit, then it commits all the rows in the transaction to the target(s). When the integration service evaluates the expression as rollback, then it roll back all the rows in the transaction from the target(s).

When you have flat file as the target, then the integration service creates an output file for each time it commits the transaction. You can dynamically name the target flat files. Look at the example for creating flat files dynamically - Dynamic flat file creation.

Creating Transaction Control Transformation

Follow the below steps to create transaction control transformation:

· Go to the mapping designer, click on transformation in the toolbar, Create.

· Select the transaction control transformation, enter the name and click on Create and then Done.

· You can drag the ports in to the transaction control transformation or you can create the ports manually in the ports tab.

· Go to the properties tab. Enter the transaction control expression in the Transaction Control Condition.

Configuring Transaction Control Transformation

You can configure the following components in the transaction control transformation:

· Transformation Tab: You can rename the transformation and add a description.

· Ports Tab: You can create input/output ports

· Properties Tab: You can define the transaction control expression and tracing level.

· Metadata Extensions Tab: You can add metadata information.

Transaction Control Expression

You can enter the transaction control expression in the Transaction Control Condition option in the properties tab. The transaction control expression uses the IIF function to test each row against the condition. Use the following syntax for the expression

Syntax:

IIF (condition, value1, value2)

Example:

IIF(dept_id=10, TC_COMMIT_BEFORE,TC_ROLLBACK_BEFORE)

Use the following built-in variables in the expression editor of the transaction control transformation:

· TC_CONTINUE_TRANSACTION: The Integration Service does not perform any transaction change for this row. This is the default value of the expression.

· TC_COMMIT_BEFORE: The Integration Service commits the transaction, begins a new transaction, and writes the current row to the target. The current row is in the new transaction.

· TC_COMMIT_AFTER: The Integration Service writes the current row to the target, commits the transaction, and begins a new transaction. The current row is in the committed transaction.

· TC_ROLLBACK_BEFORE: The Integration Service rolls back the current transaction, begins a new transaction, and writes the current row to the target. The current row is in the new transaction.

· TC_ROLLBACK_AFTER: The Integration Service writes the current row to the target, rolls back the transaction, and begins a new transaction. The current row is in the rolled back transaction.

If the transaction control transformation evaluates to a value other than the commit, rollback or continue, then the integration service fails the session.

Transaction Control Transformation in Mapping

Transaction control transformation defines or redefines the transaction boundaries in a mapping. It creates a new transaction boundary or drops any incoming transaction boundary coming from upstream active source or transaction control transformation.

Transaction control transformation can be effective or ineffective for the downstream transformations and targets in the mapping. The transaction control transformation can become ineffective for downstream transformations or targets if you have used transformation that drops the incoming transaction boundaries after it. The following transformations drop the transaction boundaries.

· Aggregator transformation with Transformation scope as "All Input".

· Joiner transformation with Transformation scope as "All Input".

· Rank transformation with Transformation scope as "All Input".

· Sorter transformation with Transformation scope as "All Input".

· Custom transformation with Transformation scope as "All Input".

· Custom transformation configured to generate transactions

· Transaction Control transformation

· A multiple input group transformation, such as a Custom transformation, connected to multiple upstream transaction control points.

Mapping Guidelines and Validation

Use the following rules and guidelines when you create a mapping with a Transaction Control transformation:

· If the mapping includes an XML target, and you choose to append or create a new document on commit, the input groups must receive data from the same transaction control point.

· Transaction Control transformations connected to any target other than relational, XML, or dynamic MQSeries targets are ineffective for those targets.

· You must connect each target instance to a Transaction Control transformation.

· You can connect multiple targets to a single Transaction Control transformation.

· You can connect only one effective Transaction Control transformation to a target.

· You cannot place a Transaction Control transformation in a pipeline branch that starts with a Sequence Generator transformation.

· If you use a dynamic Lookup transformation and a Transaction Control transformation in the same mapping, a rolled-back transaction might result in unsynchronized target data.

· A Transaction Control transformation may be effective for one target and ineffective for another target. If each target is connected to an effective Transaction Control transformation, the mapping is valid.

· Either all targets or none of the targets in the mapping should be connected to an effective Transaction Control transformation.

murali

Wednesday, 3 September 2014