Creating external stored procedures

An external stored procedure is a procedure that is written in a host language and can contain SQL statements. The source code for external procedures is separate from the definition.

To create an external stored procedure:

1. Write the external stored procedure body in assembler, C, C++, COBOL, REXX, or PL/I.
   Ensure that the procedure body that you write follows the guidelines for external stored procedures that are described in the following information:

   • Accessing other sites in an external procedure
   • Accessing non-Db2 resources in your stored procedure
   • Writing an external procedure to access IMS databases
   • Writing an external procedure to return result sets to a distributed client
   • Restrictions when calling other programs from an external stored procedure
   • External stored procedures as main programs and subprograms
   • Data types in stored procedures
   • COMMIT and ROLLBACK statements in a stored procedure

   Restrictions:

   • Do not include explicit attachment facility calls. External stored procedures that run in a WLM-established address space use Resource Recovery Services attachment facility (RRSAF) calls implicitly. If an external stored procedure makes an explicit attachment facility call, Db2 rejects the call.
   • Do not include SRRCMIT or SRRBACK service calls. If an external stored procedure invokes either SRRCMIT or SRRBACK, Db2 puts the transaction in a state where a rollback operation is required and the CALL statement fails.

   For REXX procedures, continue with step 3.

2. For assembler, C, C++, COBOL, or PL/I stored procedures, prepare the external procedure by completing the following tasks:
   1. Precompile, compile, and link-edit the application by using one of the following techniques:
      • The Db2 precompiler and JCL instructions to compile and link-edit the program
      • The SQL statement coprocessor

      Recommendation: Compile and link-edit code as reentrant.

      Link-edit the application by using DSNRLI, the language interface module for the Resource Recovery Services attachment facility, or DSNULI, the Universal language interface module. You must specify the parameter AMODE(31) when you link-edit the application with either of these modules. (24-bit applications are not supported.)

      If you want to make the stored procedure reentrant, see Creating an external stored procedure as reentrant

      If you want to run your procedure as a z/OS®-authorized program, you must also perform the following tasks when you link-edit the application:

      • Indicate that the load module can use restricted system services by specifying the parameter value AC=1.
      • Put the load module for the stored procedure in an APF-authorized library.

      You can compile COBOL stored procedures with either the DYNAM or NODYNAM COBOL compiler options. If you use DYNAM, ensure that the correct Db2 language interface module is loaded dynamically by performing one of the following actions:

      • Specify the ATTACH(RRSAF) SQL processing option.
      • Copy the DSNRLI module into a load library that is concatenated in front of the Db2 libraries. Use the member name DSNHLI.
   2. Bind the DBRM into a Db2 package by issuing the BIND PACKAGE command.

      If you want to control access to a stored procedure package, specify the ENABLE bind option with the system connection type of the calling application.

      Stored procedures require only a package. You do not need to bind a plan for the stored procedure or bind the stored procedure package to the plan for the calling application. For remote access scenarios, you need a package at both the requester and server sites.

      For more information about stored procedure packages, see Packages for external stored procedures.

      The following example BIND PACKAGE command binds the DBRM EMPDTL1P to the collection DEVL7083.

      BIND PACKAGE(DEVL7083) -
      MEMBER(EMPDTL1P) ACT(REP) ISO(UR) ENCODING(EBCDIC) -
      OWNER(DEVL7083) LIBRARY('SG247083.DEVL.DBRM')

3. Define the stored procedure to Db2 by issuing the CREATE PROCEDURE statement with the EXTERNAL option. Use the EXTERNAL NAME clause to specify the name of the load module for the program that runs when this procedure is called.

   If you want to run your procedure as a z/OS-authorized program, specify an appropriate environment with the WLM ENVIRONMENT option. The stored procedure must run in an address space with a startup procedure in which all libraries in the STEPLIB concatenation are APF-authorized.

   If you want environment information to be passed to the stored procedure when it is invoked, specify the DBINFO and PARAMETER STYLE SQL options in the CREATE PROCEDURE statement. When the procedure is invoked, Db2 passes the DBINFO structure, which contains environment information, to the stored procedure. For more information about PARAMETER STYLE, see Defining the linkage convention for an external stored procedure.

   If you compiled the stored procedure as reentrant, specify the STAY RESIDENT YES option in the CREATE PROCEDURE statement. This option makes the procedure remain resident in storage.

4. Authorize the appropriate users to use the stored procedure by issuing the GRANT EXECUTE statement.
   For example, the following statement allows an application that runs under the authorization ID JONES to call stored procedure SPSCHEMA.STORPRCA:

   GRANT EXECUTE ON PROCEDURE SPSCHEMA.STORPRCA TO JONES;

CREATE PROCEDURE B(IN V1 INTEGER, OUT V2 CHAR(9))
  LANGUAGE C
  DETERMINISTIC
  NO SQL
  EXTERNAL NAME SUMMOD
  COLLID SUMCOLL
  ASUTIME LIMIT 900
  PARAMETER STYLE GENERAL WITH NULLS
  STAY RESIDENT NO
  RUN OPTIONS 'MSGFILE(OUTFILE),RPTSTG(ON),RPTOPTS(ON)'
  WLM ENVIRONMENT PAYROLL
  PROGRAM TYPE MAIN
  SECURITY DB2
  DYNAMIC RESULT SETS 10
  COMMIT ON RETURN NO;