Debugging a Db2 stored procedure

From the Code Debug TSO primary menu, select OPTION 4 (STORED PROC) to debug a Db2 stored procedure. Press Enter and the Code Debug TSO Process DB2 Stored Procedures screen shown in the following figure is displayed.

Code Debug TSO Process DB2 Stored Procedures Screen

PROFILE: DB2SP ------------ Process DB2 Stored Procedures --------------
COMMAND ===>

Primary Commands: SEtup (display setup menu)
            Schema Name ===> XT

  Stored Procedure Name
                 or UDF ===> TRISPTG

  Client End UserID or PC Account Code
          or IP Address ===> ACMJET0
     DB2 Subsystem Name ===> D909 (D901,D909)
Maximum Number of Tests ===> 3                 (1 - 9999)
  User Supplied WLM JCL ===> Y                 (Y or N)
      Cross-LPAR Submit ===> N                 (Y or N)

Jobcard Information:
  ===> //ACMJET0   JOB (#ACCOUNT),CLASS=A,MSGCLASS=X,
  ===> // 'XPEDITER',
  ===> // CLASS=A,MSGCLASS=X
  ===> //*
  ===> /*JOBPARM S=*

            Press ENTER to process or enter END command to terminate

After the required information is entered, the JCL needed to execute the stored procedure must be submitted.

The SEtup command displays the Setup Menu screen (Code DEbug TSO Setup Menu Screen) from which you can select screens for specifying load libraries, DDIO data sets, and so on.

The fields on the Process DB2 Stored Procedures test screen are:

Schema Name

Used to supply the schema name associated with the stored procedure. Code Debug will use this name, along with the stored procedure name, to resolve the Workload Manager (WLM) application environment and the load module name. Your site’s Db2 administrator or WLM System Support specialist should be able to provide the schema and stored procedure names that have been defined to WLM and Db2. Beginning with Db2 8.1, the schema name can be up to 128 bytes long.

Stored Procedure Name or UDF

Used to identify the OS/390 Db2 stored procedure to be debugged using Code Debug TSO. Beginning with Db2 8.1, the stored procedure name can be up to 128 bytes long.

Client End UserID or PC Account Code or IP Address

Enter whichever of the following is appropriate for your test:

- The ID of the user executing the client program that calls the stored procedure. If the client is a batch job, specify the user ID assigned to the job. Code Debug will not trap the stored procedure if the client calling it is associated with a different user ID.
- The TCP/IP address where a network call of the stored procedure is done. An example would be a Db2 UDB on the PC.
- The Db2 accounting code of the PC used to execute the client program. If Db2 UDB is installed and appropriately configured on the PC, the accounting code can be set at the DOS prompt by entering DB2SET DB2ACCOUNT=xxxxxx where xxxxxx is the desired code. This will cause the account code to be passed to the mainframe when calling the stored procedure.
- An asterisk (*). This will match on anything.

This field is required.

DB2 Subsystem Name

The name of the Db2 Subsystem which this stored procedure is running under. This field is required.

Maximum Number of Tests

This field indicates how many times the stored procedure will be intercepted by Code Debug TSO. If a stored procedure is called multiple times and this field is set to 1, only the first iteration will be trapped by Code Debug TSO. Subsequent calls will run outside of Code Debug TSO.

User Supplied WLM JCL

Enter either Y (Yes) or N (No). N indicates that Code Debug TSO will retrieve the Work Load Manager (WLM) JCL from Db2 to run the stored procedure. Y indicates that you will provide the WLM JCL to run the stored procedure. A subsequent panel will be displayed to enter the data set name of the JCL.

Cross-LPAR Submit

Enter either Y (Yes) or N (No). Y indicates that an Code Debug Stored Procedure Intercept will be initiated on each LPAR previously designated for Cross-LPAR Support by the product installer. When the client application invokes the Db2 stored procedure and it is intercepted on one of the designated LPARs, a STATUS message is displayed and the Intercept jobs on the other LPARs are terminated. N indicates that the Stored Procedure Intercept is to be run on the local LPAR only.

Jobcard Information

Enter the jobcard information to be used for the stored procedure test that is executed. If N was entered in the User Supplied WLM JCL field, the JOBCARD will be used for the retrieved stored procedure JCL when the test is submitted.

Accessing the Code Debug TSO Setup Menu Screen

If you type SEtup on the command line of the Process DB2 Stored Procedures screen and press Enter, the Code Debug TSO Setup Menu screen shown in the following figure is displayed.

Code Debug TSO Setup Menu Screen

Profile: DEFAULT ------------------ SETUP MENU --------------------------
OPTION ===>

        0 ENVIRONMENT - Execution environments menu
        1 LOADLIBS     - Load module libraries
        2 DDIO         - DDIO files
        3 INCLUDES     - Test script libraries
        4 LOG          - Session log dataset disposition
        5 SCRIPT       - Test script dataset disposition
        6 DSNLOAD      - DB2 system names and DSNLOAD libraries
        7 PANEXEC      - PANEXEC load libraries

        C CODE COVERAGE- Code Coverage setup options
        D DOCUMENT     - Document dataset disposition
        E EXTENDED     - Extended Setup Menu
        A ALL          - Display all of the above in succession (except 0)

          Press ENTER to process or enter END command to terminate

To access the screens containing Load Libraries, DDIO data set, etc., type an A (All) on the command line of the Code Debug TSO Setup Menu Screen and press Enter.

Each of the successive screens (Test Script Libraries, Test Session Log Data set, DSNLOAD data sets, etc.) are accessible (in consecutive order) by continuing to press Enter.