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.