READPAST (SQL Server) equivalent for DB2

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

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

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

           

i think my answers have set the asker on the right path