Are you sick of queries eating your lunch? In this and upcoming issues of the newsletter, Carsten provides us with a control system for easily managing query attributes and optimizing query performance by User Profile.

The control system consists partly of the custom-written commands presented here, including ADDUSRQRYA (Add User Query Attributes), CHGUSRQRYA (Change User Query Attributes), and RMVUSRQRYA (Remove User Query Attributes). These commands interact with the provided Query Governor Exit Point program to put you in control of the queries your users run. (Look for other commands in an upcoming issue.)

With these commands, you can specify an interactive query time limit and a QAQQINI query options file to be used for each user profile. Using the CHGUSRQRYA command, you can also temporarily override the interactive query time limit and allow interactive queries to run for the specified user, regardless of the estimated query run time and without changing the interactive query time limit.

Here is IBM's documentation for the QAQQINI query control attributes.

By specifying an appropriate copy of the QAQQINI query options file, you can enforce different sets of query options for individual user profiles without hard coding a Change Query Attributes (CHGQRYA) command in the user profile's initial program (or anywhere else). You will, however, need to include a Set User Query Attributes (SETUSRQRYA) command in the initial program and/or in your routing programs. But one of the big advantages is that you can change the Query Attributes for any user at any time without recompiling.

Here is IBM's documentation on the V5R4 query governor exit point.

In an upcoming issue of the newsletter, I'm going to present the aforementioned SETUSRQRYA command, as well as the Work with User Query Attributes (WRKUSRQRYA) command. I'll also provide you with further information about how to make all these components work together.

Here's an overview of the sources included with today's newsletter:

CBX979      RPGLE       Query Governor Exit Program

CBX980      RPGLE       User Query Attributes - services         
CBX980B     SRVSRC      User Query Attributes - binder source    

CBX9811     RPGLE       Add User Query Attributes - CPP          
CBX9811H    PNLGRP      Add User Query Attributes - Help         
CBX9811V    RPGLE       Add User Query Attributes - VCP          
CBX9811X    CMD         Add User Query Attributes                

CBX9812     RPGLE       Change User Query Attributes - CPP       
CBX9812H    PNLGRP      Change User Query Attributes - Help  
CBX9812O    RPGLE       Change User Query Attributes - POP   
CBX9812V    RPGLE       Change User Query Attributes - VCP   
CBX9812X    CMD         Change User Query Attributes         

CBX9813     RPGLE       Remove User Query Attributes - CPP   
CBX9813H    PNLGRP      Remove User Query Attributes - Help  
CBX9813V    RPGLE       Remove User Query Attributes - VCP   
CBX9813X    CMD         Remove User Query Attributes         

CBX980M     CLP         User Query Attributes - build commands    

If you compile and run the CBX980M CL program and specify a target library as the only parameter, all of the above objects will be created for you. Please be sure to read the CBX980M source header for further instructions.

I've also included information about how to register the CBX979 Query Governor Exit Program in the source header of the CBX979 RPGLE source, but I would suggest that you to postpone that registration until the User Query Attributes command suite and the accompanying information has been completed.

You can download a zip file containing all the source code here.