Space announcement This documentation space provides the same content as before, but the organization of the content has changed. The content is now organized based on logical branches instead of legacy book titles. We hope that the new structure will help you quickly find the content that you need.

Debugging with Db2 stored procedures for PL/I


Code Debug TSO, a testing and debugging product, supports the debugging of Db2 stored procedures using the Db2 Stored Procedure option. Operation of the product requires the stored procedure to reside on an OS/390 or z/OS system. The client can be either local (OS/390 or z/OS) or remote.

Code Debug TSO Select Job Step Screen

 PROFILE: DB2SP--------- --------- SELECT JOB STEP  ----------- Row 1 to 1 of 1
COMMAND ===> Edit                                             SCROLL ===> PAGE

Line Commands:                   Primary Commands:
  I - Interactive testing          Edit   - Display converted selected steps
  U - Unattended testing           END    - Exit without processing
 Followed optionally by:           RUN    - Submit and connect
  C - Code Coverage                SEtup  - Setup work datasets
  T - Code Coverage, plus TDO      SUBmit - Convert selected steps and submit
  F - System Flow (F, CF or TF)    STatus - Display status of submitted job(s)

Dataset: 'CPWR.XT.SLXTSAMP(TRIJCLST)'

      PROGRAM     INITSCR  POSTSCR    STEPNAME  PROCNAME  PROCSTEP  EXEC PGM
-----------------------------------  -----------------------------------------
___   DSNX9WLM    ________ ________             SERVER    IEFPROC   XPTSO
******************************* Bottom of data ********************************


Sample Edit JCL Screen 

EDIT ---- SYS00306.T150619.RA000.PFHABC0.R0105038 ------------ Columns 001 072
COMMAND ===>                                                  SCROLL ===> PAGE
****** ***************************** Top of Data ******************************
000001 //PFHABC0S JOB ('OXTBAS7.7DEV'),'JOHN JONES',
000002 //       CLASS=A,NOTIFY=PFHABC0,MSGCLASS=X,REGION=6144K
000003 /*JOBPARM S=*
000004 //SERVER  PROC DB2SSN=D610,
000005 //             NUMTCB=1,                  MUST BE 1 FOR CODE DEBUG TSO
000006 //             APPLENV=XPEDAENV,
000007 //             DB2Q=DSN610,               DB2 HIGH LEVEL QUALIFLIER
000008 //             LELIB='CEE.SCEERUN',         LE LIBRARY
000009 //             USERLIB='XTTEST.X77.IVP.LOADLIB' WHERE TRISPM RESIDES
000010 //*
000011 //*************************************************************
000012 //*    JCL FOR RUNNING THE WLM-ESTABLISHED STORED PROCEDURES
000013 //*    ADDRESS SPACE
000014 //*
000015 //*       DB2SSN  -- THE DB2 SUBSYSTEM NAME.
000016 //*                  END USER REQUESTS. MUST BE 1 FOR CODE DEBUG TSO.
000017 //*       APPLENV -- THE MVS WLM APPLICATION ENVIRONMENT
000018 //*                  SUPPORTED BY THIS JCL PROCEDURE. THIS BE THE
000019 //*                  WLM NAME USED DURING THE STORED PROCEDURE
000020 //*                  DEFINITION TO DB2.
NO STEP WAS SELECTED.  JCL IS UNCHANGED.


Debugging a client application and a Db2 stored procedure together

A specialized technique has been developed that allows for the debugging of a client application which calls a Db2 stored procedure, while using a single TSO userid. Detailed instructions are provided in Debugging a Client Application and Db2 Stored Procedure.

Important

This technique will only work if the client application is submitted as a batch program set up to run with Code Debug TSO Batch Connect.

Db2 stored procedure security considerations

Be advised that there are a few security considerations concerning Code Debug TSO Stored Procedure support.

Batch connect security check

Connection can be made to any job, including production jobs, if you permit testing in production Db2 subsystems, as long as your site security grants the authority. The Batch Connect facility is shipped with a default security exit routine that allows connection to a job if the JOBNAME, without the last character, matches the TSO ID of the user attempting to connect it. When a connection cannot be made, the messages CANNOT CONNECT or SECURITY CHECK FAILED are issued.

The site installer can customize the security exit routine to tailor the security level for certain groups or individuals. When an asterisk (*) is entered in the Jobname field on the Status screen, all jobs that are waiting for connection or being tested under Batch Connect are listed. System programmers are able to connect to a remote job and use the facility as a help desk feature in debugging application programs.

Db2 subsystems to debug

Before Code Debug TSO can be used to debug stored procedures, a Code Debug TSO Stored Procedure activation program must be run on the system (after every IPL). The Db2 subsystems Code Debug TSO will be allowed to intercept are specified to the activation program. See to the Code Debug TSO and Code Debug IMS Installation and Configuration Guide for details on the execution of the Db2 Stored Procedure activation program.

Optional Security Access Facility (SAF) calls

Optionally, Code Debug TSO will issue SAF (Security Access Facility) calls to your external security package (RACF, ACF2, Top Secret, etc.). This allows your installation to limit the number of individual users who are able to debug Db2 stored procedures being called from whichever client selected. See to the Code Debug TSO and Code Debug IMS Installation and Configuration Guide for details on how to add additional Db2 stored procedure security.

Security exit

If you have specific security issues not addressed by the aforementioned security procedures, you can optionally code a Code Debug TSO and Code Debug IMS security exit. See to the Code Debug TSO and Code Debug IMS Installation and Configuration Guide for details on how to add additional Db2 stored procedure security via the ADSRAUSR exit.

Foreground debugging of Db2 stored procedures

In addition to debugging stored procedures in Batch Connect mode as described above, Code Debug enables Db2 stored procedures to be debugged within the TSO address space. This foreground debugging capability eliminates the requirement to install Code Debug TSO Batch Connect.

Important

Before testing a stored procedure in foreground mode, make sure the Db2 SDSNLOAD library has been specified on the Load Module Libraries screen (Standard Setup menu option 1). For more information, see Load Module Libraries Screens.

The following example illustrates how to perform foreground debugging of a stored procedure. For the purposes of this example, assume that an existing test profile is being used and debugging of Db2 stored procedures has been selected on the Environments Menu. Only the stored procedure can be debugged during this session. The driver or client that calls the stored procedure must be debugged in a separate foreground or batch debugging session.

  1. From the Code Debug TSO Primary Menu, select option 2 (TSO) and press Enter. The DB2 Stored Procedure screen (2.12) shown in the following figure will be displayed.

    DB2 Stored Procedure Screen (2.12)

    PROFILE: DB2SP -------------- DB2 Stored Procedure (2.12)  ---------------
    COMMAND ===>

    COMMANDS:  SEtup (Display Setup Menu)
              PROFile (Display Profile List)

               Schema Name  ===> XT

     Stored Procedure Name
                    or UDF  ===> TRISPTG

         Client End UserID
        or PC Account Code
             or IP Address  ===> MFHABC0
        DB2 Subsystem Name  ===> D909  (D901,D909)
    Maximum Number of Tests  ===> 1                 (1 - 9999)

            Initial Script  ===>
               Post Script  ===>

       File List/JCL Member ===>
             Preview Files? ===> NO
        Code Coverage Test? ===> NO   (YES, NO, TDO)  System Flow? ===> NO
                Press ENTER to process  or  enter END command to terminate
  2. Fill in all required fields on the DB2 Stored Procedure screen, then press Enter to start the test. The Test screen shown in the following figure will be displayed while waiting for the stored procedure to be intercepted.

    Test Screen During Stored Procedure Testing

    Profile: STORE --------------------- TEST ----------------------Files Allocated
    COMMAND ===>



                 ************************************************
                 *                                              *
                 *    Entering CODE DEBUG TSO Test Environment  *
                 *                                              *
                 ************************************************
                 *          TYPE OF TEST: STORED PROCEDURES     *
                 *               PROFILE: STORE                 *
                 *      STORED PROCEDURE: XT.TRISTPG            *
                 *         DB2 SUBSYSTEM: D610                  *
                 *                USERID: MFHABC0               *
                 ************************************************

    Important

    At this point, you may choose to exit the test. See Exiting an In-Progress Stored Procedure Test for details.

  3. Initiate the client that invokes the stored procedure specified in previous step.
  4. After intercepting the stored procedure, Code Debug will display the source as shown in the following figure.

    Source Display for Stored Procedure Test

    ------------------------------ CODE DEBUG TSO - SOURCE -------------------------
    COMMAND ===>                                                     SCROLL===> CSR

                              BEFORE BREAKPOINT ENCOUNTERED
                                      ---
    000010   01 TST-REC              > 345
    000014   01 TYPE-OF-TRIANGLE     >  ??         INVALID DECIMAL
            ** END **

    ------   ----------------------------------------- Before TRISPTG:15/AMODE 31 <>
    ====>> 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

    Important

    If you need to prematurely end the test, type EXIT on the command line and press Enter. This will cause a user abend so Db2 can clean up its resources.

  5. Enter the GO command (at the GOBACK) for normal test completion. When the test has completed, the Code Debug session log will be displayed. At that point you must enter the GO command again to complete the Db2 stored procedure test.

Exiting an in-progress stored procedure test

While waiting for a stored procedure to be intercepted, you can press Attention to suspend the test. The message shown in the following figure will be displayed, giving you the option of continuing or exiting the test. This gives you a way of escaping the test, if necessary, while the wait for a stored procedure intercept is in progress.

Stored Procedure Intercept In Progress Message

  DB2 STORED PROCEDURE INTERCEPT IN PROGRESS
  INTERCEPT DATA: SUBSYS=D610 USERID=TSOUSER1          STORED PROCEDURE=TRISPTG
 ENTER EXIT TO TERMINATE OR ENTER TO CONTINUE


Responding to the message with EXIT forces Code Debug to clean up the current environment and terminate the test. A few error messages similar to those shown in the following figure will be displayed as a result.

Error Messages from Stored Procedure Test Exit

  DB2 STORED PROCEDURE INTERCEPT IN PROGRESS
  INTERCEPT DATA: SUBSYS=D610 USERID=TSOUSER1          STORED PROCEDURE=TRISPTG
 ENTER EXIT TO TERMINATE OR ENTER TO CONTINUE
EXIT
BPXP009I THREAD 14E5740000000001, IN PROCESS 83886228, ENDED
ABNORMALLY WITH COMPLETION CODE 0423E000, REASON CODE 00000000.
Abend 23E000 hex occurred processing command 'XPCP    '.
***




 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*