Have SQL UPDATE throw error message on record lock like native IO does

Question

I'm somewhat new to the IBM i and I come from a Java / C# background. So if I use the wrong jargon please correct me. We are currently on IBM i 7.1 and will be shifting to 7.3 within the next year.

I've been tasked with updating records in a file. I've written two RPGLE programs here that do the same thing.

Native I/O:

    **free
    dcl-f table1 usage(*update) keyed;
    setll ('001': 'Y') table1;
    reade ('001': 'Y') table1; 
    dow not %eof(table1);
        letter = 'X';
        update table1;
        reade ('001': 'Y') table1;
    enddo;
    *inlr = *ON;
    return;

Embedded SQL:

    **free
    exec sql UPDATE table1 
            SET letter = 'X'
            WHERE GroupCode = '001' AND Newbie = 'Y';
    *inlr = *ON;
    return;

In my mind, this is one of the times where SQL has a powerful edge over native I/O with how succinct it is.

Here's my problem. My company relies on CPF messages. We, like many companies, are not perfect and have legacy code. There is quite a bit of it and sometimes we get record locks. When this happens, an error message is sent to the system and it waits for someone to log on and answer it.

This results in a call to our internal support and usually by the time someone gets to it, the program that had a lock on the file is done and we just need to answer R to retry and the update continues where it left off. This doesn't happen often enough that we can spend time working on fixing the scheduling process and all of the programs out of record locks, but often enough that not having this ability would cause us great amounts of pain.

With native I/O these messages are thrown. With embedded SQL the SQLSTATE and SQLCODE variables are set and the program continues on, without sending any message to the system. I could check this variable, write my own message, and send a message to the system if something goes wrong. However; this is less than ideal. If I manually give the option to retry, there is no way to resume the update from where it left off. I would have to run the whole update statement again.

I have looked at compiler options, control options, sql options and sql commands and none that I have seen give me the ability I'm looking for.

In short, I would like to know, is there a way that I can get embedded SQL to behave like native I/O upon an unmonitored error message?

Answer 1

In short...no.

SQL is a different paradigm. SQL error handling in DB2 for i works just like error handling in SQL in any other language...

You probably want to run your UPDATE under commitment control. Then check sqlstate. If there was no error, COMMIT the transaction.

Otherwise, ROLLBACK the transaction, and display your message. At this point an option to retry would loop back and reissue the SQL UPDATE.

Editoral
And after you add all that, your SQL version isn't looking so succinct is it? Heck the RPG version could actually be cut down to 5 or 6 lines...if you wanted to go old school and use an input primary file. Not that I'd recommend it now-a-days.

RPG's close integration with the DB does a lot for you. SQL's great and I use it all the time but sometimes a little more control is better. Thankfully, on the IBM i there's an alternative to SQL.

Answer 2

Better solution is to catch the record locked error and automatically retry the DB update operation until the lock is gone, or bail out after specific time-out interval. This can be easily implemented either with native I/O or with SQL and should cut down on the significant percentage of support incidents.

Answer 3

In this case you could just:

**free
dou sqlstate = '00000' or sqlstate = '02000';
  exec sql UPDATE table1 
          SET letter = 'X'
          WHERE GroupCode = '001' 
            AND Newbie = 'Y'
            and letter <> 'X';
enddo;
*inlr = *ON;
return;

The lock will still cause a wait in SQL for 60 seconds or so depending on the WAITRCD attribute of the file or override, so this won't cause too great an issue.

You could also create a procedure to send a message in the case of a lock and implement that as:

**free
dou sqlstate = '00000' or sqlstate = '02000';
  exec sql UPDATE table1 
          SET letter = 'X'
          WHERE GroupCode = '001' 
            AND Newbie = 'Y'
            and letter <> 'X';
  if sqlstate = '57033';
    SendSqlMsg('SQL0913');
  endif;
enddo;
*inlr = *ON;
return;

You could even make that procedure send an inquiry message and retrieve the reply. Then handle the reply as necessary. I will leave that as an exercise for you.

Answer 4

I see this in the wild all the time. Not using optimistic code. That fail you got maybe optimistic coding would have got past it even with legacy programs running.

There still is the how do I do a redo when the row is locked. Oldtimers that caused the problem with outdated coding techniques would lock the process with a message because its what they do they write code that depends on human intervention. Now anything else in the queue is blocked and more things will fail waiting for the human operator to answer a message.

A modern program will be able to reschedule itself in the future when it fails. Maybe send an email when the process reschedules. Like a runnable is scheduled in the future.

exec sql UPDATE table1 
        SET letter = 'X'
        WHERE GroupCode = '001' AND Newbie = 'Y' 
     and letter <>  'X';   /*  and letter <> 'X' is the optimistic part */
*inlr = *ON;
return;

if calledpgm failed then reschedule to run in the future send an email to support if desired.

Answer 5

If you look for a solution changing the approach altogether, there can be many ways. Taking only on one particular path (use of only native or only sql etc.) narrows down the options.
I tried to look at different answers above, here is my perspective
- with danny117's approach, if there is no dependency on the "time" when the file-field 'letter' can be updated to 'x' then having the update sql inside a 'monitor' and letting the entire program run multiple schedule with filter on letter ='x' can be an option
- for jmarkmurphy's approach but where the wait depends on the file attribute and can have problem if the file gets locked for a longer time outside.
- since even Charles option also do not help with the occasional lock and release

- i was thinking another option, if the update can run faster than the duration other program will lock the file. "allocate" the file exclusively for this update and release it after the successful update. during allocate loop the step with a delay till you get the exclusive lock on the file, then update.