Use the table function equivalents of Display Record Lock (DSPRCDLCK) and Work with Object Locks (WRKOBJLCK) in your SQL code.

If you've ever wanted to yell at users and tell them to exit a specific screen (or wanted to just happily end their job automatically) because they're locking a record, then this tip is for you. Best of all, you'll be able to use this functionality right from the comfort of your SQL code.

In the dark ages of database development, SQL couldn't process tasks such as OS API calls. But the advent of DB2 for i integrating with high-level language (HLL) programs such as RPG, C, Java, and COBOL changed all of that. Now, HLL routines can bridge the gap by allowing SQL access to the functionality of APIs and libraries of code. HLL routines coded as "external" routines interact with SQL as scalar functions, table functions, and stored procedures.

This tip will use RPG code to invoke two OS APIs to retrieve information about jobs that are holding object- and record-level locks. Once the RPG program has collected this information, it will return the data in a tabular (rows and columns) format back to the DB2 database engine. In other words, the RPG code is implemented as an external table function. It's easy to think of the concept like this:

SELECT * FROM RPG_PROGRAM

When designed correctly, the RPG program returns rows and columns in a very similar manner to how a table is queried. The code was tested on iBM i 7.1 TR7, but V5R4 and later can run these functions as written.

In order for an RPG program to interact with DB2 as an external table function, the RPG program has to follow a special protocol for passing parameters. If you're not familiar with coding table functions in HLLs such as RPG, see the references at the end of the tip. Also, see the CREATE FUNCTION (External Table) section in the SQL Reference manual.

The RPG Service Program LCKINFR

The RPG source code for service program LCKINFR contains the instructions for how to compile the RPG program and create the service program, which is what you need to do first. The source code also contains the appropriate CREATE FUNCTION SQL statements that are required to let DB2 know that the RPG subprocedures are available and ready for use. In general, you'll only need to substitute your own library names in the sample CREATE FUNCTION statements.

The two user-defined table functions furnished by this RPG program are LSTOBJLCK (List Object Locks) and LSTRCDLCK (List Record Locks). As their names imply, these table functions correspond to the familiar WRKOBJLCK and DSPRCDLCK IBM i commands. Once the RPG code is compiled and the table functions registered in SQL, you'll be able to access information about jobs locking objects or records right from SQL.

Like a good web surfer (and developer who doesn't like to reinvent the wheel), I filched this code from existing published material and modified it for my own ends. The code for the List Record Locks (QDBRRCDL) API was taken from this link:

http://www.code400.com/forum/showthread.php/611-Qdbrrcdl

Likewise, the List Object Locks (QWCLOBJL) API code was lifted from here:

http://forums.iprodeveloper.com/forums/aft/42619

For the most part, modifying the existing code to support an external user-defined table function implementation was just a matter of some housekeeping (using free-form, using the appropriate input and output parameters, etc.).

Table Function Inputs and Outputs

Both of these table functions need to know what object they should be checking for locks against. This information is supplied to the function via input parameters. These are the input parameters for each function:

Once equipped with this info, the RPG program can find any locks and return the information as rows and columns.

These are the table function columns:

The column output for the two table functions are populated directly from the API calls. Therefore, references to the APIs can be consulted for more info about the values returned in each column.

Please note that the table functions do not accept long schema or table names (because the APIs do not accept them). If you have only the long table names, you can use the SQL catalog views, such as QSYS2/SYSTABLES, to cross-reference a long table or schema name with the equivalent 10-character system table name or schema name (aka library).

The table functions are easy to use; make sure you specify TABLE in the FROM clause. Use this query to get record locks using the LSTRCDLCK table function:

SELECT * FROM TABLE(LSTRCDLCK('QIWS','QCUSTCDT','*FIRST')) X

The query returns the following output:

Code Samples

Once the functions are created, here are some SQL examples of how they can be used:

Example 1 Using LSTOBJLCK

Say you have a month-end application process that attempts to obtain an exclusive lock on an important table called AR_AGING using the LOCK TABLE statement. The reason for doing this is to make sure that only one instance of the month-end process is running and that no other process is using the table during this process. If the lock is not obtained, table function LSTOBJLCK can be used in an exit handler, for example, to show what other jobs are locking it as shown in this code snippet:

BEGIN

   DECLARE EXIT HANDLER FOR SQLSTATE '57033'

   BEGIN

       DECLARE @XML VARCHAR(4096);

       SELECT XMLSERIALIZE(XMLAGG(XMLROW(JOBNAME,JOBUSER,JOBNBR

                     OPTION ROW "JOB" AS ATTRIBUTES))

               AS VARCHAR(4096)) AS XML

       INTO @XML

       FROM TABLE(

       LstObjLck('MYLIB','AR_AGING','*FIRST','*FILE')) LOCKS

       WHERE JobName>X'00';

       SIGNAL SQLSTATE VALUE '38U10'

           SET MESSAGE_TEXT=@XML;

   END;

   -- Make sure no one else is updating this table

   LOCK TABLE MYLIB.AR_AGING IN EXCLUSIVE MODE ALLOW READ;

   -- Do month end stuff

END;

If the code fails to obtain the table lock, SQL state 57033 is returned and the exit handler code executes. An error is thrown and information about the job(s) locking the object is returned as the error message text. iNavigator RunSQL scripts show the following truncated response as the procedure reports an error and gives some info back in XML form:

SQL State: 38U10

Vendor Code: -438

Message: [SQL0438] <JOB JOBNAME="QPADEV0001" JOBUSER="MIKE     " JOBNBR="003703"/><JOB

Because of truncation issues, returning the XML info with a SIGNAL statement is not a good practice; it's shown here for simplicity. This information would be better returned to the caller with a result set or output parameter.

Note: In this code snippet, IBM i 7.1 is required because the locked jobs are returned using the new XML features. If you're on IBM i 7.1 and you've loaded TR7 (SF99701 Group PTF Level 26), then the code snippet shown above can be run as a standalone dynamic compound statement!

Example 2 Using DSPRCDLCK

In many cases, it's important to know who is locking a particular row in a table, such as a specific customer row in the customer master. Many legacy applications still use pessimistic locking for maintenance screens and hence are able to put an indefinite lock on a row while the user walks away! With the DSPRCDLCK table function, it's possible for the application to implicate a specific user for holding a lock on a row:

BEGIN

   DECLARE @CUSTOMER   DEC(6,0) DEFAULT 938472;

   DECLARE @ROW_NUMBER DEC(15,0);

   DECLARE EXIT HANDLER FOR SQLSTATE '57033'

   BEGIN

       DECLARE @XML VARCHAR(4096);      

       -- Get RRN for specified customer

       SELECT RRN(A) AS ROW_NUMBER

         INTO @ROW_NUMBER

         FROM QIWS.QCUSTCDT A

         WHERE CUSNUM=@CUSTOMER>

         WITH NC;

       -- Lookup lock information for specified RRN

       SELECT XMLSERIALIZE(XMLAGG(XMLROW(JOBNAME,JOBUSER,JOBNBR,LockedRRN

               OPTION ROW "JOB" AS ATTRIBUTES)) AS VARCHAR(4096)) AS XML

         INTO @XML

         FROM TABLE(LSTRCDLCK('QIWS','QCUSTCDT','*FIRST')) X

         WHERE X.LockedRRN=@ROW_NUMBER;

       IF @XML IS NULL THEN

           SET @XML='Record was locked';

       END IF;

       SIGNAL SQLSTATE '38U11'

           SET MESSAGE_TEXT=@XML;

   END;

   -- Do something with the customer

   UPDATE QIWS.QCUSTCDT

       SET BALDUE=0

     WHERE CUSNUM=@CUSTOMER;

END;

Similar to the first example, when the code fails to do an update, the exit handler runs and it reports the following information in iNavigator's RunSQL scripts:

SQL State: 38U11

Vendor Code: -438

Message: [SQL0438] <JOB JOBNAME="QPADEV0001" JOBUSER="MIKE     " JOBNBR="003723" LOCKEDR

This code snippet's exit handler tries to look up the user who is holding the same relative record number that it's trying to update. Also, the exit handler explicitly looks for SQLSTATE 57033, which looks like this:

[SQL0913] "Row or object QCUSTCDT in QIWS type *FILE in use"

Pay Attention

• It goes without saying that these table functions require resources that could slow your application down, so implement them with care.
• The LSTOBJLCK function will sometimes return a row (or rows) with a job name and other information containing binary zeros. I'm not sure why the API does this. In some cases, I've found it necessary to add a predicate to exclude this value. Of course, you can modify the RPG code to exclude these rows.
• If you need to live dangerously, you can theoretically issue an end job (ENDJOB) command using the job-specific data returned by these functions. The QCMDEXC API can be invoked directly from SQL to execute many IBM i commands. To aid in a month-end process, I recently implemented code to shut down all QZDASOINIT (ODBC, etc.) jobs that held a lock on a certain object.
• If you don't use multi-member files, don't forget you can create an overloaded function definition that defaults the member name to *FIRST. (See code below.)

CREATE OR REPLACE FUNCTION DEV.LstRcdLck

(Library VARCHAR(10),

File   VARCHAR(10))

RETURNS TABLE (    

JobName CHAR(10),  

JobUser CHAR(10),  

JobNbr CHAR(6),  

LockStatus CHAR(1),

LockType  CHAR(1),  

LockedRRN BIGINT

)

LANGUAGE SQL

SPECIFIC LstRcdLck_NoMember

RETURN

SELECT *

FROM TABLE(LstRcdLck(Library,File,'*FIRST')) LOCKS;

Lock It Up

In the end, using these table functions will give your SQL-based applications the ability to easily:

• Identify what jobs already have an object locked
• Return the jobs locking various rows within a given table

Once this information has been obtained by the application, appropriate action can be taken, such as relaying the info to a user, storing the information in an application log, etc. In short, furnishing SQL with the ability to call these APIs can make your applications easier to troubleshoot and smarter about the information they provide.

References

Lock Information APIs
http://iprodeveloper.com/rpg-programming/apis-example-lock-information-apis

Retrieve Record Locks (QDBRRCDL) API
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/apis/qdbrrcdl.htm

List Object Locks (QWCLOBJL) API
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/apis/qwclobjl.htm

Cool Things: SQL Functions and List APIs
http://www.mcpressonline.com/rpg/cool-things-sql-functions-and-list-apis.html

Writing UDFs as External Functions
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/sqlp/rbafywudfextern.htm