Query Control

April 14, 2010 Paul Tuohy

Query is the system killer. The lights dim. The system slows to a crawl. It must be a user running an interactive query.

Enough! No more interactive query for users–we are removing the *INTERACT option on the ALLOW parameter on the RUNQRY command. Sound familiar?

This extreme reaction of disabling interactive query is both unnecessary and, to be blunt, downright unfair to users. By disabling interactive queries, you also disable the ability to use the F5 (Report) key in Query, which is probably the most used function. And that, in turn, gets users into the frame of mind that, if they want to do any sort of analysis of data on the system, they must first of all copy it off the system.

What we want is the best of both worlds: users can use the F5 key in Query as much as they want unless it is going to have an adverse effect on system performance. In which case they should get a friendly message that tells them to submit the query to batch.

And to achieve this feat all you need is a library, a message file, a command, a data area, and two CL programs.

Controlling Interactive Queries

The ability to control the effects of interactive queries is provided by the Change Query Attributes (CHGQRYA) command. The parameter we are interested in is the Query Time Limit (QRYTIMLMT) parameter as shown in sample Code 1.

Code 1--Setting the query time limit to one minute:
CHGQRYA QRYTIMLMT(60)

The QRYTIMLMT parameter sets the maximum for the estimated number of seconds required for a query to run. This means that the decision to run a query is based on an estimate as opposed to actually running the query for the specified amount of time and then telling the user that it cannot be done.

Unfortunately it is not just a matter of simply running the CHGQRYA command in a job. There are a few terms and conditions:

The CHGQRYA command requires that the profile running the command have *JOBCTL special authority.
CHGQRYA is not just for Query, but applies to any type of query for the job, which includes interactive SQL, embedded SQL , OPNQRYF, etc.
Also, if the query is going to exceed the estimated time, the user is presented with a message that provides the option of canceling the request or continuing with the query (as shown in Code 2), which is fine if you can trust your users to make the right decision.

Code 2--Message displayed when estimated query time limit will be exceeded:
Estimated query processing time 1 exceeds limit 0 (C I)

We need a means of only applying the query time limit to interactive queries being used in Query and we don’t want the user to have the choice of continuing with the query if they get the processing time exceeded message.

We can achieve this by writing our own version of the WRKQRY command.

The Library

First, we need to have a library that precedes QSYS in the system portion of the library list. Our WRKQRY command will be placed in this library, which means that this is the command that will be run when the unqualified command name of WRKQRY is used from any command line or within any CL program. To run the original IBM-supplied command we must use the qualified name of QSYS/WRKQRY.

There is a good chance that you already have a library on your system that is being used for company specific commands or functions. You can check using the DSPLIBL command. If possible, use an existing library, but if not, create the library you want to use. The library is called MYQSYS in the following examples.

Having created the library, use the command WRKSYSVAL SYSVAL(QSYSLIBL) to change the system portion of the library list and ensure that the new library (MYQSYS) precedes QSYS in the library list. You should always be careful when changing the system portion of the library list (or any system value for that matter), so start a second session, sign in, and make sure the library list is correct. You must also make sure that the library cannot be inadvertently deleted; if it is, no one will be able to sign on.

The Message File

Let’s start with controlling the message that is displayed to the user. We do this by creating our own message file, merging a message into it, and changing the message text, as follows in sample Code 3.

Code 3:
CRTMSGF MSGF(MYQSYS/MYMSGF) TEXT('Override Query Message')
MRGMSGF FROMMSGF(QQRYMSG) TOMSGF(MYQSYS/MYMSGF) SELECT(QRY2293)
CHGMSGD MSGID(QRY2293) MSGF(MYQSYS/MYMSGF) MSG('This query will take
too long to run at the screen. Please submit it to batch.')

QRY2293 is the message that is issued by query, as shown in Code 2.

The Data Area

Although the data area is not totally necessary, I prefer to store the estimated time limit value somewhere it can be easily changed. Use the following command to create the data area:

Code 4:
CRTDTAARA DTAARA(MYQSYS/QRYTIMLMT) TYPE(*DEC) LEN(10 0)
      VALUE(60) TEXT('Query Time Limit')

We can then use the Change Data Area (CHGDTAARA) command to set the appropriate time limit value in seconds.

The CL Programs

We need two CL programs. The reason for the two programs is that we need to make use of adopted authority. The first program, WRKQRYCPP1, is shown in sample Code 5.

Code 5--The WRKQRYCPP1 program:
DCL        VAR(&INQMSGRPY) TYPE(*CHAR) LEN(10)
DCL        VAR(&QRYTIMLMT) TYPE(*DEC) LEN(10 0)

OVRMSGF    MSGF(QQRYMSG) TOMSGF(MYMSGF)

RTVJOBA    INQMSGRPY(&INQMSGRPY)
CHGJOB     INQMSGRPY(*DFT)

RTVDTAARA  DTAARA(QRYTIMLMT) RTNVAR(&QRYTIMLMT)
CHGQRYA    QRYTIMLMT(&QRYTIMLMT)

CALL       PGM(WRKQRYCPP2)

CHGJOB     INQMSGRPY(&INQMSGRPY)
CHGQRYA    QRYTIMLMT(*SYSVAL)
DLTOVR     FILE(QQRYMSG)

The main points to note are:

The QQRYMSG message file is overridden to MYMSGF. This means that when query sends the QRY2293 message it is the message in MYMSGF that will be sent as opposed to the one in QQRYMSG. Any other query messages will be sent from QQRYMSG.
The current job value for Inquiry Message Reply is retrieved and stored, and the job is changed to reply to all inquiry messages with their default replies.
The value in the QRYTIMLMT data area is retrieved and used to set the required time limit using the CHGQRYA command.
The WRKQRYCPP2 program is called. This program, as shown in Code 7, simply performs a qualified call to QSYS/WRKQRY.
Upon returning from WRKQRYCPP2, the query time limit is reset to the system value and the original value for the jobs Inquiry Message Reply value is restored. The override to QQRYMSG is then deleted.
Everything is now as it was before the program was called.

Since the CHGQRYA command requires *JOBCTL authority, the WRKQRYCPP1 program must be compiled to use adopted authority and the owning profile must be one that has *JOBCTL authority. Assuming that your profile has *JOBCTL authority, use the following command to create the program:

Code 6:
CRTCLPGM PGM(MYQSYS/WRKQRYCPP1) SRCFILE(QCLSRC) SRCMBR(WRKQRYCPP1)
       USRPRF(*OWNER)

Enter the source for the WRKQRYCPP2 CL program shown in Code 7.

Code 7--The WRKQRYCPP2 program:
QSYS/WRKQRY

The program simply runs the WRKQRY command in QSYS. The reason for the separate program is that we do not want query to be running under the adopted authority of the profile that owns WRKQRYCPP1. So, after compiling the program, change the Use Adopted Authority value for the program using the command:

Code 8:
CHGPGM PGM(MYQSYS/WRKQRYCPP2) USEADPAUT(*NO)

The Command

Lastly, we need to create our own WRKQRY command. Enter the source for the WRKQRY command shown in Code 9.

Code 9--The WRKQRY command source:
CMD        PROMPT('Work with Queries')

Use the following command to create the WRKQRY command:

Code 10:
CMD        PROMPT('Work with Queries')

Code 11:
CRTCMD CMD(MYQSYS/WRKQRY) PGM(WRKQRYCPP1) SRCFILE(QCMDSRC)
   SRCMBR(WRKQRY)

There You Have It

We have created our own version of the WRKQRY command that allows us to determine how much time should be allotted for interactive queries. We can change the allowed time by changing the value of the QRYTIMLMT data area.

Of course, you could implement a solution that, instead of using a data area, you use a database that has time limits for individual users or departments or whatever you like.

The important point is that the user will receive a “friendly” message if they go to run a query that exceeds the designated time limit–regardless of whether they press F5 from within a query definition or use option 9 to run a query.

We now have the best of both worlds: no interactive queries killing the system and users can still make use of F5–as long as it will not have a detrimental effect on the system.

Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot