Executing everything in one transaction

nick.bond · Post by **nick.bond** » Wed May 09, 2007 9:13 pm

I would also go along the lines of stored procedure for this one. You're not doing anything with the data so there's no real advantage of using datastage, it's just a step that needs to be executed. The database is better suited to performing this operation and you should get more control over rolling it back as one transaction.

Gautam19 · Post by **Gautam19** » Wed May 09, 2007 9:20 pm

Yes, the only thing is, since we already have an existing code using DS in place, I was hoping if we could have twisted or tweaked something to achieve what we wanted, rather than re-doing everything.

nick.bond · Post by **nick.bond** » Wed May 09, 2007 9:55 pm

If it was Oracle you could use Transaction grouping so that all the delete statements could be rolled back together. Unfortunately I don't know much about the Informix stage, the only thing I could recommend to make it work would be to make sure your link ordering is correct for the order you want to delete records, then set the Array size and Transaction size = 1.

This does mean that if it aborts it will not have rolled back all the transactions because your Transaction size = 1.

Can you check whether you are starting a separate session for each link to BD you have? I imagine you might be, in which case I don't see how you can control all the delete statements as if they are one transaction as each session will act independently.

Sorry can't be more help.

ray.wurlod · Post by **ray.wurlod** » Thu May 10, 2007 12:30 am

Does not the ODBC stage also support Transaction Grouping?

Gautam19 · Post by **Gautam19** » Thu May 10, 2007 12:58 am

ODBC stage does support transaction grouping. But that was another questions I had....
Would it be advisable to have all 50 links going into one ODBC stage. Will it open a new DB connection for every link.
But yes ODBC stage will solve our problem , only if use just one stage having all links to it.

trobinson · Post by **trobinson** » Thu May 10, 2007 9:31 am

I would probably go with transaction grouping but another possible method would be to capture the REJECTEDCODE/REJECT of each preceding link and coding a utility job abort if any of the 15 particular ODBC stage links returns something other than success.

ray.wurlod · Post by **ray.wurlod** » Thu May 10, 2007 4:46 pm

You can resize the stage icon to make the larger than usual number of input links easier to draw and clearer to caption and maintain.