Results 1 to 4 of 4

Thread: insert with ur

  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Unanswered: insert with ur

    Hi,
    i have inherited some as400 stored procedures code.
    i encountered an insert statement ending with "WITH UR"
    and i couldnt find any documentation of how it behaves and what is means
    on an insert statement.
    any ideas?

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I don't know the specifics of AS400, but WITH UR on an INSERT would only make sense for an INSERT from a subselect where the data to be inserted is potentially dirty-read data. The INSERT operation itself will be locked, of course, because all data modifications always set X-locks. (Otherwise, you may have other transactions at higher isolation levels that could see wrong data, and DB2 prevents that.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Mar 2009
    Posts
    2

    insert with ur

    thanks!
    well, it's just a plain insert.
    but i found the guy who did it and he claims that this was the only way to pervent locks on the table when other threads were reading from it..

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I don't believe that claim, but I don't know AS400 enough to be sure.

    Every row that is inserted, deleted, or updated must be X-locked by the transaction A performing the modification. Otherwise, another transaction B could read that row while it is being changed and the results would be unpredictable. If B is running isolation level CS, RS, or RR, it is guaranteed that B sees only committed data and if the insert is not committed, that row won't be seen by B. So there are two solutions:
    (1) B has a shadow copy of the before image of the data.
    (2) A places a lock on the changed row (or table).

    My recommendation would be to consult the manual and verify which locks are claimed in which situations.

    Which of the two DB2 iSeries is doing, I do not know. DB2 LUW and DB2 z/OS use approach (2) and claim the X locks.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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