ODI 11g – Oracle Multi Table Insert

With the IKM Oracle Multi Table Insert you can generate Oracle specific DML for inserting into multiple target tables from a single query result – without reprocessing the query or staging its result.

When designing this to exploit the IKM you must split the problem into the reusable parts – the select part goes in one interface (I named SELECT_PART), then each target goes in a separate interface (INSERT_SPECIAL and INSERT_REGULAR).

So for my statement below…

/*INSERT_SPECIAL interface */ insert  all
when 1=1 And (INCOME_LEVEL > 250000) then
into SCOTT.CUSTOMERS_NEW
(ID, NAME, GENDER, BIRTH_DATE, MARITAL_STATUS, INCOME_LEVEL, CREDIT_LIMIT, EMAIL, USER_CREATED, DATE_CREATED, USER_MODIFIED, DATE_MODIFIED)
values
(ID, NAME, GENDER, BIRTH_DATE, MARITAL_STATUS, INCOME_LEVEL, CREDIT_LIMIT, EMAIL, USER_CREATED, DATE_CREATED, USER_MODIFIED, DATE_MODIFIED)

/* INSERT_REGULAR interface */ when 1=1  then
into SCOTT.CUSTOMERS_SPECIAL
(ID, NAME, GENDER, BIRTH_DATE, MARITAL_STATUS, INCOME_LEVEL, CREDIT_LIMIT, EMAIL, USER_CREATED, DATE_CREATED, USER_MODIFIED, DATE_MODIFIED)
values
(ID, NAME, GENDER, BIRTH_DATE, MARITAL_STATUS, INCOME_LEVEL, CREDIT_LIMIT, EMAIL, USER_CREATED, DATE_CREATED, USER_MODIFIED, DATE_MODIFIED)
/*SELECT*PART interface */ select   
    CUSTOMERS.EMAIL EMAIL,
    CUSTOMERS.CREDIT_LIMIT CREDIT_LIMIT,
    UPPER(CUSTOMERS.NAME) NAME,
    CUSTOMERS.USER_MODIFIED USER_MODIFIED,
    CUSTOMERS.DATE_MODIFIED DATE_MODIFIED,
    CUSTOMERS.BIRTH_DATE BIRTH_DATE,
    CUSTOMERS.MARITAL_STATUS MARITAL_STATUS,
    CUSTOMERS.ID ID,
    CUSTOMERS.USER_CREATED USER_CREATED,
    CUSTOMERS.GENDER GENDER,
    CUSTOMERS.DATE_CREATED DATE_CREATED,
    CUSTOMERS.INCOME_LEVEL INCOME_LEVEL
from    SCOTT.CUSTOMERS   CUSTOMERS
where    (1=1)

Firstly I create a SELECT_PART temporary interface for the query to be reused and in the IKM assignment I state that it is defining the query, it is not a target and it should not be executed.

Then in my INSERT_SPECIAL interface loading a target with a filter, I set define query to false, then set true for the target table and execute to false. This interface uses the SELECT_PART query definition interface as a source.

Finally in my final interface loading another target I set define query to false again, set target table to true and execute to true – this is the go run it indicator!

To coordinate the statement construction you will need to create a package with the select and insert statements. With 11g you can now execute the package in simulation mode and preview the generated code including the SQL statements.

Hopefully this helps shed some light on how you can leverage the Oracle MTI statement. A similar IKM exists for Teradata. The ODI IKM Teradata Multi Statement supports this multi statement request in 11g, here is an extract from the paper at www.teradata.com/white-papers/born-to-be-parallel-eb3053/

Teradata Database offers an SQL extension called a Multi-Statement Request that allows several distinct SQL statements to be bundled together and sent to the optimizer as if they were one. Teradata Database will attempt to execute these SQL statements in parallel. When this feature is used, any sub-expressions that the different SQL statements have in common will be executed once, and the results shared among them.

It works in the same way as the ODI MTI IKM, multiple interfaces orchestrated in a package, each interface contributes some SQL, the last interface in the chain executes the multi statement.

Comments:

Hi David,
I'm a new user of ODI and I implemented something similar to this. I am facing a serious issue when an error occurs during execution (e.g. a primary key violation), then the whole execution fails and the error is not logged in the appropriate $Error table.
When I change the IKM to "Incremental Update" then the errors get logged correctly.
Do you have any idea about what could be causing this?

Posted by Gabriel on November 14, 2012 at 02:51 AM PST #

Hi Gabriel

Its the IKM that does all the error handling work- if you don't incorporate the flow control pattern into the IKM you won't get the error handling. In the shipped IKM Oracle Multi Table Insert there is a restrictions section in the KM's description which states 'Flow/static control is not supported'. If you are using this, then that is why, if you have built your own then you may have not included or thought about the flow control.

Cheers
David

Posted by David on November 14, 2012 at 10:32 AM PST #

Thanks for the response David!
I am using the IKM Oracle Multi Table Insert, so I guess I will need to build something custom to keep the Multi Table Insert and achieve the logging in the error tables also.

Posted by Gabriel on November 14, 2012 at 11:13 PM PST #

Hi David,
To achieve this result if we select only “IKM SQL control Append” for this 3 interfaces and we don’t do the changes like select “define query” to true & “execute” to true will it cause any problem?If yes then please describe?As I’m fresher I don’t know why you select “IKM Oracle Multitable Insert” instead of “IKM SQL control Append” please clarify?

Posted by saurabh on February 16, 2013 at 12:01 PM PST #

Hi Saurabh

If you just have 3 interfaces using IKM SQL Control append you will do 3 separate DML statements (3 inserts). So may reprocess the source data 3 times. Multi table insert is an Oracle database optimization.

Cheers
David

Posted by David on February 17, 2013 at 10:13 AM PST #

Thanks David :)

Posted by saurabh on February 17, 2013 at 10:25 AM PST #

Hi David,

when I am trying to execute this same thing I am getting an error as follows:

"ORA-00904: "TEMPORARY_CUSTOMER"."Income_level": invalid identifier"

there is also a small query regarding the screen shot given above and the documentation:

In the screen shot the filter is placed in insert regular interface, whereas in the documentation the filter is placed in the insert_special interface, so which one is correct?

I am not getting why I am getting this error.

My code is like this:

insert all

when 1=1 And (TEMPORARY_CUSTOMER."Income_level">25000) then
into ABC.CUSTOMER_SPECIAL
(CUST_ID, CUSTNAME, C_MAIL, DEPT_NO, INCOME_LEVEL)
values
(TEMPORARY_CUSTOMER."Cust_id", TEMPORARY_CUSTOMER."Cust_name", TEMPORARY_CUSTOMER."C_Mail", TEMPORARY_CUSTOMER."Dept_no", TEMPORARY_CUSTOMER."Income_level")

when 1=1 then
into ABC.CUSTOMER_REGULAR
(CUST_ID, CUSTNAME, C_MAIL, DEPT_NO, INCOME_LEVEL)
values
(TEMPORARY_CUSTOMER."Cust_id", TEMPORARY_CUSTOMER."Cust_name", TEMPORARY_CUSTOMER."C_Mail", TEMPORARY_CUSTOMER."Dept_no", TEMPORARY_CUSTOMER."Income_level")

select
CUSTOMER.CUST_ID "Cust_id",
CUSTOMER.CUSTNAME "Cust_name",
CUSTOMER.C_MAIL "C_Mail",
CUSTOMER.DEPT_NO "Dept_no",
CUSTOMER.INCOME_LEVEL "Income_level"
from ABC.CUSTOMER CUSTOMER
where (1=1)

Posted by Anindya gayen on July 22, 2013 at 04:06 AM PDT #

Hi Anindya

The filter for the insert parts of the MTI go in the 'insert' interfaces.

It looks like there is a bug in the IKM using mixed case names for columns in the temporary interface that defines the select clause. Can you try using upper case names, and ensure the insert interfaces that use it are in sync and the filter expressions that reference those columns are updated? I can get the code to work that way, and I can recreate your problem using mixed case names.

Cheers
David

Posted by David on July 22, 2013 at 10:25 AM PDT #

Thank you David.

Yes after changing the columns in the temporary interface to upper case it is working fine.

Posted by Anindya gayen on July 22, 2013 at 11:15 PM PDT #

Hi David,

There is a small query.

Is there any mechanism to use a file and a table in the target side, where there are three tables in the source joined by PK-FK relationship.

Thanks.

Posted by Anindya gayen on July 23, 2013 at 08:04 AM PDT #

Hi Anindya

The common source would need to be modeled either as a view or a temporary target and then multiple interfaces consuming it.

Cheers
David

Posted by David on July 23, 2013 at 08:15 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

ETL, CDC, Real-Time DI and Data Quality for the Oracle Database from the inside.

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today