Testing and Debugging Stored Procedures | DB2(R) Universal Database for OS/390 V7.1 Application Certification Guide (IBM DB2 Certification Guide Series)


Team-Fly

	DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson
	Table of Contents

	Chapter 9. Testing and Debugging Applications

Testing and Debugging Stored Procedures

Testing and debugging a stored procedure is quite a bit different than teting other programs. There are different tools that can be used to help in this effort.

Testing a Stored Procedure

Some commonly used debugging tools, such as TSO TEST, are not available in the environment where stored procedures run. Here are some alternative testing strategies to consider.

Standalone Program on a Workstation

If you have debugging support on a workstationI (such as with Stored Procedure Builder [SPB]; see Chapter 5), you might choose to do most of your development and testing on a workstation, before installing a stored procedure on MVS. This results in very little debugging activity on MVS.

Debug Tool and IBM VisualAge COBOL

If you have VisualAge COBOL installed on your workstation and the Debug Tool installed on your OS/390 system, you can use the VisualAge COBOL Edit/Compile/Debug component with the Debug Tool to debug a COBOL stored procedure that runs in a WLM-established stored procedures address space. After you write your COBOL stored procedure and set up the WLM environment, follow these steps to test the stored procedure with the Debug Tool:

When you compile the stored procedure, specify the TEST and SOURCE options. Ensure that the source listing is stored in a permanent dataset. VisualAge COBOL displays that source listing during the debug session.
When you define the stored procedure, include runtime option TEST with the suboption VADTCPIP& ipaddr in your RUN OPTIONS argument. VADTCPIP& tells the Debug Tool that it is interfacing with a workstation that runs VisualAge COBOL and is configured for TCP/IP communication with your OS/390 system. ipaddr is the IP address of the workstation on which you display your debug information. For example, the RUN OPTIONS value in this stored procedure definition indicates that debug information should go to the workstation with IP address 9.63.51.17:
```
 CREATE PROCEDURE WLMCOB  (IN INTEGER,INOUT VARCHAR(3000),INOUT INTEGER) MODIFIES SQL DATA LANGUAGE COBOL EXTERNAL PROGRAM TYPE MAIN WLM ENVIRONMENT WLMENV1 RUN OPTIONS 'POSIX(ON),TEST(,,,VADTCPIP&9.63.51.17:*)' 
```
In the JCL startup procedure for WLM-established stored procedures address space, add the dataset name of the Debug Tool load library to the STEPLIB concatenation. For example, suppose that ENV1PROC is the JCL procedure for application environment WLMENV1. The modified JCL for ENV1PROC might look like this:
```
 //DSNWLM PROC RGN=0K,APPLENV=WLMENV1,DB2SSN=DSN,NUMTCB=8  //IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT, //PARM='&DB2SSN,&NUMTCB,&APPLENV' //STEPLIB DD DISP=SHR,DSN=DSN710.RUNLIB.LOAD //DD DISP=SHR,DSN=CEE.SCEERUN //DD DISP=SHR,DSN=DSN710.SDSNLOAD //DD DISP=SHR,DSN=EQAW.SEQAMOD <==DEBUG TOOL 
```
On the workstation, start the VisualAge Remote Debugger daemon. This daemon waits for incoming requests from TCP/IP.
Call the stored procedure. When the stored procedure starts, a window that contains the debug session is displayed on the workstation. You can then execute Debug Tool commands to debug the stored procedure.

Debug Tool and C/C++ Productivity Tools for OS/390

If you have the C/C++ Productivity Tools for OS/390 installed on your workstation and the Debug Tool installed on your OS/390 system, you can debug an SQL procedure or a C or C++ stored procedure that runs in a WLM-established stored procedures address space. The code against which you run the debug tools is the C source program that is produced by the program preparation process for the stored procedure.

After you write your C++ stored procedure or SQL procedure and set up the WLM environment, follow these steps to test the stored procedure with the Distributed Debugger feature of the C/C++, Productivity Tools for OS/390, and the Debug Tool:

When you define the stored procedure, include runtime option TEST with the suboption VADTCPIP& ipaddr in your RUN OPTIONS argument. VADTCPIP& tells the Debug Tool that it is interfacing with a workstation that runs VisualAge C++ and is configured for TCP/IP communication with your OS/390 system. ipaddr is the IP address of the workstation on which you display your debug information. For example, this RUN OPTIONS value in a stored procedure definition indicates that debug information should go to the workstation with IP address 9.63.51.17:
```
 RUN OPTIONS 'POSIX(ON),TEST(,,,VADTCPIP&9.63.51.17:*)' 
```
Precompile the stored procedure. Ensure that the modified source program that is the output from the precompile step is in a permanent, catalogued dataset. For an SQL procedure, the modified C source program that is the output from the second precompile step must be in a permanent, catalogued dataset.
Compile the output from the precompile step. Specify the TEST, SOURCE, and OPT(0) compiler options.

In the JCL startup procedure for the stored procedures address space, add the dataset name of the Debug Tool load library to the STEPLIB concatenation. For example, suppose that ENV1PROC is the JCL procedure for application environment WLMENV1. The modified JCL for ENV1PROC might look like this:

 //DSNWLM PROC RGN=0K,APPLENV=WLMENV1,DB2SSN=DSN,NUMTCB=8  //IEFPROC EXEC PGM=DSNX9WLM,REGION=&RGN,TIME=NOLIMIT, //PARM='&DB2SSN,&NUMTCB,&APPLENV' //STEPLIB DD DISP=SHR,DSN=DSN710.RUNLIB.LOAD //DD DISP=SHR,DSN=CEE.SCEERUN //DD DISP=SHR,DSN=DSN710.SDSNLOAD //DD DISP=SHR,DSN=EQAW.SEQAMOD <==DEBUG TOOL

On the workstation, start the Distributed Debugger daemon. This daemon waits for incoming requests from TCP/IP.
Call the stored procedure. When the stored procedure starts, a window that contains the debug session is displayed on the workstation. You can then execute Debug Tool commands to debug the stored procedure.

Debugging with CODE/370

You can use the CoOperative Development Environment/370 licensed program, which works with Language Environment, to test multiple virtual system (MVS) stored procedures written in any of the supported languages. You can use CODE/370 either interactively or in batch mode.

Using CODE/370 Interactively

To test a stored procedure interactively using CODE/370, you must use the CODE/370 PWS Debug Tool on a workstation. You must also have CODE/370 installed on the MVS system where the stored procedure runs. To debug your stored procedure using the PWS Debug Tool, do the following:

Compile the stored procedure with option TEST. This places information in the program that the Debug Tool uses during a debugging session.
Invoke the Debug Tool. One way to do that is to specify the Language Environment runtime option TEST. The TEST option controls when and how the Debug Tool is invoked. The most convenient place to specify runtime options is in the RUN OPTIONS parameter of the CREATE PROCEDURE or ALTER PROCEDURE statement for the stored procedure. For example, if you code the option TEST(ALL,*,PROMPT,JBJONES%SESSNA:), the parameter values in the statement cause the following things to happen:

- ALL: The Debug Tool gains control when an attention interrupt, abend, or program or Language Environment condition of Severity 1 and above occurs. Debug commands will be entered from the terminal.

- PROMPT : The Debug Tool is invoked immediately after Language Environment initialization.

- JBJONES%SESSNA : CODE/370 initiates a session on a workstation identified to APPC/MVS as JBJONES with a session ID of SESSNA.

If you want to save the output from your debugging session, issue a command that names a log file. For example, the following command starts logging to a file on the workstation called dbgtool.log.

 SET LOG ON FILE dbgtool.log;

This should be the first command that you enter from the terminal or include in your commands file.

Using CODE/370 in Batch Mode

To test your stored procedure in batch mode, you must have the CODE/370 MFI Debug Tool installed on the MVS system where the stored procedure runs. To debug your stored procedure in batch mode using the MFI Debug Tool, do the following:

If you plan to use the Language Environment runtime option TEST to invoke CODE/370, compile the stored procedure with option TEST. This places information in the program that the Debug Tool uses during a debugging session.
Allocate a log dataset to receive the output from CODE/370. Put a DD statement for the log dataset in the startup procedure for the stored procedures address space.
Enter commands in a dataset that you want CODE/370 to execute. Put a DD statement for that dataset in the startup procedure for the stored procedures address space. To define the commands dataset to CODE/370, specify the commands dataset name or DD name in the TEST runtime option. For example, the following tells CODE/370 to look for the commands in the dataset associated with DD name TESTDD.
```
 TEST(ALL,TESTDD,PROMPT,*) 
```

The first command in the commands dataset should be

 SET LOG ON FILE  ddname  ;

That command directs output from your debugging session to the log dataset you defined in the previous step. For example, if you defined a log dataset with DD name INSPLOG in the stored procedures address space startup procedure, the first command should be

 SET LOG ON FILE INSPLOG;

Invoke the Debug Tool. Two possible methods are

- Specify the runtime option TEST. The most convenient place to do that is in the RUN OPTIONS parameter of the CREATE PROCEDURE or ALTER PROCEDURE statement for the stored procedure.

- Put CEETEST calls in the stored procedure source code. If you use this approach for an existing stored procedure, you must recompile, relink, and bind it, then issue the STOP PROCEDURE and START PROCEDURE commands to reload the stored procedure.

You can combine the runtime option TEST with CEETEST calls. For example, you might want to use TEST to name the commands dataset but use CEETEST calls to control when the Debug Tool takes control.

Using the MSGFILE Runtime Option

Language Environment supports the runtime option MSGFILE, which identifies a JCL DD statement used for writing debugging messages. You can use the MSGFILE option to direct debugging messages to a DASD file or job entry subsystem (JES) spool file. The following considerations apply:

For each MSGFILE argument, a DD statement must be added to the JCL procedure used to start the DB2 stored procedures address space.
Execute ALTER PROCEDURE with the RUN OPTIONS parameter to add the MSGFILE option to the list of runtime options for the stored procedure.
Because multiple task control blocks (TCBs) can be active in the DB2 stored procedures address space, you must serialize I/O to the dataset associated with the MSGFILE option. For example,

- To prevent multiple procedures from sharing a dataset, each stored procedure can specify a unique DD name with the MSGFILE option.

- If you debug your applications infrequently or on a DB2 test system, you can serialize I/O by temporarily running the DB2 stored procedures address space with NUMTCB=1 in the stored procedures address space startup procedure. (This normally can be done by the system administrator.)

Using Driver Applications

A small driver application can be written that calls the stored procedure as a subprogram and passes the parameter list supported by the stored procedure. You can then test and debug the stored procedure as a normal DB2 application under TSO. TSO TEST can now be used as well as other commonly used debugging tools.

Using SQL INSERTs

SQL inserts can be used to get debugging information into a DB2 table. This allows other machines in the network (such as a workstation) to easily access the data in the table using DRDA access. DB2 discards the debugging information if the application executes the ROLLBACK statement. To prevent the loss of the debugging data, code the calling application so that it retrieves the diagnostic data before executing the ROLLBACK statement.

Testing a Java Stored Procedure

To help you debug your Java stored procedures, include a JSPDEBUG DD statement in your WLM startup procedure. This DD statement specifies a dataset to which DB2 writes debug information as stored procedures execute. For more information on debugging and testing Java programs refer to Chapter 13, "Java Programming."


Team-Fly

Top