Forum Stats

  • 3,757,060 Users
  • 2,251,192 Discussions
  • 7,869,716 Comments

Discussions

How to use IKM Oracle Incremental Update (MERGE) KM

User_OFWOJ
User_OFWOJ Member Posts: 6 Green Ribbon

I am newbie to ODI.

Please point me to any tutorial which helps me create a sample using this IKM Oracle Incremental Update (MERGE) Knowledge Module.


Many Thanks,

Tilak

Tagged:

Best Answer

  • Christyxo
    Christyxo Member Posts: 146 Silver Badge
    edited Jul 1, 2021 3:03PM Accepted Answer

    OK. Leave the missing IKM for the moment. Both the Incremental Update and Incremental Merge are used in the same manner so if you can get this part done first you are half way there.

    Step by Step:

    1. We will use the existing model that contains a primary key constraint on PK_MERGE_ID = SYS_CO7229
    2. Add an alternate key to that same model. This needs to be the natural key - the thing that joins your source to your target. In this example it would be CL_COL1
    3. In the logical panel of your mapping, select the target table. Open the properties and select target. Choose "incremental Update" as the integration type, and then select the alternate key that you created in Step 2 as the update key.
    4. Select the attributes panel. Make sure that PK_MERGE_ID is not updated. Make sure that CL_COL1 is not updated.
    5. In the physical panel of your mapping, select the target table. Open the properties and select Integration KM. Choose "IKM Oracle Incremental Update" (for now).
    6. In the options, set Flow Control = FALSE

    Run your mapping. If you get any error, paste the results in response.


    Edit : Side note... I think you may have previously missed step 3 which could be the reason you can't select the correct IKM in the physical panel.

    User_OFWOJ

Answers

  • Christyxo
    Christyxo Member Posts: 146 Silver Badge

    I can't point you to any documentation but I can give you some thoughts.

    The merge IKM executes a SQL statement such as

    MERGE INTO table t
    USING ( select COL1 , COL2 from table ) s
    ON ( t.COL1 = s.COL1 )
    WHEN MATCHED THEN UPDATE SET ( t.COL2 = s.COL2 )
    WHEN NOT MATCHED THEN INSERT ( COL1 , COL2 ) VALUES ( s.COL1 , s.COL2 )
    

    That's the standard code block of the merge.

    The critical piece that you need for your ODI mapping is in the ON clause. You need to tell ODI that when you merge into this table, this is the natural key that. There are 2 methods to doing this.

    • The first relies you on you using Keys in the Model. You need to create a new alternate key and in the attributes add the columns that form this key. And then in your mapping, you select this alternate key as the update key in your target table.


    • The alternative method is to assign the keys on the target table directly in the mapping. With this method you must not pick an update such as in the above example.

    Note that in both cases, your primary key in the mapping, and the alternate key must not be selected to update. If you attempt to update either of these, your mapping will fail.

  • User_OFWOJ
    User_OFWOJ Member Posts: 6 Green Ribbon

    Hi Christyxo,

    Thanks for your answer.

    I see the "Key" field non-editable, so I could only uncheck the update field for PK_MERGE_ID and CL_COL1 for the TB_MERGE_EXAMPLE1:


    I do no see the "IKM Oracle Incremental Update (MERGE)" KM in the list:


    Please advise.

  • Christyxo
    Christyxo Member Posts: 146 Silver Badge

    I see the "Key" field non-editable, so I could only uncheck the update field for PK_MERGE_ID and CL_COL1 for the TB_MERGE_EXAMPLE1.

    If the "key" field is non-editable, it means that you have key constraints assigned in the model. If this is the correct condition for updates (the ON Clause) then you should be OK. Otherwise you will need to create a new alternate key constraint that reflects your ON clause.

    I do no see the "IKM Oracle Incremental Update (MERGE)" KM in the list:

    If you expand the project folder on the left hand side you will see the option for knowledge modules.

    If you expand this, and right click on IKM, you can then import knowledge modules.

    You should find the out of box IKM called "IKM Oracle Incremental Update (MERGE)" in this list so go ahead and import it. You may need to close down the mapping before it becomes available.

  • User_OFWOJ
    User_OFWOJ Member Posts: 6 Green Ribbon

    I created a couple of tables using the below queries:

    create table TB_MERGE_EXAMPLE( PK_MERGE_ID NUMBER(4,0), CL_COL1 VARCHAR2(50), CL_COL2 VARCHAR2(50), CL_COL3 VARCHAR2(50));

    create table TB_MERGE_EXAMPLE1( PK_MERGE_ID NUMBER(4,0), CL_COL1 VARCHAR2(50) primary key, CL_COL2 VARCHAR2(50), CL_COL3 VARCHAR2(50));

    and the I populated the first table with 3 rows:

    insert into TB_MERGE_EXAMPLE values(1, 'a','b','c');

    insert into TB_MERGE_EXAMPLE values(2, 'd','e','f');

    insert into TB_MERGE_EXAMPLE values(3, 'g','h','i');

    The Model for the Table 2 has the below cosnstraint:






    I have the Knowledge Module imported and closed, but I still cannot see it in the list:

    Should I be using the query (that you mentioned in the first reply) somewhere in the second model?:

    MERGE INTO table t
    USING ( select COL1 , COL2 from table ) s
    ON ( t.COL1 = s.COL1 )
    WHEN MATCHED THEN UPDATE SET ( t.COL2 = s.COL2 )
    WHEN NOT MATCHED THEN INSERT ( COL1 , COL2 ) VALUES ( s.COL1 , s.COL2 )
    


    Please advise.

  • Christyxo
    Christyxo Member Posts: 146 Silver Badge

    Is there a reason you need to use the merge IKM? In general the incremental update has the same end result and you have a little more control.

  • User_OFWOJ
    User_OFWOJ Member Posts: 6 Green Ribbon

    Yes, I need to support a customer use-case using this Merge IKM.

  • Christyxo
    Christyxo Member Posts: 146 Silver Badge
    edited Jul 1, 2021 3:03PM Accepted Answer

    OK. Leave the missing IKM for the moment. Both the Incremental Update and Incremental Merge are used in the same manner so if you can get this part done first you are half way there.

    Step by Step:

    1. We will use the existing model that contains a primary key constraint on PK_MERGE_ID = SYS_CO7229
    2. Add an alternate key to that same model. This needs to be the natural key - the thing that joins your source to your target. In this example it would be CL_COL1
    3. In the logical panel of your mapping, select the target table. Open the properties and select target. Choose "incremental Update" as the integration type, and then select the alternate key that you created in Step 2 as the update key.
    4. Select the attributes panel. Make sure that PK_MERGE_ID is not updated. Make sure that CL_COL1 is not updated.
    5. In the physical panel of your mapping, select the target table. Open the properties and select Integration KM. Choose "IKM Oracle Incremental Update" (for now).
    6. In the options, set Flow Control = FALSE

    Run your mapping. If you get any error, paste the results in response.


    Edit : Side note... I think you may have previously missed step 3 which could be the reason you can't select the correct IKM in the physical panel.

    User_OFWOJ
  • User_OFWOJ
    User_OFWOJ Member Posts: 6 Green Ribbon

    Thanks a ton, Christyxo !

    Yes, I missed the 3rd step, that's why the Merge IKM was not listing there.

    I am now able to select the Merge IKM in the Physical tab on the target table and run the mapping just fine.

    The mapping now looks like:

    The source table TB_MERGE_EXAMPLE has the PK_MERGE_ID as the primary key and with constraint: SYS_C007238:


    The target table TB_MERGE_EXAMPLE1 has the PK_MERGE_ID as the primary key and with constraint: SYS_C007239

    and I added an Alternate key as you mentioned:


    In Logical Tab, on the target table,

    I selected Integration Type as "Incremental Update" and selected the newly created Alternate key as Update Key:


    I also unchecked the Update checkbox for both PK_MERGE_ID and CL_COL1 columns in attributes tab:


    Now, in the physical tab, I am able to select the Merge IKM and disable the FlowControl:

    Now, when I run this mapping, it runs fine!

    And the data is merged from TB_MERGE_EXAMPLE table to TB_MERGE_EXAMPLE1 !!


    One more problem now:

    I tried to add more rows in TB_MERGE_EXAMPLE table and ran the mapping again, the new rows are NOT merged in the TB_MERGE_EXAMPLE1 table.

    What should be done to get this working as well?

  • Christyxo
    Christyxo Member Posts: 146 Silver Badge

    Glad it works.

    In order to update target, the values in your AK (COL1) from source need to match the values in your AK on target. Every column that you want to update, must be checked in your attributes. The fields associated with your primary key and your alternate key should not be marked for update.

    In order to insert the new rows from source, the value in your AK must not exist in target. Every column that you want to insert, must be checked in your attributes. The fields associated with your primary key and your alternate key should be marked for insert (you don't have to insert every column but these should be a minimum).

    If the value in your AK exists in source or target more than once it should fail with "unable to identify a stable set of source rows" or some similar error.

    It really should be as simple as that. If your new rows are not inserting, just check that the value in COL1 doesn't already exist in target. Otherwise I don't know where the issue could be that your new rows are not getting moved.

  • User_OFWOJ
    User_OFWOJ Member Posts: 6 Green Ribbon

    I see that the values of the new rows in TB_MERGE_EXAMPLE are not there in TB_MERGE_EXAMPLE1 and the all columns are selected for Insert and the PK_MERGE_ID and CL_COL1 are deselected for Update:


    Yet, the new rows not being merged into the second table.

    I updated CL_COL2 column in TB_MERGE_EXAMPLE for a particular row and ran the Mapping again.

    The mapping ran successfully, yet the update did not happen in the TB_MERGE_EXAMPLE1 table.

    I might be missing some configuration.