Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2703
  • Last Modified:

How skip locked rows for SQL UPDATE on AS400 UDB

I need to know how to structure an Update SQL statement that will only update the rows that are not locked.  We have a situation where some of the rows of a table are locked by some batch and/or interactive jobs and our update statement thows an error when a rowlock is found.  The data is such that there is no way to identify the rows that are locked, and the program locking the files is a 3rd party system so not open to change.

I have done some analysis of UDB Isolation level optoins but none of them seem to have the desired effect.

our AS400 5R4



0
NavigatorsIT
Asked:
NavigatorsIT
  • 3
  • 2
1 Solution
 
Dave FordSoftware Developer / Database AdministratorCommented:
Notice the last line in the code. It became available in V6R1.

The SKIP LOCKED DATA clause is available only in the cursor stability and read stability isolation levels, hence WITH CS in this example.

You can use SKIP LOCKED DATA with SELECT, UPDATE, and DELETE commands.
UPDATE CustMaster 
SET    chgcod = 7
WHERE  state = 'TX'
WITH CS
SKIP LOCKED DATA

Open in new window

0
 
NavigatorsITAuthor Commented:
I saw that as well on some documentation I found online, I was not aware it was in V6R1.  
We are at V5R4, is there anything similar for V5R4.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
In V5R4 with a bulk UPDATE like this, you have two basic choices:

An "all-or-nothing" UPDATE.  If any record is locked, then the UPDATE fails and changes are rolled back.

UPDATE CustMaster
SET    chgcod = 7
WHERE  state = 'TX'
WITH [ RR, RS, CS, or UR]

An "until first failure" UPDATE.  Records are updated until an error occurs, at which time processing stops.  Records that are updated are not rolled back:

UPDATE CustMaster
SET    chgcod = 7
WHERE  state = 'TX'
WITH [ RR, RS, CS, or UR]
 
Neither of those options are great.  If you want an "all that aren't locked" UPDATE, you'll have to write a program or stored procedure that FETCHes one record at a time, attempts the single-row UPDATE, and handles the exception of a locked row is encountered.

Here's a native RPG method:  http://www.itjungle.com/fhg/fhg010709-story01.html
Here's an RPG with embedded SQL method that could easily be adapted into an SQL stored procedure:  http://www.itjungle.com/fhg/fhg062304-story03.html

Bear in mind that attempting to FETCH a locked record may take a long time before it fails, depending on how the table was created.  By default DB2/400 tables have a record lock wait time of 60 seconds.  If your table uses a long default wait time like this, you might want to issue an override before opening the table to set a shorter record wait time.

OVRDBF FILE(mytable) WAITRCD(seconds/*IMMED)

You can use the DSPFD command to see the WAITRCD setting for the table you are interested in.

The reason for this log wait time is to allow other processes time to "get out of the way".  If you use a long record lock wait like this, you may find that your program will coexist peacefully with the batch programs that are causing the problem (make sure your program doesn't hold long locks - FETCH and immediately UPDATE to release the row lock.)  

Almost no good reason that a batch program should hold a record lock for this long.

- Gary Patterson

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
NavigatorsITAuthor Commented:
WOW looks like I will have some options now.  
I don't see any difference in the Update statements?  is there a typo?  Also I tried to use the WITH CS and the others but it did not seem to update the rows, but I will need to check since I did not confirm that no rows were updated. The RPG programs are a good alternate and I could have a batch program running in the background updating the field for unlocked rows.

And I agree a batch program should not hold it that long, but we also have interactive jobs where a user is on a update screen and then head to lunch, locking the row(s) for over an hour.  As long as I can leave these rows alone I think we will be fine.
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Cut and paste error!  Should be NC on the second one.

An interactive program should never hold a lock while throwing a screen. I've repaired this design flaw in numerous applications over the years.

If that's vendor supplied code you should treat it ad a big and report it for a fix. That is not acceptable behavior in professional software.
0
 
NavigatorsITAuthor Commented:
I still need to intragrate into our process but it looks like the code RPT Code referenced in the reply should do the job.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now