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

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.