Transaction Management on the target database

[turash]

Scenario:

Transferring transactions/data between two transactional databases.

Transaction Management on the target database (Loading): If a unit of work requires completion of inserting data into multiple tables then, how do we manage the transaction from DataStage?


Queries:

- Can we handle transaction management in DataStage? If yes, how can we manage?

- If transfer to each of the table is handled by an independent server job which is a part of a sequence job, can we manage the transactions across multiple server jobs?

[ray.wurlod]

One way is to use a single ODBC stage, with one input link for each table, and then you will find a tab called Transaction Management where you can group all as a single transaction.

[chulett]

If transfer to each of the table is handled by an independent server job which is a part of a sequence job, can we manage the transactions across multiple server jobs?

No, no more than you could across multiple stand-alone procedural processes.

[turash]

If transfer to each of the table is handled by an independent server job which is a part of a sequence job, can we manage the transactions across multiple server jobs?

No, no more than you could across multiple stand-alone procedural processes.

I am fairly new to this datastage tool. How do i write a single sever job transforming multiple tables and linking them in sequence.

[chulett]

As Ray noted - using a single target database stage with multiple input links. Each link would populate one of the related tables and the link order would match any RI dependancies you have between the tables. In that situation, each 'set' of records can be committed individually if you enable Transaction Grouping, or leave your Transaction Size at 0 for an 'all or nothing' load across all tables - my personal preference.

You can also build totally separate jobs and use a Sequence job or some form of job control to run them in the 'proper' order. Start with loading the top-most parent table, store what you need in hashed files and leverage those hashed files as you work you way down the family tree through the children.

[turash]

As Ray noted - using a single target database stage with multiple input links. Each link would populate one of the related tables and the link order would match any RI dependancies you have between the tables. In that situation, each 'set' of records can be committed individually if you enable Transaction Grouping, or leave your Transaction Size at 0 for an 'all or nothing' load across all tables - my personal preference.

You can also build totally separate jobs and use a Sequence job or some form of job control to run them in the 'proper' order. Start with loading the top-most parent table, store what you need in hashed files and leverage those hashed files as you work you way down the family tree through the children.

Thanks a lot ray.wurlod & chulett. I will use this approach to design my datastage jobs.

[turash]

As Ray noted - using a single target database stage with multiple input links. Each link would populate one of the related tables and the link order would match any RI dependancies you have between the tables. In that situation, each 'set' of records can be committed individually if you enable Transaction Grouping, or leave your Transaction Size at 0 for an 'all or nothing' load across all tables - my personal preference.

You can also build totally separate jobs and use a Sequence job or some form of job control to run them in the 'proper' order. Start with loading the top-most parent table, store what you need in hashed files and leverage those hashed files as you work you way down the family tree through the children.

[Multiple Source Table -> Transform-> (via Multiple input link) Target Database][/code]

Is this what you are suggesting?

[chulett]

Basically... yes. The source can be anything, I typically land what I need in a flat file and then source from that flat file.

[turash]

Basically... yes. The source can be anything, I typically land what I need in a flat file and then source from that flat file.

If I have five source table with different structure , number of rows can be
different in each table for a given key.

what should be my approach to create flat file?

Should I create five flat file? or

One flat file ?

[chulett]

The answer is... depends. Without knowing exactly what needs doing, it's hard to give very specific advice.

Properly leveraging hashed files is key. Don't try to build one huge mongo job that does everything, think modular and break the processing up into logical segments. Your last job would still handle all transactions but create 'pre' jobs that stage information in hashed files so that your main source file can pick up and use that info on the appropriate output link(s). Use a Sequence job or your job control mechanism of choice to string them together in the proper order.

[turash]

Transaction Management using ODBC vs Sybase OpenClient

I have job which transfer the data for two tables
Source - DB2
Target - Sybase

Scenario: First table insert 1 row without any error, Second Table have some data error while inserting in target

If I use Sybase OC Stage with transaction size 0 for both tables.
Transaction is not rolling back. though gives correct error for second table

If I use ODBC Stage with Transaction grouping. Transaction is correctly rolled back. but job log does not shows any db warnings and returns successful completion.

One way is to use a single ODBC stage, with one input link for each table, and then you will find a tab called Transaction Management where you can group all as a single transaction.

[turash]

ODBC Settings

Driver=/opt/dsadm/Ascential/DataStage/branded_odbc/lib/VMase19.so
Description=DataDirect Sybase Wire Protocol
ApplicationName=
ApplicationUsingThreads=1
ArraySize=50
Charset=
CursorCacheSize=1
Database=GDNADPWEB
DefaultLongDataBuffLen=1024
EnableDescribeParam=0
EnableQuotedIdentifiers=0
InitializationString=
Language=
LogonID=
NetworkAddress=livdspsoft1,4600
OptimizePrepare=1
PacketSize=0
Password=
RaiseErrorPositionBehavior=0
SelectMethod=0
WorkStationID=

Transaction Management using ODBC vs Sybase OpenClient

I have job which transfer the data for two tables
Source - DB2
Target - Sybase

Scenario: First table insert 1 row without any error, Second Table have some data error while inserting in target

If I use Sybase OC Stage with transaction size 0 for both tables.
Transaction is not rolling back. though gives correct error for second table

If I use ODBC Stage with Transaction grouping. Transaction is correctly rolled back. but job log does not shows any db warnings and returns successful completion.

One way is to use a single ODBC stage, with one input link for each table, and then you will find a tab called Transaction Management where you can group all as a single transaction.

Code: Select all