Stored Procedures Best Practices: Fenced or Unfenced?

Question

I believe I understand the reasons behind fenced and unfenced stored procedures.

Fenced run "outside" of the database (in our case DB2) so as to prevent possible corruption of the database engine should there be issues with things like pointers.

Unfenced runs "inside" of the database, which means that performance is better.

From what I have also researched, SQL PL is always basically unfenced, because it is SQL and therefore cannot access memory like programming languages can.

C/C++ and Java procedures can run fenced or unfenced. But since they can possibly access memory, there should be a consideration for running them fenced, unless there is a certainty on the quality of the code to not crash and it needs performance.

First of all, am I correct in my understand of the above?

Next, is it generally a best practice to start out with all stored procedures (even those defined as SQL PL) as fenced first?

Any other best practices for stored procedures, especially as related to fencing and/or security?

EDIT: Further research has shown that SQL PL procedures cannot run fenced. Since they do not contain any code that could harm the database engine such as pointers or file I/O, DB2 knows they are safe and runs them inside the engine (ie, unfenced). That being said, I am still looking for best practices regarding all other stored procedures.

Answer 1

To be more precise, NOT FENCED routines run in the same process space as the database manager itself. The engine is written in C, so calling a not fenced routine is just like calling another C function from main(). This is where all the memory corruption and performance aspects come from: a not fenced routine has access to all the same resources -- memory, files, etc. -- as the database manager process itself.

For a FENCED routine the database manager launches a separate process (db2fmp), which in turn executes the routine code. As a result the operating system protection prevents a fenced routine to access any memory area or resource that belongs to the database manager.

SQL routines cannot be fenced, strictly speaking, because they don't "run", but they are even better than not fenced -- they are bytecode that the DB2 runtime engine itself executes, so there's no separate thread or process.

C and C++ routines can be fenced, in which case they execute in separate processes, or not fenced, in which case they are loaded into the database manager process space and called as functions.

Java routines can only be fenced by the fact that they need a separate process, Java virtual machine, to execute. If you declare them as NOT FENCED, the option is quietly ignored.

Having said all that, your only choice between fenced and not fenced is with C/C++ routines. Typically you would run the fenced for safety, changing to the not fenced mode only when you are pretty sure they can't harm the database manager and they need higher performance¹.

---

1 - Performance difference between fenced and not fenced routines comes from the overhead associated with allocating a fenced process, as well as with interprocess communications between the database manager and the not fenced routine. Even then, the fenced process is not created every time a fenced routine is called; a pool of them will be created and reused for such calls. All this means that you might see the benefit of declaring a routine as fenced only if that routine is called very frequently (as in, tens of times per second or more).

Answer 2

As far as I can tell you are mostly right. The key is that PL SQL functions can't do memory allocation and so there really is no way to treat them specially regarding memory management. In essence PL/SQL is pretty closely tied to the db engine, but the memory management is the db engine's responsibility, not the stored procs.

In general my recommendations would be for extensively tested and/or reviewed code with a very good track record intended for use in high performance systems, unfenced makes the most sense. However databases are important, and something like a buffer overrun could potentially cause data corruption that might go undetected for some time. My recommendation would be to run all C/C++/Java stored procs fenced unless you are sure you need otherwise.

The same of course goes for UDF's.