INFORMATICA2

INFORMATICA INTERVIEW QUESTIONS ON UPDATE STRATEGY TRANSFORMATION

1. What is an update strategy transformation?

Update strategy transformation is used to flag source rows for insert, update, delete or reject within a mapping. Based on this flagging each row will be either inserted or updated or deleted from the target. Alternatively the row can be rejected.

2. Why update strategy is an active transformation?

As update strategy transformation can reject rows, it is called as an active transformation.

3. What are the constants used in update strategy transformation for flagging the rows?

• DD_INSERT is used for inserting the rows. The numeric value is 0.
• DD_UPDATE is used for updating the rows. The numeric value is 1.
• DD_DELETE is used for deleting the rows. The numeric value is 2.
• DD_REJECT is used for rejecting the rows. The numeric value is 3.

4. If you place an aggregator after the update strategy transformation, how the output of aggregator will be affected?

The update strategy transformation flags the rows for insert, update and delete of reject before you perform aggregate calculation. How you flag a particular row determines how the aggregator transformation treats any values in that row used in the calculation. For example, if you flag a row for delete and then later use the row to calculate the sum, the integration service subtracts the value appearing in this row. If the row had been flagged for insert, the integration service would add its value to the sum.

5. How to update the target table without using update strategy transformation?

In the session properties, there is an option 'Treat Source Rows As'. Using this option you can specify whether all the source rows need to be inserted, updated or deleted.

6. If you have an update strategy transformation in the mapping, what should be the value selected for 'Treat Source Rows As' option in session properties?

The value selected for the option is 'Data Driven'. The integration service follows the instructions coded in the update strategy transformation.

7. If you have an update strategy transformation in the mapping and you did not selected the value 'Data Driven' for 'Treat Source Rows As' option in session, then how the session will behave?

If you do not choose Data Driven when a mapping contains an Update Strategy or Custom transformation, the Workflow Manager displays a warning. When you run the session, the Integration Service does not follow instructions in the Update Strategy transformation in the mapping to determine how to flag rows.

8. In which files the data rejected by update strategy transformation will be written?

If the update strategy transformation is configured to Forward Rejected Rows then the integration service forwards the rejected rows to next transformation and writes them to the session reject file. If you do not select the forward reject rows option, the integration service drops rejected rows and writes them to the session log file. If you enable row error handling, the Integration Service writes the rejected rows and the dropped rows to the row error logs. It does not generate a reject file.

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.

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.

REVERSE THE CONTENTS OF FLAT FILE – INFORMATICA

Q1) I have a flat file, want to reverse the contents of the flat file which means the first record should come as last record and last record should come as first record and load into the target file. 

As an example consider the source flat file data as

Informatica Enterprise Solution
Informatica Power center
Informatica Power exchange
Informatica Data quality

The target flat file data should look as

Informatica Data quality
Informatica Power exchange
Informatica Power center
Informatica Enterprise Solution

Solution:

Follow the below steps for creating the mapping logic

• Create a new mapping.
• Drag the flat file source into the mapping.
• Create an expression transformation and drag the ports of source qualifier transformation into the expression transformation.
• Create the below additional ports in the expression transformation and assign the corresponding expressions

Variable port: v_count = v_count+1
Output port o_count = v_count

• Now create a sorter transformation and drag the ports of expression transformation into it.
• In the sorter transformation specify the sort key as o_count and sort order as DESCENDING.
• Drag the target definition into the mapping and connect the ports of sorter transformation to the target.

Q2) Load the header record of the flat file into first target, footer record into second target and the remaining records into the third target.

The solution to this problem I have already posted by using aggregator and joiner. Now we will see how to implement this by reversing the contents of the file.

Solution:

• Connect the source qualifier transformation to the expression transformation. In the expression transformation create the additional ports as mentioned above.
• Connect the expression transformation to a router. In the router transformation create an output group and specify the group condition as o_count=1. Connect this output group to a target and the default group to sorter transformation.
• Sort the data in descending order on o_count port.
• Connect the output of sorter transformation to expression transformation (don’t connect o_count port).
• Again in the expression transformation create the same additional ports mentioned above.
• Connect this expression transformation to router and create an output group. In the output group specify the condition as o_count=1 and connect this group to second target. Connect the default group to the third group.

DIFFERENCE BETWEEN STOP AND ABORT IN INFORMATICA

You can stop or abort running workflow by one of the following ways:

• Issuing stop of abort in the informatica workflow monitor
• Issuing stop of abort command in pmcmd.
• specifying in the control task.

Stopping or Aborting Task:

When you stop, the integration service first tries to stop processing the task. The integration service does not process other tasks that are in sequence. However it process the tasks that are in parallel to the task on which the stop or abort command is issued. If the Integration Service cannot stop the task, you can try to abort the task. When you abort a task, the Integration Service kills the process on the task. 

Stopping or Aborting a Session Task:

When you issue a stop command on a session, the integration service first stops reading the data from the sources. It continues processing and writing data to the targets and then commits the data.

Abort command is handled the same way as the stop command, except that the abort command has timeout period of 60 seconds. If the Integration Service cannot finish processing and committing data within the timeout period, it kills the DTM process and terminates the session.

Difference Between Stop and Abort:

When you run a session, it holds memory blocks in the OS. When issue a abort on the session, it kills the threads and leaves the memory blocks. This causes memory issues in the server and leads to poor performance. Some operating systems clean the lost memory blocks automatically. However most of the operating systems do not clean up these memory blocks. Stop is clean way of killing the sessions and cleans up the memory blocks.

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

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