[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1172
  • Last Modified:

READPAST (SQL Server) equivalent for DB2

Is there an equivalent for SQL Server's READPAST in DB2? Also, is it possible to set a lock timeout in DB2 by SQL statement rather than just at the database level and if so, how?

Thanks.
0
aporras
Asked:
aporras
  • 3
  • 2
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi aporras,

In DB2, locking is implicitly controlled by the application that performs the lock.  However, some SQL commands do have options that allow them to working around some locking.

DB2 uses what it calls "isolation levels", of which there are four.

Repeatable Read (RR).  Locks all rows the application references within a transaction.

Read Stability (RS).  Locks athe rows that the applications retrieves within the transaction.

Cursor Stability (CS).  Locks any row accessed ty a transaction while the cursor is positioned on the row.

Uncommitted Read (UR).  Allows an application to access uncommitted changes of other transactions.


Some forms of the SELECT, DELETE, INSERT, UPDATE, MERGE, and DECLARE cursor support the isolations levels by allowing you to specify the level on the SQL statement.

SELECT ... WITH ISOLATION LEVEL UR;


There's a pretty good description of them at the following link.  The link at the bottom of the page describes how to specify the isolation level.


Good Luck!
Kent


http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/admin/c0007870.htm
0
 
ghp7000Commented:
if you can tell me what the READPAST function does, I can tell you if there is an equivalent in DB2.

As for lock timeout, you can over-ride the database setting by using the CURRENT LOCK TIMEOUT special register. What you do is, either in your program or interactively, :
db2 set current lock timeout  wait [not wait] etc etc full details can be found at:
http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/r0011874.htm?resultof=%22%6c%6f%63%6b%22%20%22%74%69%6d%65%6f%75%74%22%20
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi ghp7000,

Basically, READPAST specifies that locked rows be skipped during the read.

I can't imagine the value of:

  SELECT [rows that you can conveniently fetch] from table;


Kent
0
 
ghp7000Commented:
if you want to read rows that are being changed (that means another user has a type of lock on the row which is not compatiible with the type of lock you need to read the row(s), for a cross reference please                                                                                                                                                                                                                                                                               see the link at the bottom) use the UR qualifier at the end of your sql statement
eg:
select something from sometable where whatever predicates you may or may not have WITH UR
What is returned is the state of the result set regardless of whether other transactions have been committed or not. So, if user A adds a row to the table and DOESN'T commit, and user b wants the record count of the table WITH UR, user B gets the record count INCLUDING the uncommitted transaction of A. If A decides to rollback and B doesn't re-query, B makes decisions based upon erroneous data.

Is this what you mean, because when you start talking about concurrency and locking in DB2, you have to have a clear idea of what you want to accompolish, as DB2 is designed to provide a great deal of flexibility when it comes to concurrency control, but that flexibility does come at a cost of more complex design work and now, with the sql qualifiers of with UR, RR, CS and RS, a little more complex and flexible.
Example, database run in mode RS but read for update in CS mode
Programs bound to RS database in UR mode.

My point is, what is your purpose in reading past locked rows? When you can answer that question, then you can design the concurrency/locking scheme.


http://publib.boulder.ibm.com/infocenter/db2help/topic/com.ibm.db2.udb.doc/admin/r0005274.htm?resultof=%22%6c%6f%63%6b%22%20%22%6d%6f%64%65%22%20

           



0
 
ghp7000Commented:
i think my answers have set the asker on the right path
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now