IBM Books

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. 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, Performance.

  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 or COMMIT statements in the procedure body.

When you are writing 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.

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

The following NOT FENCED stored procedures are not supported by DB2:

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 | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]