IKM Oracle Multitable Insert with Filters

IKM Oracle Multitable Insert with Filters:

Today we are going use IKM Oracle Multitable Insert with filters. This is normally used to load data from one source to multiple target table. Thanks to  OdiExperts.com team for writing a beautiful article on this.

Well I got to know from the comments section that many people are interested to implement their own filters in interace with the IKM Multi Insert. Now if you will add filter to each of interface, do you think it will work out? Nope 🙂 . The process will load correct data to 1st target table but not to the rest tables. Why is it so ????

 

Lets apply a small trick.

Source table: EMP
Target tables: EMP_TGT1,EMP_TGT2,EMP_TGT3

Create following interfaces as given.

: parallel_load_emp_tgt1
: parallel_load_emp_tgt2
: parallel_load_emp_tgt3

LKM: if required select desired LKM
IKM: IKM Oracle Multitable Insert
CKM: CKM oracle
Reverse engineer all 4 tables and do the normal mapping.
ikmmultitableinsert1
ikmmultitableinsert2
ikmmultitableinsert3
ikmmultitableinsert4
ikmmultitableinsert5
ikmmultitableinsert6
Now create a package and connect them sequentially. Next run the the package.
ikmmultitableinsert6_1
As you can see there is one filter in the select query which is the filter in 1st interface. Here it wont select data for rest interface. Any how we need to delete this filter so that it will select all data and it will use respective filter at the time of loading.
ikmmultitableinsert7
ikmmultitableinsert8
ikmmultitableinsert9
ikmmultitableinsert10
Duplicate your IKM oracle Multitable Insert. Then go to the Define Query tab. Now scroll down and remove the filter (Query.append( odiRef.getFilter(i) +nl);) as given in screenshot and then save it.
ikmmultitableinsert11
Now execute your package.
ikmmultitableinsert12
Now you can see the data loaded to corresponding target table as per your filters respectively.
Thats all for today 🙂

Comments are welcome.

0

About the author

Bhabani(http://dwteam.in) - Currently Bhabani is working as Sr Development Engineer at Harman International. He has good expertise on Oracle, Oracle Data Integrator, Pervasive Data Integrator, MSBI, Talend and Java. He is also contributing in ODI-OTN forum for last 5 years. He is from India. If you want to reach him then please visit contact us page. If you have any doubts or concerns on the above article, please put your question here. Dw Team will try to respond it as soon as possible. Also dont forget to provide your comments / suggestions / feedback for further improvement. Thanks for your time.

Similar Posts

29 comments

hi, can we try this for different structure of target table..Its working fine for same structure as i have tested. Also is their any limits on number of target table?

Hi Durga thanks for visiting my blog.
Yes, you can use different table with different structures. You may or may not include filter as well. Have fun 🙂

Hi,
I am trying to do the same but getting some error. please give me any suggestion.

ERROR:
DI-1228: Task EMP_LOAD_3 (Integration) fails on the target ORACLE connection ODI_STAGE.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: “FIRST_NAME”: invalid identifier

Thanks.

Hi Dhananjay, There might be some problem with your 3rd interface as i can see Task EMP_LOAD_3…Try dropping all temp tables and verify the table structure. It will be better if you can run the generated query in toad/sql developer. That will be easiest way to debug the error.

Hi Bhabani,
I copied that code into SQL developer now I got the solution. Thanks!!

Regards,
Dhananjay.

Anindya Chatterjee / Reply

Hi Bhabani,
I have a question why do you select “define query” true for the first interface and “execute” true for the last interface.
Thanks
Anindya

It means you are defining the select statement which will be used in your insert all sql statement. You can see the last image for generated query aswell.

Anindya Chatterjee / Reply

Hi Bhbani,
Now its clear but another question comes into mind. 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?

Nice question.Just ask yourself you will get the answer. If you use IKM Sql Control Append thats fine but in this case you made 3 different interface which will run in their own sessions it means if one interface is creating 20 steps then in total you have now 20*3=60 steps. But if you are using IKM MTI then you have just 20 sessions which is loading to 3 different table and obviously very faster than your approach.

Anindya Chatterjee

Hi Bahabni,
you are also creating 3 interfaces.Does the all interfaces work in parallel when you use “IKM MTI” ?
Or any other reason is there that only 20 steps are created instead of 60??
Please Reply .

As i said earlier this is just you can generate oracle specific DML for inserting into multiple target tables from a single query.

Samarendra Sahoo / Reply

Hi Bhabani,

Thanks for this clarifications.

i tried to load from one table to 3 different table with IKM Multi target, but it’s failing.

my requirement is the source table is in SRC Schema and TRG1,TRG2,TRG3 in TRG schema.

so how can we achive this with IKM Multi target.

What do you mean by failing ? Can you clarify with the error ? There is no issue even if they are in different schema but you need to ensure that the last interface target schema should have the privilege to insert data into the target table of 1st and 2nd interface. Because the insert all query will executed only in the last interface.

The error is ‘Table or view doesn’t exist’. Can you please suggest how to ensure that the last interface target schema should have the privilege to insert data into the target table of 1st and 2nd interface.

Can we do the same exercise for a scenario where source is table and target is multiple files? We also need dynamic sql kind of thing, where sqls can be varied based on the target system file being generated?

I dont think thats possible through KM’s. However there are few ways to achieve the requirement. You can use the above process to load data into multipe tables and then load those tables into multiple files.

Another way : Create a temporary interface to use it as source and file as target for another interface. I will consider that you have idea to make the target file name dynamic (i.e. filename will be given in run time based on certain variables)

Now drag this interface multiple times to your package. Create a table in backend with clob datatype. This column will keep the select statement for 1sty temporary interface. similarly another row will keep ,mofified select statement for second temp interface and goes on.

Now come back to package. Put one referesh vraible in the 1st step which will select the query from backend table. Something like SELECT QUERY1 FROM QUERYTABLE WHERE SLNO=1. Use this refresh variable in the temp interface. Repeat this as many times you have interfaces in the package (SELECT QUERY1 FROM QUERYTABLE WHERE SLNO=2).

In this way you need not to modify the package. You just need to modify the select statement in backend and same will be reflected in ODI

Anindya Chatterjee / Reply

Hi Bhabani,
If I can achieved this using IKM Oracle Incremental Update.Then why I’ll use IKM Oracle Multitable Insert.
What its’ advantage over IKM Oracle Incremental Update.

Thanks
Anindya

Anindya Chatterjee / Reply

Hi Bhabani,
Please update?
Thanks
Anindya

Multitable insert is used only when you are interested to insert a single source table data in to multiple table but incremental update is used to insert and update to a single table. You can also use IKM Oracle Incremental Update (MERGE)instead of IKM Oracle Incremental Update. It depends which one is faster for you.

But i can do the multi table insert (single source table to multiple table insert) through PACKAGE with IKM oracle incremental update, Right?

Hi Bhabani

I am new to ODI. I am trying to practice this exercise. When I am trying to import KM IKM Oracle Multitable Insert. It is not available. I am using Standalone Edition Version 11.1.1. Please help if there is any source/link we can download this KM from?

Regards

I have sent to your mail.
Thanks

Hi Bhabani

Thanks a lot indeed for your help and and quick turn around. You are REALLY QUICK dear.

Hope to get more of blogs from you on ODI.

thanks again:)
Regards
Vishal

hi can you answer the below question
Which profile required to access Models in Designer tool?
Which profile required to access solution in Designer tool?
Which profile required to access ODI Console?

Hi , I have a scenario where i should use dynamic sql to populate temp tables and for the next time i run the same job i should drop the target temp table and create a new temp table with the same name and then use the dynamic sql again to insert the records in the temp table.

Kindly help.

Dear Bhabani,
I have a question.
For the first interface you have set Define Query to true.
But for the other interfaces Define Query has been to false.
Could you please explain this?
I mean why not Define query true for all the interfaces and just execute option set to true for the last interfaces only.
Thanks
Papai

Is it true that to perform the Multi Table Insert like this, both SOURCE and TARGET should be under SAME DATA SERVER?

Hi Bhabhani,

Beginners should be blessed to have you for giving such articles.

I have some doubts on the above.
1. From the Description in Defintion of IKM Oracle Multi Table Insert, there it is been cleary stated that
DEFINE_QUERY: Set to Yes if this interface describes the source query (SELECT clause of the statement). This interface must have a temporary target.
which means there should be a temporary ( yellow interface) should be defined while using MTI.
but in your case you are not using temporary interface 🙂 .. could you explain me why.? 🙁

2. please refer this link https://blogs.oracle.com/warehousebuilder/entry/odi_11g_oracle_multi_table
there they have developed a package with first step as temporary interface and rest are normal. I have also followed the same. now I can load the data without removing (Query.append( odiRef.getFilter(i) +nl);) from DEFINE QUERY step.
Can you please clarify on why you removed the code (Query.append( odiRef.getFilter(i) +nl);) from it.
3. sorry to ask. ( I am a beginner to ODI ) why oracle and you are suggesting to keep define query as yes and execute as YES… this execute is been stated in description as such it should run the insert execute statement .

Thanks,
Santhosh S.

Hi Santosh,
Here are the explanations.
#1. I am telling ODI is that first interface loads data to a table. If you make it as yellow interface ODI will not consider it as target and it will not execute it. It will create the query for reuse.
#2. Thats correct. If you do not want the first interface to load data to a physical table then using yellow interface is the right approach. In my case ODI uses the select statement from first interface including the filter. Hence the subsequent interface never gets the data it needs. That the reason I modified the KM not to add any filter during select rather use it during insert.
#3. That means prepare the query for all the steps except the last one and execute it at the end.

Leave a replay to suresh Cancel reply

required*

Are you a human? *