While loading into db2 thru ds,Uncommitted data is readable?

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
vjlegendrocks
Premium Member
Premium Member
Posts: 2
Joined: Thu Sep 14, 2017 5:12 pm

While loading into db2 thru ds,Uncommitted data is readable?

Post by vjlegendrocks »

database :db2
datastage loading setting through Db2 connector: Cursor stability


Q: while updating table, data which is not committed ( uncommitted ), lets say 30000 records are still not committed to table, this transaction has taken 10 seconds to commit by Datastage as we know when auto commit is off on connector, commit happens after RECORD COUNT number records are processed.

lets say we are making company ID 'A' to 'B' for 5000 records which are being processed in those 10 seconds.

Note : 10 seconds is a rough value considered to explain the scenario.


what happens in below scenarios :

1. Is uncommitted data is readable by a different user instantaneously which is hitting table, when they have setting in their application as TRANSACTION READ UNCOMMITTED ? or it will wait till 10 seconds are passed ?
If it is going to show instantaneously without waiting, company_id value will be A or B with in 10 seconds.

2. Is uncommitted data is readable by a different user instantaneously which is hitting table when they have setting in their application as TRANSACTION READ COMMITTED ? or it will wait till 10 seconds are passed?
If it is going to show instantaneously without waiting, company_id value will be A or B with in 10 seconds.

Though below link from IBM says the changes are not readable, i would like understand in detail. Need advise.
https://www.ibm.com/support/knowledgece ... rties.html

regards
Vijay
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

1. The Read uncommitted isolation level description confirms that "other transactions can still read but not modify the uncommitted changes."

2. The Cursor stability isolation level description confirms that "uncommitted changes are not readable by other transactions." I did not see one named "Read committed."

Your questions ask about a different user, but keep in mind it could be the same user, such as you or one of your DataStage jobs running in parallel or multiple connections within the same job design, that is allowed or disallowed to read the uncommitted changes.

Your local DB2 DBA could shed some more light on it too.
Choose a job you love, and you will never have to work a day in your life. - Confucius
vjlegendrocks
Premium Member
Premium Member
Posts: 2
Joined: Thu Sep 14, 2017 5:12 pm

Post by vjlegendrocks »

Hi,
So, it means that other users cannot see uncommitted changes till transaction is committed by Datastage job with "Cursor Stability" option, irrespective of reading mode( Read uncommitted/ Read committed) they use in their program/query. However the data which is being updated by Datastage job will not allow any more updates on to it.

Pls confirm if my above understanding is incorrect.
Regards
VJ
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

That matches my understanding. My only disclaimer is that I'm not a DB2 DBA.
Choose a job you love, and you will never have to work a day in your life. - Confucius
Post Reply