Defining Stored Procedures under SQL Option

Restriction: This topic applies to Windows environments only.

A stored procedure is unusable until it is defined - use the CREATE PROCEDURE command to do this. At the command's conclusion, a record is placed in the SYSIBM.SYSROUTINES This record is used by the XDB Server when the stored procedure is executed.

The following is an example of a CREATE PROCEDURE command:

  create procedure MYAUTHID.STPROC1 (
             in    in1    char(4),
             out  out1  char(4),
             out  out2   smallint 
   ) 
   external name STPROC1
   parameter style general with nulls
   language cobol
   collid  MYCOLLID
   result sets 1
   commit on return no
   ;

The create procedure statement must uniquely identify a stored procedure. You cannot have more than one row in SYSIBM.SYSROUTINES with the same values for the PROCEDURE, AUTHID, and LUNAME columns because the combination of PROCEDURE, AUTHID, and LUNAME uniquely identifies a stored procedure. You also will provide specific application information, such as what language the stored procedure is written in, whether you are using result-sets, what parameters are passed, and how they are passed.