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:
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.
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:
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)
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.