Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012

    Unanswered: with ur for fetch only

    Hallo everyone,

    I tried to understand what this command direct after the SELECT Statement means:

    with ur for fetch only
    I tried to google, unfortunately, I could not understand what it means.
    Moreover, I tried to run a very easy code:
    select * from mySchema.myTable where id = '1' with ur for fetch only
    It worked.

    Can someone please help me, what it means? Thank you



  2. #2
    Join Date
    Oct 2012
    The UR means "uncommitted read".

    So if someone is updating a record that you want to read, you will read the after image of the record (or updated record). From what I've read, your read may put a hold on the record

    If you take out the UR and just have For Fetched Only, you will not put a hold on records. You won't get any uncommitted read either (if someone is updating the records). You'll just get the before image, if someone is updating the record.

  3. #3
    Join Date
    Apr 2007
    your read may put a hold on the record

    That's misleading and inaccurate. What exactly is a 'hold'?

    FOR FETCH ONLY and WITH UR are 2 distinct independent clauses in a SELECT statement. FOR FETCH ONLY is the the Read Only Clause of the statement and WITH UR is the Isolation Level Clause of the statement. You can specify one or the other, both, or neither.

    The Read Only Clause, FOR FETCH ONLY (or FOR READ ONLY) is specified on a Cursor and prohibits you from referring to that Cursor in a positioned UPDATE or DELETE. The Read Only Clause also allows DB2 to use Block Fetching which is a technique that can improve performance.

    WITH UR is the Isolation-Clause of the statement. DB2 does not aquire row locks for SELECT statements using Uncommitted Read. When a process executes a SELECT statement using WITH UR, DB2 places a single Mass Delete lock on the table to prevent a Mass Delete by a different process. A Mass Delete is a DELETE statement with no WHERE clause. Other than the Mass Delete lock, DB2 will not acquire any other locks for a SELECT using the UR Isolation Level. When using the UR Isolation Level, you might read Uncommitted data, but then again, you might not. DB2 makes no guarantee one way or another. However, since you might read Uncommitted data, you could be looking at data from another process that might be subsequently rolled back by that process. Or, you might not read data deleted by a different process and then subsequently put back by a roll back. The UR Isolation Level reduces contention, but it comes with risks that developers must understand. For a developer issuing a SELECT against a Prod database, WITH UR is often the best approach to prevent any locks that might reduce concurrency for Prod processes. For a Production application, WITH UR will be suitable for a smaller set of processes provided the risks are understood and mitigated.

    You didn't specify your OS and version so I included a link for z/OS v9. The discussions are fundamentally the same for the others.

    IBM Information Management Software for z/OS Solutions Information Center

    DB2 Version 9.1 for z/OS > DB2 reference information > DB2 SQL > Queries > select-statement

    DB2 Version 9.1 for z/OS > DB2 reference information > DB2 SQL > Queries > isolation-clause

  4. #4
    Join Date
    May 2012
    Thank you so much for the helps, hookemhorb and jsharon1248

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts