Preparing a DB2 stored
procedures program
This topic describes the information you need to collect and the
steps you must take to prepare a DB2® stored
procedure for debugging with Debug Tool. Debug Tool can debug stored procedures
where PROGRAM TYPE is MAIN or SUB; the preparation steps are the same.
Before you begin, verify that you
can use the supported debugging modes. Debug Tool can debug stored procedures
written in assembler, C, C++, COBOL and Enterprise PL/I in any of the following
debugging modes:
- remote debug
- full-screen mode using a dedicated terminal
- batch
Review the topic "Creating a stored procedure" in
the DB2 Application Programming and SQL Guide to verify that your stored procedure complies
with the format and restrictions for external stored procedures. Debug Tool supports
debugging only external stored procedures.
To prepare a DB2 stored procedure,
do the following steps:
- Verify that your DB2 system administrator has completed
the tasks described in section "Preparing
your environment to debug a DB2 stored
procedures" of Debug Tool Customization Guide. The DB2 system administrator must define the address
space where the stored procedure runs, give DB2 programs the appropriate RACF® read authorizations, and recycle the address
space so that the updates take effect.
- If you are not familiar with the parameters used to
create the DB2 stored procedure
you want to debug, you can enter the SELECT statement, as illustrated
in the following example, to obtain this information:
SELECT PROGRAM_TYPE,STAYRESIDENT,RUNOPTS,LANGUAGE
FROM SYSIBM.SYSROUTINES
WHERE NAME='name_of_DB2_stored_procedure';
- For stored
procedures of program type SUB that are not invoked
by the call_sub function, verify that when your system
programmer or DB2 system administrator
defines the WLM address space, the value for NUMTCB is set to 1. NUMTCB
specifies the maximum number of Task Control Blocks (TCBs) that can
run concurrently in a WLM address space. If the stored procedure might
run in a TCB other than the one it was started in, you will not able
to debug that stored procedure. Setting the value of NUMTCB to 1 ensures
that the stored procedure is not run in a different TCB.
- When you define
your stored procedure, verify the following items:
- Specify the correct value for the LANGUAGE parameter and the PROGRAM
TYPE parameter. For C, C++, COBOL or Enterprise PL/I, the PROGRAM TYPE can
be either MAIN or SUB. For assembler, the PROGRAM TYPE must be MAIN.
- For stored procedures of program type SUB that are not invoked by the call_sub function,
determine if other users might run the stored procedure while you
are debugging it. If other users might run the stored procedure, you
can not debug it.
- For stored procedures of program type SUB that are invoked by the call_sub function,
review the following options:
- If you plan to specify the TEST runtime options through
the Language Environment EQADDCXT exit routine, specify STAY RESIDENT NO.
- If you plan to specify the TEST runtime options through
the DB2 catalog, you can specify
either YES or NO for STAY RESIDENT.
- Compile or assemble your program, as described in Preparing your program for debugging. For Enterprise PL/I programs, also specify the RENT compiler
option.
- Review the following list to determine how to specify
the TEST runtime options:
- For stored procedures of program type MAIN, you can specify the TEST runtime
option either through the Language Environment EQADDCXT exit routine or through
the DB2 catalog. If you use
both methods, the Language Environment EQADDCXT exit routine take precedence
over the DB2 catalog.
- For stored procedures of program type SUB that are invoked by the call_sub function,
you can specify the TEST runtime option either through
the Language Environment EQADDCXT exit routine or through the DB2 catalog. If you choose to use the Language Environment EQADDCXT
exit routine, you must specify the NOTEST runtime option
for the RUN OPTIONS parameter when you define the stored
procedure.
- For stored procedures of program type SUB that are not invoked by the call_sub function,
you can specify the TEST runtime option through the DB2 catalog or from within a program
by coding a call to CEETEST, __ctest(), or PLITEST.
- To specify
the TEST runtime options through the Language Environment EQADDCXT
exit routine, prepare a copy of the EQADDCXT user exit as described
in Specifying the TEST runtime options through the Language Environment user
exit.
Remember that if you want to debug an existing stored procedure of program type
SUB that is invoked by the call_sub function, you must
modify the stored procedure so that it uses the NOTEST runtime
option for the RUN OPTIONS parameter. The
following example shows how to use the ALTER PROCEDURE statement to
make this modification:
ALTER PROCEDURE name_of_DB2_stored_procedure RUN OPTIONS 'NOTEST';
- To specify the TEST runtime options through
the DB2 catalog, do the following
steps:
- If you have not created the stored procedure, write the stored
procedure using the CREATE PROCEDURE statement. You can use the following
example as a guide:
CREATE PROCEDURE SPROC1
LANGUAGE COBOL
EXTERNAL NAME SPROC1
PARAMETER STYLE GENERAL
WLM ENVIRONMENT WLMENV1
RUN OPTIONS 'TEST(,,,TCPIP&9.112.27.99%8001:*)'
PROGRAM TYPE SUB;
This example creates a stored procedure
for a COBOL program called SPROC1, the program type is SUB, it runs
in a WLM address space called WLMENV1, and it is debugged in remote debug mode.
-
If you need to modify an
existing stored procedure, use the ALTER PROCEDURE statement. You
can use the following example as a guide:
The IP address for the
remote debugger changed from 9.112.27.99 to 9.112.27.21. To modify
the stored procedure, enter the following statement:
ALTER PROCEDURE name_of_DB2_stored_procedure
RUN OPTIONS 'TEST(,,,TCPIP&9.112.27.21%8001:*)';
- Verify that the stored procedure is defined correctly
by entering the SELECT statement. For example, you can enter the following
SELECT statement:
SELECT * FROM SYSIBM.SYSROUTINES;