Forum Stats

  • 3,813,793 Users
  • 2,258,772 Discussions
  • 7,892,396 Comments

Discussions

ODI 12.2.1 - Truncate the target Datastore without Knowledge Module's TRUNCATE option

kovar
kovar Member Posts: 44 Red Ribbon
edited Feb 1, 2018 3:46AM in Data Integrator

Hi,

Here is my situation: I can't truncate the target Datastore with Knowledge Module's TRUNCATE option before mapping starts (missing GRANT permissions) so I'm looking for an alternative.

pastedImage_3.png

I was thinking about getting target table names of my mappings (inside Package or Load Plan) and then forward target table names as a parameter to my stored procedure on database which will do the job.

I have few options in mind:

Option with PACKAGE:

I will put a variable as a First Step in package. It would be a Refresh Type variable and it would trigger a SQL query:

select replace(substr('<%=odiRef.getStep("OK_NEXT_STEP_NAME")%>',3),'_INSERT','') from dual

This variable would save STEP_NAME of its next step in package and after that I would forward that value to ODI procedure

BEGIN

DWH.P_TRUNCATE_TABLES(#PROJECT.TEST_VARIABLE);

END;

Stored procedure will do the truncate and after its done my mapping can start its load.

PROBLEMS:

odiRef.getStep("OK_NEXT_STEP_NAME") returns empty value when variable tries to get STEP_NAME of next mapping. Bug I guess?

pastedImage_6.png

The second problem is to insert ODI procedure somewhere in package. It somehow doesn't fit in the middle, between variable and mapping, and I'm not sure if it's correct to put procedure inside Begin Mapping Command of a mapping. My mapping naming convention looks like this: M_<target_table_name>_INSERT, so that's why I have to do substring over STEP_NAME.

Option with ODI REPO TABLES:

There will be lots of sequentially ordered mappings inside package and any type of manual job like renaming steps etc is not an option. I need to get a list of mappings inside my package and for this case I would need an advice on which ODI REPO tables to join in order to get this information. SNP_MAPPING, SNP_PACKAGE and SNP_STEP are probably the ones I need but I'm not 100% sure if I need anything else. After I get the list of mappings they are forwarded to stored procedure.

I'm also looking for a good advice on how to implement all of this in my LOAD PLAN. I have over 100 tables to load with data, each needs to be truncated first and then loaded so I guess dragging 100 tables inside package is not a good option.

If somebody has experience with this kind of situation I would be very thankful if you can share your knowledge.

Thanks in advance!

-Kovar

Tagged:

Best Answer

  • rpc1
    rpc1 Member Posts: 1,503
    edited Jan 31, 2018 8:33AM Answer ✓

    I have never try, but think that is similar with ODI procedure, it will looks like this:

    BEGIN

    DWH.P_TRUNCATE_TABLES('<%=odiRef.getPop("TABLE_NAME")%>');

    END;

    And you can get target table name directly from odiRef

Answers

  • rpc1
    rpc1 Member Posts: 1,503
    edited Jan 30, 2018 9:41AM

    1. Yes it is bug Bug 22885786 - getStep() Method returns null for OK_NEXT_STEP_NAME and KO_NEXT_STEP_NAME ppty

    fixed in ODI 12.2.1.2.6

    2.

    I'm also looking for a good advice on how to implement all of this in my LOAD PLAN. I have over 100 tables to load with data, each needs to be truncated first and then loaded so I guess dragging 100 tables inside package is not a good option

    Why you don't change KM, I think it is most simple way. You may add new KM option which calls you trancate procedure.

    Take you existing KM and duplicate it, than make changes and use it...

  • Adrian_Popescu-Oracle
    Adrian_Popescu-Oracle RomaniaPosts: 1,976 Employee
    edited Jan 30, 2018 3:57PM

    Hi,

    Patch 22885786 is available only for ODI 12.1.3. For other ODI versions (previous to ODI 12.2.1.2.6) you need to open a ticket with Oracle and request a patch.

    Adrian

  • kovar
    kovar Member Posts: 44 Red Ribbon
    edited Jan 31, 2018 7:46AM

    Hi rpc1, thank you for your response. Can you provide some sample code of how to call stored procedure inside Knowledge Module?

    Thank you very much for the effort you have made so far.

    -Kovar

  • rpc1
    rpc1 Member Posts: 1,503
    edited Jan 31, 2018 8:33AM Answer ✓

    I have never try, but think that is similar with ODI procedure, it will looks like this:

    BEGIN

    DWH.P_TRUNCATE_TABLES('<%=odiRef.getPop("TABLE_NAME")%>');

    END;

    And you can get target table name directly from odiRef

  • kovar
    kovar Member Posts: 44 Red Ribbon
    edited Feb 1, 2018 3:34AM

    This is fantastic. Thank you rpc1 for your help!

    I would add one more thing to all of this: Don't forget to GRANT EXECUTE on your stored procedure or else you'll get PLS-00201: identifier 'DWH.P_TRUNCATE_TABLES' must be declared

  • rpc1
    rpc1 Member Posts: 1,503
    edited Feb 1, 2018 3:46AM

    You are welcome!

This discussion has been closed.