Today, I will present the final pieces of the puzzle that is the User Query Attribute command suite. In previous issues of this newsletter, I have provided the following User Query Attribute commands:

• Add User Query Attributes (ADDUSRQRYA)
• Change User Query Attributes (CHGUSRQRYA)
• Remove User Query Attributes (RMVUSRQRYA)
• Work with User Query Attributes (WRKUSRQRYA)

Using the above commands, you can store, change, remove, and work with query attributes for specific user profiles. As you might recall, the user query attributes include a "Query interactive time limit" and a "Query options file library," as well as an option to temporarily allow interactive queries to be run, irrespective of the estimated run time. Now it is time to put the user query attributes themselves to work.

To do so, I've created the Set User Query Attributes (SETUSRQRYA) command. The SETUSRQRYA command runs the Change Query Attribute (CHGQRYA) command for the current job specifying the two aforementioned query attributes if the current job is an interactive job, and the "Query option file library" if the current job is a batch job.

The CHGQRYA command requires *JOBCTL special authority to run, and for this reason the SETUSRQRYA command processing program (CPP) adopts user profile QSECOFR authority. If you prefer another user profile to provide the necessary *JOBCTL special authority, you can just change the CPP ownership.

The obvious challenge related to running the SETUSRQRYA command is to ensure that it is run at job initiation time in order for the query attributes to be applied before any query or SQL activity can be performed in the current job. For interactive jobs, one option is to use the user profile's initial program. Using a subsystem routing entry program is another option. Routing entry programs are run as the first routing step in the job initiation process.

In doing so, subsystem routing entries (1) ensure that the proper job run attributes are assigned to the job and (2) eventually call the specified routing program to start processing the job request. This makes routing entry programs a good candidate for setting up job execution attributes such as the query attributes. I’ve included a routing program example (CBX983C) to show you how to take advantage of routing programs for this purpose. For more information on routing entries as well as a utility to work with them, follow the links in this article, and also check out Gary Guthrie’s "Demystifying Routing" article.

In any event, it is crucial that the SETUSRQRYA command is run as part of the initiation of the jobs that you want to be governed by the settings stored with either the user query attributes or the QAQQINI query attribute file (pointed to by the aforementioned user query attributes). For now, let us have a closer look at the SETUSRQRYA command prompt:

                  Set User Query Attributes (SETUSRQRYA)                 
                                                                           
Type choices, press Enter.                                                 
                                                                           
Group profile option . . . . . .   *NONE         *NONE, *GRPDFT, *GRPONLY  

In reality, the SETUSRQRYA command didn't need any parameters, since the command’s only purpose it to activate settings already specified by previous commands. To make setup and administration potentially easier, I've included a "group profile option" parameter. This option allows you to specify that the user query attributes should be retrieved from the current user profile’s group profile. Either exclusively or by default, as you prefer. Please refer to the command's help text for further information.

When the system applies query attributes, it examines three different sources when deciding which value to use for the current job:

1. The CHGQRYA command
2. The QAQQINI query options file applying to the job
3. The query options system values, e.g., QQRYTIMLMT and QQRYDEGREE

The query attributes are retrieved in the above sequence and preference. This implies that if a QAQQINI file has been assigned to a job (using the CHGQRYA command), the relevant query attributes will be retrieved from the QAQQINI file, as opposed to the corresponding system value. The specific QAQQINI query attribute might in turn point to the system value, but the QAQQINI file rules. Likewise, any query attribute explicitly specified on a CHGQRYA command will take precedence over QAQQINI or system value specified attributes. To help you quickly locate all user query attribute commands, I've included a command menu, CMDUSRQRYA, which provides immediate access to all relevant commands.

CMDUSRQRYA              User Query Attributes Commands                        
                                                            System:   NOVASTAR
Select one of the following:                                                  
                                                                              
  Commands                                                                    
     1. Add User Query Attributes                                   ADDUSRQRYA
     2. Change User Query Attributes                                CHGUSRQRYA
     3. Remove User Query Attributes                                RMVUSRQRYA
                                                                              
     5. Work with User Query Attributes                             WRKUSRQRYA
                                                                              
     8. Set User Query Attributes                                   SETUSRQRYA
                                                                              
  Related Command Menus                                                       
    10. Query Commands                                              CMDQRY    
                                                                              
                                                                         Bottom
Selection or command                                                          
===>                                                                          
                                                                              
F3=Exit   F4=Prompt   F9=Retrieve   F12=Cancel      

The SETUSRQRYA command and CMDUSRQRYA command menu is based on the following source members:

CBX983  -- RPGLE  -- Set User Query Attributes - CPP             
CBX983C -- CLP    -- Set User Query Attributes - Routing Program 
CBX983H -- PNLGRP -- Set User Query Attributes - Help            
CBX983X -- CMD    -- Set User Query Attributes                   
CBX983U -- UIM    -- User Query Attributes Commands
CBX983M -- CLP    -- Set User Query Attributes - Build command   

To have the command created for you, please compile and run the CBX983M CL program, following the instructions in the source header.

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