Concurrency check for ibmi physical files

[mashultz]

Hello,
We have several physical files that we access on our IBM i via VL forms. These files were previously managed by RPG programs, which used pessimistic locking to deal with concurrency. VL forms seem to prefer optimistic locking, but since these are defined as physical files and not owned by Lansa, what are my options for managing concurrency? Is there a resource that discusses this from a Lansa perspective that I could reference?

Thanks!

[BrendanB]

on the FETCH and SELECT commands there is a parameter LOCK :

FETCH #fileGroup FROM_FILE(YOURFILE) WITH_KEY(#fileKey) LOCK(*YES)

This will:

1. read the record from the file
2. lock the RECORD in the file

the lock will be released:

1. when the RECORD is UPDATEd
2. when another RECORD is read
3. when the FILE is CLOSEd.
4. when the Function/RP ends.

you can check for :

IF COND('*DBMS_RECORD_LOCKED *EQ Y')

and handle appropriately if you find the record to be already locked.

so: PESSIMISTIC LOCKING:

Code: Select all

FETCH #fileGroup FROM_FILE(YOURFILE) WITH_KEY(#fileKey) LOCK(*YES)
IF_STATUS IS(*OKAY)
* change fields  ---- NOTE - THE RECORD IS LOCKED AT THIS POINT IN TIME
UPDATE #fileField IN_FILE(YOURFILE)
CLOSE FILE(YOURFILE)
ELSE
* delay job and try again? - fail and inform user? - handle how you wish.
ENDIF

OPTIMISTIC LOCKING

Code: Select all

       UPDATE    FIELDS(#ORDERQTY) IN_FILE(ORDLINE)
                 WITH_KEY(#ORDER #LINE) IO_ERROR(TST)

       GOTO      LABEL(NXT)

  TST  IF        COND('*DBMS_RECORD_LOCKED *EQ Y')
       MESSAGE   MSGTXT('Order line record locked')
       ........  ..Required action (try again? fetch with a lock(*yes)? exit?)
       ELSE
       ........ ..Error OTHER THAN RECORD WAS LOCKED
       ABORT     MSGTXT('Fatal I/O error on ORDERLINE file')
       ENDIF 

  NXT  .......   ..Next action 

[mashultz]

Thank you, that was very helpful!

But if I wanted to move exclusively to optimistic locking, what would I need to do in order for Lansa to use its built-in concurrency management (with the @@UPID column)? Is it as simple as adding a field with that name, even to a physical file or does the file need to be removed from the repository and re-added from Lansa?

[Dino]

1st, to do optimistic locking, you don't need exclusively lansa tables.

2nd, if you want to have the table managed by LANSA instead of other, an easy way is from the vl editor , select the oldtab "maintained by other" table and copy it, for example from oldtab to newtab. The newtab will be "maintained by LANSA" and will have the @@upid column automatically.

If you not longer want to use the oldtab but want to keep the name, then,
1) after having a backup of your data,
2) you can remove the oldtab from the Lansa repository - if oldtab is maintained by other then this step do not delete the pf-
3) copy again using the vl editor, newtab to oldtab name. Notice that the new oldtab table will be created by default in the partition data library, not in the original location of the previous oldtab.
4) delete newtab from the repository
5) copy the data from the old oldtab to the new oldtab.

If you did the steps right you may not even need to recompile programs and they will work over the new version of oldtab now with @@upid column.

[mashultz]

awsome, thanks!

[atostaine]

@Dino. You would have to recompile IBM i programs correct? They will get a level check.

Art

[Dino]

If you still will have non lansa ibm programs, yes, of course.