Application Building Guide

Stored Procedures

Stored procedures are built and stored on the server. They can be remotely accessed by client applications. The stored procedure functions then perform processing locally on the server database, and send back the results to the client. This reduces network traffic and improves overall performance.

A stored procedure can be run as fenced or unfenced. An unfenced stored procedure runs in the same address space as the database manager and results in increased performance when compared to a fenced stored procedure, which runs in an address space isolated from the database manager. With unfenced stored procedures there is a danger that user code could damage the database control structures. Therefore, you should only run unfenced stored procedures when you need to maximize the performance benefits. Ensure these programs are thoroughly tested before running them as unfenced. Refer to the Application Development Guide for more information.

The stored procedures demonstrated in this book are stored on the server in the path sqllib/function. For DB2DARI parameter style stored procedures where the invoked procedure matches the shared library name, this location indicates that the stored procedure is fenced. If you want this type of stored procedure to be unfenced, you must move it to the sqllib/function/unfenced directory. For all other types of DB2 stored procedures, you indicate whether it is fenced or unfenced with the CREATE FUNCTION statement in the calling program. For a full discussion on creating and using the different types of DB2 stored procedures, please see the "Stored Procedures" chapter in the Application Development Guide.

The following sample program is used to demonstrate the steps for building and storing a stored procedure library on the server using SQL Procedures:

spserver.db2

is a CLP script containing SQL Procedures used to create a shared library on the server. It can be called by the CLP call command, or by the spclient application in the C, C++, and CLI directories.

The following sample program is used to demonstrate the steps for building and storing a stored procedure library on the server using C and C++:

spserver

is the server program of a client/server example; the client program is spclient.

The following sample program is used to demonstrate the steps for building and storing a stored procedure library on the server using Java:

Spserver

is the server program of a client/server example; the client program is Spclient.

The following sample program is used to demonstrate the steps for building and storing a stored procedure library on the server using COBOL:

outsrv

is the server program of a client/server example; the client program is outcli.