Solved

How skip locked rows for SQL UPDATE on AS400 UDB

Posted on 2010-08-18
6
2,351 Views
Last Modified: 2013-12-06
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
Comment
Question by:NavigatorsIT
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:daveslash
ID: 33468206
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
 

Author Comment

by:NavigatorsIT
ID: 33468241
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
 
LVL 34

Accepted Solution

by:
Gary Patterson earned 500 total points
ID: 33469804
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:NavigatorsIT
ID: 33481680
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
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 33482036
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
 

Author Closing Comment

by:NavigatorsIT
ID: 33503586
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Query Syntax Error 9 49
SQL join help to a thrid table 51 76
DB2 VBA - How to use Form instead of InputBox? 2 41
Need help subtracting a value within my script 7 42
As the title indicates, I have done this before. It chills me everytime I update the OS on my phone, (http://www.experts-exchange.com/articles/18084/Upgrading-to-Android-5-0-Lollipop.html) because one time I did this and I essentially had a bricked …
I use more than 1 computer in my office for various reasons. Multiple keyboards and mice take up more than just extra space, they make working a little more complicated. Using one mouse and keyboard for all of my computers makes life easier. This co…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

864 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

24 Experts available now in Live!

Get 1:1 Help Now