TEST example

The example in the following figure shows two TEST requests in a single job.

The requests test the access paths for DBRM CKPTDBRM against the previous binds. The first request checks access paths for collection ACCTPAY, and the second request checks access paths for the same DBRM in plan CUSTOMER.

Both reports in Figure 2 show a new SQL statement and a change in access path for statements with old QUERYNO 369 and new QUERYNO 379.

Figure 1. JCL for TEST example

//P390HT3   JOB ,TSCICS,CLASS=A,MSGCLASS=R,MSGLEVEL=(1,1),
// NOTIFY=&SYSUID,REGION=20M
//STEP009   EXEC PGM=CKPPTHCK 
//STEPLIB   DD   DSN=db2.SDSNLOAD,DISP=SHR 
//          DD   DSN=ckp.SCKPLOAD,DISP=SHR 
//SYSPRINT  DD SYSOUT=* 
//SYSEXPLN  DD SYSOUT=* 
//SYSOUT    DD SYSOUT=* 
//DBRMIN    DD DSN=ckp.SCKPDBRM,DISP=SHR 
//SYSIN     DD *
  -- THIS TEST WILL REPORT ON PROGNAME CKPTDBRM IN COLLECTION 
  -- ACCTPAY AND PROGNAME CKPTDBRM IN PLAN CUSTOMER 
  -- TESTBIND PROCESSING REQUIRES SQLID SET TO CORRECT QUALIFIER 
  -- HIGH LEVEL ID TO RESOLVE SQL STATEMENTS AND PLAN_TABLE FOR
  -- SAME ID 
     CONNECT TO DSN1 
     OPTIONS REPORTCHG MATCHQUERYNO 
     SET CURRENT SQLID = 'P390H9' 
     TEST DBRM CKPTDBRM AS PACKAGE ACCTPAY.* 
                        IN P390H9.PLAN_TABLE 
     OPTIONS REPORTALL 
     TEST DBRM CKPTDBRM AS PLAN CUSTOMER.* 
                        IN P390H9.PLAN_TABLE 
//

The report for this example is shown in the following figure. The access path rows that match are included and identified by an asterisk (*) because of the REPORTALL parameter.

Figure 2. TEST sample long report

1
  CKP031I EXECUTING LICENSED PATH CHECKER V2R1
11    -- ALL LINES THAT START WITH -- ARE COMMENTS
      -- THIS TEST WILL EVALUATE THE ACCESS PATHS FOR THE SAME DBRM
      -- AS BOTH A PACKAGE AND PLAN
         CONNECT TO DSN1
11       SET CATALOG QUALIFIER = 'SYSIBM'
      -- TESTBIND PROCESSING REQUIRES SQLID SET TO CORRECT QUALIFIER
      -- HIGH LEVEL ID TO RESOLVE SQL STATEMENTS AND PLAN_TABLE FOR
      -- SAME ID
11       SET CURRENT SQLID = 'P390H'
11       TEST DBRM CKPTDBRM AS PACKAGE CKPTCOLL.*
         IN P390H.PLAN_TABLE2
11 2003/08/08   SQL ID - ACCESS PATH FOR CKPTDBRM     COLLID - CKPTCOLL   OLD PLAN_TABLE - PLAN_TABLE2
  IN QRYNO M  CREATOR  TNAME      TBNO AC MC CREATOR  ACCESSNAME  IO SORTUJGO LK PF QBNO PLNO MXSQ

  2    363 0 SYSIBM   SYSTABLES     1 I    1 SYSIBM   DSNDTX01     N NNNNNNNN  IS L      1    1
  1    369 0 SYSIBM   SYSINDEXES    1 M    0                       N NNNNNNNN  IS L      1    1
  1    369 0 SYSIBM   SYSINDEXES    1 MX   1 SYSIBM   DSNDXX02     Y NNNNNNNN  IS        1    1    1
  1    369 0 SYSIBM   SYSINDEXES    1 MX   1 SYSIBM   DSNDXX01     Y NNNNNNNN  IS        1    1    2
  1    369 0 SYSIBM   SYSINDEXES    1 MU   0                       N NNNNNNNN            1    1    3
  1    369 1 SYSIBM   SYSTABLES     2 I    2 SYSIBM   DSNDTX01     Y NNNNNNNN  IS        1    2
  2    379 0 SYSIBM   SYSINDEXES    1 M    0                       N NNNNNNNN  IS L      1    1
  2    379 0 SYSIBM   SYSINDEXES    1 MX   1 SYSIBM   DSNDXX02     Y NNNNNNNN  IS        1    1    1
  2    379 0 SYSIBM   SYSINDEXES    1 MX   1 SYSIBM   DSNDXX01     Y NNNNNNNN  IS        1    1    2
  2    379 0 SYSIBM   SYSINDEXES    1 MI   0                       N NNNNNNNN            1    1    3
  2    379 1 SYSIBM   SYSTABLES     2 I    2 SYSIBM   DSNDTX01     Y NNNNNNNN  IS        1    2
CKP206I TEST  COMPLETE  FOR  PROGRAM = CKPTDBRM VERSION =
CKP203I STATEMENTS WITH SAME ACCESS PATH 9 STATEMENTS WITH DIFFERENT ACCESS PATH 1 FOR PROGRAM CKPTDBRM
CKP204I  QUERIES WITH MATCHING EXPLAIN  10      QUERIES WITHOUT MATCHING EXPLAIN 1 FOR PROGRAM CKPTDBRM
11      TEST DBRM CKPTDBRM AS PLAN CKPTPLAN.*
                                             IN P390H.PLAN_TABLE2
11   2003/08/08   SQL ID -   ACCESS PATH FOR CKPTDBRM APPLNAME  CKPTPLAN   LD PLAN_TABLE - PLAN_TABLE2
  IN QRYNO M  CREATOR  TNAME        TBNO AC MC CREATOR  ACCESSNAME   IO SORTUJGO LK PF QBNO PLNO MXSQ

  2    363 0 SYSIBM   SYSTABLES       1 I    1 SYSIBM   DSNDTX01      N NNNNNNNN  IS L      1    1
  1    369 0 SYSIBM   SYSINDEXES      1 M    0                        N NNNNNNNN  IS L      1    1
  1    369 0 SYSIBM   SYSINDEXES      1 MX   1 SYSIBM   DSNDXX02      Y NNNNNNNN  IS        1    1    1
  1    369 0 SYSIBM   SYSINDEXES      1 MX   1 SYSIBM   DSNDXX01      Y NNNNNNNN  IS        1    1    2
  1    369 0 SYSIBM   SYSINDEXES      1 MU   0                        N NNNNNNNN            1    1    3
  1    369 1 SYSIBM   SYSTABLES       2 I    2 SYSIBM   DSNDTX01      Y NNNNNNNN  IS        1    2
  2    379 0 SYSIBM   SYSINDEXES      1 M    0                        N NNNNNNNN  IS L      1    1
  2    379 0 SYSIBM   SYSINDEXES      1 MX   1 SYSIBM   DSNDXX02      Y NNNNNNNN  IS        1    1    1
  2    379 0 SYSIBM   SYSINDEXES      1 MX   1 SYSIBM   DSNDXX01      Y NNNNNNNN  IS        1    1    2
  2    379 0 SYSIBM   SYSINDEXES      1 MI   0                        N NNNNNNNN            1    1    3
  2    379 1 SYSIBM   SYSTABLES       2 I    2 SYSIBM   DSNDTX01      Y NNNNNNNN  IS        1    2
CKP206I TEST  COMPLETE  FOR  PROGRAM = CKPTDBRM VERSION =
CKP203I STATEMENTS WITH SAME ACCESS PATH 9 STATEMENTS WITH DIFFERENT ACCESS PATH 1 FOR PROGRAM CKPTDBRM
CKP204I  QUERIES WITH MATCHING EXPLAIN  10      QUERIES WITHOUT MATCHING EXPLAIN 1 FOR PROGRAM CKPTDBRM
COMPLETE RET CODE=               04
11     2003/08/08   SQL ID -  ACCESS PATH FOR CKPTDBRM  COLLID - CKPTCOLL  OLD PLAN_TABLE - PLAN_TABLE2

  DBRM      CKPTDBRM HAS A CONNECT TOKEN OF 1727F0280E8AA0CC

  STATEMENT#      287 DECLARE SELECT-1 CURSOR FOR SELECT NAME , TBNAME , COLNO , C
                      OLTYPE , LENGTH FROM SYSIBM . SYSCOLUMNS WHERE TBNAME = : H

  STATEMENT#      293 DECLARE SELE