Executing everything in one transaction
Moderators: chulett, rschirm, roy
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.
Regards,
Nick.
Nick.
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.
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.
Regards,
Nick.
Nick.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Gautam Handa
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: