Stored Procedures Best Practices: Fenced or Unfenced?
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 performance1.
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).
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.