DSXchange

Is it possible to insert rows in more than one table in a single database connection? I have a target stage as Oracle(9i) and it has three tables. What I want to do is that I want to connect to this target Oracle stage only once and insert rows in all three table. Please let me know how I can do this in DS. I am trying to do this because I don't want to commit immediatly after I insert rows in table1. I want to insert rows in all three tables and then do commit (I want to commit only after I successfully insert rows in all three tables). Thanks.

Unless this is horribly different in PX than Server, simply run three links to a single stage. All will share a single database connection. You will still have issues with trying to do an 'all or nothing' approach, as failures in one link won't necessarily keep the other two from commiting, unless you do something 'drastic' like having the job abort on your first reject.

The 'use Transaction Grouping' answer doesn't really work for this (does it exist in PX?), as it requires a commit level of 1 - meaning each 'set' of rows must be committed one at a time.

OK. Now, if I want to catch the 'abort' signal (Do I have to use unix shell script or DS BASIC routine), how would I do that? Do you think 'catching abort signal and issuing rollback' seems to be possible solution? Could you give me some outline on how I should approach this? Thanks for your time.

By the way, 'Transaction Control' is not displayed on the stage properties. I think, we have to do something with administrator to set 'Transaction Control' in PX.

Unfortunately, PX seems to handle Rejects in a completely different manner than Server jobs do and I have no PX experience. You'll need to wait for one of the PX Heavyweights like TJ or BigPoppa to chime in on this one.

To get a head start, you may want to search this and the DataStage forum for discussions on handling Rejects in PX, I seem to remember this being talked about several times in recent days.

In PX, it is not allowing me to have more than one link to the target Oracle stage. Any other way by which I can achieve this? THanks

Hi Sim,
the best way to write DS jobs is to have them completely restartable from the beginning of the job because you really do not get very good control around 'transactions' against many tables in a database.

This usually means that when you are inserting records use the insert then update option. If something goes wrong then you can restart the job...

Although it takes a longer elapsed time, I am a pretty big fan of writing output to a flat file and then loading the flat file. Where I can get errors I use insert/replace, where I am not expecting any errors, or I only commit the whole batch I use the loader...

Best of luck.

You can execute a user-defined sql statement within some of the PX database stages. You could try executing all three inserts in the user-defined sql section and put a commit at the end.

If you're pushing a large amount of data, Peter Nolan's suggestion is a good idea.

- BP

[quote="sim"]By the way, 'Transaction Control' is not displayed on the stage properties. I think, we have to do something with administrator to set 'Transaction Control' in PX.[/quote]


yes, it was discussed on this forum before.. check this viewtopic.php?t=86747