29
Fri, Nov
0 New Articles

TechTip: Add Lock Detection to Your DB2 for i Arsenal

Typography
  • Smaller Small Medium Big Bigger
  • Default Helvetica Segoe Georgia Times

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:

 

LSTOBJLCK (All Inputs Are VARCHAR(10))

LSTRCDLCK (All Inputs Are VARCHAR(10))

LibraryThe special values *CURLIB and *LIBL are allowed. Keep in mind that the SQL session's path may be different from the library list.

LibraryThe special values *CURLIB and *LIBL are allowed.

Object

Object

MemberThe special values *NONE, *ALL, and *FIRST are allowed when the object type is *FILE.

MemberThe special value *FIRST is allowed.

Object TypeAny valid object type (e.g., *FILE, *DTAARA, *PGM, etc.)

 



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:

LSTOBJLCK

LSTRCDLCK

JobName CHAR(10)

JobName CHAR(10)

JobUser CHAR(10)

JobUser CHAR(10)

JobNbr CHAR(6)

JobNbr CHAR(6)

LockState CHAR(10)
*NONE
No lock exists.

*SHRRDLock shared for read.

*SHRUPDLock shared for update.

*SHRNUPLock shared no update.

*EXCLRDLock exclusive allow read.

*EXCLLock exclusive no read.

LockStatus CHAR(1)

0The record lock is held.

1The record lock is being waited on.

LockStatus INTEGER
1
Currently held by the job or thread

2Waiting for the lock (synchronous)

3Outstanding lock request (async)

LockType CHAR(1)

0Shared read lock

1Exclusive update lock

2Shared internal lock

LockType INTEGER

1Object

2Member control block

3Access path

4Member data

LockedRRN BIGINTRelative record number of row being locked

MemberName CHAR(10)

 

Share CHAR(1)

 

LockScope CHAR(1)

0Job scope

1Thread scope

2Lock space scope

 

ThreadId BINARY(8)unique thread ID

 

*See OBJL0100 field descriptions for QWCLOBJL API

*See RRCD0200 Format Field Descriptions for the QDBRRCDL API

 

 

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:

 

JobName

JobUser

JobNbr

LockStatus

LockType

LockedRRN

QPADEV0001

MIKE

1443

0

1

1

QZDASOINIT

QUSER    

1446

0

1

12

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

 

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. He can be contacted at This email address is being protected from spambots. You need JavaScript enabled to view it..


BLOG COMMENTS POWERED BY DISQUS

LATEST COMMENTS

Support MC Press Online

$

Book Reviews

Resource Center

  • SB Profound WC 5536 Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application. You can find Part 1 here. In Part 2 of our free Node.js Webinar Series, Brian May teaches you the different tooling options available for writing code, debugging, and using Git for version control. Brian will briefly discuss the different tools available, and demonstrate his preferred setup for Node development on IBM i or any platform. Attend this webinar to learn:

  • SB Profound WP 5539More than ever, there is a demand for IT to deliver innovation. Your IBM i has been an essential part of your business operations for years. However, your organization may struggle to maintain the current system and implement new projects. The thousands of customers we've worked with and surveyed state that expectations regarding the digital footprint and vision of the company are not aligned with the current IT environment.

  • SB HelpSystems ROBOT Generic IBM announced the E1080 servers using the latest Power10 processor in September 2021. The most powerful processor from IBM to date, Power10 is designed to handle the demands of doing business in today’s high-tech atmosphere, including running cloud applications, supporting big data, and managing AI workloads. But what does Power10 mean for your data center? In this recorded webinar, IBMers Dan Sundt and Dylan Boday join IBM Power Champion Tom Huntington for a discussion on why Power10 technology is the right strategic investment if you run IBM i, AIX, or Linux. In this action-packed hour, Tom will share trends from the IBM i and AIX user communities while Dan and Dylan dive into the tech specs for key hardware, including:

  • Magic MarkTRY the one package that solves all your document design and printing challenges on all your platforms. Produce bar code labels, electronic forms, ad hoc reports, and RFID tags – without programming! MarkMagic is the only document design and print solution that combines report writing, WYSIWYG label and forms design, and conditional printing in one integrated product. Make sure your data survives when catastrophe hits. Request your trial now!  Request Now.

  • SB HelpSystems ROBOT GenericForms of ransomware has been around for over 30 years, and with more and more organizations suffering attacks each year, it continues to endure. What has made ransomware such a durable threat and what is the best way to combat it? In order to prevent ransomware, organizations must first understand how it works.

  • SB HelpSystems ROBOT GenericIT security is a top priority for businesses around the world, but most IBM i pros don’t know where to begin—and most cybersecurity experts don’t know IBM i. In this session, Robin Tatam explores the business impact of lax IBM i security, the top vulnerabilities putting IBM i at risk, and the steps you can take to protect your organization. If you’re looking to avoid unexpected downtime or corrupted data, you don’t want to miss this session.

  • SB HelpSystems ROBOT GenericCan you trust all of your users all of the time? A typical end user receives 16 malicious emails each month, but only 17 percent of these phishing campaigns are reported to IT. Once an attack is underway, most organizations won’t discover the breach until six months later. A staggering amount of damage can occur in that time. Despite these risks, 93 percent of organizations are leaving their IBM i systems vulnerable to cybercrime. In this on-demand webinar, IBM i security experts Robin Tatam and Sandi Moore will reveal:

  • FORTRA Disaster protection is vital to every business. Yet, it often consists of patched together procedures that are prone to error. From automatic backups to data encryption to media management, Robot automates the routine (yet often complex) tasks of iSeries backup and recovery, saving you time and money and making the process safer and more reliable. Automate your backups with the Robot Backup and Recovery Solution. Key features include:

  • FORTRAManaging messages on your IBM i can be more than a full-time job if you have to do it manually. Messages need a response and resources must be monitored—often over multiple systems and across platforms. How can you be sure you won’t miss important system events? Automate your message center with the Robot Message Management Solution. Key features include:

  • FORTRAThe thought of printing, distributing, and storing iSeries reports manually may reduce you to tears. Paper and labor costs associated with report generation can spiral out of control. Mountains of paper threaten to swamp your files. Robot automates report bursting, distribution, bundling, and archiving, and offers secure, selective online report viewing. Manage your reports with the Robot Report Management Solution. Key features include:

  • FORTRAFor over 30 years, Robot has been a leader in systems management for IBM i. With batch job creation and scheduling at its core, the Robot Job Scheduling Solution reduces the opportunity for human error and helps you maintain service levels, automating even the biggest, most complex runbooks. Manage your job schedule with the Robot Job Scheduling Solution. Key features include:

  • LANSA Business users want new applications now. Market and regulatory pressures require faster application updates and delivery into production. Your IBM i developers may be approaching retirement, and you see no sure way to fill their positions with experienced developers. In addition, you may be caught between maintaining your existing applications and the uncertainty of moving to something new.

  • LANSAWhen it comes to creating your business applications, there are hundreds of coding platforms and programming languages to choose from. These options range from very complex traditional programming languages to Low-Code platforms where sometimes no traditional coding experience is needed. Download our whitepaper, The Power of Writing Code in a Low-Code Solution, and:

  • LANSASupply Chain is becoming increasingly complex and unpredictable. From raw materials for manufacturing to food supply chains, the journey from source to production to delivery to consumers is marred with inefficiencies, manual processes, shortages, recalls, counterfeits, and scandals. In this webinar, we discuss how:

  • The MC Resource Centers bring you the widest selection of white papers, trial software, and on-demand webcasts for you to choose from. >> Review the list of White Papers, Trial Software or On-Demand Webcast at the MC Press Resource Center. >> Add the items to yru Cart and complet he checkout process and submit

  • Profound Logic Have you been wondering about Node.js? Our free Node.js Webinar Series takes you from total beginner to creating a fully-functional IBM i Node.js business application.

  • SB Profound WC 5536Join us for this hour-long webcast that will explore:

  • Fortra IT managers hoping to find new IBM i talent are discovering that the pool of experienced RPG programmers and operators or administrators with intimate knowledge of the operating system and the applications that run on it is small. This begs the question: How will you manage the platform that supports such a big part of your business? This guide offers strategies and software suggestions to help you plan IT staffing and resources and smooth the transition after your AS/400 talent retires. Read on to learn: