Debugging with Db2 stored procedures for PL/I

Code Debug TSO Select Job Step Screen

Sample Edit JCL Screen 

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.

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.

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

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