ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Interface Parallel Execution – A new solution

Hi Friends,

Two days ago Devendra talk to me about parallel executions in ODI and we are discussing the solution from Oracle Metalink that works but, at true, I never was stopped to analyse.

In face of that, I decided try something in a  new way and it worked fine!

Let me share my 2 minutes approach.

Problem Description:

Oracle Data Integrator uses the target table name to build the work tables (I$, E$, C$) and, because of that if more then one interface that loads the same target table is executed at same time the work tables creation, insert and drop process got mixed and nothing works.

Solution:

The solution is pretty obvious… It is necessary to make the work table name variable and linked to each execution. The ODI path for that is the Session Number.

My approach:

All solutions that I saw until today uses an Oracle Data Integrator variable but got problems if the table name is near to the maximum name length allowed for the technology.

In face of that, I decide to use the ODI natural behavior to manage the lenght.

All that is necessary to do is:

1. Edit all your KM (IKM and LKM if some) and add a new step (move it to be the first step)

Step Name: Parallel Setup

Technology:  Java Bean Shell

Code:  <? String vSess = odiRef.getSession(“SESS_NO”); ?>

Importante note: this change can be done to any KM that will not interfier with process not parallel

2. Go to Topology Module, in the Physical Schema that will have parallel execution (or in all if you wish) and change the Work Table Prefix to:

E$_<?=vSess?>

C$_<?=vSess?>

I$_<?=vSess?>

Well my friends… that is all! Any Parallel or Single execution can be executed with no conflicts between work table name or length name  problem will be raise.

The best about it is be very simple!!!

See you all in next post!

Regards,

Cezar Santos

27 Comments

  1. Pingback: Chapter 5. Knowledge Module Internals | Oracle-dba

  2. Hi Kshitiz,

    Thanks for your quickly response , still iam facing this issue , because , it didnt implement steps mentioned in this article , my scenario is different what you mentioned,

    from my scheduler it is trying to hit the odi parallel in same database schemas , whether the steps mentioned here are applicable if source and target schemas are in same database

    like stg and ods and edw schema are in same database , as we are trying to create I$ C$ tables for ods schema and edw schema parallelly , then it throws the error

    take example :–

    i had created odi_temp schema for all the I$ and c$ tables for odi work tables creation and droping , And scheduler is hitting the odi at parallel 3 jobs , i have around 300 jobs running daily , daily iam facing this error , 1 or 2 jobs getting failed daily , help me how to solve this issue

    1. ORA-00942: table or view does not exist
    2. ORA-00955: name is already used by an existing object..

    schemas –>ods ,edw schema in same database

    source and target in oracle database…..for this situation whether your tips will be working … reply me asap

    Thanks
    Subbu

    • Subbu,

      If you are parallelly triggering the two interface which can call the same c$ or i$ and so would throw the error. This process will help you but please remember ,ODI will create multiple $ table for each session run and make sure you modify the KM correctly.

  3. Hi Cezar santos,

    iam facing bit relevant issue which you had discussed in this article ,

    Using Job scheduler , we are hitting parallel 3 to 5 jobs to ODI , due to that reason few of my jobs getting failed while creating,dropping ,inserting, I$ tables and C$tables..

    Errors and Warning in ODI success and failed interfaces
    1. ORA-00942: table or view does not exist
    2. ORA-00955: name is already used by an existing object..

    All the success interfaces throws same error message as warning ..

    All the failed interfaces throws error message and got getting failed , this issue is very critical we are facing in prod,

    whether ODI can handle if two different schemas from Same Oracle Database ,try to hit 3 or 5 jobs to ODI for creating and droping the c$,I$,E$ tables

    Really Appreciate your help regarding this , Very Gud Site , which helps lot of ODI developers ..
    Thanks

  4. It seems the above post didn’t like my copy paste! :)

    The changes mentioned are the ones you mention above.
    on the topology:
    E$_

    C$_

    I$_
    and on the KMs:

  5. I have one other question.
    What is the implication of making the changes to the topology:
    E$_

    C$_

    I$_

    but not changing the KM with:

    will it work smoothly or will it raise an error?

    I ask this because I can’t change the default KM but only my own, which is a duplicate of the default. But if someone else uses the same Physical Schema for other projects but doesn’t include that line in the KMs will it work anyway?

  6. Hi Cezar,
    I’ve been working around with this solution but I have a question.
    When you use a non-persistent variable in a scenario that may be run in parallel executions, can one execution replace the variable value of another execution or is it guaranteed that the variable’s value is unique?

    Thanks

  7. Hi Cezar,
    On your post, I have a problem with the C$ work table,
    My interface fails in unload data step(unloading from source view to c$).
    Now, the second session comes and tries to drop the work table.
    But the session hangs up during drop work table step.
    As per your approach, my second session might work smoothly.But the c$ table created by session 1 remains on the DB.and if i have a few failed sessions,the number of unused temp tables on my db piles up.
    How do we overcome this?
    Please correct me if my understanding is wrong.and let me know your suggestoins on the issue i have explained above.

    • Hi,

      All of a sudden every session is hanging on ‘Drop Work Table’. There is no connection established in DB which is trying to drop the work table but in ODI it shows hanging on drop work table and do nothing more.

      Sometimes, it gets past the ‘Drop Work Table’ and then hangs on to ‘Drop Flow Table’. The same drop sql is running fine when executed using SQL Plus but it hangs on ODI.

      Please suggest a solution. Thanks.

  8. Have you tried this in ODI 11? I can’t get it work at all, even with different variation.

    Thanks,
    Exstreamliners

  9. Hi Cesar,
    Thanks for your e-mail.
    I’ve tried to execute the interface alone first and in the scenario. I’ve changed in topology the option for integration “I$_” with “I$_TEST_”, in IKM the ODI create table STG.”I$_TEST_0BATCH”. In second time I’ve tried “I$_1111_”, the ODI create table STG.I$_1111_0BATCH without delimeter.
    I’ve changed the type of the variable String on Long, but I still have the object name with a delimeter.
    Regards,
    Nap.

  10. Hi,

    I have a problem with IKM to create an index because ODI add delimiter “” (STG.”I$_111101_BATCH”_idx). I’ve modified Oracle Technologie and set “Object Delimiter” to nothing, but I still have a same problem.

    Regards,
    Nap.

  11. Hi Tomo,

    You need change the original code from “ ” to ” ”

    I had the same problem when I tried just copy

  12. Hi Cezar,

    I’ve found changing the LKM code to :

    <?
    String v_Sess = "”;
    ?>

    uses the internal interface id instead, which eliminates the problem of the number of tables rapidly growing out of control.

    Thanks
    Matt

  13. Hi Cezar,

    I’ve found that making the small amendment to the LKM code :

    <?
    String v_Sess = "”;
    ?>

    gives me the interface internal id, and therefore will eliminate the problem with table proliferation.

    Strangely, the longer the variable name ( v_Sess), the shorter the temp table name generated

    Thanks
    Matt

  14. Hello,
    i found your solution easy to implement, but i get error during execution:
    com.sunopsis.tools.core.exception.SnpsSimpleMessageException: Error during task interpretation
    Task:11
    java.lang.Exception: BeanShell script error: Sourced file: inline evaluation of: “ String vSess = odiRef.getSession(u201cSESS_NOu201d); ;” Token Parsing Error: Lexical error at line 1, column 35. Encountered: “u201c” (8220), after : “”:
    BSF info: Korak za paralelno izvršavanje at line: 0 column: columnNo
    at com.sunopsis.dwg.codeinterpretor.a.a(a.java)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)
    at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)
    at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)
    at com.sunopsis.dwg.cmd.e.k(e.java)
    at com.sunopsis.dwg.cmd.g.A(g.java)
    at com.sunopsis.dwg.cmd.e.run(e.java)
    at java.lang.Thread.run(Thread.java:619)

    Text:
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)
    at com.sunopsis.dwg.cmd.DwgCommandSession.treatCommand(DwgCommandSession.java)
    at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)
    at com.sunopsis.dwg.cmd.e.k(e.java)
    at com.sunopsis.dwg.cmd.g.A(g.java)
    at com.sunopsis.dwg.cmd.e.run(e.java)
    at java.lang.Thread.run(Thread.java:619)

  15. Hi Cezar,

    Nice idea, but one drawback is the number of E$ tables will grow out of control very quickly. Do you have any thoughts on trying to use this with the OdiRef.getPOP(“I_POP”) to get the interface ID instead? This would seem to give a neater solution, but we get an error when we try this:

    com.sunopsis.tools.core.exception.SnpsSimpleMessageException: Error during task interpretation
    Task:20
    java.lang.Exception: The application script threw an exception: com.sunopsis.tools.core.exception.SnpsSimpleMessageException: The method getPop(“WSTAGE” cannot be resolved by the agent.

    I’ll post again if I can resolve this issue.

    Matt

    • Hi Matt,

      I agree with you, maybe is better do not use this at E$.

      An alternative is add the Session Number into constraint message an let the E$ unique. Make any sense?

  16. Thank you Ken!

    I altered the post!

  17. I was directed to this blog by an Oracle contact who was helping me configure our ODI setup to allow us to pull from multiple sources concurrently and put that data into the same target tables.

    The explanation was rather clear and simple to implement, but I do have to say I fought with syntax for a bit on this:

    until I added a semicolon to end out the command:

  18. Cezar how are you friend.

    I need to run several processes in parallel with the same target, but this technique you describe in your post, for the following reasons:

    1 .- This method creates many tmp tables, “and $_####”,” c $_$$$$”, etc.
    2 .- The number SESS_NO has 7 characters, which makes that it can not create a table of maximum 22 letters (for my 32 are a few letters, imagine 22 letters).
    3 .- When a process fails, this leaves many temporary tables, hardly eliminates them as they are running with Other session.
    4 .- The recycling process may fail because they are “N” tables “E $ _” and always look on the table with the new SESS_NO error and not the previous one (it may modify this KM ….)

    What I have done is change the LKM, CKM and IKM to run in parallel and works well.

    Steps:

    1) adds an option type KM Value each containing an identifier of the table, which is not as big as the sess_no.

    2) In the LKM comnmand where we change the work creates the tables (“C $ _”) add the value of Crado option in step 1, something like this:

    create table
    (

    )

    3) The change in the IKM so that you can integrate different control tables (Insert I $):

    / * DETECTION_STRATEGY = * /
    insert / * + APPEND * / Into
    (
    ,
    IND_UPDATE
    )

    0) (%> select * from (
    select

    ‘I’ IND_UPDATE

    JRN_FLAG

    from
    DW_DATA.C $ _0MD_FINANZAS, DW_DATA.C $ _1MD_FINANZAS_3 * /
    F = A.substring (A.indexOf (txtIzq) +9) / * MD_FINANZAS “,” W “)?> * /
    M = F.indexOf (“,”);
    if (M> 0) (
    A = F.substring (0, M-1) / * MD_FINANZAS * /
    )
    / * VARIABLE A IS THE NAME OF THE TABLE TO * /

    C = odiRef.getFrom () / * READ THE CHAIN FROM:
    % = SnpRef.getObjectName (“L”, “% COL_PRF0MD_FINANZAS”, “Repository OSIPTEL”, “W “)?>, DW_DATA.C $ _1MD_FINANZAS * /
    / * Since only the first API is referenced by using this code to incrementarel suffix * /
    C.indexOf ZIndex = int (A);
    if (ZIndex> 0) (
    C.substring zIzquierda = (0, ZIndex)
    zDerecha = C.substring (ZIndex + A.length ());
    )
    Windex int = zDerecha.indexOf (“?>”);
    if (Windex> 0) (
    C.substring zIzquierda = (0, ZIndex + A.length () + Windex +2);
    zDerecha = zDerecha.substring (Windex +2);
    )
    Suffix = odiRef.getOption (IDENTIF_TABLENAME “);
    zDerecha.replaceAll zDerecha = (A, A.concat (suffix));
    %>

    where (a = 1)

    and NOT EXISTS (
    select ‘X’
    from T
    WHERE

    )

    0) (%>) S
    where not exists (
    select ‘X’
    from T
    WHERE and

    )

    minus
    select
    ,
    ‘I’ IND_UPDATE
    from

    4) CKM logic was changed to eliminate the “E $ _” ORIGIN adding at the table of errors, such as CK comnand Insert Error:

    insert / * + APPEND * / Into
    (
    ROW_ID,
    ERR_TYPE,
    ERR_MESS,
    CHECK_DATE,
    ORIGIN,
    CONS_NAME,
    CONS_TYPE,

    )
    select
    rowid,
    ”, CT_ERR_TYPE
    ”, MESS
    , DEST_DATE_FCT
    ”,
    ”, COND_NAME
    ‘CK’

    from
    where not (

    )

    I would like to send a ZIP with the LKM, CKM and IKM so that you upload to your website.

    Greetings and hope to see you some day in these parts.

  19. Hi Cezar,,
    This was good post.we are facing the problem when trying to run 3 interfaces at one time.All the 3 interfaces are sitting under one project and are associated one Hyperion essbase application.
    Below is the error we always come across when we try to run all at one time.and i tired wt you said in your post..but i’m getting the below error.If i run 1 after other i don’t see any failures..
    Hoping you can help me in this

    The package SFDM_GFee_GfeeAcqsnbase_Load with session ID 6766000 encountered an error and returned with return code -21 and error message -21 : S0001 : java.sql.SQLException: Table already exists: C$_0GFee_GFeeData in statement [create table "C$_0GFee_GFeeData"]
    java.sql.SQLException: Table already exists: C$_0GFee_GFeeData in statement [create table "C$_0GFee_GFeeData"]
    at org.hsqldb.jdbc.jdbcUtil.throwError(jdbcUtil.java:62)
    at org.hsqldb.jdbc.jdbcPreparedStatement.executeUpdate(jdbcPreparedStatement.java:412)
    at com.sunopsis.sql.SnpsQuery.executeUpdate(SnpsQuery.java)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.execStdOrders(SnpSessTaskSql.java)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTaskTrt(SnpSessTaskSql.java)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSqlC.treatTaskTrt(SnpSessTaskSqlC.java)
    at com.sunopsis.dwg.dbobj.SnpSessTaskSql.treatTask(SnpSessTaskSql.java)
    at com.sunopsis.dwg.dbobj.SnpSessStep.treatSessStep(SnpSessStep.java)
    at com.sunopsis.dwg.dbobj.SnpSession.treatSession(SnpSession.java)
    at com.sunopsis.dwg.cmd.DwgCommandScenario.treatCommand(DwgCommandScenario.java)
    at com.sunopsis.dwg.cmd.DwgCommandBase.execute(DwgCommandBase.java)
    at com.sunopsis.dwg.cmd.e.k(e.java)
    at com.sunopsis.dwg.cmd.g.A(g.java)
    at com.sunopsis.dwg.cmd.e.run(e.java)
    at java.lang.Thread.run(Unknown Source)

    Thanks
    Yugi

    • Yugi ,as from the above error it is clear there are no SESSION_NO created in the c$ table . you need to replace work table prefix for the particular physical schema i.e replace C$_ with C$_ [ refer blog for correct syntax ] , and place as your first step in your LKM.
      Reason its running sequential is because at a time ODI is creating and dropping C$_0GFee_GFeeData but in parallel , another interface is also using it for loading purpose and so its failing. Please do the necessary change and it should work. Please feel free to contact us if you require any more help .

Leave a Reply

Required fields are marked *.