Command language examples

The following command language examples show some typical examples of using the command language for DB2® Path Checker.

The commands are entered from the SYSIN data set.

A sample of the JCL that you can use to run DB2 Path Checker with commands supplied in SYSIN is shown in the following figure. Modify the JCL to work with your installation configuration.

Figure 1. JCL for specifying commands in SYSIN

//STEP002   EXEC PGM=CKPPTHCK
//STEPLIB   DD   DSN=ckp.SCKPLOAD,DISP=SHR
//          DD   DSN=db2.SDSNLOAD,DISP=SHR 
//SYSPRINT  DD   SYSOUT=*
//SYSEXPLN  DD   SYSOUT=*
//SYSOUT    DD   SYSOUT=*
//DBRMIN    DD   DSN=ckp.SCKPDBRM,DISP=SHR
//SYSIN     DD * 
-- ALL LINES THAT START WITH -- ARE COMMENTS 
-- ENTER YOUR COMMANDS HERE

Command language example 1

The following request produces a report on the access path for program ACCTPAY.ORDERS from the data in the specified plan table.

REPORT ON PACKAGE ACCTPAY.ORDERS IN P390H.PLAN_TABLE

Command language example 2

The following example produces a report on the most current versions of all the programs for the collection ACCTPAY and the most current access path in the plan table. The START WITH clause starts the report at the first program name whose name is greater than or equal to A.

 REPORT ON PACKAGE ACCTPAY.* IN P390H.PLAN_TABLE START WITH A

Command language example 3

The following example produces a report on all the program names that begin with AA in plan CUSTOMER. The START WITH clause is specified correctly, but all the program names that begin with AA would be greater than A in any case.

 REPORT ON PLAN CUSTOMER.AA* IN P390H.PLAN_TABLE START WITH A

Command language example 4

The first collection and plan table are considered the new access path and the second collection and plan table are the old access path. Either the second user ID, plan table, or collection ID identifies the old access path. The target (second) program name for TEST and COMPARE requests is always an asterisk (*) because the same program name is used for both sides of the comparison.

COMPARE PACKAGE ACCTPAY.ORDERS IN userid.PLANTABLE TO ACCTPAY.* 
IN userid.PLANTABLE2

Command language example 5

The following request compares all the package names (program names) in collection ACCTPAY as if they had previously been bound into collection SAVECOLL. The DBRM name can be specified with wildcards, and the list of names to be processed will be selected from SYSPACKAGE. This request will start with program name ORDERS.

COMPARE PACKAGE ACCTPAY.* IN userid.PLANTABLE TO SAVECOLL.* IN
userid PLANTABLE START WITH ORDERS

Command language example 6

The first plan name and plan table are considered the new access path and the second plan name and plan table are the old access path. The following request compares the access paths for program ORDERS to data in the plan table as if the plan had been bound with the name TESTPLN2.

COMPARE PLAN CUSTOMER.ORDERS IN userid.PLANTABLE TO TESTPLN2.* IN
userid.PLANTABLE

Command language example 7

The following request compares the access paths for all the programs in plan CUSTOMER to data in the plan table as if the plan had been bound with the name TESTPLN2. The list of names to be processed will be determined from SYSIBM.SYSDBRM.

COMPARE PLAN CUSTOMER.* IN userid.PLANTABLE TO TESTPLN2.* IN
userid.PLANTABLE START WITH A

Command language example 8

TEST processing requires that the SQLID be set to the qualifier of the tables to be processed.

The following request reads the DBRM ORDERS from ddname DBRMIN, creates an Explain for each access path, and compares it to the previous access path. This will identify potential access path changes.

SET CURRENT SQLID = 'PROD'
TEST DBRM ORDERS AS PACKAGE COLLNAME.* IN userid.PLANTABLE

Command language example 9

The DBRM name can be specified with wildcards and the list of names to be processed will be determined from SYSIBM.SYSPACKAGE.

The following request processes all the programs in the collection COLLNAME starting with program NAXX. The START WITH clause is optional.

SET CURRENT SQLID = 'PROD'
TEST DBRM * AS PACKAGE COLLNAME.* IN userid.PLANTABLE START WITH NAXX

Command language example 10

The following report reads the DBRM ORDERS from ddname DBRMIN, Explain each access path, and compares it to the previous access path. This will identify potential access path changes.

SET CURRENT SQLID = 'PROD'
TEST DBRM ORDERS AS PLAN CUSTOMER.* IN userid.PLANTABLE

Command language example 11

The DBRM name can be specified with wildcards, and the list of names to be processed will be determined from SYSIBM.SYSDBRM. The following request processes all the program names that start with TEST from plan CUSTOMER.

SET CURRENT SQLID = 'PROD'
TEST DBRM TEST* AS PLAN CUSTOMER.* IN userid.PLANTABLE

Command language example 12

The following request assigns the optimization hint PROD to a statement in package PROGNAME of collection ACCTPAY. The timestamp and statement number uniquely identify the plan table row to be modified by the addition of a hint.

 MAKE STATEMENT 134 WITH TIMESTAMP = '2001012311210955' IN PACKAGE
ACCTPAY.PROGNAME IN userid.PLAN_TABLE BE HINT PROD