DB2 Version 9.7 for Linux, UNIX, and Windows

Upgrade essentials for routines

Upgrade essentials describe changes in application development support, changes to support new functionality, unsupported functionality, and deprecated functionality that might impact your routines.

The changes described in Upgrade essentials for database applications could also impact your routines.

Development software support

The information about development software support in Upgrade essentials for database applications applies to external stored procedures and user-defined functions (UDFs).

Implicit casting

After function invocation, the database manager must decide which function in a group of like-named functions is the "best fit". A comparison of the data types of the arguments with the defined data types of the parameters of the functions under consideration forms the basis for this decision. An untyped parameter marker or an untyped NULL constant argument accepts any parameter type as a best fit.

This change to support implicit casting impacts function resolution that involves modified system built-in functions and any new functions that you create using these arguments.

XML data is passed by reference in SQL routines

In SQL routines, when you assign XML data to input and output parameters of XML type or local variables of XML type, the XML data is now passed by reference. In previous releases, the XML data was passed by value in SQL procedures. Therefore, some operations using XML data in SQL procedures can return results that are different from the results returned by the same operations in previous releases.

Refer to XML data is passed by reference in stored procedures for details about this difference in behavior.

SQL procedures

SQL procedures that you created in DB2 UDB Version 8.1 will run in DB2 Version 9.7 if you upgrade from a DB2 UDB Version 8 32-bit instance to a DB2 Version 9.7 32-bit instance, provided that they do not reference any unsupported functionality. This also applies if you upgrade from a DB2 UDB Version 8 64-bit instance to a DB2 Version 9.7 64-bit instance. However, if you upgrade from a DB2 UDB Version 8.1 32-bit instance to a DB2 Version 9.7 64-bit instance, your SQL procedures do not run because the 64-bit DB2 engine cannot load the 32-bit libraries associated to these procedures. You must drop and re-create these SQL procedures.

If you created SQL procedures in DB2 UDB Version 8.2 or later and upgrade your databases to DB2 Version 9.7, your SQL procedures will function successfully provided that they do not reference any unsupported functionality.

Unfenced external routines

During database upgrade to DB2 Version 9.7 on Linux and UNIX operating systems, all external unfenced routines that have no dependency on the DB2 engine libraries (libdb2e.a or libdb2apie.a) are altered to FENCED and NOT THREADSAFE so you can safely run these routines under the new multithreaded database manager. Running external routines defined as NOT FENCED and THREADSAFE in the new multithreaded database manager that are not thread safe can yield incorrect results, database corruption, or abnormal termination of the database manager. Refer to Upgrading C, C++, and COBOL routines for details about how to manage this change.

32-bit external routines

The implementation for LOB locators depends on DB2 database product installed. If you have 32-bit external UDFs that use LOB locators as input parameters and you upgrade from a DB2 UDB Version 8 32-bit instance to a DB2 Version 9.7 64-bit instance, you must rebuild them as 64-bit unfenced UDFs.

Support for default function entry points in external routine libraries is deprecated in DB2 Version 9.1. If you upgraded from a DB2 UDB Version 8 32-bit instance on AIX® or Windows operating systems, you should specify an explicit entry point for your routine library.

31-bit external routines (Linux on zSeries)

All upgrade considerations for 32-bit external routines also apply to 31-bit external routines running on a DB2 database on Linux on zSeries.

Java™ external routines

DB2 Version 9.7 installs the 32-bit IBM® Software Developer's Kit (SDK) for Java 6 by default on Linux on x86 and Windows (when DB2 Version 9.7 32-bit product is installed) operating systems. For all other supported operating systems, DB2 Version 9.7 installs a 64-bit SDK for Java 6.

If you upgrade an instance to DB2 Version 9.7, the jdk_path database manager configuration parameter is set to the installation path of SDK for Java 6. The following table indicates the possible values for jdk_path :

Table 1. jdk_path database manager configuration parameter settings
DB2 Version 9.7 instance	Operating System	jdk_path value
32-bit instance	Linux	INSTHOME/sqllib/java/jdk32
64-bit instance	Linux and UNIX	INSTHOME/sqllib/java/jdk64
32-bit or 64-bit instance	Windows	DB2PATH\java\jdk

In DB2 Version 9.7 64-bit instances, Java external routines require that the jdk_path parameter is set to a 64-bit SDK for Java installation path to run successfully. A DB2 Version 9.7 64-bit instance cannot load a 32-bit JVM.

The IBM Software Developer's Kit (SDK) for Java 1.4.2 is deprecated and might be discontinued in a future release.

Starting with DB2 Version 9.5, the default JDBC driver to run JDBC routines is the IBM Data Server Driver for JDBC and SQLJ. See Upgrading Java routines for details on how to manage this change.

Upgrade of routines from DB2 Version 9.1 or DB2 UDB Version 8

If you are upgrading from DB2 Version 9.1 or earlier, review the changes in routine development support, changes to support new functionality, unsupported functionality, and deprecated functionality that might also impact your routines: