Solved

How skip locked rows for SQL UPDATE on AS400 UDB

Posted on 2010-08-18
6
2,321 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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 …
Windows 10 is here and for most admins this means frustration and challenges getting that first working Windows 10 image. As in my previous sysprep articles, I've put together a simple help guide to get you through this process. The aim is to achiev…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 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

11 Experts available now in Live!

Get 1:1 Help Now