Embedded SQL Programming Guide

Stored Procedures

• Invocation

  A client program can invoke a server program by issuing an SQL CALL statement. Each server works a little differently to the other servers in this case.

  MVS or OS/390

  The procedure name must not be more than 8 bytes long and must be defined in the SYSIBM.SYSPROCEDURES catalog on the server.

  VSE or VM

  Stored procedures are not supported.

  OS/400

  The procedure name must be an SQL identifier. You can also use the DECLARE PROCEDURE or CREATE PROCEDURE statements to specify the actual path name (the schema-name or collection-name) to locate the stored procedure.

  All CALL statements to DB2 for AS/400 from REXX/SQL must be dynamically prepared and executed by the application as the CALL statement implemented in REXX/SQL maps to CALL USING DESCRIPTOR.

  For the syntax of the SQL CALL statement, see the SQL Reference.

  The server program on DB2 Universal Database is invoked with a different parameter convention than a server program on DB2 for MVS/ESA, DB2 for OS/390, or DB2 for AS/400. For more information on the parameter convention for a specific platform, refer to the DB2 product documentation for that platform.

  All the SQL statements in a stored procedure are executed as part of the SQL unit of work started by the client SQL program.

• Do not pass indicator values with special meaning to or from stored procedures.

  Between DB2 Universal Database, the systems pass whatever you put into the indicator variables. However, when using the DRDA protocol (such as through DB2 Connect), you can only pass 0, -1, and -128 in the indicator variables.

• You should define a parameter to return any errors or warning encountered by the server application.

  A server program on DB2 Universal Database can update the SQLCA to return any error or warning, but a stored procedure on DB2 for OS/390, DB2 for AS/400, or DB2 for MVS/ESA has no such support. If you want to return an error code from your stored procedure, you must pass it as a parameter. The SQLCODE and SQLCA is only set by the server for system detected errors.

[ DB2 List of Books | Search the DB2 Books ]