IBM Books

Embedded SQL Programming 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:

  1. 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.

  2. 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.

  3. 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 will have no effect when the UDF returns to DB2.

  4. 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 21, (Failure to initialize OLE library).

  5. Reentrancy 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.

  6. 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. This practice is not recommended.

  7. 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.

  8. 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.

  9. 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.

  10. 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.

  11. Use the FENCED option, unless you are either:

  12. For considerations on using UDFs with EUC code sets, see "Considerations for UDFs".

  13. For an application running unfenced 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 unfenced 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 unfenced and fenced UDFs, two separate blocks of memory, each of the size indicated by the UDF_MEM_SZ parameter are used. See the Administration Guide for more information about this configuration parameter.

  14. Use the DISALLOW PARALLELISM option in the following situations:

    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.

  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, see 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 UID of the DB2 Agent Process (NOT FENCED), or the UID which owns the db2udf executable (FENCED). This UID controls the system resources available to the UDF. For information on the db2udf executable, refer to the Quick Beginnings.

  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.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]