ANLSTPR test program

You can use the ANLSTPR test program to test your stored procedures.

To start the ANLSTPR test program, run the ssidSTPR JCL job in high_level_qualifier.SANLJCL. The JCL of the test program is shown below.

When Generic ID needs to own EXPLAIN tables, modify the following JCL as stated in the "Note" in this sample (below).

To start the ANLSTPR test program, run the ssidSTPR JCL job in high_level_qualifier.SANLJCL:

//JOBNAME JOB (ACCTG),’PLI RRSAF’,CLASS=1,MSGCLASS=X,NOTIFY=USERID
//*DSN9STPR                                                             
//*                                                                     
//********************************************************************  
//*  CALLS EXPLAIN-ENHANCED STORED PROCEDURE ANLPRCR (RRSAF)            
//*  STORED PROCEDURE (ANLPRCR) TEST PROGRAM - ANLSTPR (PL/I) SAMPLE    
//*                                                                     
//*  LICENSED MATERIALS - PROPERTY OF IBM CORPORATION -  5697-W51       
//*      COPYRIGHT IBM CORPORATION 2000, 2015 ALL RIGHTS RESERVED       
//*                                                                     
//*  US GOVERNMENT USERS RESTRICTED RIGHTS -  USE, DUPLICATION OR       
//*  DISCLOSURE RESTRICTED BY GSA ADP SCHEDULE CONTRACT WITH IBM.        
//*                                                                     
//********************************************************************  
//*  Note: To use a Generic ID as the PLAN_TABLE owner, add            
//*  '+OFF+' as third parameter of PARM= for ANLSTPR program.    
//*  Sample : PARM='ssid,JOHNDOE,+OFF+                                                                   
//********************************************************************  
//STEP1    EXEC PGM=ANLSTPR,PARM=’ssid,JOHNDOE’
//STEPLIB  DD DISP=SHR,                                                 
//         DSN=SYSX.ANL520.SANLLOAD                              
//         DD DISP=SHR,DSN=SYS1.CEE.SCEERUN                             
//       DD DISP=SHR,DSN=SYS1.DSN###.SDSNEXIT                           
//       DD DISP=SHR,DSN=SYS1.DSN###.SDSNLOAD                           
//DSNTRACE DD SYSOUT=*                                                  
//SYSPRINT DD SYSOUT=*                                                  
//QUERYIN  DD DISP=SHR,                                                 
//         DSN=SYSX.ANL520.SANLSQL(ANLSTEST)

The QUERYIN DD statement points to the file containing the SQL statements that you want to analyze. If the test program was not bound properly before running, an SQL -805 error code could result.

For each SQL statement in your test input file, the test program ANLSTPR lists the SQL statement, the SQL error code, the Db2 SQL Performance Analyzer warning flags, CPU time, QUNITS, and cost, as shown in the following figure.

Figure 1. ANLSTPR sample program output

 * RRS IDENTIFY RETURNS      0         0          0
                                                   
 * SIGNON USER RETURNS       0         0          0
                                                   
 * CREATE THREAD RETURNS     0         0          0
 
 *  EXPLAIN PLAN FOR (LENGTH   240)
 *   UPDATE SYSIBM.SYSTABLES
 *   SET NPAGES = -1
 *   WHERE NPAGES = -1

 * ANLPRCR SQLCODE IS             0
 * ANLPRCR RETURNS ==>  WARNING FLAGS:  -----
    ELAPSED:    38.70953  CPU TIME:     0.27816
   I/O COUNT:       101   QUNITS:           16
   MONETARY:        1.18
   ANL CODE:          0   SQL CODE:          0

 *  EXPLAIN PLAN FOR (LENGTH   320)
 *   SELECT SUM(NTABLES), AVG(PARTITIONS) FROM SYSIBM.SYSTABLESPACE
 *   WHERE CREATOR <> 'SYSIBM'
 *   AND NTABLES > 1 AND
 *   SEGSIZE = 0

 * ANLPRCR SQLCODE IS             0
 * ANLPRCR RETURNS ==>  WARNING FLAGS:  -----
    ELAPSED:     1.09482  CPU TIME:     0.08612
   I/O COUNT:        18   QUNITS:            5
   MONETARY:        0.20
   ANL CODE:          0   SQL CODE:          0


 *  EXPLAIN PLAN FOR (LENGTH   640)
 *   SELECT A.CREATOR, A.NAME, B.COLNAME, B.ORDERING, C.COLSEQ
 *   FROM SYSIBM.SYSINDEXES A, SYSIBM.SYSKEYS B, SYSIBM.SYSFOREIGNKEYS C
 *   WHERE A.CREATOR = B.IXCREATOR
 *   AND   A.NAME  = B.IXNAME
 *   AND   A.NAME  = C.TBNAME
 *   AND   A.CREATOR = C.CREATOR
 *   AND   A.COLCOUNT > 1
 *   ORDER BY C.COLSEQ

 * ANLPRCR SQLCODE IS             0
 * ANLPRCR RETURNS ==>  WARNING FLAGS:  YY-YY
    ELAPSED:  5223.66996  CPU TIME:  3403.41581
   I/O COUNT:       112   QUNITS:       185853
   MONETARY:      771.94
   ANL CODE:          0   SQL CODE:          0

 * RRS TERMINATE IDENTIFY    0         0          0
 * PROGRAM TERMINATION