Advanced Database Feature Guide
An application can be designed to run in two parts, one on the client and
the other on the server. The stored procedure is the part that runs at
the database within the same transaction as the application. Stored
procedures can be written in either embedded SQL or using the DB2 CLI
functions (see "Writing a Stored Procedure in CLI"). In general, stored
procedures have the following advantages:
- Avoid network transfer of large amounts of data obtained as part of
intermediate results in a long sequence of queries.
- Deployment of client database applications into client/server
pieces.
In addition, stored procedures written in embedded static SQL have the
following advantages:
- Performance - static SQL is prepared at precompile time and has no run
time overhead of access plan (package) generation.
- Encapsulation (information hiding) - users' do not need to know the
details about the database objects in order to access them. Static SQL
can help enforce this encapsulation.
- Security - users' access privileges are encapsulated within the
package(s) associated with the stored procedure(s), so there is no need to
grant explicit access to each database object. For example, a user can
be granted run access for a stored procedure that selects data from tables for
which the user does not have select privilege.
Stored procedures are invoked using a protocol on a connection protocol or
via a procedure descriptor:
connection executeProcedure: <procedure name>
- or -
connection newProcedureDescriptor
procedureName: <procedure name>;
executeOn: connection.
It is also possible to pass parameter values to the procedure using the
following syntax:
connection
executeProcedure: <procedure name>
withParameterTypes: <collection: of parameter types>
withParameterValues: <collection of parameter values>
- or -
connection newProcedureDescriptor
procedureName: <procedure name>;
parameterTypes: <collection of parameter types>
withParameterValues: <collection of parameter types>.
withParameterValues: <collection of <collection of parmateter values>
A stored procedure name can take one of the following forms:
- procedure-name
- The name (with no extension) of the procedure to execute. The
procedure invoked is determined as follows.
- The procedure-name is used both as the name of the stored procedure
library and the function name within that library. For example, if
procedure-name is proclib, the DB2 server will load the stored procedure
library named proclib and execute the function routine proclib() within that
library.
- If the library or function could not be found, the procedure-name is used
to search the defined procedures (in SYSCAT.PROCEDURES) for a matching
procedure. A matching procedure is determined using the steps that
follow.
- Find the procedures from the catalog (SYSCAT.PROCEDURES) where the
PROCNAME matches the procedure-name specified and the PROCSCHEMA is a schema
name in the function path.
- Next, eliminate any of these procedures that do not have the same number
of parameters as the number of arguments specified in the CALL statement
- Chose the remaining procedure that is earliest in the function
path.
- If there are no remaining procedures after step 2, an error is returned
(SQLSTATE 42884).
Once the procedure is selected, DB2 will invoke the procedure defined by
the external name.
- procedure-name!func-name
- The use of the exclamation sign allows the specification of a library name
identified by procedure-name and the function to be executed is given by
func-name. This allows similar function routines to be placed in the
same stored procedure library.
- /u/db2user/procedure-name!func-name
- The name of the stored procedure library is specified as a full path
name. The function to be executed is given by func-name.
For more information regarding the calling of stored procedures, refer to
DB2’s SQL Reference and the Embedded SQL Programming Guide.
For information on registering stored procedures on the server, see
Appendix H. "Pseudo Catalog Table for Stored Procedure Registration" in
the DB2’s Call Level Interface Guide and Reference.
If the stored procedure resides on a DB2 for MVS/ESA V4.1 or later
server, the name of the stored procedure must be defined in the
SYSIBM.SYSPROCEDURES catalog table. The pseudo catalog table
used by DB2 Universal Database is a derivation and extension of the DB2 for
MVS/ESA SYSIBM.SYSPROCEDURES catalog table).
If the stored procedure resides on a DB2 for AS/400 V3.1 server, the
application must know the actual path and name of the stored procedure ahead
of time. There is no real or pseudo catalog table to retrieve
information on stored procedures or their argument list.
[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]