If your shop allows users to create and run their own queries or if your systems include many predefined queries that users run often, you may want to consider using the Change Query Attributes (CHGQRYA) command to help lessen interactive workloads.
The Change Query Attributes command allows you to establish processing attributes for all subsequent queries executed in a job. It covers all database queries regardless of their origin (i.e., Query/400, SQL, Query Management, etc.).
Predictive query governor stops runaway queries before they start
The Query processing time limit parameter (QRYTIMLMT) governs the amount of CPU processing time a query is allowed to use. The best part is the system estimates (i.e., predicts) how long the query will take to run before it actually starts running it. If the estimate exceeds the value you specify on this parameter, the system will send an inquiry message to the user asking whether or not to proceed. The message shows the estimated time and the time limit specified on the QRYTIMLMT parameter. The user has the option of canceling the query (which will result in an escape message being sent to your program) or ignoring the message and allowing the query to run.
Parallel processing boosts performance
You use the Parallel processing degree parameter (DEGREE) to set the extent that the system can process the query using parallel I/O processing methods. Accordingly, this only applies to systems with multiple I/O processors. Specifying this parameter on a single I/O processor machine does not result in an error; the system simply ignores it.
How to put CHGQRYA to work
The CHGQRYA command can be executed on a command line or in a program. All subsequent queries are executed with the attributes specified on the last CHGQRYA command executed in the job. Attributes stay in effect until they are changed again with another CHGQRYA command or the job ends.
One negative aspect of this command is that it lets the user decide whether or not to run a query that is expected to exceed the time limit. Many shops do not want the user to have that authority. In those cases, you have a couple of options:
- Add a reply list entry to the system reply list (ADDRPYLE command) for message CPA4259 so any query expected to exceed the time limit is automatically canceled. The program running the query will be sent an escape message. Note: You must also use the CHGJOB command (as in CHGJOB INQMSGRPY(*SYSRPYL)), so the job will search the system reply list for automatic replying of inquiry messages.
- Change the valid reply values for message CPA4259 from 'I' or 'C' to just 'C'. Use the following command:
CHGMSGD MSGID(CPA4259) MSGF(QCPFMSG) VALUES('C')
SPCVAL(('c' 'C'))
You may also want to change the message text so 'I' does not appear as a valid value to the user. Use the WRKMSGD command to edit the message text. Caution: Changing an IBM message description is fine; however, any changes will most likely be lost the next time you upgrade your operating system. So be prepared.
Note: The first method above is more or less job-specific, although the added system reply list entry will be used by any job specifying INQMSGRPY(*SYSRPYL). The second method is system wide; all jobs will be affected.
-----------------------------------
About the author: Ron Turull is editor of Inside Version 5. He has more than 20 years experience programming for and managing AS/400-iSeries systems.
==================================
MORE INFORMATION
==================================
- Improving OPNQRYF performance
Search400.com member David Moore shows you how to improve OPNQRYF performance by entering STRDBG and running a CL that contains the OPNQRYF that needs to be improved.
- Limiting maximum Qtemp storage on query joins
If you have ever experienced the problem of a runaway Query or SQL, you may know of the potential danger that exists with your DASD reserve going critical. Often ill constructed joins will run for an excessive amount of time, building work files in the jobs Qtemp library. This technique provides a way to set a ceiling limit of the amount of temporary storage a job can use.
- Tuning your iSeries
Is there a simple way to tune up the iSeries? Site expert Tim Granatir points out a few methods.
