Optional: Configuring Db2 SQL Performance Analyzer for use with QMF

You can optionally configure Db2 SQL Performance Analyzer to interface with QMF. Tools Customizer creates the necessary JCL, but some of the following steps must be completed before running the JCL.

Procedure

  1. Create the interface between Db2 SQL Performance Analyzer and QMF.
    1. Copy the contents of the ANLQMF member in the high_level_qualifier.SANLSAMP library.
    2. In the DSQUEGV1 member of the high_level_qualifier.SDSQUSRE library, paste the ANLQMF code between the code that is labeled GET ADDRESSES OF DXEXCBA AND DXEGOVA CNTL BLOCKS, which is located before the USING WORK,WORKPTR statement, and the code that is labeled SET SCOPE VALUE TO ZERO ON INITIALIZATION, which is located before the CLI GOVFUNCT,GOVINIT statement, in the governor exit program in any supported version of QMF.
    3. Save the changes to DSQUEGV1.
  2. Customize the interface between Db2 SQL Performance Analyzer and QMF by modifying the parameters that define the target host system.
    1. Open the Db2 SQL Performance Analyzer (SQL PA) Governor Interface program, ANLGOV1, which is located in the high_level_qualifier.SANLSAMP library, and find the ANLPARM parameters list near the end of the program code.
      The following code shows the default ANLPARM settings in ANLGOV1:
      Figure 1. Default host parameter set for SQL PA under QMF
      ANLPARM  DC  H'800'                 STORE UP TO 50 SQL PA PARMS HERE
ANLCARDS DS  0CL800                 PASSED TO PLI PGM ANLQMF
         DC  CL16'SUBSYST DSN '     REQUIRED SUBSYTEM ID FOR CAF
         DC  CL16'REPORTS MAX'      MAX= WARNINGS ONLY; ANY=ALL QUERIES
         DC  CL16'SEEMSGS NO '      SEE INFORMATIONAL ERROR MSGS? 
         DC  CL16'VERSION V10R1'    CURRENT Db2 RELEASE V10R1 V11R1 ...
         DC  CL16'SUBVERS V10NFM'   Db2 LEVEL IS V10NFM V10CM8...
         DC  CL16'STORAGE 3390-3'   DASD POOL IS 3390 MOD 3
         
         DC  CL16'DEGREES ANY'      ENABLE PARALLEL I/O: ANY OR ONE|1
         DC  CL16'REFRESH ANY'      REFRESH MQTS USE FOR APS: ANY|NO
         DC  CL8'QUALIFY '          SETS THE QUALIFIER FOR
ANLQUAL  DC  CL8' '                   UNQUALIFIED OBJECTS
         DC  CL8'USEPLAN '          SETS THE EXPLAIN TABLE OWNER
USEPLAN  DC  CL8'NOT USED'            UNLESS THE VALUE IS 'NOT USED'
         DC  CL8'SETPLAN ALLOW'    ALLOWS USEPLAN TO FUNCTION
SETPLAN  DC  CL8'ALLOW'            IF THE VALUE IS 'ALLOW'
         DC  CL16'BUFFERS 00002000' 4K APPLICATION BUFFER POOL
         DC  CL16'BUFF08K 00002000' 8K APPLICATION BUFFER POOL
         DC  CL16'BUFF16K 00001000' 16K APPLICATION BUFFER POOL
         DC  CL16'BUFF32K 00000100' 32k APPLICATION BUFFER POOL
         DC  CL16'CPUTIME 00010'    WARNINGS FOR 10 SECS CPU TIME
         DC  CL16'COSTING 00000015' FLAG ANY QUERY > $15
         DC  CL16'COSTQUN 100'      FLAG ANY QUERY > 100 QUNITS
         DC  CL16'CPUCOST 0800.000' CPU TIME AT $800 PER HOUR
         
         DC  CL16'TIMCOST 0010.000' $10 PER HOUR CONNECT TIME
         DC  CL16'MONEYIS DOLLARS ' MONETARY UNITS
         DC  CL16'CURRSYM $'        CURRENCY SYMBOL
         
         DC  CL16'PRECISE YES '     USE Db2 OPTIMIZER COSTS
         DC  CL16'DBTRACE NO '      TRACE FOR QMF TEST: ON|ALL|DMP|NO
         DC  CL16'AUTHIDO        '  SET BLANK FOR AUTHO DETECTION
         DC  CL240' '               RESERVATIONS (+320 FOR 20 PARMS)
         DC  CL80' '

*************************************************************************
* TO ALLOW THE ABILITY TO SPECIFY THE OWNER OF THE EXPLAIN TABLES, SET 
* THE VALUE OF THE SETPLAN PARAMETER TO 'ALLOW'. WHEN THIS FEATURE IS  
* ENABLED, THE CURRENT SQLID IS INSERTED INTO THE USEPLAN PARAMETER.   
* 
* TO USE A GENERIC ID AS THE OWNER OF THE EXPLAIN TABLES, SET THE VALUE
* OF THE SETPLAN PARAMETER TO 'GENERIC' OR 'NOTALLOW'.  
* 
* NOTE: WHEN USERS WANT THE TRACE DATA FROM ANLQMF, SET DBTRACE TO
* ON|ALL|DMP. TO CAPTURE THE OUTPUT INTO A SEQUENTIAL DATA SET 
* INSTEAD OF HAVING IT PRINT TO THE TERMINAL, ALLOCATE THE DESIRED
* SEQUENTIAL DATA SET AS SYSPRINT. 
**********************************************************************
    2. Modify each parameter for your target host system.

      The bottom slots are reserved for the future and must not be changed. Any errors in modifying this parameter list can cause unpredictable or incorrect results. The length of the parameter list must remain at 800 characters. Be sure to note the spacing and spelling of parameters.

      Tip: Refer to ANLPARM user parameter settings for QMF for the required settings for using Db2 SQL Performance Analyzer with QMF. For optimal results, modify the parameters in the exit to correspond to the specific host machine that is running QMF with Db2 SQL Performance Analyzer.
  3. Optional: Force an unconditional cancellation of queries that exceed the defined limits.
    If you do not complete this step, Db2 SQL Performance Analyzer gives you the option of canceling each query that exceeds the defined limits. To force an unconditional cancellation of queries that exceed the defined limits:
    1. Find the following code fragment in the ANLGOV1 program: 
      LOGUSER ST   R15,XCBERRET        PUT RETURN CODE IN XCB
        MVC  XCBMGTXT,ANLER01    ANLGOV1 LIMITS MESSAGE
        MVI  XCBLOGM,XCBLOGMY    SET QMF TO LOG MESSAGE
* (ALT) B    EXIT16              FOR ALWAYS CANCEL, TAKE EXIT16 NOW
    2. Delete the asterisk (*) and the (ALT) from the EXIT16 instruction.
  4. Optional: To use a Generic ID as the PLAN_TABLE owner, set 'SETPLAN GENERIC' or 'SETPLAN NOTALLOW'.

    If there is no Generic ID available and 'SETPLAN NOTALLOW' is specified, the TSO ID is used as the PLAN_TABLE owner. If there is no Generic ID available and 'SETPLAN GENERIC' is specified, an error is reported.

  5. Save the changes to ANLGOV1.
  6. Assemble and link-edit ANLGOV1 and DSQUEGV1.
    1. Edit the JCL for the ANLEXIT procedure, which is located in the job_sequence_IDGVQMDb2_entry_ID member of the Tools Customizer jobs.
      • Check the ANLEXIT procedure for valid system library names.
      • Optional: To use a test library for final testing of the exit modifications instead of using the production load library for QMF, change the target output library in the OUTLIB member and locate the two locations within the Tools Customizer JCL that points to that same target output library.

      For reference, the processing portion of the JCL is:

      //*
//ANL1  EXEC ANLEXIT
//        MBR=ANLGOV1
//LNK.SYSIN  DD *
  INCLUDE SYSLIB(ANLQMF)
  NAME ANLGOV1(R)
    2. Save the changes to job_sequence_IDGVQMDb2_entry_ID.
    3. Edit the JCL for the QMFEXIT procedure, which is located in the job_sequence_IDXTQMDb2_entry_ID member of the Tools Customizer jobs.
      • Check the QMFEXIT procedure for valid system library names.
      • Optional: To use a test library for final testing of the exit modifications instead of using the production load library for QMF, change the target output library in the OUTLIB member and locate the two locations within the Tools Customizer JCL that points to that same target output library.

      For reference, the processing portion of the JCL is:

      //*
//QMF1    EXEC QMFEXIT
//        MBR=DSQUEGV1
//LNK.SYSIN  DD *
  NAME DSQUEGV1(R)
    4. Save the changes to job_sequence_IDXTQMDb2_entry_ID.
    5. Submit job_sequence_IDGVQMDb2_entry_ID and job_sequence_IDXTQMDb2_entry_ID.
  7. Optional: Test the QMF governor exits before putting them into production.
    1. Preallocate ISPLLIB (or the ISPF load library) to the test library that you chose by using the standard TSO mode without ISPF and entering the following command or equivalent:
      ALLOC F(ISPLLIB) DA(test.library) SHR REUS
    2. Restart ISPF and select the QMF option that starts the ANLDSQ1 CLIST or equivalent. When you invoke a QMF query, the Db2 SQL Performance Analyzer Governor Interface program intercepts the query and provides the cost analysis directly to the panel.
    3. Make any further changes to the settings for the governor exits based on your test results.
    4. When you have completed the changes, migrate the ANLGOV1 and DSQUEGV1 modules into the production library for QMF, which is typically SYS1.SDSQLOAD.
  8. Verify that the ANLCDSQ1 CLIST includes the data sets that are currently used for your QMF and GDDM environments.
    Depending on which release of QMF you are currently running, the actual library names and high-level qualifiers might differ. In the ANLCDSQ1 CLIST, SYS3 is used as the high-level qualifier for most data sets.
    Table 1. Sample data set names and allocations for QMF files
    File name Sample data set name Allocation
    ISPPLIB SYS3.SDSQPLBE QMF ISPF panel library
    ISPMLIB SYS3.SDSQMLBE QMF ISPF message library
    ISPSLIB SYS3.SDSQSLBE QMF ISPF skeleton library
    SYSEXEC SYS3.SDSQEXCE TSO REXX execution library
    SYSPRINT * (terminal) ANLGOV1 and ANLQMF messages
    DSQPRINT SYSOUT(X) Print data output to hold class (X)
    DSQDEBUG SYSOUT(X) Trace dump output to hold class (X)
    DSQEDIT work space Edit transfer file (temp)
    DSQUDUMP SYSOUT(X) Output snap dump to hold class (X)
    DSQSPILL work space Overflow spill file (temp)
    DSQUCFRM DSQUCFRM Saves user-defined ICUFORMS
    DSNPNLE SYS3.DSQPNLE QMF panel file
    ADMGGMAP SYS3.DSQMAPE GDDM map group for QMF
    ADMCFORM SYS3.DSQCHART GDDM chart forms for QMF
    ADMCDATA SYS3.ADMCDATA GDDM data file
    ADMGDF SYS3.ADMGDF GDDM form definitions file
    ADMSYMBL SYS3.ADMSYMBL GDDM symbols file
    ADMPRNTQ SYS3.ADMPRINT. REQUEST.QUEUE GDDM output print file
    ADMDEFS SYS3.GDDMDEF1 GDDM definitions file
  9. Optional: Set up Db2 SQL Performance Analyzer so that you can start it from an existing QMF interface, rather than starting QMF through Db2 SQL Performance Analyzer.
    1. Add the following allocations to your startup CLIST (or REXX exec): 
      ALLOC FI(DSQLLIB) DA( 
'QMF###.SDSQLOAD', 
'sysx.hiqual.SANLLOAD', +
'DSN###.SDSNEXIT', 
'DSN###.SDSNLOAD')  SHR  REUSE
ISPEXEC  LIBDEF  ISPLLIB  LIBRARY  ID(DSQLLIB)
ALLOC  FI(SYSPRINT)  DA(*)  SHR
      These statements cause the QMF load library, Db2 SQL Performance Analyzer program load library, and Db2® exit and load libraries to become available.
    2. If you install the modified QMF Governor exit into a different library, add the library to the list of allocated libraries as the first entry. The SYSPRINT allocation is required by Db2 SQL Performance Analyzer to direct warning messages to the terminal.
    3. Optionally, use the following code at the end of your QMF CLIST (or REXX exec) to free the allocated libraries. 
      ISPEXEC  LIBDEF  ISPLLIB  LIBRARY  ID( ) 
FREE  FI(DSQLLIB)