Solved

How skip locked rows for SQL UPDATE on AS400 UDB

Posted on 2010-08-18
6
2,563 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:Dave Ford
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 35

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
 Watch the Recording: Learning MySQL 5.7

MySQL 5.7 has a lot of new features. If you've dabbled with an older version of MySQL, it is definitely worth learning.

 

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 35

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
Suggested Courses

630 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