Application Development Guide

Other Coding Considerations

This section documents additional considerations for implementing a UDF, items to keep in mind, and items to avoid.

Hints and Tips

The following are recommendations to consider to successfully implement your UDF:

• UDF bodies need to be protected. The executable function bodies are not captured or protected in any way by DB2. The CREATE FUNCTION statement merely points to the body. To preserve the integrity of the function and the database applications which depend on the function, you must, by managing access to the directory containing the function and by protecting the body itself, prevent the function body from being inadvertently or intentionally deleted or replaced.
• DB2 passes pointers to all of the buffers in the interface between DB2 and SQL (that is, all the SQL arguments and the function return value). Be sure you define your UDF arguments as pointers.
• All SQL argument values are buffered. This means that a copy of the value is made and presented to the UDF. If a UDF changes its input parameters, the changes have no effect on SQL values or processing, but may cause DB2 to malfunction.
• For OLE automation, do not change the input parameters, otherwise memory resources may not be freed and you may encounter a memory leak.

  In case of a major OLE library version mismatch or a failure in initializing the OLE library, the database manager returns SQLCODE -465 (SQLSTATE 58032) with reason code 34, (Failure to initialize OLE library).

• Re-entrancy is strongly recommended for UDFs on all operating platforms, so that one copy of it can be used for multiple concurrent statements and applications.

  Note that the SCRATCHPAD facility can be used to circumvent many of the limitations imposed by re-entrancy.

• If the body of a function currently being used is modified (for example, recompiled and relinked), DB2 will not change functions in mid-transaction. However, the copy used in a subsequent transaction may be different if this kind of dynamic modification is taking place. Your operating system may also prevent you from changing a UDF body that is in use. This practice is not recommended.
• If you allocate dynamic memory in the UDF, it should be freed before returning to DB2. This is especially important for the NOT FENCED case. The SCRATCHPAD facility can be used, however, to anchor dynamic memory needed by the UDF across invocations. If you use the scratchpad in this manner, specify the FINAL CALL attribute on the CREATE FUNCTION for the UDF so that it can free the allocated memory at end-of-statement processing. The reason for this is that the system could run out of memory over time, with repeated use of the UDF.

  This reasoning holds as well for other system resources used by the UDF.

• Use the NOT NULL CALL option if it makes sense to do so. With this CREATE FUNCTION option, you do not have to check whether each SQL argument is null, and it performs better when you do have NULL values.
• Use the NOT DETERMINISTIC option if the result from your UDF depends on anything other than the input SQL arguments. This option prevents the SQL compiler from performing certain optimizations which can cause inconsistent results.
• Use the EXTERNAL ACTION option if your UDF has any side effects which need to be reliably performed. EXTERNAL ACTION prevents the SQL compiler from performing certain optimizations which can prevent invocation of your UDF in certain circumstances.
• Regarding the choice between FENCED and UNFENCED:

  FENCED UDF

  A FENCED UDF runs in its own process and thus cannot access most DB2 internal control and data areas, whether inadvertently or deliberately. This makes a FENCED UDF a safer choice for the database. However, it is still possible for a FENCED UDF that contains programming errors to bring down DB2, though not as easy as for a NOT FENCED UDF. A UDF that performs a massive overwrite of a return variable, for example, can cause DB2 to abend.

  UNFENCED UDF

  A NOT FENCED UDF performs better than a FENCED UDF, because a NOT FENCED UDF is loaded and executed directly in the DB2 engine process. NOT FENCED UDFs avoid the performance expense of process communication overhead. However, a NOT FENCED UDF could conceivably access or alter DB2 internal control or data areas. It is easier for an improperly written NOT FENCED UDF to bring down DB2 than a FENCED UDF.

  Obviously, with both FENCED and NOT FENCED UDFs, you should:

  • ensure the UDF is robustly written
  • subject the UDF to a rigorous design and code review
  • test the UDF in an environment where no harm can be done if it is not correctly written; for example, a test database.

  Most abends caused by a UDF are caught by DB2, which returns a -430 SQLCODE and prevents the database from being corrupted. However, certain types of UDF misbehavior, including a massive overwrite of a return value buffer, can cause DB2 to fail as well as the UDF. Pay attention particularly to any UDF which returns variable-length data, or which calculates how many bytes it must move to the return value buffer.

• For considerations on using UDFs with EUC code sets, see Considerations for UDFs.
• For an application running NOT FENCED UDFs, the first time such a UDF is invoked, a block of memory of the size indicated by the UDF_MEM_SZ configuration parameter is created. Thereafter, on a statement by statement basis, memory for interfacing between DB2 and NOT FENCED UDFs is allocated and deallocated from this block of memory as needed.

  For FENCED UDFs, a different block of memory is used in the same way. It is different because the memory is shared between processes. In fact, if an application uses both NOT FENCED and FENCED UDFs, two separate blocks of memory, each of the size indicated by the UDF_MEM_SZ parameter are used. Refer to the Administration Guide for more information about this configuration parameter.

• Use the DISALLOW PARALLELISM option in the following situations:
  • On scalar UDFs, if your UDF absolutely depends on running the same copy. Generally, this will be the case for NOT DETERMINISTIC SCRATCHPAD UDFs. (For an example, see the counter UDF specified in Scratchpad Considerations.)
  • If you do not want the UDF to run on multiple partitions at once for a single reference.
  • If you are specifying a table function.

  Otherwise, ALLOW PARALLELISM (the default) should be specified.

UDF Restrictions and Caveats

This section discusses items to be avoided in your UDF:

1. In general DB2 does not restrict the use of operating system functions. A few exceptions are:
   1. Registering of signal or exception handlers may interfere with DB2's use of these same handlers and may result in unexpected failure.
   2. System calls that terminate a process may abnormally terminate one of DB2's processes and result in system or application failure.

      Other system calls may also cause problems if they interfere with the normal operation of DB2; for example, a UDF that attempts to unload a library containing a UDF from memory could cause severe problems. Be careful in coding and testing any UDFs containing system calls.

2. The values of all environment variables beginning with 'DB2' are captured at the time the database manager is started with db2start, and are available in all UDFs whether or not they are FENCED. The only exception is the DB2CKPTR environment variable. Note that the environment variables are captured; any changes to the environment variables after db2start is issued are not available to the UDFs.
3. With respect to LOBs passed to an external UDF, you are limited to the maximum size specified by the UDF Shared Memory Size DB2 system configuration parameter. The maximum that you can specify for this parameter is 256M. The default setting on DB2 is 1M. For more information on this parameter, refer to the Administration Guide.
4. Input to, and output from, the screen and keyboard is not recommended. In the process model of DB2, UDFs run in the background, so you cannot write to the screen. However, you can write to a file.

   Note:

   DB2 does not attempt to synchronize any external input/output performed by a UDF with DB2's own transactions. So for example, if a UDF writes to a file during a transaction, and that transaction is later backed out for some reason, no attempt is made to discover or undo the writes to the file.

5. On UNIX-based systems, your UDF runs under the user ID of the DB2 Agent Process (NOT FENCED), or the user ID which owns the db2udf executable (FENCED). This user ID controls the system resources available to the UDF. For information on the db2udf executable, refer to the Quick Beginnings for your platform.
6. When using protected resources, (that is, resources that only allow one process access at a time) inside UDFs, you should try to avoid deadlocks between UDFs. If two or more UDFs deadlock, DB2 will not be able to detect the condition.
7. Character data is passed to external functions in the code page of the database. Likewise, a character string that is output from the function is assumed by the database to use the database's code page. In the case where the application code page differs from the database code page, the code page conversions occur as they would for other values in the SQL statement. You can prevent this conversion, by coding FOR BIT DATA as an attribute of the character parameter or result in your CREATE FUNCTION statement. If the character parameter is not defined with the FOR BIT DATA attribute, your UDF code will receive arguments in the database code page.

   Note that, using the DBINFO option on CREATE FUNCTION, the database code page is passed to the UDF. Using this information, a UDF which is sensitive to the code page can be written to operate in many different code pages.

8. When writing a UDF using C++, you may want to consider declaring the function name as:

        extern "C" void SQL_API_FN udf( ...arguments... ) 
   

   The extern "C" prevents type decoration (or 'mangling') of the function name by the C++ compiler. Without this declaration, you have to include all the type decoration for the function name when you issue the CREATE FUNCTION statement.