Distributed Transaction stage

sheema · Post by **sheema** » Tue Dec 07, 2010 10:19 pm

Hi,

I have a requirement were i have to load data from a dataset to 4 diffrent oracle tables(Master,Detail) which have constraints that detail cannot be loaded until Master is loaded . This has to be achieved in a single datastage job as the data has to happen in a single commit after all the data has been loaded into all 4 tables.

I have seen posts that Distributed transaction stage can be used if the source is a MQ stage. I was just curious if we can use Distributed Transaction stage in my case where the source is a dataset and load into 4 different oracle tables.

Thanks
Sheema

Mike · Post by **Mike** » Wed Dec 08, 2010 6:42 am

MQ (the product) is required to support the Distributed Transaction Stage. I'm not sure whether MQ (the stage) is required to be the source or not.

Mike

eostic · Post by **eostic** » Wed Dec 08, 2010 7:22 am

I haven't had a chance to test it extensively, but in 8.5 the Connectors offer this capability directly. Alternatively, it can be done prior to 8.5 in a Server Job. Each link coming from a single Transformer can go to a separate table with its own SQL, and then a single commit can be utiilized. Been there since about release 4.

Ernie

sheema · Post by **sheema** » Wed Dec 08, 2010 9:11 am

Thanks for your reply.

If we use a server job to build this, i have below 2 questions

1.how can we see that it loads data into the master first and then the detail.

2. How do we do a single commit for all the tables once.

chulett · Post by **chulett** » Wed Dec 08, 2010 10:06 am

You use a single target stage and multiple links from the transformer, then enable 'Transaction Grouping'. Master first via proper link ordering.

sheema · Post by **sheema** » Wed Dec 08, 2010 11:32 am

Thanks for the reply. I will try this option.

sheema · Post by **sheema** » Thu Dec 09, 2010 8:01 pm

That option is working but the job is slow,any suggestions to improve the performance would be great. As Craig has suggested it is a server job a

seq file---->Transformer------->Oracle Oci stage

From transformer i have 4 different links going into Oracle stage and I am inserting data using custom sql statements, I have enabled transaction grouping.
Job is inserting data at 300 rows/sec.

chulett · Post by **chulett** » Thu Dec 09, 2010 10:36 pm

That's because in order to do the 'all or nothing' part of the rollback for each 'transaction group' it forces the Transaction Size to 1.. It's the slowest way to put data in tables but also the only way to accomplish this. Seems like your only opportunity for improvement comes with the 8.5 release... perhaps.