Transaction Handling

calvinlo · Post by **calvinlo** » Thu Aug 14, 2003 3:45 am

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

roy · Post by **roy** » Thu Aug 14, 2003 4:56 am

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.

chulett · Post by **chulett** » Thu Aug 14, 2003 6:28 am

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

calvinlo · Post by **calvinlo** » Thu Aug 14, 2003 7:42 pm

I have staging tables and now it is the apply stage. Records in one table in staging table needs to populate into two tables in system of records. So not multiple links input to single stage, instead is one input to multiple stage.
Hope you all could help me.

Thanks,
Cal

ray.wurlod · Post by **ray.wurlod** » Thu Aug 14, 2003 11:58 pm

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

calvinlo · Post by **calvinlo** » Fri Aug 15, 2003 12:38 am

Thanks.
But there are many cases that data from staging table populate to several tables in SoR. How can i handle this?

Cal

ray.wurlod · Post by **ray.wurlod** » Fri Aug 15, 2003 3:36 am

The table name is a property of the link, not of the stage.
A stage connects to a database instance. A link connects to a table.