Mainframe Documents

Xpeditor Quick Sample Debugging

2006-02-01T02:26:00.000-08:00

Quick Sample Debugging Session
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

This chapter demonstrates the basic debugging features of XPEDITER/TSO,
using the sample program TRIMAIN. A quick overview shows you how to do
the following:

* Run and stop the program

* Step through code

* View and modify program variables

* Debug called modules

* Analyze data flow

* Trace logic flow

* Review execution in reverse direction

3.1 Preparing the Programs
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

The source for the sample program TRIMAIN, which calls TRITST and TRIRPT
by use of CALL literal, is contained in the SAMPLIB dataset on the
XPEDITER/TSO product tape. The sample programs can be found in the common
load module, SIR, and file list libraries that have been created and
verified by your site installer. If not found, compile the sample
programs TRITST, TRIRPT, and TRIMAIN with the SIR processor.

Select option 2.1 (XPEDITER/TSO standard environment) from the XPEDITER
Primary Menu. Review the SETUP screens that have been initialized by your
site installer by typing SETUP in the primary command line. Following
this, type TRIMAIN in the Program field and the dataset name that contains
the file list for TRIMAIN in the File Allocation List field. The File
Allocation List should contain the INFILE and OUTFILE, which are defined
in the FD statements of TRIMAIN and must be preallocated when program
TRIMAIN is executed. The input test data can be located in member TRIDATA
of the SAMPLIB dataset. Be sure to direct the program output to the
terminal by specifying TERM for the OUTFILE DD. The file list can be
created by accessing the File Allocation List screen (option 1 on the
XPEDITER Primary Menu or through the use of the ALLOC command).

To start the debugging session, press <ENTER>. XPEDITER/TSO processes
the file list, allocates the necessary datasets, loads the program
TRIMAIN, and displays the COBOL source code. You should be able to see
the message "Before Breakpoint Encountered" with the execution arrow
pointing to the PROCEDURE DIVISION statement. This means that TRIMAIN is
stopped before the start of the program. An example of the source code
display is shown in Figure 3-1.

|----------------------------------------------------------------------------------|
| |
| ------------------------------ XPEDITER/TSO - SOURCE ----------------------------|
| COMMAND ===> SCROLL===> CSR |
| Before Breakpoint Encountered |
| ------ ------------------------------------------------------ Before TRIMAIN --|
| =====> B PROCEDURE DIVISION. |
| 000035 MAIN-PARA. |
| 000036 PERFORM INIT-PARA. |
| 000037 PERFORM ANALYZE-NEXT-REC |
| 000038 UNTIL OUT-OF-RECS = 'Y'. |
| 000039 PERFORM ENDING-PARA. |
| 000040 A GOBACK. |
| 000041 INIT-PARA. |
| 000042 MOVE ZERO TO N-CNTR (1) N-CNTR (2) N-CNTR (3) N-CNTR (4). |
| 000043 OPEN INPUT INFILE. |
| 000044 MOVE 'N' TO OUT-OF-RECS. |
| 000045 ANALYZE-NEXT-REC. |
| 000046 READ INFILE INTO WORK-REC |
| 000047 AT END |
| 000048 MOVE 'Y' TO OUT-OF-RECS. |
| 000049 IF OUT-OF-RECS = 'N' |
| 000050 MOVE ZERO TO TRIANGLE-TYPE |
| 000051 CALL 'TRITST' USING WORK-REC TRIANGLE-TYPE |
| 000052 SET TX TO TRIANGLE-TYPE |
| 000053 ADD 1 TO N-CNTR (TX). |
| |
|----------------------------------------------------------------------------------|

Figure 3-1. Debugging Screen Showing TRIMAIN Program

To access HELP, press the <PF1> key. To terminate the debugging session
and return to the Standard screen, press the <PF4> (EXIT) key. If you
need to reload the program and test again from the beginning, type the
following command in the primary command line:

RETEST

The RETEST command resets all the breakpoints and data displays.

RETEST is not valid in Dialog, BTS, or IMS environments, or with DB2
databases.

3.2 Setting Breakpoints
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

You can control program execution by setting breakpoints and running the
program. A breakpoint is a certain location in your program where you
want program execution to stop so you can inspect the data the program is
processing and check the logic to make certain it is correct.

A simple way to set a breakpoint is to enter it from the line command
area. Move the cursor to the compiler-generated statement number 43 at
the OPEN verb, then type over the statement number with the B(efore) line
command, as shown in Part A of Figure 3-2, and press <ENTER>. A "B"
appears in column 9 on statement 43, as seen in Part B, indicating that a
BEFORE breakpoint has been set. This breakpoint causes program execution
to pause before executing the OPEN statement.

|------------------------------------------------------------------------------|
| Part A: Entering B Line Command on Statement 43 |
|------------------------------------------------------------------------------|
| =====> B PROCEDURE DIVISION. |
| 000035 MAIN-PARA. |
| 000036 PERFORM INIT-PARA. |
| 000037 PERFORM ANALYZE-NEXT-REC |
| 000038 UNTIL OUT-OF-RECS = 'Y'. |
| 000039 PERFORM ENDING-PARA. |
| 000040 A GOBACK. |
| 000041 INIT-PARA. |
| 000042 MOVE ZERO TO N-CNTR (1) N-CNTR (2) N-CNTR (3) N-CNTR (4). |
| B 043 OPEN INPUT INFILE. |
| 000044 MOVE 'N' TO OUT-OF-RECS. |
|------------------------------------------------------------------------------|
| Part B: BEFORE Breakpoint Displayed for Statement 43 |
|------------------------------------------------------------------------------|
| =====> B PROCEDURE DIVISION. |
| 000035 MAIN-PARA. |
| 000036 PERFORM INIT-PARA. |
| 000037 PERFORM ANALYZE-NEXT-REC |
| 000038 UNTIL OUT-OF-RECS = 'Y'. |
| 000039 PERFORM ENDING-PARA. |
| 000040 A GOBACK. |
| 000041 INIT-PARA. |
| 000042 MOVE ZERO TO N-CNTR (1) N-CNTR (2) N-CNTR (3) N-CNTR (4). |
| 000043 B OPEN INPUT INFILE. |
| 000044 MOVE 'N' TO OUT-OF-RECS. |
|------------------------------------------------------------------------------|

Figure 3-2. Entering a BEFORE Breakpoint on Line 43

Press <PF12> (GO) to execute TRIMAIN until the breakpoint is reached. The
program stops at statement 43, where the execution arrow is pointing. The
execution status field on the fourth line also shows that execution is at
"Before TRIMAIN.43," as shown in Figure 3-3.

|----------------------------------------------------------------------------------|
| |
| ------------------------------ XPEDITER/TSO - SOURCE ----------------------------|
| COMMAND ===> SCROLL===> CSR |
| Before Breakpoint Encountered |
| ------ --------------------------------------------------- Before TRIMAIN.43 --|
| 000034 B PROCEDURE DIVISION. |
| 000035 MAIN-PARA. |
| 000036 PERFORM INIT-PARA. |
| 000037 PERFORM ANALYZE-NEXT-REC |
| 000038 UNTIL OUT-OF-RECS = 'Y'. |
| 000039 PERFORM ENDING-PARA. |
| 000040 A GOBACK. |
| 000041 INIT-PARA. |
| 000042 MOVE ZERO TO N-CNTR (1) N-CNTR (2) N-CNTR (3) N-CNTR (4). |
| =====> B OPEN INPUT INFILE. |
| 000044 MOVE 'N' TO OUT-OF-RECS. |
| 000045 ANALYZE-NEXT-REC. |
| 000046 READ INFILE INTO WORK-REC |
| 000047 AT END |
| 000048 MOVE 'Y' TO OUT-OF-RECS. |
| 000049 IF OUT-OF-RECS = 'N' |
| 000050 MOVE ZERO TO TRIANGLE-TYPE |
| 000051 CALL 'TRITST' USING WORK-REC TRIANGLE-TYPE |
| 000052 SET TX TO TRIANGLE-TYPE |
| 000053 ADD 1 TO N-CNTR (TX). |
| 00054 ENDING-PARA. |
| |
|----------------------------------------------------------------------------------|

Figure 3-3. BEFORE Breakpoint at Statement 43

Press <PF9> (GO 1) to execute statements one-by-one. The program stops at
statement 44 after the input file is opened and before the OUT-OF-RECS
switch is set to "N." Type the SHOW FILE command in the primary command
line.

The DD names and DCB parameters that you generally specify in the JCL
statements are listed, together with the file I/O status, as shown in
Figure 3-4.

|----------------------------------------------------------------------------------|
| |
| ------------------------------- XPEDITER/TSO - SHOW -----------------------------|
| COMMAND ===> SCROLL===> CSR |
| PROGRAM: TRIMAIN MODULE: TRIMAIN COMP DATE: 07/29/92 COMP TIME: 14.41.59 |
| ------------------------------------------------------------ Before TRIMAIN.44 --|
| ******************************* TOP OF DATA ********************************** |
| ** FILE ATTRIBUTES FOR APPLICATION MODULE TRIMAIN *** |
| DSORG RECFM BLKSI LRECL |
| NON-VSAM FILE FOR DDNAME INFILE OPEN DCB = PS FB 23440 80 |
| DSN=AXPTSO.QA.TSODATA.DATA JFCB= PO FB 23440 80 |
| MBR=TRIDATA |
| DATA SET ALLOCATED ON VOLUME AXP001 DSCB= PO FB 23440 80 |
| ORGANIZATION = SEQUENTIAL ACCESS MODE = SEQUENTIAL RECFM = FB |
| OPEN VERB OPTION = INPUT LAST I/O STATEMENT = OPEN STATUS = 00 |
| |
| ** FILE ATTRIBUTES FOR APPLICATION MODULE TRIRPT *** |
| DSORG RECFM BLKSI LRECL |
| NON-VSAM FILE FOR DDNAME OUTFILE CLOSED DCB = PS -- 0 80 |
| DSN=*TERMINAL* JFCB= PS -- 0 0 |
| ORGANIZATION = SEQUENTIAL ACCESS MODE = SEQUENTIAL RECFM = FB |
| |
| ** END OF FILE ATTRIBUTE DISPLAY *** |
| ****************************** BOTTOM OF DATA ******************************** |
| |

Figure 3-4. File Attributes Displayed by the SHOW FILE Command

Press <PF3> (END) to return to the Source screen.

There are several variations of the breakpoint commands. A B line command
sets a BEFORE breakpoint. An A line command sets an AFTER breakpoint.
You may want to place an AFTER breakpoint on a conditional statement since
XPEDITER/TSO tells you what the next logical statement will be when the
breakpoint is encountered.

To remove breakpoints, enter the D(elete) line command on a line that has
a breakpoint and press <ENTER>.

3.3 Displaying Data Contents
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

In the TRIMAIN program, move the cursor to statement 42, where a table
would have been initialized with zeros, type over the statement number
with the P (PEEK) line command, and press <ENTER>. The screen
automatically scrolls up to the DATA DIVISION statement and displays the
data content of N-CNTR, as shown in Figure 3-5.

|----------------------------------------------------------------------------------|
| |
| ------------------------------ XPEDITER/TSO - SOURCE ----------------------------|
| COMMAND ===> SCROLL===> CSR |
| PROGRAM: TRIMAIN MODULE: TRIMAIN COMP DATE: 07/29/92 COMP TIME: 14.41.59 |
| ------ --------------------------------------------------- Before TRIMAIN.44 --|
| > 1 OCCURRENCE |
| 000027 10 N-CNTR > 0000 DECIMAL |
| 000028 01 OUT-OF-RECS PIC X. |
| 000029 01 TRIANGLE-TYPE PIC 9. |
| 000030 01 WORK-REC. |
| 000031 05 SIDE-A PIC 9(01). |
| 000032 05 SIDE-B PIC 9(01). |
| 000033 05 SIDE-C PIC 9(01). |
| 000034 B PROCEDURE DIVISION. |
| 000035 MAIN-PARA. |
| 000036 PERFORM INIT-PARA. |
| 000037 PERFORM ANALYZE-NEXT-REC |
| 000038 UNTIL OUT-OF-RECS = 'Y'. |
| 000039 PERFORM ENDING-PARA. |
| 000040 A GOBACK. |
| 000041 INIT-PARA. |
| 000042 MOVE ZERO TO N-CNTR (1) N-CNTR (2) N-CNTR (3) N-CNTR (4). |
| 000043 B OPEN INPUT INFILE. |
| =====> MOVE 'N' TO OUT-OF-RECS. |
| 000045 ANALYZE-NEXT-REC. |
| |
|----------------------------------------------------------------------------------|

Figure 3-5. Displaying the Data Content of N-CNTR

Tab down to the arrow that shows the occurrence number and type over the 1
with a 2 and press <ENTER>. Continue typing over the occurrence number,
each time adding 1 to the previous number. Eventually, XPEDITER/TSO
displays the warning shown in Figure 3-6, indicating that the index
boundary has been reached.

|----------------------------------------------------------------------------------|
| |
| ------------------------------ XPEDITER/TSO - SOURCE ----------------------------|
| COMMAND ===> SCROLL===> CSR |
| NUMBER EXCEEDS MAXIMUM OCCURRENCES FOR ITEM |
| ------ --------------------------------------------------- Before TRIMAIN.44 --|
| > 5 OCCURRENCE |
| 000027 10 N-CNTR > ???? INVALID DECIMAL |
| 000028 01 OUT-OF-RECS PIC X. |
| 000029 01 TRIANGLE-TYPE PIC 9. |
| 000030 01 WORK-REC. |
| 000031 05 SIDE-A PIC 9(01). |
| 000032 05 SIDE-B PIC 9(01). |
| 000033 05 SIDE-C PIC 9(01). |
| 000034 B PROCEDURE DIVISION. |
| 000035 MAIN-PARA. |
| 000036 PERFORM INIT-PARA. |
| 000037 PERFORM ANALYZE-NEXT-REC |
| 000038 UNTIL OUT-OF-RECS = 'Y'. |
| 000039 PERFORM ENDING-PARA. |
| 000040 A GOBACK. |
| 000041 INIT-PARA. |
| 000042 MOVE ZERO TO N-CNTR (1) N-CNTR (2) N-CNTR (3) N-CNTR (4). |
| 000043 B OPEN INPUT INFILE. |
| =====> MOVE 'N' TO OUT-OF-RECS. |
| 000045 ANALYZE-NEXT-REC. |
| |
|----------------------------------------------------------------------------------|

Figure 3-6. Message Indicating That the Index Boundary Has Been Reached

Press <PF6> (LOCATE *) to scroll down to where the execution arrow is.
See Figure 3-7.

|----------------------------------------------------------------------------------|
| |
| ------------------------------ XPEDITER/TSO - SOURCE ----------------------------|
| COMMAND ===> SCROLL===> CSR |
| PROGRAM: TRIMAIN MODULE: TRIMAIN COMP DATE: 07/29/92 COMP TIME: 14.41.59 |
| ------ --------------------------------------------------- Before TRIMAIN.44 --|
| =====> MOVE 'N' TO OUT-OF-RECS. |
| 000045 ANALYZE-NEXT-REC. |
| 000046 READ INFILE INTO WORK-REC |
| 000047 AT END |
| 000048 MOVE 'Y' TO OUT-OF-RECS. |
| 000049 IF OUT-OF-RECS = 'N' |
| 000050 MOVE ZERO TO TRIANGLE-TYPE |
| 000051 CALL 'TRITST' USING WORK-REC TRIANGLE-TYPE |
| 000052 SET TX TO TRIANGLE-TYPE |
| 000053 ADD 1 TO N-CNTR (TX). |
| 000054 ENDING-PARA. |
| 000055 CLOSE INFILE. |
| 000056 CALL 'TRIRPT' USING NAME-N-CNTR-TABLE. |
| ****** *********************** BOTTOM OF MODULE *************************** |
| |

Figure 3-7. TRIMAIN After Entering LOCATE * Command

Place an A line command on statement 46, setting an AFTER breakpoint at
the READ statement. Next, type the K2 line command on statement 46 to
open a Keep window at the top of the screen and display the contents of
Working Storage item WORK-REC (the second variable identified on line 46).
The display is shown in Figure 3-8.

|----------------------------------------------------------------------------------|
| |
| ------------------------------ XPEDITER/TSO - SOURCE ----------------------------|
| COMMAND ===> SCROLL===> CSR |
| PROGRAM: TRIMAIN MODULE: TRIMAIN COMP DATE: 07/29/92 COMP TIME: 14.41.59 |
| --- |
| 000030 01 WORK-REC > ... |
| ------ --------------------------------------------------- Before TRIMAIN.44 --|
| =====> MOVE 'N' TO OUT-OF-RECS. |
| 000045 ANALYZE-NEXT-REC. |
| 000046 A READ INFILE INTO WORK-REC |
| 000047 AT END |
| 000048 MOVE 'Y' TO OUT-OF-RECS. |
| 000049 IF OUT-OF-RECS = 'N' |
| 000050 MOVE ZERO TO TRIANGLE-TYPE |
| 000051 CALL 'TRITST' USING WORK-REC TRIANGLE-TYPE |
| 000052 SET TX TO TRIANGLE-TYPE |
| 000053 ADD 1 TO N-CNTR (TX). |
| 000054 ENDING-PARA. |
| 000055 CLOSE INFILE. |
| 000056 CALL 'TRIRPT' USING NAME-N-CNTR-TABLE. |
| ****** ************************ BOTTOM OF MODULE ***************************** |
| |

Figure 3-8. Opening Up a Keep Window for the Second Variable (WORK-REC)
on Line 46

Press <PF12> (GO) to execute TRIMAIN. As shown in Figure 3-9, you can
see that record '345' was read when the READ verb was executed.

|----------------------------------------------------------------------------------|
| |
| ------------------------------ XPEDITER/TSO - SOURCE ----------------------------|
| COMMAND ===> SCROLL===> CSR |
| Next logical instruction is TRIMAIN.49 |
| --- |
| 000030 01 WORK-REC > 345 |
| ------ ---------------------------------------------------- After TRIMAIN.46 --|
| 000044 MOVE 'N' TO OUT-OF-RECS. |
| 000045 ANALYZE-NEXT-REC. |
| ====>> A READ INFILE INTO WORK-REC |
| 000047 AT END |
| 000048 MOVE 'Y' TO OUT-OF-RECS. |
| 000049 IF OUT-OF-RECS = 'N' |
| 000050 MOVE ZERO TO TRIANGLE-TYPE |
| 000051 CALL 'TRITST' USING WORK-REC TRIANGLE-TYPE |
| 000052 SET TX TO TRIANGLE-TYPE |
| 000053 ADD 1 TO N-CNTR (TX). |
| 000054 ENDING-PARA. |
| 000055 CLOSE INFILE. |
| 000056 CALL 'TRIRPT' USING NAME-N-CNTR-TABLE. |
| ***** ************************** BOTTOM OF MODULE ***************************** |
| |

Figure 3-9. Displaying a Variable in a Keep Window

Press <PF12> (GO) again. Paragraph ANALYZE-NEXT-REC is performed until
EOF, and the next record '789' is read in the second time through the
loop. WORK-REC is updated to reflect the change. As you execute your
program, XPEDITER/TSO updates the Keep window to reflect the current
values. You can also type over the displayed fields and modify the data
values as if the program had executed the MOVE statement.

3.4 Debugging Subroutines
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Statement 51 shows that the program TRIMAIN calls TRITST and passes
parameters WORK-REC and TRIANGLE-TYPE. In order to examine how TRITST is
processing these parameters, you might want to set a breakpoint at the
beginning of TRITST to gain control of the execution. Therefore, type the
following command in the primary command line:

BEFORE TRITST.

The period (.) after the program name indicates that a module-level
breakpoint is requested. Press <PF12> (GO) to execute the program.
XPEDITER/TSO pauses execution at the PROCEDURE DIVISION USING statement.
Now, type the following command in the primary command line.

PEEK LINKAGE

The Linkage Section shows that the correct values, '789' for TST-REC and
'0' for TYPE-OF-TRIANGLE were passed from the driver TRIMAIN.

|----------------------------------------------------------------------------------|
| |
| ------------------------------ XPEDITER/TSO - SOURCE ----------------------------|
| COMMAND ===> SCROLL===> CSR |
| PROGRAM: TRITST MODULE: TRITST COMP DATE: 07/29/92 COMP TIME: 14.41.59 |
| ------ ------------------------------------------------------- Before TRITST --|
| --- |
| 000010 01 TST-REC > 789 |
| 000011 05 A PIC 9. |
| 000012 05 B PIC 9. |
| 000013 05 C PIC 9. |
| 000014 01 TYPE-OF-TRIANGLE > 0 DECIMAL |
| =====> B PROCEDURE DIVISION USING TST-REC |
| 000016 TYPE-OF-TRIANGLE. |
| 000017 VALIDATE-TRIANGLE. |
| 000018 ADD A B GIVING A-N-B. |
| 000019 ADD A C GIVING A-N-C. |
| 000020 ADD B C GIVING B-N-C. |
| 000021 IF (B-N-C NOT > A) OR (A-N-C NOT > B) OR (A-N-B NOT > C) |
| 000022 MOVE 4 TO TYPE-OF-TRIANGLE. |
| 000023 DETERMINE-TYPE. |
| 000024 IF TYPE-OF-TRIANGLE = 4 |
| 000025 NEXT SENTENCE |
| 000026 ELSE |
| 000027 IF (A = B) AND (B = C) |
| 000028 MOVE 1 TO TYPE-OF-TRIANGLE |
| |
|----------------------------------------------------------------------------------|

Figure 3-10. Displaying LINKAGE SECTION in the Called Module TRITST

3.5 Analyzing Data Flow
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

To better understand how the parameters are processed in the subroutine,
XPEDITER/TSO allows you to cross-reference data and to analyze the data
flow in your program. The 01 level for TST-REC has three 05 levels: A,
B, and C. Essentially, the elementary items are the aliases of a group
item. Type the following command in the primary command line:

FIND TST-REC ALIAS

XPEDITER/TSO highlights all the statements that reference (DEFine,
MODify, USE) TST-REC and its aliases. The message shown in Figure 3-11 is
issued, which states how many data definitions were found.

|----------------------------------------------------------------------------------|
| |
| ------------------------------ XPEDITER/TSO - SOURCE ----------------------------|
| COMMAND ===> SCROLL===> CSR |
| 24 DATA REFS: 4 DEFS, 20 USES FOUND FOR TST-REC |
| ------ ------------------------------------------------------- Before TRITST --|
| --- |
| 000010 01 TST-REC > 789 |
| 000011 05 A PIC 9. DEF |
| 000012 05 B PIC 9. DEF |
| 000013 05 C PIC 9. DEF |
| 000014 01 TYPE-OF-TRIANGLE > 0 DECIMAL |
| =====> B PROCEDURE DIVISION USING TST-REC USE |
| 000016 TYPE-OF-TRIANGLE. |
| 000017 VALIDATE-TRIANGLE. |
| 000018 ADD A B GIVING A-N-B. 2 USE |
| 000019 ADD A C GIVING A-N-C. 2 USE |
| 000020 ADD B C GIVING B-N-C. 2 USE |
| 000021 IF (B-N-C NOT > A) OR (A-N-C NOT > B) OR (A-N-B NOT > C) 3 USE |
| 000022 MOVE 4 TO TYPE-OF-TRIANGLE. |
| 000023 DETERMINE-TYPE. |
| 000024 IF TYPE-OF-TRIANGLE = 4 |
| 000025 NEXT SENTENCE |
| 000026 ELSE |
| 000027 IF (A = B) AND (B = C) 4 USE |
| 000028 MOVE 1 TO TYPE-OF-TRIANGLE |
| |
|----------------------------------------------------------------------------------|

Figure 3-11. Finding Statements That Reference TST-REC

The analysis concludes that parameter TST-REC is USEd, but never MODified
in the subroutine. What about parameter TYPE-OF-TRIANGLE? Type the
following command in the primary command line:

FIND TYPE-OF-TRIANGLE MOD

The message "4 DATA MODS FOUND FOR TYPE-OF-TRIANGLE" is displayed in the
message line, as shown in Figure 3-12, which means that the parameter is
MODified four times.

|----------------------------------------------------------------------------------|
| |
| ------------------------------ XPEDITER/TSO - SOURCE ----------------------------|
| COMMAND ===> SCROLL===> CSR |
| 4 DATA MODS FOUND FOR TYPE-OF-TRIANGLE |
| ------ ------------------------------------------------------- Before TRITST --|
| --- |
| 000010 01 TST-REC > 345 |
| 000011 05 A PIC 9. |
| 000012 05 B PIC 9. |
| 000013 05 C PIC 9. |
| 000014 01 TYPE-OF-TRIANGLE > 0 DECIMAL |
| =====> B PROCEDURE DIVISION USING TST-REC |
| 000016 TYPE-OF-TRIANGLE. |
| 000017 VALIDATE-TRIANGLE. |
| 000018 ADD A B GIVING A-N-B. |
| 000019 ADD A C GIVING A-N-C. |
| 000020 ADD B C GIVING B-N-C. |
| 000021 IF (B-N-C NOT > A) OR (A-N-C NOT > B) OR (A-N-B NOT > C) |
| 000022 MOVE 4 TO TYPE-OF-TRIANGLE. MOD |
| 000023 DETERMINE-TYPE. |
| 000024 IF TYPE-OF-TRIANGLE = 4 |
| 000025 NEXT SENTENCE |
| 000026 ELSE |
| 000027 IF (A = B) AND (B = C) |
| 000028 MOVE 1 TO TYPE-OF-TRIANGLE MOD |
| |
|----------------------------------------------------------------------------------|

Figure 3-12. Result of Entering FIND TYPE-OF-TRIANGLE MOD Command

The FIND command under XPEDITER/TSO is sensitive to COBOL-structure
keywords as well as data reference keywords. For instance, you can use
the FIND command to search conditional statements or I/O statements. The
highlighting effect helps you capture the program logic and understand
what the program does.

3.6 Tracing Logic Flow
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Subroutine TRITST evaluates the type of triangle by using TST-REC and
updates TYPE-OF-TRIANGLE. Paragraph DETERMINE-TYPE (statement 23) has a
nested IF structure. XPEDITER/TSO can automatically trace the logic flow
to show which path was chosen. Type the following command in the primary
command line:

TRACE ALL STATEMENTS;GO

Tracing pauses when the AFTER breakpoint is reached when control is
returned to the calling module TRIMAIN, as shown in Figure 3-13.

|----------------------------------------------------------------------------------|
| |
| ------------------------------ XPEDITER/TSO - SOURCE ----------------------------|
| COMMAND ===> SCROLL===> CSR |
| Next logical instruction is TRIMAIN.49 |
| --- |
| 000030 01 WORK-REC > 563 |
| ------ ---------------------------------------------------- After TRIMAIN.46 --|
| 000044 MOVE 'N' TO OUT-OF-RECS. |
| 000045 ANALYZE-NEXT-REC. |
| ====>> A READ INFILE INTO WORK-REC |
| 000047 AT END |
| 000048 MOVE 'Y' TO OUT-OF-RECS. |
| 000049 IF OUT-OF-RECS = 'N' |
| 000050 MOVE ZERO TO TRIANGLE-TYPE |
| 000051 CALL 'TRITST' USING WORK-REC TRIANGLE-TYPE |
| 000052 SET TX TO TRIANGLE-TYPE |
| 000053 ADD 1 TO N-CNTR (TX). |
| 000054 ENDING-PARA. |
| 000055 CLOSE INFILE. |
| 000056 CALL 'TRIRPT' USING NAME-N-CNTR-TABLE. |
| ****** *********************** BOTTOM OF MODULE ***************************** |
| |
| |
| |
| |
| |
|----------------------------------------------------------------------------------|

Figure 3-13. Tracing is Paused for the AFTER Breakpoint in the Calling
Module TRIMAIN

Press <PF12> (GO) to return to TRITST again. If execution is too fast
for you to follow, type the following command in the primary command line
to control the tracing speed:

SET DELAY 1;GO

Otherwise, press <PF12> (GO) to trace through TRITST.

3.7 Review Mode
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Type the following command in the primary command line:

MONITOR ALL

Press <PF12> (GO) to start execution, followed by another <PF12> (GO) to
continue execution. When the AFTER breakpoint is reached in TRIMAIN, type
the following command in the primary command line to change the direction
of execution processing:

REVERSE

The execution status line shows that XPEDITER/TSO is reviewing in the
reverse direction. See Figure 3-14.

|----------------------------------------------------------------------------------|
| |
| ------------------------------ XPEDITER/TSO - SOURCE ----------------------------|
| COMMAND ===> SCROLL===> CSR |
| Next logical instruction is RESUME EXECUTION |
| --- |
| 000030 01 WORK-REC > 345 |
| ------ -------------------------------------------Reverse - After TRIMAIN.46 --|
| 000044 MOVE 'N' TO OUT-OF-RECS. |
| 000045 ANALYZE-NEXT-REC. |
| ====>> A READ INFILE INTO WORK-REC |
| 000047 AT END |
| 000048 MOVE 'Y' TO OUT-OF-RECS. |
| 000049 IF OUT-OF-RECS = 'N' |
| 000050 MOVE ZERO TO TRIANGLE-TYPE |
| 000051 CALL 'TRITST' USING WORK-REC TRIANGLE-TYPE |
| 000052 SET TX TO TRIANGLE-TYPE |
| 000053 ADD 1 TO N-CNTR (TX). |
| 000054 ENDING-PARA. |
| 000055 CLOSE INFILE. |
| 000056 CALL 'TRIRPT' USING NAME-N-CNTR-TABLE. |
| ****** *********************** BOTTOM OF MODULE ****************************** |
| |
| |
| |
| |
| |
|----------------------------------------------------------------------------------|

Figure 3-14. Review Mode Execution

Now step through each statement backwards by pressing <PF9> (GO 1)
several times. Data values in the Keep window reshow the original state
as the MOVE, ADD, and READ verbs are being "undone."

You can remove the Keep window by typing the following command in the
primary command line:

DELETE KEEP

If you want to remove a certain data item from the Keep window, enter the
D line command on the appropriate line. Press <PF4> (EXIT) to exit the
debugging session and to return to the Standard screen.

Xpeditor Screen

2006-02-01T02:24:00.000-08:00

Xpeditor - Screen Format
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

XPEDITER/TSO uses ISPF/PDF-like screens. Figure 2-1 shows the Source
screen.

|----------------------------------------------------------------------------------|
| |
| ------------------------------ XPEDITER/TSO - SOURCE ----------------------------|
| COMMAND ===> SCROLL===> CSR |
| PROGRAM: TRIMAIN MODULE: TRIMAIN COMP DATE: 07/29/92 COMP TIME: 14.41.59 |
| ------ ------------------------------------------------------ Before TRIMAIN --|
| =====> B PROCEDURE DIVISION. |
| 000035 MAIN-PARA. |
| 000036 PERFORM INIT-PARA. |
| 000037 PERFORM ANALYZE-NEXT-REC |
| 000038 UNTIL OUT-OF-RECS = 'Y'. |
| 000039 PERFORM ENDING-PARA. |
| 000040 A GOBACK. |
| 000041 INIT-PARA. |
| 000042 MOVE ZERO TO N-CNTR (1) N-CNTR (2) N-CNTR (3) N-CNTR (4). |
| 000043 OPEN INPUT INFILE. |
| 000044 MOVE 'N' TO OUT-OF-RECS. |
| 000045 ANALYZE-NEXT-REC. |
| 000046 READ INFILE INTO WORK-REC |
| 000047 AT END |
| 000048 MOVE 'Y' TO OUT-OF-RECS. |
| 000049 IF OUT-OF-RECS = 'N' |
| 000050 MOVE ZERO TO TRIANGLE-TYPE |
| 000051 CALL 'TRITST' USING WORK-REC TRIANGLE-TYPE |
| 000052 SET TX TO TRIANGLE-TYPE |
| 000053 ADD 1 TO N-CNTR (TX). |
| |
|----------------------------------------------------------------------------------|

Figure 2-1. XPEDITER/TSO Source Display Screen

The first four lines of the XPEDITER/TSO screens are formatted as follows:

|------------------------------------------------------------------------|
| Title |
|---------------------------------------------------------|--------------|
| Command Area | Scroll |
| | Amount |
|-----------------|------------------|-----------------|--|--------------|
| Program | Module | Compile Date | Compile Time |
|-----------------|------------------|-----------------|-----------------|
| Execution Status |
|------------------------------------------------------------------------|

These areas are described as:

Title (line 1)--Identifies the screen name: SOURCE, LOG, SHOW,
MEMORY, etc.

Command Area (line 2)--Primary command line, which can be increased to
three lines using the SET CMDSIZE command.

Scroll Amount (line 2)--Indicates the current scroll amount. You can
type over the current value with one of the following
values:

1 to 9999 Specifies scrolling by the number of lines or
columns.

CSR or C Specifies scrolling based on the current
position of the cursor.

DATA or D Specifies scrolling by one line or column less
than PAGE.

HALF or H Specifies scrolling by a half page.

MAX or M Specifies scrolling to the top, bottom, left
margin, or right margin.

PAGE or P Specifies scrolling by one page.

Program (line 3)--Identifies the source program currently
displayed. This is an unprotected field and can be typed
over with another program name.

Module (line 3)--Displays the load module name.

Compile Date (line 3)--Displays the compile date.

Compile Time (line 3)--Displays the compile time.

Message Area (line 3)--Displays short or informational messages. When a
message is issued, it overlays the program information in
line 3. Press <ENTER> to flush the message and display the
Program field. Additional information can be accessed by
pressing <PF1> (HELP).

Execution Status (line 4)--Identifies the current execution point in your
program. In an Assembler program, an arrow is displayed at
the end of this line to indicate that the source can be
scrolled to the left and/or right.

When a Keep window is present, the execution status line is
displayed at the bottom of the Keep window.

2.1.1 Source Area
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

The source area begins on line 5 and displays 68 to 70 bytes of text on
the screen at a time. In Assembler, the screen can be scrolled RIGHT
and/or LEFT to allow access to the full source.

The AFTER, BEFORE, KEEP, and SKIP indicators are displayed on the left
side of the screen in column 9. A 7-digit counter set by the COUNT
command is displayed on the right side beginning at column 74.

2.2 PF Keys
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

The default settings for the 24 PF keys are listed below. These values
are valid during the XPEDITER/TSO session. ISPF PF keys are not affected.
The ISPF KEYS command or the SET PFn command can be used to override the
default values.

|-----------|--------------|---------------------------------------------|
| PF Key | Default | Description of Function |
| | Setting | |
|-----------|--------------|---------------------------------------------|
| PF1/PF13 | HELP | Further elaborates an XPEDITER/TSO message |
| | | and invokes the context-sensitive tutorial |
| | | facility. |
|-----------|--------------|---------------------------------------------|
| PF2 | PEEK CSR | Displays the contents of the data name |
| | | defined by the current cursor position. |
| | | The cursor must be in the source window |
| | | under a valid data name. |
|-----------|--------------|---------------------------------------------|
| PF14 | FIND CSR | Displays the data name located under the |
| | | cursor position. |
|-----------|--------------|---------------------------------------------|
| PF3/PF15 | END | Returns you to the previous menu if you are |
| | | in the LOG, HELP, BROWSE, or SHOW |
| | | functions. |
|-----------|--------------|---------------------------------------------|
| PF4/PF16 | EXIT | Ends the current XPEDITER/TSO session. |
|-----------|--------------|---------------------------------------------|
| PF5 | FIND | Repeats the action of the previous FIND |
| | | command. |
|-----------|--------------|---------------------------------------------|
| PF17 | FIND IND | Scrolls the source display to successive |
| | | levels of indirect references related to a |
| | | previously entered FIND INDIRECT command. |
|-----------|--------------|---------------------------------------------|
| PF6/PF18 | LOCATE * | Scrolls the source display to the current |
| | | location where execution has been |
| | | suspended. |
|-----------|--------------|---------------------------------------------|
| PF7/PF19 | UP | Scrolls the source display up, or toward |
| | | the top of the file. |
|-----------|--------------|---------------------------------------------|
| PF8/PF20 | DOWN | Scrolls the source display down, or toward |
| | | the bottom of the file. |
|-----------|--------------|---------------------------------------------|
| PF9/PF21 | GO 1 | Executes the next logical instruction in |
| | | your program, then pauses. |
|-----------|--------------|---------------------------------------------|
| PF10/PF22 | LEFT | Scrolls the display to the left. |
|-----------|--------------|---------------------------------------------|
| PF11/PF23 | RIGHT | Scrolls the display to the right. |
|-----------|--------------|---------------------------------------------|
| PF12/PF24 | GO | Starts or resumes execution of your |
| | | program. |
|-----------|--------------|---------------------------------------------|
Table 2-1. Program Function (PF) Keys

2.3 Command Processing
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

In interactive mode, the results of command execution are immediately
visible on the source display. Additional commands can also be entered
dynamically.

XPEDITER/TSO commands can be entered in three ways:

1. Enter the command in the primary command area and press <ENTER>.
Command stacking--delimited by a semicolon (;), the default--is
allowed except in the DIALOG environment.

The primary command area can be extended up to three lines by using
the SET CMDSIZE command. The previous primary command can be recalled
by entering a question mark (?).

2. Press the PF key that was assigned to the desired command. See
Table 2-1 in topic 2.2 for a list of the PF key assignments.

3. Type over the 6-digit compiler-generated statement number with a valid
line command and press <ENTER>.

XPEDITER/TSO records the line command in the log in the same manner as
the primary command.

By default, commands entered in lowercase are converted to uppercase. To
override the default, use the SET CAPS OFF command. Also, to display
lowercase data, use the SET LOWCASE ASIS command.

2.4 HELP and Tutorial (Option T) Information
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

The HELP function provides you with access to general or specific
information regarding an XPEDITER/TSO command, panel, or message. Use the
END command to terminate the HELP function.

The ISPF HELP function is supported from all menus within the XPEDITER/TSO
ISPF Interface. To obtain information on any menu or message, press the
HELP PF key, <PF1>, or enter HELP on the command line.

Once you are in the Tutorial, you can continue to press <ENTER> to see
subsequent pages of the HELP text. The UP, BACK, NEXT, INDEX, and HELP
subcommands of the ISPF HELP function can be used to quickly locate a
particular topic.

You can also select option T to gain access to the Tutorial directly from
the Primary Menu.

2.5 General Product Information (Option B)
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

The Bulletin screen contains updated information relevant to the new
release. A similar version of this information is contained in the
"Changes for This Release" in topic 1.1.

XPEDITER TSO Overview

2006-02-01T01:58:00.000-08:00

XPEDITER/TSO Overview
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

     XPEDITER/TSO is a debugging and testing tool that automates the tasks of
    identifying problems, applying solutions, analyzing the impacts of
    changes, and testing the fixes.

1.1 Changes for This Release
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    *   Batch Connect Facility Enhancements

        DB2 and DB2/DLI JCL statements that previously required manual changes
        are now automatically converted for submitting a job in batch connect
        mode.  Three new commands have also been added to further enhance JCL
        conversion, submitting the job, and connecting to the job.

    *   Abend-AID Integration

        Enhances XPEDITER's fault diagnosis function by providing a direct
        connection to Abend-AID during an XPEDITER/TSO test session.
        Abend-AID analyzes the abend and XPEDITER/TSO displays the information
        in a report formatted by Abend-AID.

    *   COBOL/370 Compatibility

        Provides support for IBM's newest COBOL release--COBOL/370.

    *   Test Case Facility (TCF) Support

        Automatically collects the data read by the program during an
        XPEDITER/TSO test session.  With Compuware's File-AID/PC product, the
        collected data can be downloaded, browsed and edited, and used for
        testing on the workstation.

    *   User Interface Enhancements

        Further refinement of XPEDITER screens, commands, and functions.

    *   Performance Improvement

        Access to the source code display is now faster than ever.

    *   Documentation Changes

        XPEDITER/TSO and XPEDITER/IMS documentation has been updated to
        include information about the installation and use of the above
        features.

1.2 Changes for the Previous Release
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    *   Product Integration

        XPEDITER/TSO has been integrated with File-AID for DB2, Compuware's
        easy to use DB2 database management and SQL development and analysis
        tool.  To use the integrated product, you must be licensed for
        XPEDITER for DB2 Extension and File-AID for DB2.  The (FADB2) option
        was added to the Primary Menu for direct access to File-AID for DB2.
        You can also access File-AID for DB2 from within an XPEDITER/TSO
        debugging session.

    *   Batch Connect Facility

        With this feature, you can submit an XPEDITER/TSO debugging session as
        a Batch Connect job allowing you to interactively connect to a batch
        job.

    *   Sourceless Debugging

        XPEDITER/TSO now allows you to test and debug programs that do not
        have the source (no SIR records) available.  The new AT command can be
        used to set breakpoints in sourceless programs.

    *   Profile Handling Enhancement

        This feature provides a flexible and easy to use method for using and
        maintaining multiple profiles.  Multiple profiles are useful since
        some of the data you see during an XPEDITER/TSO debugging session
        depends on the values in the current profile.  This includes
        installation defaults established by your system programming staff,
        and most importantly, environment parameters and SETUP options for the
        current debugging session.

    *   Single Point of Entry

        This feature lets you access a CICS region from a Primary Menu option
        without logging off the TSO session.  Pressing a PF key returns you to
        TSO.

    *   Programmer Command Center Results Interface

        For Programmer Command Center customers, XPEDITER offers a results
        interface that notifies you of the status of your Batch Connect jobs.

    *   Documentation Changes

        The XPEDITER/TSO and XPEDITER/IMS documentation has been updated to
        include information about the installation and use of XPEDITER for DB2
        Extension and File-AID for DB2, Profile handling, and the VTAM Batch
        Connect facility.

1.3 Overview
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    When you run your program under interactive XPEDITER/TSO, the source code
    is displayed in a fully scrollable window where you can view the inner
    workings of the program as each statement is executed.  For instance, you
    can see the execution arrow moving from a PERFORM statement to the actual
    out-of-line perform paragraph, or you can see a data identifier changing
    its value as the MOVE, INITIALIZE, STRING, or COMPUTE statements are
    executed.

    Figure 1-1 shows the source display screen under XPEDITER/TSO.  All
    debugging and testing functions are accessible from this screen without
    having to exit to a separate panel.

    XPEDITER/TSO does not require any source code or load module changes;
    however, you need to create a "symbolic" information dataset called the
    Shared Information Repository (SIR) to debug at the source level.  The SIR
    records are created during the compile step after the compiler generates
    the compiler listing.  The SIR processor analyzes the listing information
    and outputs the SIR records into a PDS file.  XPEDITER/TSO displays and
    processes the SIR source records during the testing session.

    |----------------------------------------------------------------------------------|
    |                                                                                  |
    | ------------------------------ XPEDITER/TSO - SOURCE ----------------------------|
    | COMMAND ===>                                                     SCROLL===> CSR  |
    |                       Before Breakpoint Encountered                              |
    | ------   ------------------------------------------------------ Before TRIMAIN --|
    | =====> B PROCEDURE DIVISION.                                                     |
    | 000035   MAIN-PARA.                                                              |
    | 000036       PERFORM INIT-PARA.                                                  |
    | 000037       PERFORM ANALYZE-NEXT-REC                                            |
    | 000038           UNTIL OUT-OF-RECS = 'Y'.                                        |
    | 000039       PERFORM ENDING-PARA.                                                |
    | 000040 A     GOBACK.                                                             |
    | 000041   INIT-PARA.                                                              |
    | 000042       MOVE ZERO TO N-CNTR (1) N-CNTR (2) N-CNTR (3) N-CNTR (4).           |
    | 000043       OPEN INPUT INFILE.                                                  |
    | 000044       MOVE 'N' TO OUT-OF-RECS.                                            |
    | 000045   ANALYZE-NEXT-REC.                                                       |
    | 000046       READ INFILE INTO WORK-REC                                           |
    | 000047          AT END                                                           |
    | 000048          MOVE 'Y' TO OUT-OF-RECS.                                         |
    | 000049       IF OUT-OF-RECS = 'N'                                                |
    | 000050          MOVE ZERO TO TRIANGLE-TYPE                                       |
    | 000051          CALL 'TRITST' USING WORK-REC TRIANGLE-TYPE                       |
    | 000052          SET TX TO TRIANGLE-TYPE                                          |
    | 000053          ADD 1 TO N-CNTR (TX).                                            |
    |                                                                                  |
    |----------------------------------------------------------------------------------|

    Figure  1-1. Source Display Screen Under XPEDITER/TSO

1.3.1 Major XPEDITER/TSO Debugging and Testing Features
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    The capabilities of XPEDITER/TSO are extensive enough to assist the power
    programmers' needs, and yet the implementation is simple and efficient to
    curtail the learning curve that novice programmers generally experience.
    The following list summarizes XPEDITER/TSO's major features:

    *   Intercept application program abends.

         XPEDITER/TSO detects application abends before the offending
        instruction is executed and displays a diagnostic message.  You can
        request an Abend-AID formatted report if you have Abend-AID Release
        6.1.4 or higher or you can view the log for more information.  If the
        problem is a recoverable error, you can fix the error dynamically and
        resume execution.

    *   Start or stop execution at any point.

         You can set conditional or unconditional breakpoints, so you can stop
        and figure out what the program has processed so far, and what the
        consequences of any changes could be.  You can also step through code
        line-by-line to understand how each statement affects the program.

    *   Display and modify variable, register, and storage contents.

         You can view the variable contents and alter the values as if the
        program had actually moved in the value you requested.  Tables can be
        displayed by dimension, and you can browse through each entry by
        incrementing and decrementing the index or subscript.  General-purpose
        registers and memory can be displayed in a Keep window to analyze the
        problem at the lower level.

    *   Trace logic flow.

         Statements can be highlighted to identify the execution path as
        XPEDITER/TSO traces through the program.  You can control the speed of
        the execution so that you can follow the logic interactively at a
        speed that best suits you.

    *   Logically review execution in the reverse direction.

         XPEDITER/TSO can display the execution history in the reverse
        direction to allow you to review the execution path with associated
        data values.  The execution arrow moves backwards, and the data
        display shows the original values.

    *   Monitor execution coverage.

         You can set counters on statements to test execution coverage or to
        monitor loops and procedure calls for optimization.

    *   Alter logic.

        You can dynamically change the control flow by modifying the data
        values or by forcing a wild branch to test an alternate path.

    *   Bypass unwanted code.

         A range of statements or programs can be skipped in lieu of providing
        program stubs.

    *   Temporarily insert XPEDITER/TSO debugging statements.

         You can imbed XPEDITER/TSO commands to control debugging conditions
        to take place at a certain location, and to force data and logic
        changes.  This feature lets you prototype some COBOL-like constructs.
        If you have XPEDITER for DB2 Extension and File-AID for DB2, you can
        also insert SQL statements.

    *   Analyze program structure and data flow.

         XPEDITER/TSO identifies certain COBOL program structures
        (conditionals, branches, I/Os, statements that alter data values,
        etc.) by highlighting the applicable statements when requested.  Also,
        statements that make references (MODIFY or USE) to a particular data
        item can be identified.

    *   Display file status and DCBs.

         VSAM file status and last I/O operation can be retrieved through
        XPEDITER/TSO.  DCB information can also be checked in the case of I/O
        error.  When a DD statement is not preallocated, XPEDITER/TSO
        intercepts execution and gives you an opportunity to allocate the
        missing files before resuming execution.

    *   Display called module stacking.

         The called module configuration such as the load address, entry
        point, size, attribute, AMODE, RMODE, and language can be retrieved.

    *   Maintain multiple profiles.

        XPEDITER/TSO's Profile Handling facility provides a flexible and
        easy-to-use method for using and maintaining multiple profiles.
        Multiple profiles are used because some of the data you see during an
        XPEDITER/TSO debugging session depends on the current profile.  This
        includes installation defaults established by your system programming
        staff, and most importantly, environment parameters and SETUP options
        for the debugging session.

    *   Interactively connect to a batch job.

        The Batch Connect facility enables you to run an XPEDITER/TSO
        debugging session in batch and interactively connect to the batch job.

    *   Optionally run in unattended batch mode.

        XPEDITER/TSO can be submitted as a batch job to debug programs that
        require large region size, a tape input, or multiple job steps that
        process files.  Command stream is read as input, and any functions
        that require terminal interaction do not pause but write to the log
        indicating that the functions were performed.

1.3.2 XPEDITER/TSO Execution Operating Modes
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

     XPEDITER/TSO can be used in three ways:

    1.  In interactive mode.

         The chief advantage of using XPEDITER/TSO interactively is the
        ability to "pause" and resume execution of your program throughout a
        debugging session.  Interactive mode is preferable to batch mode
        testing when you have a specific problem for which you want to
        dynamically try out a solution.

    2.  In unattended batch mode.

         During a batch session, you cannot interact with the XPEDITER/TSO
        debugging session from your terminal.  In batch mode, XPEDITER/TSO
        receives its input from a test script file and writes to the log file.

        Some reasons for using batch mode testing are the following:

        *   Uses less processor resources than debugging in interactive mode.

        *   Allows testing of programs that require tape files.

        *   Executes long running programs and programs that perform many I/O
            operations in the background.

        *   Tests a job stream that has multiple job steps.

        *   Collects execution coverage and regression testing results.

    3.  In Batch Connect Mode.

        You can submit an XPEDITER/TSO session through the Batch Connect
        facility to run the job in batch and interactively connect to that
        batch job.  The source is displayed allowing you to interactively set
        breakpoints, display and modify variables, etc..

1.3.3 Primary Input to XPEDITER/TSO
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    The primary input to XPEDITER/TSO is the following:

    1.  Shared Information Repository (SIR) data library.

    2.  Load libraries containing the module(s) to be tested and debugged.

        Note:   The SIR data library and your load libraries are the output of
        the compile and link process discussed in "Compiling With the SIR
        Processor" in topic 4.2.

    3.  JCL, file list, or CLIST that can be processed to allocate the input
        files or databases needed by your program.

    4.  Optionally, a test script library.

1.3.4 Output From the Execution of XPEDITER/TSO
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    The primary output of the debugging session is:

    1.  Data files generated by the execution of the program.

    2.  Session log.

         XPEDITER/TSO automatically records (to the log file) the commands
        entered and the responses made to each command during a debugging
        session.  The log dataset can have an LRECL of 133 or 80.  The log
        does not contain the interactive manipulation commands nor does it
        contain the actual program displays.

         In batch mode, the log file shows the output of a batch test and is
        available after the completion of the debugging session.  In
        interactive mode, it is available at all times.

        The log file can be kept as part of the documentation, to be
        referenced whenever maintenance is performed on the program.  It can
        be viewed for further information in determining the cause and
        possible resolution of an abend.  Chapter 8, "Handling Run-Time
        Errors" in topic 8.0 contains an example of the log display and shows
        what is recorded in the log as a result of a S0C7 abend.

    3.  Session script, which can be saved as a member of a test script
        library.

         XPEDITER/TSO automatically records the commands entered to a
        sequential test script file.  After the session is terminated, you can
        save, copy, or move the generated test script to a member of a test
        script library (INCLUDE dataset), which can then be used as input to
        another debugging session.

        Test scripts can be used to set up, run, or rerun a session.  They are
        used to gather information at the close of a session (a post script),
        and in batch mode, to specify a series of commands to be executed when
        an abend occurs (abend script).  It is also used to store any
        customization made to XPEDITER/TSO or shop defaults.

1.3.5 LOGON Region Size Requirements
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

     Debugging with XPEDITER/TSO may require an increase to the default TSO
    LOGON region size.  This provides protection against abends caused by
    insufficient space.

    XPEDITER/TSO requires about 640K bytes minimum, in addition to the
    application programs to be tested and debugged.  Additional storage could
    be required, depending on the number and size of the programs that are
    referenced symbolically during the debugging session.

    It has been our experience that 2048K to 4096K (2M to 4M) is sufficient
    size for debugging most applications.

1.4 Restrictions and Warnings
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    The following list explains some of the technical restrictions and
    questions related to the functioning of XPEDITER/TSO:

    Attention (User Interrupt--PA1 Key) Processing:

    Depending on the timing in your use of the PA1 key/user interrupt from
    within XPEDITER/TSO, the result can be a recursive abend.

    Multitasking Support:

    Compuware does not support multitasking applications within XPEDITER/TSO,
    except in the dialog environment, where single task testing is supported
    within a multitasking environment.

    Optimized Code:

    XPEDITER/TSO displays the source for optimized code as it was originally
    written, but executes the code generated by the optimizer.  As a result,
    depending on the optimizing algorithm applied to the code, the following
    can occur:

    *   Highlighting during execution of the trace of the optimized code can
        be misleading.

    *   Abends can occur when you use XPEDITER/TSO commands that alter the
        program execution paths, such as GOTO and SKIP.  These abends occur
        when the altered execution paths are in conflict with path
        dependencies generated by the optimizer.

    *   If you have used Copy Suppress in the Procedure Division you might not
        be able to set a breakpoint on the first statement following the
        copied code.

    IMS Testing:

    *   Retention of breakpoints across IMS transactions is not supported.

    *   Transactions specified on the test MPP screen are queued up to run in
        the XPEDITER/TSO region regardless of where the transactions were
        initiated.  XPEDITER/TSO assigns a unique class to each of the
        specified transaction codes and forces them to run only in your
        XPEDITER/TSO region.  The transaction codes are not reassigned to
        their original class until you have completely finished testing in the
        MPP region.

    *   If a DLI program uses the XRST facility, the first DLI call must be
        the XRST call.  Otherwise, you can cause an abend 04E with reason code
        00D44054.

    *   Within a BTS/DLI setup, recovery of DB2 tables and IMS databases are
        uncoordinated.  An SQL COMMIT/ROLLBACK call commits or rolls back
        changes made to DB2 tables only, not your IMS databases.

    *   Customers who are experiencing system abends while using the BTS TRACE
        to monitor DB2 activity should check with IBM for fixes available for
        the BTS program product.  Problems have occurred in MVS/XA IMS 1.3 and
        BTS 1.3, as well as MVS/ESA IMS 3.1 and BTS 3.1.

    MVS/ESA Support:

    For assembler programs, all ESA instructions are supported except the
    instructions that use the access registers.
    Naming Convention:

    Program names should not begin with IBM reserved prefixes such as IHO,
    ILBO, IGZ, etc.

    COBOL Ready Trace:

    Unpredictable results can occur in XPEDITER/TSO when the COBOL READY TRACE
    command is used.

    Memory Allocations:

    User programs and other vendor packages that perform FREEMAINs on
    subpool=0 or subpool=3 are not supported.

    COBOL Declaratives Section:

    Breakpoints or GOTOs in the Declaratives Section are not supported.

2.0 Chapter 2.  User Interface to XPEDITER/TSO
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

    This chapter describes how to interact with XPEDITER/TSO in general.  The
    screen format, PF key assignments, command processing, HELP, and tutorial
    are discussed.

Using EXPLAIN in DB2

2006-02-01T01:44:00.000-08:00

Using EXPLAIN to improve SQL performance
The information under this heading, up to the end of this chapter, is
Product-sensitive Programming Interface and Associated Guidance Information, as
defined in Appendix I, “Notices” on page 893.
Definitions and purpose: EXPLAIN is a monitoring tool that produces information
about the following:
. A plan, package, or SQL statement when it is bound. The output appears in a
table you create called PLAN_TABLE, which is called a plan table. For
experienced users, you can use PLAN_TABLE to give optimization hints to
DB2.
| . An estimated cost of executing an SQL SELECT, INSERT, UPDATE, or
| DELETE statement. The output appears in a table you create called
| DSN_STATEMNT_TABLE, which is called a statement table. For more
| information about statement tables, see “Estimating a statement's cost” on
| page 679.
| . User-defined functions referred to in the statement, including the specific name
| and schema. The output appears in a table you create called
| DSN_FUNCTION_TABLE, which is called a function table. For more
| information about function tables, see “Ensuring that DB2 executes the
| intended user-defined function” on page 294.
Other tools: Other tools that can help you tune SQL queries include:
. DB2 Visual Explain
Visual Explain is a graphical workstation feature of DB2 that provides:
– An easy-to-understand display of a selected access path
– Suggestions for changing an SQL statement
– An ability to invoke EXPLAIN for dynamic SQL statements
– An ability to provide DB2 catalog statistics for referenced objects of an
access path
– A subsystem parameter browser with keyword 'Find' capabilities
For information on using DB2 Visual Explain, which is a separately packaged
CD-ROM provided with your DB2 Version 6 license, see DB2 Visual Explain
online help.
. DB2 Performance Monitor (PM)
DB2 PM is a performance monitoring tool that formats performance data. DB2
PM combines information from EXPLAIN and from the DB2 catalog. It displays
access paths, indexes, tables, table spaces, plans, packages, DBRMs, host
variable definitions, ordering, table access and join sequences, and lock types.
Output is presented in a dialog rather than as a table, making the information
easy to read and understand.
. DB2 Estimator
| DB2 Estimator for Windows is an easy-to-use, stand-alone tool for estimating
| the performance of DB2 for OS/390 applications. You can use it to predict the
| performance and cost of running the applications, transactions, SQL
Ò Copyright IBM Corp. 1983, 1999 637
| statements, and utilities. For instance, you can use DB2 Estimator for
| estimating the impact of adding or dropping an index from a table, estimating
| the change in response time from adding processor resources, and estimating
| the amount of time a utility job will take to run.
Chapter overview: This chapter includes the following topics:
. “Obtaining PLAN_TABLE information from EXPLAIN”
| . “Estimating a statement's cost” on page 679
. “First questions about data access” on page 645
. “Interpreting access to a single table” on page 655
. “Interpreting access to two or more tables” on page 661
. “Interpreting data prefetch” on page 669
. “Determining sort activity” on page 674
. “Processing for views and nested table expressions” on page 675
. “ Chapter 7-5. Parallel operations and query performance” on page 685
Obtaining PLAN_TABLE information from EXPLAIN
The information in PLAN_TABLE can help you to:
. Design databases, indexes, and application programs
. Determine when to rebind an application
. Determine the access path chosen for a query
For each access to a single table, EXPLAIN tells you if an index access or table
space scan is used. If indexes are used, EXPLAIN tells you how many indexes and
index columns are used and what I/O methods are used to read the pages. For
joins of tables, EXPLAIN tells you the join method and type, the order in which DB2
joins the tables, and when and why it sorts any rows.
The primary use of EXPLAIN is to observe the access paths for the SELECT parts
of your statements. For UPDATE and DELETE WHERE CURRENT OF, and for
INSERT, you receive somewhat less information in your plan table. And some
| accesses EXPLAIN does not describe: for example, the access to LOB values,
| which are stored separately from the base table, and access to parent or
dependent tables needed to enforce referential constraints.
The access paths shown for the example queries in this chapter are intended only
to illustrate those examples. If you execute the queries in this chapter on your
system, the access paths chosen can be different.
Steps to obtain PLAN_TABLE information: The summary of steps to obtain
information from EXPLAIN is as follows:
1. Have appropriate access to a plan table. To create the table, see “Creating
PLAN_TABLE” on page 639.
2. Populate the table with the information you want. For instructions, see
“Populating and maintaining a plan table” on page 644.
3. Select the information you want from the table. For instructions, see
“Reordering rows from a plan table” on page 645.
638 Application Programming and SQL Guide
Creating PLAN_TABLE
Before you can use EXPLAIN, you must create a table called PLAN_TABLE to hold
the results of EXPLAIN. A copy of the statements needed to create the table are in
| the DB2 sample library, under the member name DSNTESC. (Unless you need the
| information they provide, it is not necessary to create a function table or statement
| table to use EXPLAIN.)
Figure 168 shows the format of a plan table. Table 68 on page 640 shows the
content of each column.
Your plan table can use many formats, but use the 49-column format because it
gives you the most information. If you alter an existing plan table to add new
columns, specify the columns as NOT NULL WITH DEFAULT, so that default
values are included for the rows already in the table. However, as you can see in
Figure 168, certain columns do allow nulls. Do not specify those columns as NOT
NULL WITH DEFAULT.
QUERYNO INTEGER NOT NULL PREFETCH CHAR(1) NOT NULL WITH DEFAULT
QBLOCKNO SMALLINT NOT NULL COLUMN_FN_EVAL CHAR(1) NOT NULL WITH DEFAULT
APPLNAME CHAR(8) NOT NULL MIXOPSEQ SMALLINT NOT NULL WITH DEFAULT
PROGNAME CHAR(8) NOT NULL -------28 column format --------
PLANNO SMALLINT NOT NULL VERSION VARCHAR(64) NOT NULL WITH DEFAULT
METHOD SMALLINT NOT NULL COLLID CHAR(18) NOT NULL WITH DEFAULT
CREATOR CHAR(8) NOT NULL -------3ð column format --------
TNAME CHAR(18) NOT NULL ACCESS_DEGREE SMALLINT
TABNO SMALLINT NOT NULL ACCESS_PGROUP_ID SMALLINT
ACCESSTYPE CHAR(2) NOT NULL JOIN_DEGREE SMALLINT
MATCHCOLS SMALLINT NOT NULL JOIN_PGROUP_ID SMALLINT
ACCESSCREATOR CHAR(8) NOT NULL -------34 column format --------
ACCESSNAME CHAR(18) NOT NULL SORTC_PGROUP_ID SMALLINT
INDEXONLY CHAR(1) NOT NULL SORTN_PGROUP_ID SMALLINT
SORTN_UNIQ CHAR(1) NOT NULL PARALLELISM_MODE CHAR(1)
SORTN_JOIN CHAR(1) NOT NULL MERGE_JOIN_COLS SMALLINT
SORTN_ORDERBY CHAR(1) NOT NULL CORRELATION_NAME CHAR(18)
SORTN_GROUPBY CHAR(1) NOT NULL PAGE_RANGE CHAR(1) NOT NULL WITH DEFAULT
SORTC_UNIQ CHAR(1) NOT NULL JOIN_TYPE CHAR(1) NOT NULL WITH DEFAULT
SORTC_JOIN CHAR(1) NOT NULL GROUP_MEMBER CHAR(8) NOT NULL WITH DEFAULT
SORTC_ORDERBY CHAR(1) NOT NULL IBM_SERVICE_DATA VARCHAR(254) NOT NULL WITH DEFAULT
SORTC_GROUPBY CHAR(1) NOT NULL ------43 column format --------
TSLOCKMODE CHAR(3) NOT NULL WHEN_OPTIMIZE CHAR(1) NOT NULL WITH DEFAULT
TIMESTAMP CHAR(16) NOT NULL QBLOCK_TYPE CHAR(6) NOT NULL WITH DEFAULT
REMARKS VARCHAR(254) NOT NULL BIND_TIME TIMESTAMP NOT NULL WITH DEFAULT
-------25 column format -------- ------46 column format -----------
OPTHINT CHAR(8) NOT NULL WITH DEFAULT
HINT_USED CHAR(8) NOT NULL WITH DEFAULT
PRIMARY_ACCESSTYPE CHAR(1) NOT NULL WITH DEFAULT
-------49 column format-----------
| Figure 168. Format of PLAN_TABLE
Chapter 7-4. Using EXPLAIN to improve SQL performance 639
Table 68 (Page 1 of 4). Descriptions of columns in PLAN_TABLE
Column Name Description
QUERYNO | A number intended to identify the statement being explained. For a row produced by
| an EXPLAIN statement, you can specify the number in the SET QUERYNO clause.
| For a row produced by non-EXPLAIN statements, you can specify the number using
| the SET QUERYNO clause, which is an optional part of the SELECT, INSERT,
| UPDATE and DELETE statement syntax. Otherwise, DB2 assigns a number based
| on the line number of the SQL statement in the source program.
| FETCH statements do not each have an individual QUERYNO assigned to them.
| Instead, DB2 uses the QUERYNO of the DECLARE CURSOR statement for all
| corresponding FETCH statements for that cursor.
| When the values of QUERYNO are based on the statement number in the source
| program, values greater than 32767 are reported as 0. Hence, in a very long
| program, the value is not guaranteed to be unique. If QUERYNO is not unique, the
| value of TIMESTAMP is unique.
QBLOCKNO The position of the query in the statement being explained (1 for the outermost query,
2 for the next query, and so forth). For better performance, DB2 might merge a query
block into another query block. When that happens, the position number of the
merged query block will not be in QBLOCKNO.
APPLNAME The name of the application plan for the row. Applies only to embedded EXPLAIN
statements executed from a plan or to statements explained when binding a plan.
Blank if not applicable.
PROGNAME The name of the program or package containing the statement being explained.
Applies only to embedded EXPLAIN statements and to statements explained as the
result of binding a plan or package. Blank if not applicable.
PLANNO The number of the step in which the query indicated in QBLOCKNO was processed.
This column indicates the order in which the steps were executed.
METHOD A number (0, 1, 2, 3, or 4) that indicates the join method used for the step:
0 First table accessed, continuation of previous table accessed, or not used.
1 Nested loop join. For each row of the present composite table, matching
rows of a new table are found and joined.
2 Merge scan join. The present composite table and the new table are
scanned in the order of the join columns, and matching rows are joined.
3 Sorts needed by ORDER BY, GROUP BY, SELECT DISTINCT, UNION, a
quantified predicate, or an IN predicate. This step does not access a new
table.
4 Hybrid join. The current composite table is scanned in the order of the
join-column rows of the new table. The new table is accessed using list
prefetch.
CREATOR The creator of the new table accessed in this step; blank if METHOD is 3.
TNAME The name of a table, temporary table, materialized view, table expression, or an
intermediate result table for an outer join that is accessed in this step; blank if
METHOD is 3.
For an outer join, this column contains the temporary table name of the work file in
the form DSNWFQB( qblockno). Merged views show the base table names and
correlation names. A materialized view is another query block with its own
materialized views, tables, and so forth.
TABNO Values are for IBM use only.
640 Application Programming and SQL Guide
Table 68 (Page 2 of 4). Descriptions of columns in PLAN_TABLE
Column Name Description
ACCESSTYPE The method of accessing the new table:
I By an index (identified in ACCESSCREATOR and ACCESSNAME)
I1 By a one-fetch index scan
N By an index scan when the matching predicate contains the IN keyword
R By a table space scan
M By a multiple index scan; followed by MX, MI, or MU
MX By an index scan on the index named in ACCESSNAME
MI By an intersection of multiple indexes
MU By a union of multiple indexes
blank Not applicable to the current row.
MATCHCOLS For ACCESSTYPE I, I1, N, or MX, the number of index keys used in an index scan;
otherwise, 0.
ACCESSCREATOR For ACCESSTYPE I, I1, N, or MX, the creator of the index; otherwise, blank.
ACCESSNAME For ACCESSTYPE I, I1, N, or MX, the name of the index; otherwise, blank.
INDEXONLY Whether access to an index alone is enough to carry out the step, or whether data
too must be accessed. Y=Yes; N=No.
SORTN_UNIQ Whether the new table is sorted to remove duplicate rows. Y=Yes; N=No.
SORTN_JOIN Whether the new table is sorted for join method 2 or 4. Y=Yes; N=No.
SORTN_ORDERBY Whether the new table is sorted for ORDER BY. Y=Yes; N=No.
SORTN_GROUPBY Whether the new table is sorted for GROUP BY. Y=Yes; N=No.
SORTC_UNIQ Whether the composite table is sorted to remove duplicate rows. Y=Yes; N=No.
SORTC_JOIN Whether the composite table is sorted for join method 1, 2 or 4. Y=Yes; N=No.
SORTC_ORDERBY Whether the composite table is sorted for an ORDER BY clause or a quantified
predicate. Y=Yes; N=No.
SORTC_GROUPBY Whether the composite table is sorted for a GROUP BY clause. Y=Yes; N=No.
TSLOCKMODE An indication of the mode of lock to be acquired on either the new table, or its table
space or table space partitions. If the isolation can be determined at bind time, the
values are:
IS Intent share lock
IX Intent exclusive lock
S Share lock
U Update lock
X Exclusive lock
SIX Share with intent exclusive lock
N UR isolation; no lock
If the isolation cannot be determined at bind time, then the lock mode determined by
the isolation at run time is shown by the following values.
NS For UR isolation, no lock; for CS, RS, or RR, an S lock.
NIS For UR isolation, no lock; for CS, RS, or RR, an IS lock.
NSS For UR isolation, no lock; for CS or RS, an IS lock; for RR, an S lock.
SS For UR, CS, or RS isolation, an IS lock; for RR, an S lock.
The data in this column is right justified. For example, IX appears as a blank followed
by I followed by X. If the column contains a blank, then no lock is acquired.
Chapter 7-4. Using EXPLAIN to improve SQL performance 641
Table 68 (Page 3 of 4). Descriptions of columns in PLAN_TABLE
Column Name Description
TIMESTAMP Usually, the time at which the row is processed, to the last .01 second. If necessary,
DB2 adds .01 second to the value to ensure that rows for two successive queries
have different values.
REMARKS A field into which you can insert any character string of 254 or fewer characters.
PREFETCH Whether data pages are to be read in advance by prefetch. S = pure sequential
prefetch; L = prefetch through a page list; blank = unknown or no prefetch.
COLUMN_FN_EVAL When an SQL column function is evaluated. R = while the data is being read from
the table or index; S = while performing a sort to satisfy a GROUP BY clause; blank
= after data retrieval and after any sorts.
MIXOPSEQ The sequence number of a step in a multiple index operation.
1, 2, ... n For the steps of the multiple index procedure (ACCESSTYPE is MX,
MI, or MU.)
0 For any other rows (ACCESSTYPE is I, I1, M, N, R, or blank.)
VERSION The version identifier for the package. Applies only to an embedded EXPLAIN
statement executed from a package or to a statement that is explained when binding
a package. Blank if not applicable.
COLLID The collection ID for the package. Applies only to an embedded EXPLAIN statement
executed from a package or to a statement that is explained when binding a
package. Blank if not applicable.
Note: The following nine columns, from ACCESS_DEGREE through CORRELATION_NAME, contain the null value if
the plan or package was bound using a plan table with fewer than 43 columns. Otherwise, each of them can
contain null if the method it refers to does not apply.
ACCESS_DEGREE The number of parallel tasks or operations activated by a query. This value is
determined at bind time; the actual number of parallel operations used at execution
time could be different. This column contains 0 if there is a host variable.
ACCESS_PGROUP_ID The identifier of the parallel group for accessing the new table. A parallel group is a
set of consecutive operations, executed in parallel, that have the same number of
parallel tasks. This value is determined at bind time; it could change at execution
time.
JOIN_DEGREE The number of parallel operations or tasks used in joining the composite table with
the new table. This value is determined at bind time, and can be 0 if there is a host
variable. The actual number of parallel operations or tasks used at execution time
could be different.
JOIN_PGROUP_ID The identifier of the parallel group for joining the composite table with the new table.
This value is determined at bind time; it could change at execution time.
SORTC_PGROUP_ID The parallel group identifier for the parallel sort of the composite table.
SORTN_PGROUP_ID The parallel group identifier for the parallel sort of the new table.
PARALLELISM_MODE The kind of parallelism, if any, that is used at bind time:
I Query I/O parallelism
C Query CP parallelism
X Sysplex query parallelism
MERGE_JOIN_COLS The number of columns that are joined during a merge scan join (Method=2).
CORRELATION_NAME The correlation name of a table or view that is specified in the statement. If there is
no correlation name then the column is blank.
PAGE_RANGE Whether the table qualifies for page range screening, so that plans scan only the
partitions that are needed. Y = Yes; blank = No.
642 Application Programming and SQL Guide
Table 68 (Page 4 of 4). Descriptions of columns in PLAN_TABLE
Column Name Description
JOIN_TYPE The type of an outer join.
F FULL OUTER JOIN
L LEFT OUTER JOIN
blank INNER JOIN or no join
RIGHT OUTER JOIN converts to a LEFT OUTER JOIN when you use it, so that
JOIN_TYPE contains L.
GROUP_MEMBER The member name of the DB2 that executed EXPLAIN. The column is blank if the
DB2 subsystem was not in a data sharing environment when EXPLAIN was
executed.
IBM_SERVICE_DATA Values are for IBM use only.
WHEN_OPTIMIZE When the access path was determined:
blank At bind time, using a default filter factor for any host variables, parameter
markers, or special registers.
B At bind time, using a default filter factor for any host variables, parameter
markers, or special registers; however the statement will be reoptimized at
run time using input variable values for input host variables, parameter
markers, or special registers. The bind option REOPT(VARS) must be
specified for reoptimization to occur.
R At run time, using input variables for any host variables, parameter markers,
or special registers. The bind option REOPT(VARS) must be specified for
this to occur.
QBLOCK_TYPE For each query block, the type of SQL operation performed. For the outermost query,
it identifies the statement type. Possible values:
SELECT SELECT
INSERT INSERT
UPDATE UPDATE
DELETE DELETE
SELUPD SELECT with FOR UPDATE OF
DELCUR DELETE WHERE CURRENT OF CURSOR
UPDCUR UPDATE WHERE CURRENT OF CURSOR
CORSUB Correlated subquery
NCOSUB Noncorrelated subquery
BIND_TIME The time at which the plan or package for this statement or query block was bound.
For static SQL statements, this is a full-precision timestamp value. For dynamic SQL
statements, this is the value contained in the TIMESTAMP column of PLAN_TABLE
appended by 4 zeroes.
| OPTHINT A string that you use to identify this row as an optimization hint for DB2. DB2 uses
| this row as input when choosing an access path.
| HINT_USED If DB2 used one of your optimization hints, it puts the identifier for that hint (the value
| in OPTHINT) in this column.
| PRIMARY_ACCESSTYPE Indicates whether direct row access will be attempted first:
| D DB2 will try to use direct row access. If DB2 cannot use direct row access at
| runtime, it uses the access path described in the ACCESSTYPE column of
| PLAN_TABLE.
| blank DB2 will not try to use direct row access.
Chapter 7-4. Using EXPLAIN to improve SQL performance 643
Populating and maintaining a plan table
For the two distinct ways to populate a plan table, see:
. “Execute the SQL statement EXPLAIN”
. “Bind with the option EXPLAIN(YES)”
| When you populate the plan table through DB2's EXPLAIN, any INSERT triggers
| on the table are not activated. If you insert rows yourself, then those triggers are
| activated.
For tips on maintaining a growing plan table, see “Maintaining a plan table.”
Execute the SQL statement EXPLAIN
You can populate PLAN_TABLE by executing the SQL statement EXPLAIN. In the
statement, specify a single explainable SQL statement in the FOR clause.
You can execute EXPLAIN either statically from an application program, or
dynamically, using QMF or SPUFI. For instructions and for details of the
authorization you need on PLAN_TABLE, see DB2 SQL Reference.
Bind with the option EXPLAIN(YES)
You can populate a plan table when you bind or rebind a plan or package. Specify
the option EXPLAIN(YES). EXPLAIN obtains information about the access paths for
all explainable SQL statements in a package or the DBRMs of a plan. The
information appears in table package_owner.PLAN_TABLE or
plan_owner.PLAN_TABLE. For dynamically prepared SQL, the qualifier of
PLAN_TABLE is the current SQLID.
Performance considerations: EXPLAIN as a bind option should not be a
performance concern. The same processing for access path selection is performed,
regardless of whether you use EXPLAIN(YES) or EXPLAIN (NO). With
EXPLAIN(YES), there is only a small amount of overhead processing to put the
results in a plan table.
If a plan or package that was previously bound with EXPLAIN(YES) is automatically
rebound, the value of field EXPLAIN PROCESSING on installation panel DSNTIPO
determines whether EXPLAIN is run again during the automatic rebind. Again, there
is a small amount of overhead for inserting the results into a plan table.
EXPLAIN for remote binds: A remote requester that accesses DB2 can specify
EXPLAIN(YES) when binding a package at the DB2 server. The information
appears in a plan table at the server, not at the requester. If the requester does not
support the propagation of the option EXPLAIN(YES), rebind the package at the
requester with that option to obtain access path information. You cannot get
information about access paths for SQL statements that use private protocol.
Maintaining a plan table
DB2 adds rows to PLAN_TABLE as you choose; it does not automatically delete
rows. To clear the table of obsolete rows, use DELETE, just as you would for
deleting rows from any table. You can also use DROP TABLE to drop a plan table
completely.
644 Application Programming and SQL Guide
Reordering rows from a plan table
Several processes can insert rows into the same plan table. To understand access
paths, you must retrieve the rows for a particular query in an appropriate order.
Retrieving rows for a plan
The rows for a particular plan are identified by the value of APPLNAME. The
following query to a plan table returns the rows for all the explainable statements in
a plan in their logical order:
SELECT \ FROM JOE.PLAN_TABLE
WHERE APPLNAME = 'APPL1'
ORDER BY TIMESTAMP, QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ;
The result of the ORDER BY clause shows whether there are:
. Multiple QBLOCKNOs within a QUERYNO
. Multiple PLANNOs within a QBLOCKNO
. Multiple MIXOPSEQs within a PLANNO
All rows with the same non-zero value for QBLOCKNO and the same value for
QUERYNO relate to a step within the query. QBLOCKNOs are not necessarily
executed in the order shown in PLAN_TABLE. But within a QBLOCKNO, the
PLANNO column gives the substeps in the order they execute.
For each substep, the TNAME column identifies the table accessed. Sorts can be
shown as part of a table access or as a separate step.
What if QUERYNO=0? In a program with more than 32767 lines, all values of
QUERYNO greater than 32767 are reported as 0. For entries containing
QUERYNO=0, use the timestamp, which is guaranteed to be unique, to distinguish
individual statements.
Retrieving rows for a package
The rows for a particular package are identified by the values of PROGNAME,
COLLID, and VERSION. Those columns correspond to the following four-part
naming convention for packages:
LOCATION.COLLECTION.PACKAGE_ID.VERSION
COLLID gives the COLLECTION name, and PROGNAME gives the PACKAGE_ID.
The following query to a plan table return the rows for all the explainable
statements in a package in their logical order:
SELECT \ FROM JOE.PLAN_TABLE
WHERE PROGNAME = 'PACK1' AND COLLID = 'COLL1' AND VERSION = 'PROD1'
ORDER BY QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ;
First questions about data access
When you examine your EXPLAIN results, try to answer the following questions:
. “Is access through an index? (ACCESSTYPE is I, I1, N or MX)” on page 646
. “Is access through more than one index? (ACCESSTYPE=M)” on page 646
. “How many columns of the index are used in matching? (MATCHCOLS=n)” on
page 647
. “Is the query satisfied using only the index? (INDEXONLY=Y)” on page 648
Chapter 7-4. Using EXPLAIN to improve SQL performance 645
| . “Is direct row access possible? (PRIMARY_ACCESSTYPE = D)” on page 648
. “Is a view or nested table expression materialized?” on page 652
. “Was a scan limited to certain partitions? (PAGE_RANGE=Y)” on page 652
. “What kind of prefetching is done? (PREFETCH = L, S, or blank)” on page 653
. “Is data accessed or processed in parallel? (PARALLELISM_MODE is I, C, or
X)” on page 653
. “Are sorts performed?” on page 654
. “Is a subquery transformed into a join?” on page 654
. “When are column functions evaluated? (COLUMN_FN_EVAL)” on page 654
As explained in this section, they can be answered in terms of values in columns of
a plan table.
Is access through an index? (ACCESSTYPE is I, I1, N or MX)
If the column ACCESSTYPE in the plan table has one of those values, DB2 uses
an index to access the table named in column TNAME. The columns
ACCESSCREATOR and ACCESSNAME identify the index. For a description of
methods of using indexes, see “Index access paths” on page 656.
| Is access through more than one index? (ACCESSTYPE=M)
Those values indicate that DB2 uses a set of indexes to access a single table. A
set of rows in the plan table contain information about the multiple index access.
The rows are numbered in column MIXOPSEQ in the order of execution of steps in
the multiple index access. (If you retrieve the rows in order by MIXOPSEQ, the
result is similar to postfix arithmetic notation.)
The examples in Figure 169 and Figure 170 on page 647 have these indexes: IX1
on T(C1) and IX2 on T(C2). DB2 processes the query in these steps:
| 1. Retrieve all the qualifying record identifiers (RIDs) where C1=1, using index
| IX1.
2. Retrieve all the qualifying RIDs where C2=1, using index IX2. The intersection
of those lists is the final set of RIDs.
3. Access the data pages needed to retrieve the qualified rows using the final RID
list.
SELECT \ FROM T
WHERE C1 = 1 AND C2 = 1;
Figure 169. PLAN_TABLE output for example with intersection (AND) operator
TNAME
ACCESSTYPE
MATCHCOLS
ACCESSNAME
INDEXONLY
PREFETCH
MIXOPSEQ
T M 0 N L 0
T MX 1 IX1 Y 1
T MX 1 IX2 Y 2
T MI 0 N 3
646 Application Programming and SQL Guide
The same index can be used more than once in a multiple index access, because
more than one predicate could be matching, as in Figure 170 on page 647.
SELECT \ FROM T
WHERE C1 BETWEEN 1ðð AND 199 OR
C1 BETWEEN 5ðð AND 599;
Figure 170. PLAN_TABLE output for example with Union (OR) Operator
TNAME
ACCESSTYPE
MATCHCOLS
ACCESSNAME
INDEXONLY
PREFETCH
MIXOPSEQ
T M 0 N L 0
T MX 1 IX1 Y 1
T MX 1 IX1 Y 2
T MU 0 N 3
The steps are:
1. Retrieve all RIDs where C1 is between 100 and 199, using index IX1.
2. Retrieve all RIDs where C1 is between 500 and 599, again using IX1. The
union of those lists is the final set of RIDs.
3. Retrieve the qualified rows using the final RID list.
| How many columns of the index are used in matching?
| (MATCHCOLS=n)
If MATCHCOLS is 0, the access method is called a nonmatching index scan. All
the index keys and their RIDs are read.
If MATCHCOLS is greater than 0, the access method is called a matching index
scan: the query uses predicates that match the index columns.
In general, the matching predicates on the leading index columns are equal or IN
predicates. The predicate that matches the final index column can be an equal, IN,
or range predicate (<, <=, >, >=, LIKE, or BETWEEN).
The following example illustrates matching predicates:
SELECT \ FROM EMP
WHERE JOBCODE = '5' AND SALARY > 6ðððð AND LOCATION = 'CA';
INDEX XEMP5 on (JOBCODE, LOCATION, SALARY, AGE);
The index XEMP5 is the chosen access path for this query, with MATCHCOLS = 3.
There are two equal predicates on the first two columns and a range predicate on
the third column. Though there are four columns in the index, only three of them
can be considered matching columns.
Chapter 7-4. Using EXPLAIN to improve SQL performance 647
Is the query satisfied using only the index? (INDEXONLY=Y)
In this case, the method is called index-only access. For a SELECT operation, all
the columns needed for the query can be found in the index and DB2 does not
access the table. For an UPDATE or DELETE operation, only the index is required
to read the selected row.
| Index-only access to data is not possible for any step that uses list prefetch
| (described under “What kind of prefetching is done? (PREFETCH = L, S, or blank)”
| on page 653. Index-only access is not possible when returning varying-length data
| in the result set or a VARCHAR column has a LIKE predicate, unless the
| VARCHAR FROM INDEX field of installation panel DSNTIP4 is set to YES and
| plan or packages have been rebound to pick up the change. See Section 2 of DB2
| Installation Guide for more information.
If access is by more than one index, INDEXONLY is Y for a step with access type
MX, because the data pages are not actually accessed until all the steps for
intersection (MI) or union (MU) take place.
| When an SQL application uses index-only access for a ROWID column, the
| application claims the table space or table space partition. As a result, contention
| may occur between the SQL application and a utility that drains the table space or
| partition. Index-only access to a table for a ROWID column is not possible if the
| associated table space or partition is in an incompatible restrictive state. For
| example, an SQL application can make a read claim on the table space only if the
| restrictive state allows readers.

CREATE PLAN TABLE

2006-02-01T01:32:00.000-08:00

CREATE TABLE XTRNBBA.PLAN_TABLE
(
  QUERYNO          INTEGER NOT NULL,
  QBLOCKNO          SMALLINT NOT NULL,
  APPLNAME          CHAR(8) NOT NULL,
  PROGNAME          CHAR(8)  NOT NULL,
  PLANNO          SMALLINT NOT NULL,
METHOD          SMALLINT NOT NULL,
  CREATOR          CHAR(8)  NOT NULL,
TNAME          CHAR(18)  NOT NULL,
TABNO          SMALLINT NOT NULL,
ACCESSTYPE          CHAR(2)  NOT NULL,
  MATCHCOLS          SMALLINT NOT NULL,
ACCESSCREATOR     CHAR(8)  NOT NULL,
ACCESSNAME          CHAR(18)  NOT NULL,
INDEXONLY          CHAR(1)  NOT NULL,
SORTN_UNIQ          CHAR(1)  NOT NULL,
SORTN_JOIN          CHAR(1)  NOT NULL,
SORTN_ORDERBY     CHAR(1)  NOT NULL,
SORTN_GROUPBY     CHAR(1)  NOT NULL,
SORTC_UNIQ          CHAR(1)  NOT NULL,
SORTC_JOIN          CHAR(1)  NOT NULL,
SORTC_ORDERBY     CHAR(1)  NOT NULL,
SORTC_GROUPBY     CHAR(1)  NOT NULL,
TSLOCKMODE          CHAR(3)  NOT NULL,
TIMESTAMP          CHAR(16)  NOT NULL,
REMARKS          VARCHAR(254)  NOT NULL,
PREFETCH          CHAR(1)  NOT NULL WITH DEFAULT,
COLUMN_FN_EVAL     CHAR(1)  NOT NULL WITH DEFAULT,
MIXOPSEQ          CHAR(1)  NOT NULL WITH DEFAULT,
VERSION          VARCHAR(64)  NOT NULL WITH DEFAULT,
COLLID          CHAR(18)  NOT NULL WITH DEFAULT,
ACCESS_DEGREE     SMALLINT,
ACCESS_PGROUP_ID     SMALLINT,
JOIN_DEGREE          SMALLINT,
JOIN_PGROUP_ID     SMALLINT,
SORTC_PGROUP_ID     SMALLINT,
SORTN_PGROUP_ID     SMALLINT,
PARALLELISM_MODE   CHAR(1),
MERGE_JOIN_COLS     SMALLINT,
CORRELATION_NAME     CHAR(18),
PAGE_RANGE          CHAR(1)  NOT NULL,
JOIN_TYPE          CHAR(1)  NOT NULL,
GROUP_MEMBER     CHAR(8)  NOT NULL,
IBM_SERVICE_DATA     VARCHAR(254) NOT NULL,
WHEN_OPTIMIZE            CHAR(1)  NOT NULL,
QBLOCK_TYPE     CHAR(6)  NOT NULL,
BIND_TIME          TIMESTAMP NOT NULL
)  IN DATABASE.TABLESPACE;

EXEC SQL

     EXPLAIN ALL SET QUERYNO =  n FOR
     SQL statement

end-exec.

SELECT

QUERYNO,  QBLOCKNO, QBLOCK_TYPE,  APPLNAME, PROGNAME,
PLANNO, METHOD,  CREATOR, TNAME, TABNO, ACCESSTYPE, JOIN_TYPE,
MATCHCOLS, ACCESSNAME,  INDEXONLY, SORTN_PGROUP_ID,
SORTN_UNIQ, SORTN_JOIN, SORTN_ORDERBY,  SORTN_GROUPBY,
SORTC_PGROUP_ID, SORTC_UNIQ, SORTC_JOIN, SORTC_ORDERBY,
SORTC_GROUPBY, TSLOCKMODE, TIMESTAMP,
PREFETCH, COLUMN_FN_EVAL, MIXOPSEQ, COLLID, VERSION,
ACCESS_DEGREE, ACCESS_PGROUP_ID, JOIN_DEGREE, JOIN_PGROUP_ID,
PARALLELISM_MODE, MERGE_JOIN_COLS, CORRELATION_NAME,
PAGE_RANGE, GROUP_MEMBER, WHEN_OPTIMIZE, BIND_TIME

FROM XTRNBBA.PLAN_TABLE

ORDER BY APPLNAME, COLLID, VERSION, PROGNAME,
                     TIMESTAMP DESC, QUERYNO, QBLOCKNO, PLANNO;

Method      -
          0     First table accessed
          1     Nested loop join
          2     Merge scan join
          3     Independent Sort
          4     Hybrid join

Accesstype     I     Indexed access
          I1     One-fetch index scan
          R     Tablespace scan
          N     Index access with an IN predicate
          M     Multiple index scan
          MX     Specification of the index name for multiple index access
          MI     Multiple index access by RID intersection
          MU     Multiple index access by RID union

Matchcols     Contains number of index columns used in an index scan when accesstype is I, I1, N, or MX

Accessname     Indicates the name of the index used

Indexonly     Y - access to index is suffcient to satisfy query
          N - access to tablespace is required

Sortn_uniq     Y - indicates that a sort must be performed on the new table to remove duplicates

Sortn_join     Y - indicates that a sort must be performed on the new table to accomplish a merge scan join or a sort is performed on the RID list and intermediate table of a hybrid join

Sortn_orderby     Y - indicates that a sort must be performed on the new table to order rows

Sortn_groupby     Y - indicates that a sort must be performed on the new table to group rows

Sortc_uniq     Y - indicates that a sort must be performed on the composite table to remove duplicates

Sortc_join     Y - indicates that a sort must be performed on the composite table to accomplish a merge scan join or a sort is performed on the RID list and intermediate table of a hybrid join

Sortc_orderby     Y - indicates that a sort must be performed on the composite table to order rows

Sortc_groupby     Y - indicates that a sort must be performed on the composite table to group rows

Tslockmode     Lock level applied to the new table, its table space or partitions
          If isolation level can be determined at bind time:
          IS     intent share lock
          IX     intent exclusive lock
          S     share lock
          U     update lock
          X     exclusive lock
          SIX     share with intent exclusive lock
          N     no lock

          If isolation level cannot be determined at bind time:

          NS     share lock (CS, RR, RS)
          NIS     Intent share lock (CS, RR, RS)
          NSS     Intent share lock (CS, RS), share lock (RR)
          SS     Intent share lock (CS, RS, UR) share lock (RR)

Prefetch     S     sequential prefetch
          L     list prefetch
          blank     prefetch not used initially

Column_fn_eval     Indicates when the column function is evaluated
          R     data retrieval time
          S     sort time

Mixopseq     Indicates sequence of multiple index operation

Access_degree     Number of parallel tasks utilized by the query

Access_pgroup_id  A sequential number identifying the parallel group accessing the new table

Parallelism_mode     I - i/o parallelism
               C- cpu parallelism
               X - Sysplex parallelism

Merge_join_cols   indicates the number of columns joined during a merge scan

Join_type     F     full outer join
          L     Left outer join
          blank     Inner join (or no join)

When_optimize     blank     at bind time
          B     At bind time, but will be re-optimised at run time
          R     At run time

Qblock_type     Type of SQL operation

          select     select
          selupd     select with for update of
          insert
          update
          delete
          updcur     update where current of cursor
          delcur     delete where current of cursor
          corsub     correlated sub-query
          ncosub     non-correlated sub-query

Most efficient access path when a limited number of rows are to be retrieved:

Index only direct index look up
Direct index look up with data access
Index-only matching index scan
Index-only non-matching index scan
Matching clustered index access
Matching non-clustered index access
Non-Matching clustered index access
Non-Matching non-clustered index access
Partitioned tablespace scan skipping multiple partitions (partition scan)
Segmented tablespace scan
Simple table scan

The above sequence is reversed if most of the rows in the table are being accessed