Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How skip locked rows for SQL UPDATE on AS400 UDB

Posted on 2010-08-18
6
Medium Priority
?
2,632 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 2000 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
How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

 

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 Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Many people tend to confuse the function of a virus with the one of adware, this misunderstanding of the basic of what each software is and how it operates causes users and organizations to take the wrong security measures that would protect them ag…
In this article we will discuss all things related to StageFright bug, the most vulnerable bug of android devices.
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.

715 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