Update Strategy T/R

UPDATE STRATEGY TRANSFORMATION

 
1. WHAT IS UPDATE STRATEGY TRANSFORM? 
2. WHAT ARE UPDATE STRATEGY CONSTANTS? 
3. HOW CAN WE UPDATE A RECORD IN TARGET TABLE WITHOUT USING UPDATE STRATEGY? 
4. WHAT IS DATA DRIVEN? 
5. WHAT HAPPENS WHEN DD_UPDATE IS DEFINED IN UPDATE STRATEGY AND TREAT SOURCE ROWS AS INSERT IS SELECTED IN SESSION? 
6. WHAT ARE THE THREE AREAS WHERE THE ROWS CAN BE FLAGGED FOR PARTICULAR TREATMENT? 
7. BY DEFAULT OPERATION CODE FOR ANY ROW IN INFORMATICA WITHOUT BEING ALTERED IS INSERT. THEN STATE WHEN DO WE
NEED DD_INSERT? 
8. WHAT IS THE DIFFERENCE BETWEEN UPDATE STRATEGY AND FOLLOWING UPDATE OPTIONS IN TARGET? 
9. WHAT IS THE USE OF FORWARD REJECT ROWS IN MAPPING? 56
10. SCENARIO IMPLEMENTATION 1 

UPDATE STRATEGY

 121.Name the scenario in which Informatica server reject files.  

When the server faces a rejection of the update strategy transformation, it regrets files. The database consisting of the information and data also gets disrupted. This is a rare case scenario.

This happens when it faces DD_Reject in update strategy transformation. Moreover it disrupts the database constraint filed in the rows was condensed.

 122. What is Update Strategy transform?

Update strategy defines the sources to be flagged for insert, update, delete, and reject at the targets.

Whenever the row has to be updated or inserted based on some sequence then update strategy is used. But in this condition should be specified before for the processed row to be tick as update or inserted.

Row by row processing is done by informatica. Every row is inserted in the target table because it is marked as default. Update strategy is used whenever the row has to be updated or inserted based on some sequence. Moreover the condition must be specified in update strategy for the processed row to be marked as updated or inserted.

 

To flag source records as INSERT, DELETE, UPDATE or REJECT for target database. Default flag is Insert. This is must for Incremental Data Loading.

This is the important transformation,is used to maintain the history data or just most recent changes into the target table.

We can set or flag the records by using these two levels.

  1. Within a session :
    When you configure the session,you can instruct the informatica server to either treat all the records in the same way.
  2. Within a mapping :
    within a mapping we use update strategy transformation to flag the records like insert,update,delete or reject.

Default option for update strategy transformation is dd_insert or we can put ‘0’ in session level data driven.

 

Update Strategy in Informatica is used to control data passing through it and tag it such as INSERT, UPDATE, DELETE and REJECT. We can set a conditional Logic within the Update Strategy Transformation to tag it.

123. What are Update Strategy Constants?

  •  DD_INSERT – 0
  •  DD_UPDATE – 1
  •  DD_DELETE – 2
  •  DD_REJECT – 3

124. How can we update a record in target table without using Update strategy?
A target table can also be updated without using “Update Strategy”. For this, we need to define the key in the target table in Informatica level and then we need to connect the key and the field we want to update in the mapping Target. In the session level, we should set the target property as “Update as Update” and enable the “Update” check-box.
Let’s assume we have a target table “Customer” with fields as “Customer ID”, “Customer Name” and “Customer Address”. Suppose we want to update “Customer Address” without an Update Strategy. Then we have to define “Customer ID” as primary key in Informatica level and we will have to connect Customer ID and Customer Address fields in the mapping. If the session properties are set correctly as described above, then the mapping will only update the customer address field for all matching customer IDs.

. How can we update a record in target table without using Update strategy?

A target table can be updated without using ‘Update Strategy’. For this, we need to define the key in the target table in Informatica level and then we need to connect the key and the field we want to update in the mapping Target. In the session level, we should set the target property as “Update as Update” and check the “Update” check-box.Let’s assume we have a target table “Customer” with fields as “Customer ID”, “Customer Name” and “Customer Address”.

Suppose we want to update “Customer Address” without an Update Strategy. Then we have to define “Customer ID” as primary key in Informatica level and we will have to connect Customer ID and Customer Address fields in the mapping. If the session properties are set correctly as described above, then the mapping will only update the customer address field for all matching customer IDs.

125. What is Data Driven?
Update strategy defines the sources to be flagged for insert, update, delete, and reject at the targets.
Treat input rows as Data Driven: This is the default session property option selected while using an Update Strategy transformation in a mapping.
The integration service follows the instructions coded in mapping to flag the rows for insert, update, delete or reject.

This is done by mentioning DD_UPDATE or DD_INSERT or DD_DELETE in the update strategy transformation.

“Treat source rows as” property in session is set to “Data Driven” by default when using a update strategy transformation in a mapping.

126. What happens when DD_UPDATE is defined in update strategy and Treat source rows as INSERT is selected in Session?
If in Session anything other than DATA DRIVEN is mentioned then Update strategy in the mapping is ignored.
127. What are the three areas where the rows can be flagged for particular treatment?

  •  In Mapping – Update Strategy
  •  In Session – Treat Source Rows As
  •  In Session – Target Insert / Update / Delete Options.

128. By default operation code for any row in Informatica without being altered is INSERT. Then state when do we need DD_INSERT?
When we handle data insertion, updating, deletion and/or rejection in a single mapping, we use Update Strategy transformation to flag the rows for Insert, Update, Delete or Reject. We flag it by either providing the values 0, 1, 2, 3 respectively or by DD_INSERT, DD_UPDATE, DD_DELETE or DD_REJECT in the Update Strategy transformation. By default the transform has the value ‘0’ and hence it performs insertion.
Suppose we want to perform insert or update target table in a single pipeline. Then we can write the below expression in update strategy transformation to insert or update based on the incoming row.
IIF (LKP_EMPLOYEE_ID IS NULL, DD_INSERT, DD_UPDATE)
If we can use more than one pipeline then, it’s not a problem. For the Insert part we don’t even need an Update Strategy transform explicitly (DD_INSERT), we can map it straight away.
129. What is the difference between update strategy and following update options in target?Update as Update – Update as Insert – Update else Insert Even if we do not use update strategy we can still update the target by setting, for example Update as Update and treating target rows as data driven. So what’s the difference here?
The operations for the following options will be done in the Database Level.

  •  Update as Update
  •  Update as Insert
  •  Update else Insert

It will write a ‘select’ statement on the target table and will compare with the source. Accordingly if the record already exits it will do an update else it will insert. On the other hand the update strategy the operations will be done at the Informatica level itself.
Update strategy also gives conditional update option – wherein based on some condition you can update/ insert even reject the rows. Such conditional options are not available in target based updates (wherein it will either “update” or it will perform “update else insert” based on the keys defined in Informatica level)
130. What is the use of Forward Reject rows in Mapping?
If DD_REJECT is selected in the Update Strategy, then we need to select this option to generate the Reject/Bad file.
131.Scenario Implementation 1
Suppose we have source employee table and we want to load employees who belong to department 10 to Target 1, 20 to Target 2 and 30 to Target 3. Describe the approach without using FILTER or ROUTER Transformations.

We will use three separate Update Strategy transformations before each of the target tables (T1, T2, T3),
and provide below condition in their expression editor:
UPD_T1: IIF (DEPTNO = 10, DD_INSERT, DD_REJECT)
UPD_T2: IIF (DEPTNO = 20, DD_INSERT, DD_REJECT)
UPD_T3: IIF (DEPTNO = 30, DD_INSERT, DD_REJECT)