Specifying user parameters to the SQL Performance Analyzer stored procedure

The SQL Performance Analyzer stored procedure call parameter list contains a list of SQL Performance Analyzer user parameters in the third host variable. You can specify up to 15 user parameters in this list.

Procedure

To specify user parameters to the SQL Performance Analyzer stored procedure rather than using the default settings, perform the following steps:
1. Enter the name and the value for the SQL PA parameter, preceded and followed by at least one blank.
  The following set of user parameters are supplied by default:
```
      REPORTS   STP  (STP is required)
      VERSION   V10R1
      STORAGE   3390-3
      REFRESH   ANY
      DEGREES   1
      QUALIFY   authid
      CONNECT   CAF (or RRSAF)
      PRECISE   YES
```
  Requirement: The first parameter must be the REPORTS STP parameter. The list is 240 bytes long. You can supply up to 15 parameter and value sets. Each can be a maximum of 16 characters long, including blanks.
  
  The default value for the VERSION parameter, which indicates the version of Db2 that you are using, is V10R1. If you use a different version of Db2, you must edit the VERSION parameter, especially if no PLAN_TABLE exists. Otherwise, the parameters are automatically created in Db2 10 format.
To report access plans and catalog statistics in the ANLPRER program, add the RETPLAN YES parameter to the passed parameter list.

Example

The following figure shows a sample of the formatted output.

Figure 1. RETPLAN YES sample output

* CAF OPEN RETCODE IS             0                                             
                                                                                
*  EXPLAIN PLAN FOR (LENGTH   480)                                              
*    SELECT COUNT(*) FROM                                                       
*    SYSIBM.SYSPACKSTMT A, SYSIBM.SYSPACKDEP B                                  
*    WHERE  A.LOCATION = ? AND                                                  
*         B.DLOCATION = ? AND                                                   
*         B.DCOLLID = ? AND                                                     
*         A.COLLID <> ?                                                         
                                                                                
* ANLPRER SQLCODE IS             0                                             
* ANLPRER RETURNS ==>  WARNING FLAGS:  ---Y-                                   
   ELAPSED:    10.34282  CPU TIME:     7.24000                                  
  I/O COUNT:         6   QUNITS:       160886                                   
  MONETARY:        1.61                                                         
  ANL CODE:          0   SQL CODE:          0                                   
   RECORDS:          2                                                          
                                                                                
 The Db2 Access Plan:                                                           
 Qry:  100000001 Blk:   1 Pln:   1 Mth:   0 Typ:        Mix:   0 Rid:    Bif:   
 Acc: I   Tno:   1 Table: SYSIBM   . SYSPACKSTMT        Corr: A                 
 Index: SYSIBM   . DSNKSX01           Ixo: Y  Mtch:  1 SortC|N UJOG: NNNN  NNNN
 Jon:    Mrg:  -1 Adg:  -1 Aid:  -1 Jdg:  -1 Jid:  -1 Cid:  -1 Nid:  -1 Par:    
 Rng:    Typ: T  Enc:    CCSID S|M|D:     0     0     0 Coll: SQLPA             
 Grp:          Pref: S Lock:  IS App:          Pgm: ANLPRER Ver:               
                                                                                
 The Db2 Statistics used:                                                       
 Tty: T Loc:                  Ncol:    18 Rowz:   404 CARD:               212216
 Npag:                14830 Pctp:  90 COMP:   0 Encd: E  Tsty:    Part:     0   
 Nidx:   1 Pgsz:     4 Ntab:    8 Nact:                15543 Sgsz:   4 Tclo: N  
 Lkpt: N  Maxr: 255 Lksz: A  Crat:   0.000000 Cled: N  Cing: Y  Ixsz:  4096     
 Iclo: N  Leaf:         -1 1key:                   -1 Fkey:                   -1
 Nlvl:    -1 Kcol:     5 Iunq: U  Ityp: 2  Piec:          0 Log: Y              
 Tver:   0 Iver:   0 Nmqt:    0 Ailn:     0 Arln:    404                        
 Dsiz:          0 Pools: BP0     BP0      Volt: N  Ipad: -  Dpsi:     0 Irel: Y 
                                                                                
 Proc MS:       7240 Proc SU:     160886 Category: A  Reason:  NORMAL           
                                                                                
 The Db2 Access Plan:                                                           
 Qry:  100000001 Blk:   1 Pln:   2 Mth:   1 Typ:        Mix:   0 Rid:    Bif:   
 Acc: I   Tno:   2 Table: SYSIBM   . SYSPACKDEP         Corr: B                 
 Index: SYSIBM   . DSNKDX01           Ixo: Y  Mtch:  2 SortC|N UJOG: NNNN  NNNN 
 Jon:    Mrg:  -1 Adg:  -1 Aid:  -1 Jdg:  -1 Jid:  -1 Cid:  -1 Nid:  -1 Par:    
 Rng:    Typ: T  Enc:    CCSID S|M|D:     0     0     0 Coll: SQLPA             
 Grp:          Pref:   Lock:  IS App:          Pgm: ANLPRER Ver:               
                                                                                
 The Db2 Statistics used:                                                       
 Tty: T Loc:                  Ncol:    10 Rowz:    97 CARD:                11829
 Npag:                  292 Pctp:  90 COMP:   0 Encd: E  Tsty:    Part:     0   
 Nidx:   3 Pgsz:     4 Ntab:    8 Nact:                15543 Sgsz:   4 Tclo: N  
 Lkpt: N  Maxr: 255 Lksz: A  Crat:   0.000000 Cled: N  Cing: Y  Ixsz:  4096     
 Iclo: N  Leaf:         -1 1key:                   -1 Fkey:                   -1
 Nlvl:    -1 Kcol:     4 Iunq: D  Ityp: 2  Piec:          0 Log: Y              
 Tver:   0 Iver:   0 Nmqt:    0 Ailn:     0 Arln:     97                        
 Dsiz:          0 Pools: BP0     BP0      Volt: N  Ipad: -  Dpsi:     0 Irel: Y 
                                                                                
 Proc MS:       7240 Proc SU:     160886 Category: A  Reason:  NORMAL