Transaction Management on the target database
Moderators: chulett, rschirm, roy
Transaction Management on the target database
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?
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?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: Transaction Management on the target database
No, no more than you could across multiple stand-alone procedural processes.turash wrote: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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: Transaction Management on the target database
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 wrote:No, no more than you could across multiple stand-alone procedural processes.turash wrote: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?
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Thanks a lot ray.wurlod & chulett. I will use this approach to design my datastage jobs.chulett wrote: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]chulett wrote: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.
Is this what you are suggesting?
If I have five source table with different structure , number of rows can bechulett wrote:Basically... yes. The source can be anything, I typically land what I need in a flat file and then source from that flat file.
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 ?
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
ray.wurlod wrote: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.
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=
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=
turash wrote: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.
ray.wurlod wrote: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