DB2 ordered targets and transactions
Posted: Mon Sep 24, 2012 10:54 am
I'm trying a DB2 load with multiple input links to the stage, one link to do a "delete" and one to do an "insert".
The "Transaction" section has disappeared. It is no longer present in either of the links nor on the stage. The Isolation Level has moved from the Link page to the Stage page. Does anyone know what Transaction Record Count will be used? Or will it just commit after every record ordering key?
*Edit* The documentation says this:
Isolation level
Specify the degree to which the data that is being accessed by the DB2 connector stage is locked or isolated from other concurrently executing transactions, units of work, or processes. (Transactions or units or work are committed in a target stage when the end of wave marker is detected.)
What's an "end of wave marker"?
*Edit* Am I supposed to have a third link that does a User-defined SQL "commit" statement? My job worked fine for a small test but fell over with "unavailable resource" on a larger volume run. Adding a third "commit" link seems to have overcome this.
One neat effect that can be achieved this way is to commit before reaching a certain record limit. If you set the constraint on the "commit" link to "Mod( @INROWNUM, pCommitLevel ) = 0", and set the "commit" link to be the first link in the Link Order, then the commit will be issued on a change of key that would have breached the commit level. So if you have a limit of 1000, and you have got to 997, and the next key inserts 4 rows, the commit will be issued before those 4 rows are inserted.
The "Transaction" section has disappeared. It is no longer present in either of the links nor on the stage. The Isolation Level has moved from the Link page to the Stage page. Does anyone know what Transaction Record Count will be used? Or will it just commit after every record ordering key?
*Edit* The documentation says this:
Isolation level
Specify the degree to which the data that is being accessed by the DB2 connector stage is locked or isolated from other concurrently executing transactions, units of work, or processes. (Transactions or units or work are committed in a target stage when the end of wave marker is detected.)
What's an "end of wave marker"?
*Edit* Am I supposed to have a third link that does a User-defined SQL "commit" statement? My job worked fine for a small test but fell over with "unavailable resource" on a larger volume run. Adding a third "commit" link seems to have overcome this.
One neat effect that can be achieved this way is to commit before reaching a certain record limit. If you set the constraint on the "commit" link to "Mod( @INROWNUM, pCommitLevel ) = 0", and set the "commit" link to be the first link in the Link Order, then the commit will be issued on a change of key that would have breached the commit level. So if you have a limit of 1000, and you have got to 997, and the next key inserts 4 rows, the commit will be issued before those 4 rows are inserted.