Application Development Guide

NOT FENCED Stored Procedures

Your stored procedure can run as either a FENCED or a NOT FENCED stored procedure, depending on whether you register the stored procedure as FENCED or NOT FENCED in the CREATE PROCEDURE statement.

A NOT FENCED stored procedure runs in the same address space as the database manager (the DB2 Agent's address space). Running your stored procedure as NOT FENCED results in increased performance when compared with running it as FENCED because FENCED stored procedures, by default, run in a special DB2 process. The address space of this process is distinct from the DB2 System Controller.

Notes:

  1. While you can expect performance improvements from running NOT FENCED stored procedures, user code can accidentally or maliciously damage the database control structures. You should only use NOT FENCED stored procedures when you need to maximize the performance benefits. Test all your stored procedures thoroughly prior to running them as NOT FENCED.

  2. If a severe error does occur while you are running a NOT FENCED stored procedure, the database manager determines whether the error occurred in the stored procedure code or the database code, and attempts an appropriate recovery.

For debugging purposes, consider using local FENCED stored procedures. A local FENCED procedure is a PARAMETER STYLE DB2DARI procedure. To call a local FENCED procedure, issue CALL <library-name>!<entry-point>, where library-name represents the name of the shared library, and entry-point represents the entry point of the shared library for the stored procedure. If the name of the shared library and the entry point are the same, you can issue CALL <entry-point>.

NOT FENCED and regular FENCED stored procedures complicate your debugging efforts by giving your debugger access to additional address space. Local FENCED stored procedures run in the application's address space and allow your debugger to access both the application code and the stored procedure code. To enable local FENCED stored procedures for debugging, perform the following steps:

  1. Register the stored procedure as a FENCED stored procedure.
  2. Set the DB2_STPROC_ALLOW_LOCAL_FENCED registry variable to true. For information on registry variables, refer to the Administration Guide: Implementation.
  3. Run the client application on the same machine as the DB2 server.
Note:While debugging a local FENCED stored procedure, be careful not to introduce statements that violate the restrictions listed in Restrictions. DB2 treats calls to local FENCED stored procedures as calls to a subroutine of the client application. Therefore, local FENCED stored procedures can contain statements that violate restrictions on normal stored procedures, like performing CONNECT statements in the procedure body.

When you write a NOT FENCED stored procedure, keep in mind that it may run in a threaded environment, depending on the operating system. Thus, the stored procedure must either be completely re-entrant, or manage its static variables so that access to these variables is serialized.
Note:You should not use static data in stored procedures, because DB2 cannot guarantee that the static data in a stored procedure is or is not reinitialized on subsequent invocations.

NOT FENCED stored procedures must be precompiled with the WCHARTYPE NOCONVERT option. See The WCHARTYPE Precompiler Option in C and C++ for more information.

DB2 does not support the use of any of the following features in NOT FENCED stored procedures:

The following DB2 APIs and any DB2 CLI API are not supported in a NOT FENCED stored procedure:

[ Top of Page | Previous Page | Next Page ]