Tag Archives: record lock

Kill all jobs with a record lock on a specific file via “DB2 for i” SQL

Below is a stored procedure that uses the DB2 for i service QSYS2.RECORD_LOCK_INFO to get all the record locks on a specific file.  It then iterates over all the records and passes the JOB_NAME to ENDJOB to kill the jobs that are creating the lock on the file.  Of course this is very dangerous and should be only used in certain instances where you are sure that the jobs you’ll be killing will not matter.

 

Advertisements

Handling a DB2 record lock exception in PHP on IBMi

Ever run into this exception?

SQLSTATE[HY000]: General error: -913 [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0913 – Row or object TABLE in LIB type *FILE in use

Below is some example code on how you could handle a DB2 file lock in your PHP script, by catching the exception and doing want you want to do when someone has locked the record.  It could be that you email that person and tell them the need to get out of that file for your PHP script to work.  You need to make sure that your script can put what it was working on hold until someone can turn off the lock.

Alternative solution (Use a stored Procedure to find out about the file lock ahead of time.)

You could use a stored procedure to call

DSPRCDLCK   FILE(MASTER/PAYROLL)  MBR(*FIRST)  RCDNBR(1)  OUTPUT(*)

and you could get the RCDNBR from a SQL query like:

SELECT RRN(MY_TABLE)
FROM MY_TABLE WHERE ID=2

Just make sure your program doesn’t get into a race condition.  You may want to do all your sensitive changes in a SQL stored procedure that’s able to lock the file make changes and then release the locks.  You’ll also get the ability to rollback changes or not commit changes in case of a failure.

It appears that db2 php extension IBM_DB2.so (db2_* functions) will actually wait for the record lock to go away.  This will most likely make the PHP script hit its max execution time (causing a timeout) before the record lock is removed.  Therefore you have to check for the record lock ahead of time, create a record lock then do some update then unlock the record.

If you want to dive deeper into this you should check out commitment control, lock table, commit, rollback atomic etc…in the IBMi DB2 SQL reference pdf .

LOCK TABLE MY_TABLE IN EXCLUSIVE MODE  -- Will try to obtain a lock on the table