Hi all,
I have a server job with one input, one transformer and two outputs for two tables. I would like to know how DataStage handle transaction. e.g. If one of the table fails to insert, could it rollback two tables?? can i make it one single transaction?
I am using DB2.
Thanks,
Cal
Transaction Handling
Moderators: chulett, rschirm, roy
Hi,
what is your specific configuration?
In General,
Each stage is an independant connection to the DB,
hence there is no real way to bind the 2 in the same transaction using the graphic GUI.
(I do Think, though I'm not sure, that there is a certain stage which you could use several links to the same stage and get what you need.)
in case you have staging area you don't really have a problem since a partial load won't be transfered to the production by you and an abort would be raised, you could then rerun the entire process again and only upon successfull run continue.
if you don't have a staging area:
what you could try is prepare ascii files and a single sql load script which will unite the 2 loads in the same transaction, a rollback in case any of the sql statements failed should be automatical (though you might need to find the exact syntax).
then you need to invoke a command line utility of your DB provider to load/bulk-load the file/s using the script you built.
Good Luck,
Roy R.
what is your specific configuration?
In General,
Each stage is an independant connection to the DB,
hence there is no real way to bind the 2 in the same transaction using the graphic GUI.
(I do Think, though I'm not sure, that there is a certain stage which you could use several links to the same stage and get what you need.)
in case you have staging area you don't really have a problem since a partial load won't be transfered to the production by you and an abort would be raised, you could then rerun the entire process again and only upon successfull run continue.
if you don't have a staging area:
what you could try is prepare ascii files and a single sql load script which will unite the 2 loads in the same transaction, a rollback in case any of the sql statements failed should be automatical (though you might need to find the exact syntax).
then you need to invoke a command line utility of your DB provider to load/bulk-load the file/s using the script you built.
Good Luck,
Roy R.
Roy - that 'certain stage' you mention are the Oracle OCI stages. They allow this functionality when multiple links come into a single OCI stage from the same transform. Note that the option (known as Transaction Grouping) *only* shows up in the above situation.
Are you sure this isn't an option for DB2?
-craig
Are you sure this isn't an option for DB2?
-craig
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You don't get transaction grouping with multiple stages. You can only get transaction grouping with multiple inputs into the same passive (for example ODBC) stage.
You can have multiple outputs from a Transformer stage running into the same passive stage.
+------+ +------+
| | -----> | |
----->| Tfmr | -----> | ODBC |
| | -----> | |
+------+ +------+
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
You can have multiple outputs from a Transformer stage running into the same passive stage.
+------+ +------+
| | -----> | |
----->| Tfmr | -----> | ODBC |
| | -----> | |
+------+ +------+
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: