CL exit program to dump plan cache information for query

This Query Supervisor exit program dumps plan cache information about the query that reached a threshold.

To compile this ILE CL program, use the following command. Before compiling, the sample source must be modified as indicated by the comment in the code. The USER parameter on the SBMJOB command must name a profile on the system that has the required authority to call QSYS2.DUMP_PLAN_CACHE(). This authority is *JOBCTL special authority or QIBM_DB_SQLADM function usage. In addition, the owner of the QS_DMPQRY program must have *USE authority to the profile specified on the USER parameter. One option is to make USER the same as the program owner.

CRTBNDCL PGM(SUPERVISOR/QS_DMPQRY) SRCFILE(SUPERVISOR/QCLSRC) DFTACTGRP(*NO) ACTGRP(*CALLER) USRPRF(*OWNER)

The program can be registered as an exit program using this command:

ADDEXITPGM EXITPNT(QIBM_QQQ_QRY_SUPER) FORMAT(QRYS0100) PGMNBR(*LOW) PGM(SUPERVISOR/QS_DMPQRY) 
THDSAFE(*YES) TEXT('Query supervisor dump plan cache')

By using USRPRF(*OWNER), *PUBLIC does not need to be given access to the objects used by this program. The owning profile of the created program must have authority to the commands and objects used by the exit program. The program will run in the activation group of the caller.

             PGM        PARM(&QRYS0100 &RC)                           
             DCL        VAR(&QRYS0100) TYPE(*CHAR) LEN(8192)          
             DCL        VAR(&RC) TYPE(*INT) LEN(4)                    
             DCL        VAR(&BINLOW) TYPE(*UINT) LEN(4)               
             DCL        VAR(&BINHIGHU) TYPE(*UINT) LEN(4)             
             DCL        VAR(&MAXINT) TYPE(*DEC) LEN(15 0) +           
                          VALUE(4294967296)                           
             DCL        VAR(&PLANID) TYPE(*DEC) LEN(15 0)             
                                                                      
             MONMSG     MSGID(CPF0000)                                
 /* INIT RETURN CODE TO CONTINUE RUNNING QUERY */                     
             CHGVAR     VAR(&RC) VALUE(0)                             
                                                                      
 /* GET VALUES FROM THE INPUT FORMAT */                               
             CHGVAR     VAR(&BINHIGHU) VALUE(%BINARY(&QRYS0100 67 4)) 
             CHGVAR     VAR(&BINLOW) VALUE(%BINARY(&QRYS0100 71 4))   
             CHGVAR     VAR(&PLANID) VALUE(%DEC(&BINHIGHU 15 0) * +    
                          &MAXINT + %DEC(&BINLOW 15 0))                
                                                                       
 /* SUBMIT JOB TO DUMP THE PLAN CACHE FOR THIS QUERY */                
             SBMJOB     CMD(RUNSQL SQL('CALL QSYS2.DUMP_PLAN_CACHE(' + 
                          *BCAT 'FILESCHEMA => ''SUPERVISOR'', +       
                          FILENAME => ''PLANDUMPS'',  +                
                          PLAN_IDENTIFIER=> ''' *TCAT +                
                          %CHAR(&PLANID) *TCAT ''')')) +               
                          USER(MYAUTHUSER)                                
                          /* Replace MYAUTHUSER with a user profile */
                          /* authorized to DUMP_PLAN_CACHE().       */                                            
             ENDPGM