While the Query/400 product is an old work horse heading for retirement, it is still widely used in many shops around the world. For this reason, and even as more contemporary query and BI products are taking over, it is important to emphasize that as long as Query/400 is installed on a system it calls for consideration both in terms of operational and security aspects.

In order to address some of these requirements Query/400 has over time been subject to a number of enhancements delivered by means of special data areas. To document all these data areas and the features implemented, IBM has created a Technical Document, which in detail describes each data area, when it was introduced and exactly how it is used. You can find the document here.

While the majority of the data areas are depending on a quite simple format as far as the data area content is concerned, there's especially one, very useful data area which defines a lot of settings. The QQUPRFOPTS Query User Option data area, which controls many of the most significant parameters that are specified for the Submit Job (SBMJOB) command when a query is submitted to batch -- and also a recently added (V5R1) switch allowing you to disable the query output file option for specific user profiles. I therefore decided to create a command interface to manage the QQUPRFOPTS data area and came up with the Set Query Profile Options (SETQRYPRFO) command.

Here's how the command's prompt panel looks:

                     Set Query Profile Options (SETQRYPRFO)                   
                                                                              
 Type choices, press Enter.                                                   
                                                                              
 Profile options library  . . . .                 Name                        
 Debug cursor position  . . . . .   *MSGLIN       *MSGLIN, *NONDBG            
 Submit job parameters:                                                       
   Routing data . . . . . . . . .   *JOBD         *JOBD, QCMDB                
   Output queue . . . . . . . . .   *JOBD         *JOBD, *CURRENT, *USRPRF... 
   Print device . . . . . . . . .   *JOBD         *JOBD, *CURRENT, *USRPRF... 
   Inquiry message reply  . . . .   *JOBD         *JOBD, *RQD, *DFT, *SYSRPYL 
   Job name . . . . . . . . . . .   *JOBD         Name, *JOBD, *QRY           
   Job description  . . . . . . .   *USRPRF       Name, *USRPRF               
     Library  . . . . . . . . . .                 Name                        
   Job queue  . . . . . . . . . .   *JOBD         Name, *JOBD                 
     Library  . . . . . . . . . .                 Name                        
   User . . . . . . . . . . . . .   *JOBD         *JOBD, *CURRENT             
 Allow output file  . . . . . . .   *YES          *NO, *YES                   
 Output file initial size:                                             
   Initial number of records  . .   *NOMAX        1-2147483646, *NOMAX 
   Increment number of records  .                 1-32767              
   Maximum increments . . . . . .                 1-32767              
 Create profile opts data area  .   *NO           *NO, *YES     

All parameters are documented in the online help text panel group accompanying the SETQRYPRFO command -- and of course also in the IBM Technical Document referred to above. The following sources are involved in creating the SETQRYPRFO command:

CBX993      RPGLE       Set Query Profile Options - CPP           
CBX993H     PNLGRP      Set Query Profile Options - Help          
CBX993O     RPGLE       Set Query Profile Options - POP           
CBX993V     RPGLE       Set Query Profile Options - VCP           
CBX993X     CMD         Set Query Profile Options                 
CBX993M     CLP         Set Query Profile Options - Build Command 

To have the SETQRYPRFO command created for you, copy the source members to their respective source files and follow the instructions in the CBX993M CL program.

You can download the code here!

In an upcoming issue of the Systems Management Newsletter I will present a Work with Query Profile Options (WRKQRYPRFO) command allowing you to monitor and manage all or a subset of Query Profile Options data areas on your system, so stay tuned!