Insert rows in more than one tables

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
sim
Participant
Posts: 13
Joined: Wed Feb 18, 2004 6:20 pm

Insert rows in more than one tables

Post by sim »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
sim
Participant
Posts: 13
Joined: Wed Feb 18, 2004 6:20 pm

Post by sim »

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.
sim
Participant
Posts: 13
Joined: Wed Feb 18, 2004 6:20 pm

Post by 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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
sim
Participant
Posts: 13
Joined: Wed Feb 18, 2004 6:20 pm

Post by sim »

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
peternolan9
Participant
Posts: 214
Joined: Mon Feb 23, 2004 2:10 am
Location: Dublin, Ireland
Contact:

Re: Insert rows in more than one tables

Post by peternolan9 »

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.
Best Regards
Peter Nolan
www.peternolan.com
bigpoppa
Participant
Posts: 190
Joined: Fri Feb 28, 2003 11:39 am

Insert rows in more than one tables

Post by bigpoppa »

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
jreddy
Premium Member
Premium Member
Posts: 202
Joined: Tue Feb 03, 2004 5:09 pm

Post by jreddy »

[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
Post Reply