Most iSeries sites have a lot of Query/400 users, and most iSeries sites have to put up with the headache of users running the killer interactive query that brings response times to its knees.
Some shops take the extreme step of disabling interactive queries so users have to run all queries in batch. Of course, that has the side effect of disabling the F5 function while the user is developing a query which just about removes 60% of the benefit of query from the user's point of view. F5 is probably the key most used by users when developing queries.
What you really want is the ability to control the overhead that interactive queries have on the system. You want users to be able to make copious use of F5 unless it will have an adverse effect of the system. The ability to do that is provided by the Change Query Attributes (CHGQRYA) command, which allows you to specify the maximum amount of time that can be taken by a query running in the job.
CHGQRYA
Let's start by looking at how the CHGQRYA command works. The parameter you are interested in is the Query Time Limit (QRYTIMLMT) parameter as shown in Figure 1.
CHGQRYA QRYTIMLMT(60)
Figure 1: Setting the query time limit to one minute.
The QRYTIMLMT parameter sets the maximum for the estimated number of seconds required for a query to run. There is a good and a bad side to this parameter. The good side is that the value is used as an estimate, i.e. 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. The bad side is that the specified value is not just for Query but applies to any type of query for the job, which includes OPNQRYF, embedded SQL, etc.
Also, if the query is going to exceed the estimated time, the user is presented with a message that provides the option of cancelling the request or continuing with the query (as shown in Figure 2), which is fine if you can trust your users to make the right decision.
Estimated query processing time 1 exceeds limit 0 (C I)
Figure 2: Message displayed when estimated query time limit will be exceeded.
So you want a means of applying the query time limit to interactive queries being used in Query, and you don't want the user to have the choice of continuing with the query.
You can achieve this by writing your own version of the WRKQRY command, but first you will need to make sure that the environment is right. This consists of a library (which you may already have), a message file, a data area, two CL programs and a command.
The Library
You will need to have a library that precedes QSYS in the system portion of the library list. Your 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. You can still run the original command by qualifying it -- QSYS/WRKQRY.
Make sure the library exists (the library is named MYQSYS in the following examples), and use the command WRKSYSVAL SYSVAL(QSYSLIBL) to change the system portion of 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.
The Message File
You need a message file that will contain a new version of the QRY2293 message. Use the Create Message File command to create a message file (the message file is named MYMSGF in the following examples) in the library created above.
Now you need to copy a couple of messages into it. Use the Merge Message File command (MRGMSGF) as follows to copy the QRY2293 message to your message file.
MRGMSGF FROMMSGF(QQRYMSG) TOMSGF(MYQSYS/MYMSGF) SELECT(QRY2293)
Now change the text of the message (in MYMSGF) to something that might make more sense to the users; maybe something along the lines of this:
This query may not be run at the screen. Please submit it to batch.
The Data Area
You need a data area that will contain the estimated time limit value. Use the following command to create the data area:
CRTDTAARA DTAARA(MYQSYS/QRYTIMLMT) TYPE(*DEC) LEN(10 0)
VALUE(60) TEXT('Query Time Limit')
You can then use the Change Data Area command (CHGDTAARA) to set the appropriate time limit value in seconds.
The CL Programs
Enter the source for the WRKQRYCPP1 CL program shown below.
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 program starts by overriding the QQRYMSG message file to MYMSGF. The current job value for Inquiry Message Reply is retrieved, 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 and, upon returning, 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.
The WRKQRYCPP1 program must be compiled to use adopted authority, and the owning profile must be one that has *JOBCTL authority because the CHGQRYA command requires *JOBCTL authority. Use the following command to create the program:
CRTCLPGM PGM(MYQSYS/WRKQRYCPP1) SRCFILE(QCLSRC) SRCMBR(WRKQRYCPP1)
USRPRF(*OWNER)
Enter the source for the WRKQRYCPP2 CL program shown below.
QSYS/WRKQRY
The program simply runs the WRKQRY command in QSYS. The reason it is placed in a separate program is because you 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:
CHGPGM PGM(MYQSYS/WRKQRYCPP2) USEADPAUT(*NO)
The Command
Enter the source for the WRKQRY command shown below.
CMD PROMPT('Work with Queries')
Use the following command to create the WRKQRY command:
CRTCMD CMD(MYQSYS/WRKQRY) PGM(WRKQRYCPP1) SRCFILE(QCMDSRC)
SRCMBR(WRKQRY)
And now . . .
You have created your own version of the WRKQRY command that allows you to determine how much time should be allotted for interactive queries. You change the allowed time by changing the value of the QRYTIMLMT data area.
The user will receive a "friendly" message if he or she tries 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.
---------------------------
About the author: Paul Tuohy is CEO of ComCon, an iSeries consulting company.
He is the author of Re-Engineering RPG Legacy Applications and is one of the quoted
industry experts in the IBM Redbook "Who Knew You Could Do That With RPG IV?"
