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
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
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.
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.
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 terminateFill 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 *
************************************************- Initiate the client that invokes the stored procedure specified in previous step.
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- 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
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
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 '.
***