Load Last N Records of File into Target Table - Informatica

How to load only the last N rows from source file into the target table using the mapping in informatica?

First take a look at the below data in the source file:

Products
--------
Windows
Linux
Unix
Ubuntu
Fedora
Centos
Debian

I want to load only the last record or footer into the target table. The target should contain only the product "Debain". Follow the below steps for implementing the mapping logic in informatica:

• The mapping flow and the transformations are shown below:

SRC->SQ->EXPRESSION->SORTER->EXPRESSION->FILTER->TGT

• Create a new mapping and drag the source into the mapping. By default, it creates the source qualifier transformation.
• Now create an expression transformation and drag the ports from source qualifier into the expression transformation. In the expression transformation, create the below additional ports and assign the corresponding expressions:

v_count (variable port) = v_count+1
o_count (output port) = v_count

• The output of expression transformation is

Products, o_count
-----------------
Windows, 1
Linux,   2
Unix,    3
Ubuntu,  4
Fedora,  5
Centos,  6
Debian,  7

• Now connect the expression transformation to a sorter transformation and sort the rows on the o_count port in descending order. The output of sorter transformation is shown below:

Products
--------
Debian
Centos
Fedora
Ubuntu
Unix
Linux
Windows

• Create another expression transformation and connect the Products port of sorter to expression transformation. Create the following ports in the expression transformation:

v_count (variable port) = v_count+1
o_count (output port) = v_count

• Connect the expression to a filter transformation and specify the filter condition as o_count = 1.
• Connect the filter to the target and save the mapping.

Informatica online training - Informatica Jobs

Load Alternative Records / Rows into Multiple Targets - Informatica

How to load records alternativly into multiple targets in informatica? Implement mapping logic for this.

I have a source file which contains N number of records. I want to load the source records into two targets, such that first row goest into target 1, second row goes into target2, third row goes into target3 and so on.
Let see how to create a mapping logic for this in informatica with an example. Consider the following source flat file as an example:

Products
---------
Informatica
Datastage
Pentaho
MSBI
Oracle
Mysql

The data in the targets should be:

Target1
-------
Informatica
Pentaho
Oracle

Target2
-------
Datastage
MSBI
Mysql

Solution:

The mapping flow and the transformations used are mentioned below:

SRC->SQ->EXP->RTR->TGTS

• First create a new mapping and drag the source into the mapping.
• Create an expression transformation. Drag the ports of source qualifier into the expression transformation. Create the following additional ports and assign the corresponding expressions:

v_count (variable port) = v_count+1
o_count (output port) = v_count

• Create a router transformation and drag the ports (products, v_count) from expression transformation into the router transformation. Create an output group in the router transformation and specify the following filter condition:

MOD(o_count,2) = 1

• Now connect the output group of the router transformation to the target1 and default group to target2. Save the mapping.

In the above solution, I have used expression transfromation for generating numbers. You can also use sequence generator transformation for producing sequence values.

Informatica online training - Informatica Jobs

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?

Informatica online training - Informatica Jobs

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.

Informatica online training - Informatica Jobs

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.

 

Informatica online training - Informatica Jobs

PMCMD Command Usage in Informatica

Informatica provides four built-in command line programs or utilities to interact with the informatica features. They are:

• infacmd
• infasetup
• pmcmd
• pmrep

This article covers only about the pmcmd command. The pmcmd is a command line utility provided by the informatica to perform the following tasks.

• Start workflows.
• Start workflow from a specific task.
• Stop, Abort workflows and Sessions.
• Schedule the workflows.

How to use PMCMD Command in Informatica:
1. Scheduling the workflow
The pmcmd command syntax for scheduling the workflow is shown below:

pmcmd scheduleworkflow -service informatica-integration-Service -d domain-name -u user-name -p password -f folder-name -w workflow-name

You cannot specify the scheduling options here. This command just schedules the workflow for the next run.
2. Start workflow
The following pmcmd command starts the specified workflow:

pmcmd startworkflow -service informatica-integration-Service -d domain-name -u user-name -p password -f folder-name -w workflow-name

3. Stop workflow
Pmcmd command to stop the infromatica workflow is shown below:

pmcmd stopworkflow -service informatica-integration-Service -d domain-name -u user-name -p password -f folder-name -w workflow-name

4. Start workflow from a task
You can start the workflow from a specified task. This is shown below:

pmcmd startask -service informatica-integration-Service -d domain-name -u user-name -p password -f folder-name -w workflow-name -startfrom task-name

5. Stopping a task.
The following pmcmd command stops the specified task instance:

pmcmd stoptask -service informatica-integration-Service -d domain-name -u user-name -p password -f folder-name -w workflow-name task-name

6. Aborting workflow and task.
The following pmcmd commands are used to abort workflow and task in a workflow:

pmcmd abortworkflow -service informatica-integration-Service -d domain-name -u user-name -p password -f folder-name -w workflow-name

Informatica online training - Informatica Jobs

pmcmd aborttask -service informatica-integration-Service -d domain-name

Informatica Problems With Solutions - Part 1

1. In this problem we will see how to implement the not equal operator, greater than, greater than or equal to, less than and less than or equal to operators when joining two tables in informatica.
Consider the below sales table as an example?
Table name: Sales

product, prod_quantity, price , Year
A         , 10                 , 100  , 2010
B         , 15                 , 150  , 2010
A         , 8                   , 80    , 2011
B         , 26                 , 260  , 2011

Now the problem is to identify the products whose sales is less than in the current year (In this example: 2011) when compared to the last year.
Here in this example, Product A sold less in 2011 when compared with the sales in 2010.
This problem can be easily implemented with the help of SQL query as shown below

SELECT  cy.*
FROM    SALES cy,
SALES py
WHERE   cy.product = py.product
AND        cy.year=2011
AND        py.year=2010
AND       cy.prod_quantity < py.prod_quantity;

In informatica, you can specify only equal to condition in joiner. Now we will see how to implement this problem using informatica.
Solution:
STEP1: Connect two source qualifier transformations to the source definition. Call the first source qualifier transformation as sq_cy  (cy means current year) and the other as sq_py  (py means previous year).
STEP2: In the sq_cy source qualifier transformation, specify the source filter as price=2011. In the sq_py, specify the source filter as price=2010
STEP3: Now connect these two source qualifier transformations to joiner transformation and make sq_cy as master, sq_py as detail. In the join condition, select the product port from master and detail.
STEP4: Now connect all the master ports and only the prod_quantity port from detail to the filter transformation. In the filter transformation specify the filter condition as prod_quantity < prod_quantity1. Here pord_quantity port is from master port and prod_quantity1 is from detail port.

Informatica online training - Informatica Jobs
STEP4: Connect all the ports except the prod_quantity1 of filter transformation to the target definition.
2. How to implement the not exists operator in informatica which is available in database?
Solution:
Implementing the Not Exists operator is very easy in informatica. For example, we want to get only the records which are available in table A and not in table B. For this use a joiner transformation with A as master and B as detail. Specify the join condition and in the join type, select detail outer join. This will get all the records from A table and only the matching records from B table.
Connect the joiner to a filter transformation and specify the filter condition as B_port is NULL. This will give the records which are in A and not in B. Then connect the filter to the target definition.