Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2007
    Posts
    15

    Unanswered: FOR READ ONLY WITH UR in nested select

    First of all, I am new to DB2 (3 weeks). I want to update a TEST table with PRODUCTION table in a nested select with read only with uncommitted read.

    This is what I wanted to do but I got error message. Please help, thanks!

    Code:
    UPDATE TEST_STAFF S2
    SET DEPT = (SELECT DEPT from PRODUCTION_STAFF
                       WHERE JOB = S2.JOB
                       ORDER BY DEPT
                       FETCH FIRST 1 ROWS ONLY
                       FOR READ ONLY WITH UR 
                     );

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That is because the FOR READ ONLY and the WITH UR are not allowed in a full-select. They are only allowed in a select-statement.

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ----------------------------------
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Apr 2007
    Location
    Chicago
    Posts
    68
    You're attempting to take a round-about way to get the lowest PRODUCTION_STAFF.DEPT value for a given TEST_STAFF.JOB. You'll be better off using a MIN function. Also, since you have the same column names on both tables, I'd recommend qualifying both tables for clarity.

    Code:
    UPDATE TEST_STAFF S2
    SET DEPT = (SELECT MIN(P1.DEPT) from PRODUCTION_STAFF P1
                       WHERE P1.JOB = S2.JOB
                     );

  5. #5
    Join Date
    Dec 2007
    Posts
    15
    In SQL Server if I remember it correctly NOLOCK can be used in FULLSELECT. The example I showed is something I came up with. I can change it to ORDER BY ID or any order I needed.

    My question is: Is there a way not to get the production table locked and to read the latest data in production.

    Thanks.

  6. #6
    Join Date
    Dec 2007
    Posts
    15
    Never mind. I found a solution. http://publib.boulder.ibm.com/infoce...n/c0004121.htm
    FYI:
    Uncommitted Read (UR)

    For SELECT INTO, FETCH with a read-only cursor, fullselect in an INSERT, row fullselect in an UPDATE, or scalar fullselect (wherever it is used), the Uncommitted Read level allows:

    * Any row read during a unit of work to be changed by other application processes.
    * Any row changed by another application process to be read, even if the change has not been committed by that application process.
    Code:
    UPDATE TEST_STAFF S2
    SET DEPT = (SELECT DEPT from PRODUCTION_STAFF
                       WHERE JOB = S2.JOB
                       ORDER BY SALARY
                       FETCH FIRST 1 ROWS ONLY                   
                     ) WITH UR ;

Posting Permissions

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