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:

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:

  1. 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.
  2. 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';
  3. 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.
  4. When you define your stored procedure, verify the following items:
  5. Compile or assemble your program, as described in Preparing your program for debugging. For Enterprise PL/I programs, also specify the RENT compiler option.
  6. Review the following list to determine how to specify the TEST runtime options:
  7. 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';
  8. To specify the TEST runtime options through the DB2 catalog, do the following steps:
    1. 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.
    2. 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:*)';
    3. 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;