Restrictions on external routines

When you develop or debug external routines, you must be aware of certain restrictions on these routines.

Some restrictions on external routines apply to all external routines, while some apply only to external procedures or external functions. The following lists describe the restrictions.

Restrictions that apply to all external routines:

  • New threads and processes cannot be created in external routines.
  • Connection level APIs cannot be called from within external functions or external methods.
  • Receiving inputs from the keyboard and displaying outputs to standard output is not possible from external routines. Do not use standard input-output streams. For example:
    • In external Java™ routine code, do not issue the System.out.println() methods.
    • In external C or C++ routine code, do not issue printf().
    • In external COBOL routine code, do not issue display
    Although external routines cannot display data to standard output, they can include code that writes data to a file on the database server file system.

    For fenced routines that run in Linux® or UNIX environments, the target directory where the file is to be created, or the file itself, must have the appropriate permissions such that the owner of the sqllib/adm/.fenced file can create it or write to it. For not fenced routines, the instance owner must have create, read, and write permissions for the directory in which the file is opened.

    Note: The database manager does not attempt to synchronize any external input or output activities that are performed by a routine with the database 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.
  • Connection-related statements or commands cannot be executed in external routines. This restriction applies to the following statements and commands:
    • BACKUP DATABASE
    • CONNECT
    • CONNECT TO
    • CONNECT RESET
    • CREATE DATABASE
    • DROP DATABASE
    • FORWARD RECOVERY
    • RESTORE DATABASE
  • Avoid the use of operating system functions within routines. The use of operating system functions is restricted in the following cases:
    • User-defined signal handlers must not be installed for external routines. Failure to adhere to this restriction can result in unexpected external routine run-time failures, database abends, or other problems. Installing signal handlers can also interfere with operation of the JVM for Java routines.
    • System calls that terminate a process can abnormally terminate one of the database processes and result in database system or database application failure.

      Other system calls can also cause problems if they interfere with the normal operation of the database manager. For example, a function that attempts to unload a library containing a user-defined function from memory could cause severe problems. Be careful in coding and testing external routines containing system calls.

  • Use of operating system function that results in the creation of a new process cannot be used in unfenced routines. These functions include fork(), popen(), and system(). Using these functions can interfere with the communications between the database servers and the cluster caching facility, which causes the routine to return an SQL0430N error.
  • External routines must not contain commands that would terminate the current process. An external routine must always return control to the database manager without terminating the current process.
  • External routine libraries, classes, or assemblies must not be updated while the database is active except in special cases. If an update is required while the database manager is active, and stopping and starting the instance is not an option, create the new library, class, or assembly for the routine with a different. Then, use the ALTER statement to change the external routine's EXTERNAL NAME clause value so that it references the name of the new library, class, or assembly file.
  • Environment variable DB2CKPTR is not available in external routines. All other environment variables with names beginning with 'DB' are captured at the time the database manager is started and are available for use in external routines.
  • Some environment variables with names that do not start with 'DB' are not available to external routines that are fenced. For example, the LIBPATH environment variable is not available for use. However these variables are available to external routines that are not fenced.
  • Environment variable values that were set after the database manager is started are not available to external routines.
  • Use of protected resources, resources that can only be accessed by one process at a time, within external routines should be limited. If used, try to reduce the likelihood of deadlocks when two external routines try to access the protected resource. If two or more external routines deadlock while attempting to access the protected resource, the database manager will not be able to detect or resolve the situation. This will result in hung external routine processes.
  • Memory for external routine parameters should not be explicitly allocated on the database server. The database manager automatically allocates storage based upon the parameter declaration in the CREATE statement for the routine. Do not alter any storage pointers for parameters in external routines. Attempting to change a pointer with a locally created storage pointer can result in memory leaks, data corruption, or abends.
  • Do not use static or global data in external routines. The database manager cannot guarantee that the memory used by static or global variables will be untouched between external routine invocations. For UDFs and methods, you can use scratchpads to store values for use between invocations.
  • All SQL parameter values are buffered. This means that a copy of the value is made and passed to the external routine. If there are changes made to the input parameters of an external routine, these changes will have no effect on SQL values or processing. However, if an external routine writes more data to an input or output parameter than is specified by the CREATE statement, memory corruption has occurred, and the routine can abend.
  • The LOAD utility does not support loading into tables that contain columns that reference fenced procedures. If you issue the LOAD command on such table, you will receive error message SQL1376N. To work around this restriction, you can redefine the routine to be unfenced, or use the import utility.
  • Modifications to the Java Virtual Machine (JVM) or the JVM start arguments is not supported for external Java routines.

Restrictions that apply to external procedures only

  • When returning result sets from nested stored procedures, you can open a cursor with the same name on multiple nesting levels. However, pre-version 8 applications will only be able to access the first result set that was opened. This restriction does not apply to cursors that are opened with a different package level.

Restrictions that apply to external functions only

  • External functions cannot return result sets. All cursors opened within an external function must be closed by the time the final-call invocation of the function completes.
  • Dynamic allocations of memory in an external routine should be freed before the external routine returns. Failure to do so will result in a memory leak and the continuous growth in memory consumption of a database process that could result in the database system running out of memory.

    For external user-defined functions and external methods, scratchpads can be used to allocate dynamic memory required for multiple function invocations. When scratchpads are used in this way, specify the FINAL CALL attribute in the CREATE FUNCTION or CREATE METHOD statement. This ensures that allocated memory is freed before the routine returns.