Your stored procedure can run as a not-fenced or fenced stored procedure. A not-fenced stored procedure runs in the same address space as the database manager (the DB2 Agent's address space). A fenced stored procedure called from the server, runs in an address space (the application's address space) that is isolated from the database manager's address space. A fenced stored procedure called from a remote machine runs in a special DB2 process whose address space is distinct from the DB2 System Controller. Running your stored procedure as not-fenced results in increased performance when compared with running it as fenced.
This performance increase is realized regardless of whether the client application runs locally or remotely from the server machine where you are running your stored procedure application. Note that while performance improvements can be expected when running a not-fenced stored procedure, there is the possibility that user code could accidentally or maliciously damage the database control structures. In addition, local fenced stored procedures are easier for you to debug as the stored procedures run in the application's address space. Thus, when debugging your application, the debugger will have access to the stored procedure code. With not-fenced stored procedures, the debugger will also have access to the database manager's address space, thus complicating your debugging activity.
| Note: | Due to the associated risk of damaging your database, you should only use not-fenced stored procedures when you need to maximize the performance benefits. In addition, ensure that all your stored procedures are thoroughly tested prior to running them as not-fenced. 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. |
Keep in mind when you are writing a not-fenced stored procedure, 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.
You can indicate that a stored procedure is fenced or not-fenced by placing it in a special directory. See the DB2 SDK Building Applications book for your operating system for information on where to place your not-fenced stored procedure.
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: