DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
vjlegendrocks



Group memberships:
Premium Members

Joined: 14 Sep 2017
Posts: 2

Points: 44

Post Posted: Thu Oct 12, 2017 6:40 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
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/knowledgecenter/SSZJPZ_11.3.0/com.ibm.swg.im.iis.conn.db2db.help.doc/topics/r_DB2conninputlinkproperties.html

regards
Vijay
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2666
Location: USA
Points: 19826

Post Posted: Fri Oct 13, 2017 6:48 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
vjlegendrocks



Group memberships:
Premium Members

Joined: 14 Sep 2017
Posts: 2

Points: 44

Post Posted: Sun Oct 15, 2017 8:44 pm Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2666
Location: USA
Points: 19826

Post Posted: Mon Oct 16, 2017 7:44 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours