Application Development 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.

  OS/390

  The schema name must be no more than 8 bytes long, the procedure name must be no more than 18 bytes long, and the stored procedure must be defined in the SYSIBM.SYSPROCEDURES catalog on the server.

  VSE or VM

  The procedure name must not be more than 18 bytes long and must be defined in the SYSTEM.SYSROUTINES catalog on the server.

  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, refer to the SQL Reference.

  You can invoke the server program on DB2 Universal Database with the same parameter convention that server programs use on DB2 Universal Database for OS/390, DB2 Universal Database for AS/400, or DB2 for VSE & VM. For more information on invoking DB2 Universal Database stored procedures, see Stored Procedures. For more information on the parameter convention on other platforms, 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 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 Universal Database for OS/390 or DB2 Universal Database for AS/400 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 for VSE & VM Version 7 or higher and DB2 Universal Database for OS/390 Version 5.1 or higher are the only host or AS/400 Application Servers that can return the result sets of stored procedures at this time.

Stored Procedure Builder

DB2 Stored Procedure Builder provides an easy-to-use development environment for creating, installing, and testing stored procedures. It allows you to focus on creating your stored procedure logic rather than the details of registering, building, and installing stored procedures on a DB2 server. Additionally, with Stored Procedure Builder, you can develop stored procedures on one operating system and build them on other server operating systems.

Stored Procedure Builder is a graphical application that supports rapid development. Using Stored Procedure Builder, you can perform the following tasks:

• Create new stored procedures.
• Build stored procedures on local and remote DB2 servers.
• Modify and rebuild existing stored procedures.
• Test and debug the execution of installed stored procedures.

You can launch Stored Procedure Builder as a separate application from the DB2 Universal Database program group, or you can launch Stored Procedure Builder from any of the following development applications:

• Microsoft Visual Studio
• Microsoft Visual Basic
• IBM VisualAge for Java

You can also launch Stored Procedure Builder from Control Center for DB2 for OS/390. You can start Stored Procedure Builder as a separate process from the Control Center Tools menu, toolbar, or Stored Procedures folder. In addition, from the Stored Procedure Builder Project window, you can export one or more selected SQL stored procedures built to a DB2 for OS/390 server to a specified file capable of running within the Command Line Processor (CLP).

Stored Procedure Builder manages your work by using projects. Each Stored Procedure Builder project saves your connections to specific databases, such as DB2 for OS/390 servers. In addition, you can create filters to display subsets of the stored procedures on each database. When opening a new or existing Stored Procedure Builder project, you can filter stored procedures so that you view stored procedures based on their name, schema, language, or collection ID (for OS/390 only).

Connection information is saved in a Stored Procedure Builder project; therefore, when you open an existing project, you are automatically prompted to enter your user id and password for the database. Using the Inserting SQL Stored Procedure wizard, you can build SQL stored procedures on a DB2 for OS/390 server. For a SQL stored procedure built to a DB2 for OS/390 server, you can set specific compile, pre-link, link, bind, runtime, WLM environment, and external security options.

Additionally, you can obtain SQL costing information about the SQL stored procedure, including information about CPU time and other DB2 costing information for the thread on which the SQL stored procedure is running. In particular, you can obtain costing information about latch/lock contention wait time, the number of getpages, the number of read I/Os, and the number of write I/Os.

To obtain costing information, Stored Procedure Builder connects to a DB2 for OS/390 server, executes the SQL statement, and calls a stored procedure (DSNWSPM) to find out how much CPU time the SQL stored procedure used.