It is important to debug your UDF in an environment where you cannot harm the database. You should do your testing on a test database instance until you are absolutely sure your UDF is correct. This is especially true if you plan to define your UDF as NOT FENCED, as a problematic NOT FENCED UDF can bring down an entire instance and this affects all the users. This risk is minimized by using a test instance for debugging. This is because DB2 may not fail gracefully in the case of certain actions by NOT FENCED UDFs, for example, an erroneous modification of storage.
DB2 does check for certain types of limited actions that erroneously modify storage (for example, if the UDF moves a few too many characters to a scratchpad or to the result buffer). In that case, DB2 returns an error, SQLCODE -450 (SQLSTATE 39501), if it detects such a malfunction. DB2 is also designed to fail gracefully in the event of an abnormal termination of a UDF with SQLCODE -430 (SQLSTATE 38503), or a user interrupt of the UDF's processing with SQLCODE -431 (SQLSTATE 38504).
There is no known scenario where a FENCED UDF can harm an instance. That is why FENCED is the default, and why DB2 demands a special authority to define a UDF as NOT FENCED.
For security and database integrity reasons, it is important to protect the body of your UDF, once it is debugged and defined to DB2. This is particularly true if your UDF is defined as NOT FENCED. If either by accident or with malicious intent, anyone (including yourself) overwrites an operational UDF with code that is not debugged, the UDF could conceivably destroy the database if it is NOT FENCED, or compromise security.
Unfortunately, there is no easy way to run a source-level debugger on a UDF. There are several reasons for this:
Note that valuable debugging tools such as printf() do not normally work as debugging aids for your UDF, because the UDF normally runs in a background process where stdout has no meaning. As an alternative to using printf(), it may be possible for you to instrument your UDF with file output logic, and for debugging purposes write indicative data and control information to a file.
Another technique to debug your UDF is to write a driver program for invoking the UDF outside the database environment. With this technique, you can invoke the UDF with all kinds of marginal or erroneous input arguments to attempt to provoke it into misbehaving. In this environment, it is not a problem to use printf() or a source level debugger.