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 z/OS® Debugger. z/OS Debugger 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. z/OS Debugger 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 the Terminal Interface Manager
  • 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. z/OS Debugger 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 IBM® z/OS Debugger 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. 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, review the following options:
      • If you plan to specify the TEST runtime options through the Language Environment® EQAD3CXT 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.
  4. Compile or assemble your program, as described in Preparing your program for debugging. For Enterprise PL/I programs, also specify the RENT compiler option.
  5. 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 EQAD3CXT exit routine, or through the Db2 catalog. If you use both methods, the Language Environment EQAD3CXT exit routine take precedence over the Db2 catalog.
    • For stored procedures of program type SUB, you can specify the TEST runtime option either through the Language Environment EQAD3CXT exit routine or through the Db2 catalog. If you choose to use the Language Environment EQAD3CXT exit routine, you must specify the NOTEST runtime option for the RUN OPTIONS parameter when you define the stored procedure.
  6. To specify the TEST runtime options through the Language Environment EQAD3CXT exit routine, prepare a copy of the EQAD3CXT 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, 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';
  7. 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;