Blog
DB2utor

Categories

January 21, 2014

DB2 10-11 Migration: Application and SQL release incompatibilities

I previously noted that DB2 11 allows applications with incompatible SQL or XML to run in compatibility mode.

Here I'll examine a related question: What will cause an application to be incompatible?

IBM provides this information in the DB2 for z/OS Installation and Migration Guide, in the section titled, "Application and SQL release incompatibilities."

A list of potential incompatibilities, along with brief descriptions, follows. See the installation and migration guide to understand how these incompatibilities can impact your applications and how to deal with them.

* Change to determination of ASUTIME for dynamic statements -- In Version 11, new-function mode with application compatibility is set to V11R1, and the dynamic SQL ASUTIME limit for each routine is used by the resource limit facility.

* Automatic rebind of plans and packages created before DB2 Version 9 -- Plans and packages that were last bound before Version 9 are not supported in Version 11 conversion mode and later.

* Change in default for ODBC-limited block fetch initialization keyword -- The default for the LIMITEDBLOCKFETCH initialization keyword is changed in Version 11.

* DB2 objects created with period specifications in Version 10 -- In Version 11, views, materialized query tables and SQL table functions that were created with period specifications in Version 10 are not supported.

* Dropping columns -- In Version 11 new-function mode, columns named CLONE, ORGANIZATION or VERSIONING should be specified as a delimited identifier to be dropped from a table.

* XPath processing -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, XPath is allowed to continue processing even when errors occur on filtered results when using predicate expressions with explicit cast or operations with an invalid value.

* XML document node implicitly added on insert and update -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, if an XML document does not have a document node, then one is added during insert and update operations.

* Client information special registers length -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, special registers for client information fields might return different length values. The values in the CURRENT CLIENT_USERID, CURRENT CLIENT_WRKSTNAME, CURRENT CLIENT_APPLNAME, and CURRENT CLIENT_ACCTNG special registers are determined by the application compatibility level.

* Client information results from ADMIN_COMMAND_DB2 -- In DB2 Version 11 conversion mode, the ADMIN_COMMAND_DB2 result set row returns changes in the created global temporary table SYSIBM.DB2_THREAD_STATUS when processing-type = "THD". The column data type and maximum lengths for WORKSTATION, USERID, APPLICATION and ACCOUNTING change.

* Altering limit keys blocks immediate definition changes -- In DB2 Version 11 new-function mode, if a limit key is altered for certain table space types, immediate definition changes cannot be made until the limit key changes are materialized.

* Removing the SYSPUBLIC schema from the SQL PATH routine option -- Starting in DB2 Version 11 conversion mode, SYSPUBLIC is the schema that is used for public aliases. As such, the SQL PATH routine option must not specify the SYSPUBLIC schema.

* SYSIBMADM schema added to the SQL path -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, SYSIBMADM is added to the SQL path as an implicit schema.

* Change in result for CAST(string AS TIMESTAMP) -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, the result of CAST(string AS TIMESTAMP) is changed in some cases.

* New maximum lengths for values that are returned for some built-in functions -- In DB2 Version 11 new-function mode with application compatibility set to V11R1, the maximum lengths for values that are returned for some built-in functions is decreased.

* Timestamp string representations -- DB2 Version 11 new-function mode with application compatibility set to V11R1 strictly enforces valid string representations of timestamp values.

* SQL reserved words -- Version 11 has several new SQL reserved words. These are listed in reserved words.

 

* Fully qualified user-defined function names -- When using a user-defined function with the same name as a built-in function that has been added to Version 11, the function name must be fully qualified. If the function name is unqualified and SYSIBM precedes the schema used for this function in the SQL path, DB2 invokes one of the built-in functions.

* SQLCODE changes -- Some SQLCODE numbers and message text may change in DB2 Version 11. Also, the conditions under which some SQLCODEs are issued may change.

With earlier DB2 releases, addressing incompatibilities would require substantial changes and testing before the migration could take place. That's the major benefit of DB2 10 compatibility mode. It enables customers to migrate quickly to DB2 11 while maintaining current applications until they can be modified and tested on the new release.