DSXchange

Dear DSXchange readers,

First I would like to intoduce myself, I'm a J2EE developer (Switzerland) which came to the pleasure to work with datastage. At the beginning it was not really simple but this forum helped me a lot (I have started reading before 2-3 months), thx to everyone who make this forum living!!!
So now it's time for my first post/question :D

Environment:
Datastage Version 7.51a
Operating System: W2K Server
Database: Oracle 10g

Jobdescription:

So we have two output links from one transformer. The sequence is relevant and I'm able to control that over 'Output Link Execution Order', so I can ensure that OracleOCIStage_1 is executed before OracleOCIStage_2 (on record level) . Another requirement is that the data written from OracleOCIStage_1 has to be committed before OracleOCIStage_2 writes to the database due to referential integrity (they write to different tables). Therefore I have to set the transaction size to 1 that the data is commited from OracleOCIStage_1 before OracleOCIStage_2 writes data to a table (that an assumption). For performance reason a transaction size of 1 is not really preferable.
Is there any other solution than splitting the job into two jobs (one for the inserts from OracleOCIStage_1 and one job for the OracleOCIStage_2 inserts)?

Thanks for your help and best regards
Peter

Peter - use a single OCI stage rather than two separate ones. That way all your transactions will occur as a single 'unit of work' and you won't have to commit on every record.

Actually splitting this process into two jobs would save you a lot of your problems. Is there a business reason why you would not want to create two simple jobs to handle this as opposed to one job?

You could however write to your first table to establish the referential integrity and then within the same job insert a transform at the end and call your hashed file a second time within the same job and write to your second OCI stage. The catch to this is that you will have to create a dummy call on the output of your initial OCI stage (select count(*)) something that does not return high overhead then within your joining transform make sure that you add @OUTROWNUM=1 to your constraint. This transform is essentially doing nothing but linking your first job to your second. The beauty of it is that the second job will not run until the first piece has finished.

Hashed File ---> Tranformer ---> OracleOCIStage_1 ---> DummyTranformer --->
Hashed File ---> Tranformer ---> OracleOCIStage_2

Hope this helps!

Peter wrote:Dear DSXchange readers,

First I would like to intoduce myself, I'm a J2EE developer (Switzerland) which came to the pleasure to work with datastage. At the beginning it was not really simple but this forum helped me a lot (I have started reading before 2-3 months), thx to everyone who make this forum living!!!
So now it's time for my first post/question :D

Environment:
Datastage Version 7.51a
Operating System: W2K Server
Database: Oracle 10g

Jobdescription:

Code: Select all

Hashed File ---> Tranformer ---> OracleOCIStage_1
                            ---> OracleOCIStage_2

So we have two output links from one transformer. The sequence is relevant and I'm able to control that over 'Output Link Execution Order', so I can ensure that OracleOCIStage_1 is executed before OracleOCIStage_2 (on record level) . Another requirement is that the data written from OracleOCIStage_1 has to be committed before OracleOCIStage_2 writes to the database due to referential integrity (they write to different tables). Therefore I have to set the transaction size to 1 that the data is commited from OracleOCIStage_1 before OracleOCIStage_2 writes data to a table (that an assumption). For performance reason a transaction size of 1 is not really preferable.
Is there any other solution than splitting the job into two jobs (one for the inserts from OracleOCIStage_1 and one job for the OracleOCIStage_2 inserts)?

Thanks for your help and best regards
Peter

What problems? Using one OCI would solve all of them in one swell foop.

Hmmm, loading parents simultaneously with children. I'd think a best practice would be to do parents first, then do children. You'll benefit from dedicated mass loading of a single table instead of straddling two tables. But if what you're doing is more "real-time" microbatch using Server rather than TX I guess I can see your desire.

But, to do what you want just use one OCI stage with two links and check the enable transition grouping button. Magic things happen in the Oracle OCI stage. Check it out.

I understand your point, Ken. In most cases, yes - split the two and power load them separately. Just wanted to point out that in this (seemingly small) case, using one OCI stage would do the trick. And no 'transaction grouping' needed as that would force a commit level of 1 for a net gain of zero. It will work 'fine' without it.

True Craig, but if rollback space does not allow commit at the end you'll have to commit as you go. Will that still work if you do periodic commits on separate links? I think you'll get into some issues there. I believe with double, triple, or more links you could get into a situation where you're running some rollback risks.

Since I don't do things this way, I'm just guessing at theory.

Yes, you'll have balance the commit and rollback issues. I've done this before but always with a commit level of zero - all or nothing - so it is all done as a single transaction across the FK'd tables. A lack of UNDO space then becomes a DBA problem.

WOW thanks a lot for all your input! I will try the different solutions mentioned and let you know what I have choosen.

Best regards
Peter

See what happens when you go away for a day and don't check in on the kids once in a while?