Application and SQL release incompatibilities

When you migrate from Db2 10 to Db2 11, be aware of and plan for application and SQL release incompatibilities that might affect your migration.

re

Begin general-use programming interface information.

Plan for the following changes in Db2 11 that might affect your migration.

Change to determination of ASUTIME for dynamic statements

In Db2 11 new-function mode with application compatibility set to ’V11R1’, the dynamic SQL ASUTIME limit for each routine is used by the resource limit facility.

Explanation

The ASUTIME limit that is specified for the routine determines the limit. If the dynamic SQL statements in a routine use more ASUTIME than the limit, then SQLCODE -905 is returned. This SQLCODE occurs even if the value is lower than the ASUTIME limit of a top-level calling package. The ASUTIME limit that is specified for the top-level calling package is not considered. In previous versions of Db2, SQLCODE -905 is issued when the limit of the top-level calling package is encountered. Now in Db2 11 the time that is used by each nested routine is compared to the ASUTIME limit.

Possible impact to your Db2 environment

Because the limit is enforced for each monitored routine, your applications might return more SQLCODE -905 errors.

Actions to take

While in conversion mode with application compatibility for your package set to value ’V10R1’, run your applications with IFCID 0366 or IFCID 0376 enabled. Then, review the trace output for incompatible changes with the identifier ’1103’. Review and, if necessary, adjust the ASUTIME limits on routines and packages that use dynamic SQL.

Automatic rebind of plans and packages created before DB2 9

Explanation

Plans and packages that were last bound before DB2® 9 are not supported in Db2 11 conversion mode and later.

Possible impact to your Db2 environment

If you specify YES or COEXIST for the ABIND subsystem parameter, Db2 11 automatically rebinds plans and packages that were bound before DB2 9. As a result, an execution delay might occur the first time that such a plan or package is loaded. Also, the automatic rebind might change the access path to a potentially more efficient access path.

If you specify NO for the ABIND subsystem parameter, negative SQLCODEs are returned for each attempt to run a package or plan that was bound before DB2 9. SQLCODE -908, SQLSTATE 23510 is returned for packages, and SQLCODE -923, SQLSTATE 57015 is returned for plans until they are rebound in Db2 11.

Actions to take

To identify plans and packages that were bound before DB2 9, run the Db2 11 premigration job DSNTIJPM on your DB2 10 catalog.

Invalidated plans and packages

During the enabling-new-function mode processing, plans and packages that reference the affected Db2 catalog and directory table spaces become invalidated.

Explanation

The following table spaces in the Db2 catalog and directory are modified when you run job DSNTIJEN to enable Db2 11 new-function mode:
  • DSNDB01.SYSUTILX
  • DSNDB01.SYSLGRNX
  • DSNDB06.SYSCOPY
  • DSNDB06.SYSRTSTS
  • DSNDB06.SYSTSIXS
  • DSNDB06.SYSTSTAB
  • DSNDB06.SYSSTR
As these table spaces are processed, Db2 invalidates packages or plans that reference them.
The packages that are dependent on the following catalog tables are also invalidated:
  • SYSIBM.SYSCOPY
  • SYSIBM.SYSCHECKS
  • SYSIBM.SYSCHECKS2
  • SYSIBM.SYSCHECKDEP
  • SYSIBM.SYSCOLUMNS
  • SYSIBM.SYSINDEXES
  • SYSIBM.SYSPENDINGDDL
  • SYSIBM.SYSSTRINGS
  • SYSIBM.SYSTABLES
  • SYSIBM.SYSTABLESPACE
  • SYSIBM.SYSINDEXSPACESTATS
  • SYSIBM.SYSTABLESPACESTATS

Possible impact to your Db2 environment

If you have autobind enabled, the invalid packages are bound on the first run after they were marked invalid.

If you have autobind disabled, each attempt to use an invalidated package fails with SQLCODE -908 to indicate that the application must be bound before it can be run.

Actions to take

For SYSLGRNX, existing CHAR(6) columns were changed to CHAR(10). You might need to modify your application before it can run successfully.

For SYSUTILX, the RBA fields were moved to new fields. Applications might need to be updated before you can see the new fields.

The SYSCOPY table space was replaced by a new table space, SYSTSCPY. You might need to modify your application before it can run successfully.

The SYSRTSTS table space was replaced by two new table spaces, SYSTSTSS and SYSTSISS. SYSTSTSS contains the SYSIBM.SYSTABLESPACESTATS catalog table and SYSTSISS contains the SYSIBM.SYSINDEXSPACESTATS table. You might need to modify your application before it can run successfully.

The SYSSTR table space was replaced by four new table spaces, SYSTSCKS, SYSTSCHX, SYSTSCKD, and SYSTSSRG. SYSTSCKS contains SYSIBM.SYSCHECKS, SYSTSCHX contains SYSIBM.SYSCHECKS2, SYSTSCKD contains SYSIBM.SYSCHECKDEP, and SYSTSSRG contains SYSIBM.SYSSTRINGS catalog table. You might need to modify your application before it can run successfully.

Default for ODBC limited block fetch

The default for the LIMITEDBLOCKFETCH initialization keyword changed.

Explanation

In DB2 10, ODBC limited block fetch was disabled by default. In Db2 11, ODBC limited block fetch is enabled by default.

Possible impact to your Db2 environment

Your applications might use limited block fetch, when they did not do so previously.

Actions to take

If the default is not appropriate for your ODBC applications, you can change it by modifying the value of the LIMITEDBLOCKFETCH initialization keyword.

Views, materialized query tables, and SQL table functions with period specifications

Explanation

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

Possible impact to your Db2 environment

If such views, materialized query tables, or SQL functions are used in Db2 11, incorrect results might occur.

Actions to take

To prepare for this change, drop all views, materialized query tables, and SQL table functions that contain a SYSTEM_TIME or BUSINESS_TIME period specification.

To identify such existing views, materialized query tables, and SQL table functions, run the Db2 11 premigration job DSNTIJPM on your DB2 10 catalog.

You can also manually issue the following queries.

To identify views and materialized query tables that were created with a period specification, issue the following query:

SELECT * FROM SYSIBM.SYSVIEWDEP WHERE BTYPE IN ('W', 'Z') AND DTYPE IN ('V', 'M'); 

To identify SQL table functions that were created with a period specification, issue the following query:

SELECT * FROM SYSIBM.SYSDEPENDENCIES WHERE BTYPE = 'Z';  

To identify SQL scalar functions that were created with a period specification or period clause, issue the following query:

SELECT * FROM SYSIBM.SYSPACKDEP WHERE BTYPE IN ('W', 'Z') AND DTYPE = 'N'; 

Dropping columns named CLONE, ORGANIZATION, or VERSIONING

In Db2 11 new-function mode, a column that is named CLONE, ORGANIZATION, or VERSIONING should be specified as a delimited identifier in order to be dropped from a table.

Explanation

Prior to Db2 11, CLONE, ORGANIZATION, and VERSIONING are reserved keywords that can appear after the DROP keyword in an ALTER TABLE statement. When CLONE, ORGANIZATION, or VERSIONING is specified as a simple token (that is, not as a delimited identifier), these keywords can only match the DROP CLONE, DROP ORGANIZATION, or DROP VERSIONING clauses on an ALTER TABLE statement.

Possible impact to your Db2 environment

If you intend to drop a column named CLONE, ORGANIZATION, or VERSIONING in Db2 11, and the name is specified as a simple token on the ALTER TABLE statement, the Db2 subsystem might interpret the ALTER TABLE statement as specifying the DROP CLONE, DROP ORGANIZATION, or DROP VERSIONING clauses instead of the DROP COLUMN clause.

Actions to take

To drop a column named CLONE, ORGANIZATION, or VERSIONING in Db2 11, the name must be specified as a delimited identifier. For example: DROP "ORGANIZATION" or DROP "CLONE" (assuming " is the delimiter for a delimited identifier).

Alternatively, you can specify the optional COLUMN keyword in the DROP COLUMN clause. For example: DROP COLUMN ORGANIZATION or DROP COLUMN CLONE.

Allow XPath processing to continue even if error on filtered results

In Db2 11 new-function mode with application compatibility set to ’V11R1’, XPath processing might return fewer errors on predicate expressions with an explicit cast or an operation with an invalid value.

Explanation

In previous versions, even though the invalid result is filtered from the result set, XPath processing would return an error SQLCODE. In Db2 11, examples of XPath expressions that have fewer errors include situations when:
  • Data is filtered from the result by the predicate before an invalid operation such as division of a number by zero
  • Data is explicitly cast to an incompatible data type

Possible impact to your Db2 environment

Your applications might return fewer error SQLCODEs.

Actions to take

While in conversion mode with application compatibility for your package set to value ’V10R1’, run your applications with IFCID 0366 or IFCID 0376 enabled. Then, review the trace output for incompatible changes with the identifier ’1102’.

XML document node implicitly added on insert and update

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

Explanation

In previous versions of Db2, document nodes are not implicitly added and an SQL insert or update of an XML document returned SQLCODE -20345. To avoid the error, an application invokes the XMLDOCUMENT function before the insert or update. In Db2 11, an XML document node is added if one does not exist in the XML document.

Possible impact to your Db2 environment

Your applications might return fewer errors on insert and update operations.

Actions to take

While in conversion mode with application compatibility for your package set to value ’V10R1’, run your applications with IFCID 0366 or IFCID 0376 enabled. Then, review the trace output for incompatible changes with the identifier ’1101’. In addition, you can review your applications for use of the XMLDOCUMENT function.

Client information special registers length

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

Explanation

In previous versions of Db2, client information values were truncated and padded to the maximum length. In Db2 11 the following lengths increase:
  • The maximum length of CURRENT CLIENT_USERID increases from 16 bytes to 128 bytes.
  • The maximum length of CURRENT CLIENT_WKSTNAME increases from 18 bytes to 255 bytes.
  • The maximum length of CURRENT CLIENT_APPLNAME increases from 32 bytes to 255 bytes.
  • The maximum length of CURRENT CLIENT_ACCTNG increases from 200 bytes to 255 bytes.

In Db2 11, trailing blanks are removed.

Possible impact to your Db2 environment

When the application compatibility for your package is set to value ’V11R1’, your applications might receive a different length client information value than they did previously. The value is no longer padded to the supported maximum length and trailing blanks are removed.

Actions to take

Review your applications for use of these special registers. While in conversion mode with application compatibility for your package set to value ’V10R1’, run your applications with IFCID 0366 or IFCID 0376 enabled. Then, review the trace output for incompatible changes with the identifier ’1104’, ’1105’, ’1106’, or ’1107’.

Client information results from ADMIN_COMMAND_DB2

In Db2 11 conversion mode, the ADMIN_COMMAND_DB2 result set row returned 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.

Explanation

In Db2 11 the following column data types and lengths change:
  • WORKSTATION increases from CHAR(18) to VARCHAR(255).
  • USERID increases from CHAR(16) to VARCHAR(128).
  • APPLICATION increases from CHAR(32) to VARCHAR(255).
  • ACCOUNTING increases from CHAR(247) to VARCHAR(255).

Possible impact to your Db2 environment

Your applications now receive a VARCHAR data type and possibly a different length client information value. The length is no longer padded to the supported maximum length.

Actions to take

Review your applications for use of the ADMIN_COMMAND_DB2 stored procedure.

ALTER statements that change limit keys are pending changes

Starting in Db2 11 new function mode, ALTER statements that modify limit key values result in pending data definition changes, which do not take effect until materialized by the REORG utility. They also block all subsequent immediate changes until materialized.

Explanation

In Db2 11, ALTER statements that change limit keys for the following types of table spaces result in pending data definition changes:

  • partition-by-range spaces
  • Partitioned (non-UTS) tables spaces with table-controlled partitioning

Affected partitions are placed in advisory REORG-pending (AREOR) status. In DB2 10, such ALTER statements resulted in immediate changes, which placed affected partitions in restrictive REORG-pending (REORP) status.

ALTER statements that alter the last partition are an exception if they change MAXVALUE to a value less than MAXVALUE for ascending, or from MINVALUE to a value greater than MINVALUE for descending. In such cases, the changes are immediate and affected partitions are placed in restricted REORG-pending status.

Possible impact to your Db2 environment

Affected partitions remain in advisory REORG-pending (AREOR) status, the old limit key values remain in effect, and the data remains available until the pending definition change is materialized. However, all subsequent immediate data definition changes, including in the same statement, remain blocked until the pending data definition changes are materialized. Table spaces with pending definition changes have an entry in SYSIBM.SYSPENDINGDDL.

Also, you can no longer materialize changes for ALTER statements that change limit keys by using the REORG TABLESPACE utility with SHRLEVEL NONE or the LOAD utility with REPLACE.

Actions to take

Modify any existing jobs that materialize limit key changes to run the REORG TABLESPACE utility with SHRLEVEL CHANGE or SHRLEVEL REFERENCE.

For jobs that use the LOAD utility with REPLACE, modify the job to run the REORG TABLESPACE utility with SHRLEVEL CHANGE or SHRLEVEL REFERENCE before running LOAD.

SYSTABLEPART.LIMITKEY format variations

Explanation

Starting in Db2 11 conversion mode, the LIMITKEY column of the SYSTABLEPART catalog table can contain a mix of differently formatted values:

  • Date and time values are delimited by single quotation marks (for example, '2001-01-01'). However, values that were added in releases prior to Db2 11 do not contain the delimiters.
  • When the decimal point indicator is comma, a space follows any comma delimiter in the value. The comma decimal point indicator is used when the DECIMAL POINT IS field setting is , (comma) or a COBOL program that executes the ALTER statement uses the COMMA processing option. No space follows comma delimiters when period decimal point indicators are used, or for values added in releases prior to Db2 11.

Possible impact to your Db2 environment

Applications that do not tolerate the format variations might fail.

Actions to take

Modify any applications that use the LIMITKEY column to tolerate the format variations.

For example, to remove the single-quote delimiters for date and time values, from a single column in a partitioning key, you might use the following SQL statements:

SELECT DATE(STRIP(LIMITKEY,B,X'27'))  FROM SYSIBM.SYSTABLEPART WHERE …;
SELECT TIME(STRIP(LIMITKEY,B,X'27'))  FROM SYSIBM.SYSTABLEPART WHERE …;

If (STRIP(LIMITKEY,B,X'27')) is omitted from the statements, Db2 issues SQLCODE -180.

The use type of the column is also changed to S, which indicates a product-sensitive programming interface. It is expected that programs written to such interfaces might need to be changed in order to run with new product releases or versions, or as a result of service.

Removing the SYSPUBLIC schema from the SQL PATH routine option

Starting in Db2 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.

Explanation

In previous versions of Db2, you could not define functions, procedures, distinct types, and sequences in the SYSPUBLIC schema, but you were not restricted from specifying SYSPUBLIC as part of the SQL PATH. However, doing so no effect on applications. In Db2 11 you can no longer specify SYSPUBLIC as part of the SQL PATH.

Possible impact to your Db2 environment

Creation or resolution of some objects that worked in previous versions, might fail in Db2 11 with SQLCODE -713 if SYSPUBLIC is specified as part of the SQL PATH.

Actions to take

Query the catalog to see if any object schemas use SYSPUBLIC as the schema qualifier. This is highly unlikely for any object, but most likely with objects that use the SQL PATH (functions, procedures, distinct types, and sequences).

Change any existing SET PATH statements to not specify SYSPUBLIC as a schema.

SYSIBMADM schema added to the SQL path

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

Explanation

If SYSIBMADM is not specified as an explicit schema in the SQL path, it is included as an implicit schema at the beginning of the path after SYSIBM, SYSFUN, and SYSPROC.

Possible impact to your Db2 environment

Applications that reference the content of the CURRENT PATH special register now have the SYSIBMADM schema returned when implicit schemas are included in the path. For example, the statement SELECT CURRENT PATH FROM SYSIBM.SYSDUMMY1 now returns "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","authid," where authid is the authorization ID of the statement, instead of "SYSIBM","SYSFUN","SYSPROC","authid."

Actions to take

No action is required.

Change in result for CAST(string AS TIMESTAMP)

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

Explanation

Previously, when Db2 executed CAST(string AS TIMESTAMP), Db2 interpreted an 8-byte string as a Store Clock value and a 13-byte string as a GENERATE_UNIQUE value. This interpretation might result in an incorrect result from the CAST specification. Starting with Db2 11, with the application compatibility set to V11R1, when an 8-byte string or a 13-byte string is input to CAST(string AS TIMESTAMP), Db2 interprets the input strings as string representations of TIMESTAMP values.

Possible impact to your Db2 environment

An invalid representation of an 8-byte or 13-byte string in CAST(string AS TIMESTAMP) results in SQLCODE -180.

For example, suppose that you execute the following SQL statements in Db2 11 new-function mode:

-- SET APPLICATION COMPATIBILITY TO V10R1
SET CURRENT APPLICATION COMPATIBILITY='V10R1';
-- CAST AN 8-BYTE STRING REPRESENTATION OF A DATETIME VALUE
-- TO TIMESTAMP
SELECT CAST('1/1/2013' AS TIMESTAMP) FROM SYSIBM.SYSDUMMY1;
-- CAST AN 8-BYTE STRING REPRESENTATION OF A STORE CLOCK VALUE
-- TO TIMESTAMP
SELECT CAST(X'CAB5060708090100' AS TIMESTAMP) FROM SYSIBM.SYSDUMMY1;

The result is of the first SELECT statement is 2034-07-25-16.43.41.599503, which is an incorrect result. The result of the second SELECT statement is 2013-01-01-20.37.04.246928, which is the correct result if the input string is interpreted as a Store Clock value.

If you execute the following SQL statements, the results differ:

-- SET APPLICATION COMPATIBILITY TO V11R1
SET CURRENT APPLICATION COMPATIBILITY='V11R1';
-- CAST AN 8-BYTE STRING REPRESENTATION OF A DATETIME VALUE
-- TO TIMESTAMP
SELECT CAST('1/1/2013' AS TIMESTAMP) FROM SYSIBM.SYSDUMMY1;
-- CAST AN 8-BYTE STRING REPRESENTATION OF A STORE CLOCK VALUE
-- TO TIMESTAMP
SELECT CAST(X'CAB5060708090100' AS TIMESTAMP) FROM SYSIBM.SYSDUMMY1;

The result of the first SELECT statement is 2013-01-01-00.00.00.000000, which is the correct result. The result of the second SELECT statement is SQLCODE -180, because a Store Clock value is not valid input to CAST(string AS TIMESTAMP).

Actions to take

While in Db2 11 conversion mode, or in Db2 11 new-function mode with application compatibility set to V10R1, identify applications with this incompatibility by starting a trace for IFCID 0366 or IFCID 0376, and then running the applications. Review the trace output for incompatible changes with the identifier 1109. If you need to convert Store Clock values to the TIMESTAMP data type, use the TIMESTAMP built-in function instead of CAST(string AS TIMESTAMP).

For example:

-- SET APPLICATION COMPATIBILITY TO V11R1
SET CURRENT APPLICATION COMPATIBILITY='V11R1';
-- CONVERT AN 8-BYTE STRING REPRESENTATION OF A STORE CLOCK VALUE
-- TO TIMESTAMP
SELECT TIMESTAMP(X'CAB5060708090100') FROM SYSIBM.SYSDUMMY1;

You receive the correct result of 2013-01-01-20.37.04.246928.

New maximum lengths for values that are returned for some built-in functions

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

Explanation

For the SPACE and VARCHAR built-in functions, the maximum length of the result is changed from 32767 to 32764 bytes.

Possible impact to your Db2 environment

If the length of the output string for any of these functions is greater than 32764 bytes, SQLCODE -171 is returned.

Actions to take

Review your applications for use of these functions, and, if necessary, modify the function input so that the result does not exceed 32764 bytes. While in conversion mode with application compatibility for your package set to value ’V10R1’, run your applications with IFCID 0366 or IFCID 0376 enabled. Then, review the trace output for incompatible changes with the identifier ’1110’ or ’1111’.

Timestamp string representations

String representations of timestamp values must adhere to the rules in the SQL Reference. However, releases before DB2 10 inadvertently tolerate some string representations of timestamps with invalid syntax.

The behavior is controlled by the BIF_COMPATIBILITY subsystem parameter and the application compatibility setting.
  • Db2 11 with application compatibility set to V11R1 strictly enforces valid string representations of timestamp values. This is equivalent to DB2 10 with the BIF_COMPATIBILITY subsystem parameter set to CURRENT.
  • Db2 11 with application compatibility set to V10R1, the enforcement of valid string representations depends on the BIF_COMPATIBILITY value.

Actions to take

Review your setting of the BIF_COMPATIBILITY subsystem parameter. If the value is not CURRENT, the application compatibility for your package set to V10R1, and you have applications that require string representations of timestamp values supported in a pre-DB2 10, you should make appropriate changes to your SQL to use one of the supported formats.

To modify your applications:

  1. Use IFCID 0366 or IFCID 0376 trace to identify applications that depend on the pre-DB2 10 formats.
  2. Review the trace output with the function identifier ’3’ to identify SQL with unsupported timestamp values.
  3. Make appropriate changes to your SQL statements.
  4. Set the BIF_COMPATIBILITY value to CURRENT.

Data types of output arguments from a stored procedure call in a Java application

In Db2 11 new-function mode, when a Java™ application that uses the IBM® Data Server Driver for JDBC and SQLJ calls a stored procedure, the data types of stored procedure output arguments match the data types of the parameters in the stored procedure definition.

Explanation

Before DB2 10, if a Java client called a Db2 for z/OS® stored procedure, the data types of output arguments matched the data types of the corresponding CALL statement arguments. Starting in DB2 10, the data types of the output arguments match the data types of the parameters in the stored procedure definition.

In Db2 11 conversion mode, or when application compatibility is set to V10R1, you can set the DDF_COMPATIBILITY subsystem parameter to SP_PARMS_JV to keep the behavior that existed before DB2 10. However, when application compatibility is set to V11R1, SP_PARMS_JV is no longer supported.

In Db2 11 with application compatibility set to V11R1, if the version of the IBM Data Server Driver for JDBC and SQLJ is lower than 3.63 or 4.13, a java.lang.ClassCastException might be thrown when an output argument value is retrieved.

Actions to take

Take one of the following actions:

  • Upgrade the IBM Data Server Driver for JDBC and SQLJ to version 3.63 or 4.13, or later.
  • Modify the data types in CallableStatement.registerOutParameter method calls to match the parameter data types in the stored procedure definitions. You can set application compatibility to V10R1 and run a trace for IFCID 0366 or 0376 to identify affected applications. Trace records for those applications have a QW0366FN field value of 8.

CHAR9 and VARCHAR9 functions for compatibility with pre-DB2 10 string formatting of decimal data

DB2 10 changed the formatting of decimal data by the CHAR and VARCHAR built-in functions and CAST specifications with a CHAR or VARCHAR result type. In Db2 11 you can use alternative functions for compatibility with applications that require decimal to string output in the pre-DB2 10 formats:

Important: For portable applications that might run on platforms other than Db2 for z/OS, do not use the CHAR9 and VARCHAR9 functions. Other Db2 family products do not support the these functions.

Actions to take

Review your setting for the BIF_COMPATIBILITY subsystem parameter. If the value is not CURRENT, and you have applications that require decimal to string output in the pre-DB2 10 format, you can rewrite SQL statements to use the CHAR9 and VARCHAR9 functions instead. This approach enables the development of new applications that can accept the current string formatting of decimal data.

To modify your applications to take advantage of the CHAR9, VARCHAR9 functions:

  1. Use an IFCID 0366 trace to identify applications that depend on the pre-DB2 10 formats.
  2. Rewrite the SQL statements in the identified applications to use the CHAR9 and VARCHAR9 functions instead of the CHAR and VARCHAR functions.
  3. Set the BIF_COMPATIBILITY value to CURRENT.

Subsystem parameter BIF_COMPATIBILITY and SQL schemas for compatibility with pre-DB2 10 string formatting of decimal data

DB2 10 changed the formatting of decimal data by the CHAR and VARCHAR built-in functions and CAST specifications with a CHAR or VARCHAR result type. You can temporarily override these changes on a subsystem level by setting the BIF_COMPATIBILITY subsystem parameter to one of the pre-DB2 10 settings. You can also temporarily override these changes on an application level by adding schema SYSCOMPAT_V9 to the front of the PATH bind option or CURRENT PATH special register. The latter approach works for CHAR and VARCHAR functions and does not affect CAST specifications.

The recommended approach is to modify your applications to handle the DB2 10 and later behavior for these functions, as described in the following steps.

Actions to take

To modify your applications to handle the DB2 10 and later behavior for CHAR, VARCHAR, and CAST:

  1. Identify applications that need to be modified to handle this change. Run a trace for IFCID 0376 to identify affected applications.
  2. Ensure that the BIF_COMPATIBILITY subsystem parameter is set to V9_DECIMAL_VARCHAR.

    To handle the change for the CHAR function only, you can set BIF_COMPATIBILITY to V9, and complete the following steps for the CHAR function.

  3. Change any affected applications to handle the DB2 10 and later CHAR and VARCHAR behavior, including stored procedures, non-inline user-defined functions, and trigger packages. Rewrite affected CAST specifications with the appropriate CHAR or VARCHAR function and a CAST to the correct length if needed.
  4. Rebind and prepare packages with the PATH(SYSCURRENT,SYSIBM) rebind option. Putting the SYSCURRENT schema at the beginning of the SQL path causes the DB2 10 and later behavior to be used for the CHAR and VARCHAR built-in functions.

    Repeat this step for native stored procedures (SQLPL) and non-inline SQL scalar functions.

  5. For views that reference these casts or built-in functions, determine whether the view needs to be changed to have the expected output. Drop and re-create the views with the PATH(SYSCURRENT,SYSIBM) rebind option only if necessary. Rebind any applications that reference the views with the PATH(SYSCURRENT,SYSIBM) option to use the DB2 10 and later CHAR and VARCHAR built-in functions. Repeat this step for inline SQL scalar functions.
  6. For materialized query tables or indexes on expressions that reference these casts or built-in functions, drop and re-create the materialized query tables or indexes on expressions with the PATH(SYSCURRENT,SYSIBM) rebind option. Issue the REFRESH TABLE statement for materialized query tables. Rebind any applications that reference the materialized query tables or indexes on expressions with the PATH(SYSCURRENT,SYSIBM) option to use the DB2 10 and later CHAR and VARCHAR built-in functions.
  7. Change the value of the BIF_COMPATIBILITY subsystem parameter to CURRENT. When the subsystem parameter value is CURRENT, new applications, rebinds, and CREATE statements use the DB2 10 and later CHAR, VARCHAR, and CAST behavior.

Materialized query tables and expression-based indexes use the CHAR, VARCHAR, and CAST behavior that is specified during its creation. If a reference statement has a different behavior that is specified by the BIF_COMPATIBILITY parameter or a different path, the materialized query table or expression-based index is not used.

Change in CREATE TRIGGER statements with a WHEN clause in which the search-condition references a system-period temporal table

If a CREATE TRIGGER statement is issued before Db2 11 new-function mode, and the triggered-action contains a WHEN clause in which the search-condition references a system-period temporal table, the CREATE TRIGGER statement succeeds, and the trigger continues to work in Db2 11 new-function mode. However, when a CREATE TRIGGER statement with a WHEN clause in which the search-condition references a system-period temporal table is issued in Db2 11 new-function mode, the CREATE TRIGGER statement fails. This happens because in Db2 11, the trigger package is created with the SYSTIMESENSITIVE(YES) bind option, which does not allow a reference to the system-period temporal table.

Actions to take

To create a trigger that references a system-period temporal table in the search-condition of a WHEN clause, follow these steps:

  1. Issue an ALTER TABLE statement with the DROP VERSIONING clause on the system-period temporal table to temporarily disconnect the system-period temporal table from the related history table.
  2. Issue the CREATE TRIGGER statement.
  3. Issue an ALTER TABLE statement with the ADD VERSIONING clause to redefine the system-period temporal table. In the ALTER TABLE statement, specify the related history table in the USE HISTORY TABLE clause.
  4. Issue the REBIND TRIGGER PACKAGE command with option SYSTIMESENSITIVE(NO) on the trigger package that was generated when you performed step 2.

SQL reserved words

Begin program-specific programming interface information.

Db2 11 introduces several new SQL reserved words, which are listed in Reserved words.

In some cases, the use of these reserved words might cause an incompatibility in Db2 11 conversion mode, regardless of the setting of the APPLCOMPAT flag.

Actions to take

Collect IFCID 0366 trace records in Db2 10. Values 4, 5, and 6 for the QW0366FN field indicate instances of reserved words in applications that will cause an incompatibility in Db2 11. Adjust these applications by changing the reserved word to a delimited identifier or by using a word that is not reserved in Db2 11.End program-specific programming interface information.

Built-in function and existing user-defined functions

For built-in and user-defined functions the combination of the function name and the parameter list form the signature that Db2 uses to identify the function. If the signatures of new or changed built-in functions in Db2 11 match the signatures existing user-defined functions, applications with unqualified references to the existing user-defined functions might start invoking the new or changed built-in functions instead of the user-defined functions. Db2 11 introduces the following built-in function changes:

Begin general-use programming interface information.

Table 1. New functions
Function name Description
ARRAY_AGG The ARRAY_AGG function returns an array in which each value of the input set is assigned to an element of the array.
ARRAY_DELETE The ARRAY_DELETE function deletes elements from an array.
ARRAY_FIRST The ARRAY_FIRST function returns the minimum array index value of an array.
ARRAY_LAST The ARRAY_LAST function returns the maximum array index value of an array.
ARRAY_NEXT The ARRAY_NEXT function returns the next larger array index value, relative to a specified array index value.
ARRAY_PRIOR The ARRAY_PRIOR function returns the next smaller array index value, relative to a specified array index value.
BLOCKING_THREADS The BLOCKING_THREADS function returns a table that contains one row for each lock or claim that threads hold against specified databases.
CARDINALITY The CARDINALITY function returns the number of elements in an array.
CHAR9 The CHAR9 function returns a fixed-length character string representation of the argument. The CHAR9 function is intended for compatibility with previous releases of Db2 for z/OS that depend on the result format that is returned for decimal input values in Version 9 and earlier.
Important: For portable applications that might run on platforms other than Db2 for z/OS, use the CHAR function instead. Other Db2 family products do not support the CHAR9 function.
MAX_CARDINALITY The MAX_CARDINALITY function returns the maximum number of elements that an array can contain.
MEDIAN The MEDIAN function returns the median of a set of numbers. This function can run only on an accelerator server.
TRIM_ARRAY The TRIM_ARRAY function deletes elements from the end of an ordinary array.
VARCHAR9 The VARCHAR9 function returns a fixed-length character string representation of the argument. The VARCHAR9 function is intended for compatibility with previous releases of Db2 for z/OS that depend on the result format that is returned for decimal input values in Version 9 and earlier.
Important: For portable applications that might run on platforms other than Db2 for z/OS, use the VARCHAR function instead. Other Db2 family products do not support the VARCHAR9 function.

End general-use programming interface information.

Actions to take

To continue to execute a user-defined function with the same name or signature as a new built-in function or signature, qualify the name of the existing user defined function in your application. For more information about Db2 resolves qualified and unqualified references to functions, see Function resolution.

SQLCODE changes

Some SQLCODE numbers and message text might have changed in Db2 11. Also, the conditions under which some SQLCODEs are issued might have changed. For more information, see New, changed, and deleted codes.

End general-use programming interface information.