Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
ISOLATION UR

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Sairamjaya

New User


Joined: 21 Feb 2006
Posts: 4

PostPosted: Tue Feb 21, 2006 8:57 pm    Post subject: ISOLATION UR
Reply with quote

Hello!!

Can we use the isolation UR along with Select count(*) from table. ?
Here the table is a production table. We have to be very cautious while using Isolation UR right?. What would happen if we use in the above select. Is it necessary?

Please advice me a brief reply and please share your experience.

Thank you very much.
Back to top
View user's profile Send private message

DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Tue Feb 21, 2006 11:01 pm    Post subject: Re: ISOLATION UR
Reply with quote

Sairamjaya

Yes, of course. This is probably a very good use of it. There is a small likelihood that you will get an incorrect count using the ?WITH UR?. See the post below on what the UR means and how it handles locks.

Previous Post WITH UR

If this does not answer you question, please come back,

Dave
Back to top
View user's profile Send private message
Sairamjaya

New User


Joined: 21 Feb 2006
Posts: 4

PostPosted: Wed Feb 22, 2006 1:25 am    Post subject:
Reply with quote

Do you mean we can use the isolation UR with Select Count(*) from table
is good?

Please explain me.
Thanks.
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Wed Feb 22, 2006 1:46 am    Post subject: Re: ISOLATION UR
Reply with quote

Sairamjaya,

The WITH UR ignores the locks placed on any rows that have been updated, but not yet committed. This allows your SELECT to run even if another program is updating the table. If you do not specify the WITH UR, you program will stop when it encounters a row lock, until the lock has been released. Depending on how many locks are placed, the duration of the locks, you could be looking at a timeout for your program/SQL.

The drawback to using WITH UR is that you are counting rows that have been updated and not yet committed. There is a possibility that a row you counted could be rolled back and if the columns that were updated would affect your count, then your count will be off. It's a trade-off.

Using WITH UR isn't going to impact the production table other than the resource it's taking up doing the select.

I don't think twice about doing selects from production tables. I usually test the SQL on one of the TEST tables to make sure it's efficient and then run it on the production tables. Now, I do run in an initiator that gives production programs a higher priority.

Dave,
Back to top
View user's profile Send private message
Sairamjaya

New User


Joined: 21 Feb 2006
Posts: 4

PostPosted: Wed Feb 22, 2006 2:08 am    Post subject:
Reply with quote

Thank you very much for your explanation Davidatk. It is really helpful.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Current Isolation level Pandora-Box DB2 2 Fri Jan 13, 2012 6:59 pm
No new posts Problem related to isolation level. G1NXU6T DB2 1 Fri Feb 25, 2011 6:50 pm
No new posts Lock termination with RR isolation Level Manojtyagi1979 DB2 1 Mon Dec 13, 2010 8:56 pm
No new posts Confusion over Isolation level in a plan mailsaurabh.tripathi DB2 2 Thu Aug 05, 2010 3:59 pm
No new posts can Isolation UR allows the applicati... PKB DB2 11 Fri Jun 04, 2010 11:54 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us