in Technology by
How do you update the records with or without using Update Strategy?

1 Answer

0 votes
by

We can use the session configurations to update the records. We can have several options for handling database operations such as insert, update, delete.

During session configuration, you can select a single database operation for all rows using the Treat Source Rows As setting from the ‘Properties’ tab of the session.

  • Insert: – Treat all rows as inserts.
  • Delete: – Treat all rows as deletes.
  • Update: – Treat all rows as updates.
  • Data Driven :- Integration Service follows instructions coded into Update Strategy flag rows for insert, delete, update, or reject.

Once determined how to treat all rows in the session, we can also set options for individual rows, which gives additional control over how each rows behaves. We need to define these options in the Transformations view on mapping tab of the session properties.

  • Insert: – Select this option to insert a row into a target table.
  • Delete: – Select this option to delete a row from a table.
  • Update :- You have the following options in this situation:
    • Update as Update: – Update each row flagged for update if it exists in the target table.
    • Update as Insert: – Insert each row flagged for update.
    • Update else Insert: – Update the row if it exists. Otherwise, insert it.
  • Truncate Table: – Select this option to truncate the target table before loading data.

Steps:

  1. Design the mapping just like an ‘INSERT’ only mapping, without Lookup, Update Strategy Transformation.Design-mapping-informatica-interview-questions
  2. First set Treat Source Rows As property as shown in below image.
    Treat-source-rows-informatica-interview-questions
  3. Next, set the properties for the target table as shown below. Choose the properties Insert and Update else Insert.
    Set-properties-informatica-interview-questions

These options will make the session as Update and Insert records without using Update Strategy in Target Table.

When we need to update a huge table with few records and less inserts, we can use this solution to improve the session performance.

The solutions for such situations is not to use Lookup Transformation and Update Strategy to insert and update records.

The Lookup Transformation may not perform better as the lookup table size increases and it also degrades the performance.

Related questions

0 votes
    Why update strategy and union transformations are Active? Explain with examples....
asked Mar 28, 2021 in Technology by JackTerrance
0 votes
    The strategy can retrieve a single record if the equality condition is on a key; multiple records may ... Operation in section Query Processing Techniques of Database Management...
asked Oct 10, 2021 in Education by JackTerrance
0 votes
    I writing a function in my form that require user to upload a file, but doesn't necessary have to ... JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Mar 2, 2022 in Education by JackTerrance
0 votes
    I have this example code which raises a "Duplicate Expection". Instead, I want just to skip if a duplicate exists. (1..10).each do |page| group.products...
asked May 26, 2022 in Education by JackTerrance
0 votes
    The strategy used to ensure that the message is a multiple of the cipher block size without compromising the size ... 1)Ciphertext stealing (2)Padding (3)Refactoring (4)Diffusion...
asked Mar 19, 2021 in Technology by JackTerrance
0 votes
    I've been using K8S ConfigMap and Secret to manage our properties. My design is pretty simple, that keeps ... 't been created Select the correct answer from above options...
asked Jan 30, 2022 in Education by JackTerrance
0 votes
    How do you load first and last records into target table? How many ways are there to do it? Explain through mapping flows...
asked Mar 28, 2021 in Technology by JackTerrance
0 votes
    How do you load alternate records into different tables through mapping flow?...
asked Mar 28, 2021 in Technology by JackTerrance
0 votes
    How do you load only null records into target? Explain through mapping flow....
asked Mar 28, 2021 in Technology by JackTerrance
0 votes
    How do you remove Duplicate records in Informatica? And how many ways are there to do it?...
asked Mar 27, 2021 in Technology by JackTerrance
0 votes
    Which one of the following is not ID generating strategy using @GeneratedValue annotation? (a) Auto (b) Manual ... Annotations of Java Select the correct answer from above options...
asked Feb 16, 2022 in Education by JackTerrance
0 votes
    I have batch of user accounts and every user account's password is not crypted.I want to insert to ... Questions for Interview, JavaScript MCQ (Multiple Choice Questions)...
asked Jul 20, 2022 in Education by JackTerrance
0 votes
    Can anyone tell me why you need a Digital Marketing strategy? Select the correct answer from above options...
asked Jan 10, 2022 in Education by JackTerrance
...