DSXchange

Hi

how can we insert data into 2 different tables from transformer in a single transaction?

Scenario : Think of Master table and child table. From seq file i am getting data to both Master and child in a single row, i am separating the data and would liek to insert master data first and up on success insert the child records(multiple records) in a single transcation?

Any help would be greatly appreciated.

Take the master and child links into a single ODBC stage. Within that ODBC stage set up a "grouped transaction".

Hi Zenith,

As per your suggestion, tried to insert data into Master & Detail table using
Oracle OCI stage by checking Enable Transaction Grouping property in Transaction Handling tab.
However i am facing issues while loading data into master table.

For Example
Source Data :
col1 Col2 Col2
1 aa bb
2 bb cc

Master Table : Table1
Detail Table : Table2

In Transformer, condition used for Master Table link is

trim(Source col1)<>trim(lkp col1)

condition for Detail link is

trim(Source col1)=trim(lkp_col1)

Before first run there is no data in both Master & Detail table.
However, For First time run Data is not populated in the Master table even though Link contrain is satisfied.

Can you please suggest to how to handle this issue


Thanks a lot in Advance.

Different issue now. And you haven't explained the source or nature of this "lookup column" in your constraint. How is it derived?

Hi,

Source is Flat File and Look up is based on Master Table(Key column col1).
Before 1st run, lookup contains no records. Therefore in 1st run all source records has to be loaded in the Master Table (by satisfying Master Table link condition). Again in 2nd run, if the source records exists in the master table then it has to load in the Detail table, also if there are any new records in the 2nd run source file then those records has to be loaded in the master table.

Thanks in Advance.

You need to check the success or failure of the lookup, not any kind of equality test. A lookup failure returns NULL values which will cause your contraints to always fail. Test for that instead.

Hi Chulett,

Thanks a lot. Issue is resolved :D .

Thanks a lot Ray.. I never thought that we can do transaction handling in such a way using datastage. Thanks again

Thanks for the information.

Never know that we can do that in DataStage...