READPAST (SQL Server) equivalent for DB2

Posted on 2005-04-13
Last Modified: 2008-01-09
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?

Question by:aporras
    LVL 45

    Expert Comment

    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.


    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!
    LVL 13

    Accepted Solution

    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:
    LVL 45

    Expert Comment

    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;

    LVL 13

    Expert Comment

    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
    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.


    LVL 13

    Expert Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now