ODIExperts.com

The blog for Oracle Data Integrator ( ODI )

Direct load by interface with no work tables (C$, I$)

Hi friends…

I got this video from Craig Stewart that shows us how to direct load data from source to target with no work (C$ and I$) table use.

http://s3.amazonaws.com/Ora/ODI-Simple_SELECT_and_INSERT-interface.swf

Simple, fast and very clever!

Thank you for share your expertize Craig!

13 Comments

  1. IKM SQL to SQL append not present in 11.1.1.7. need help.

  2. Hi,

    I am trying to direct load in my ODI interface without work tables (C$ and I$). I am using ODI version 11.1.1.7.

    I am not able to find out Staging area : MYSQL:MYSQL Source
    and
    Not able to find out IKM SQL to SQL append

    Is it not possible in ODI version 11.1.1.7 or should I need to import any IKM or any drivers to achive this requirement.

    Please help me on this.

  3. Hi ,

    I have a requirement wherein I need to avoid C$ and I$. But in my case, I have fetch/extract data from many tables and I don’t want to create staging in Source db as my source application is a web commerce portal and the portal team does not recommend to create staging area on an OLTP db as it might have a un-nesessary IO. The interface will run on a daily basis and it will extract around 200 thousand records.

    Do you know if there is any other way of bypassing I$ and C$.

    • Hi,

      it’s a simple KM customization where you alter the “target” in the LKM to be the “real” target and, for IKM kill all steps.

      BUT, it’ll work only if you have every single data transformation, filters or joins mapped to be executed at source.

      Let me know if works and thank you for visit us!

      • Hi.

        Sorry, I didn’t understand what you mean by customizing the KLM to a “real” target. Can you pls give an example.

        Since my source area has tables from different data servers, I don’t think we can bypass C$table. Correct? Please do let me know.

  4. Hi ,

    I have tried with SQL Server (source database) to Oracle (target database) and Oracle (source database) to Oracle (target database) but in flow tab we should select LKM also or else while saving the interface it is showing error pop up box.

    Could you please suggest me to avoid the C$ table or how to get bulk data from source database instead of 30 records every time.

    ODI installed on Oracle 10g.

    Thanks and Regards,
    Shaik.

    • Hi,

      the amount of data that is fetched from some connection is controlled in the correspondent Data Server (Topology). You can change it over there.

      About the Bulk SQL, if you have distinct databases instances as source and target unless it possible to build some kind of “bridge” (DBLink, External Table, etc) it’s necessary go thru some text files to get a fast integration. But if a bridge is possible, then is just indicate it at ODI and use the right LKM.

      Make sense?

      Thank you for visit us!

  5. Raul can you be more specific on how to modify “generate ctl ” step.

    Thanks

  6. hi, i was use another method.
    working with sql loader integrated feature, (file to oracle), i modified the lkm specific “generate ctl ” step , i change odi variable to load data directly to target table rather than temp c$ table.

  7. Yes indeed, this has always been available, it is just one of those “you gotta know it to use it!” things.
    I hope you found this useful.

  8. Is it working only in ODI 11g or is it possible in 10g also ?

Leave a Reply

Required fields are marked *.