 | |||
|
|
||
| |||
| |||
| |
|
Home > AS/400 Tips > iSeries programmer tips > Take control of Query |
| |
|
|
|
| iSeries 400 Tips: |
|
|
|
|
|
|
|
|
|||||
|
ISERIES PROGRAMMER TIPS Take control of QueryPaul Tuohy 10.07.2004 Rating: -3.88- (out of 5)
|
[IMAGE] | |||||
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.
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 t
Digg This!
 StumbleUpon
 Del.icio.us
|
 |
 |
| |
RELATED RESOURCES
|
he 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.
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.
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:
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:
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.
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:
Enter the source for the WRKQRYCPP2 CL program shown below.
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:
The Command
Enter the source for the WRKQRY command shown below.
Use the following command to create the WRKQRY command:
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?"
|
DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk. |
|
|||
