Incompatible changes for APPLCOMPAT levels in Db2 13

Identifying potential incompatible changes in APPLCOMPAT levels with the IFCID 0376 trace

You can run package level accounting or monitor traces with IFCID 0239 and review the QPACFLGS field, which indicates an SQL incompatible change. If a trace is started for IFCID 0376, and the application runs at a lower APPLCOMPAT level, details about features and functions that have a change in behavior at higher APPLCOMPAT levels are written in field QW0376FN.

Descriptions of potential incompatible changes that for SQL statements that start running at higher APPLCOMPAT levels are also available in the IFCID 0376 section of the DSNWMSGS flat file. The most current version of DSNWMSGS is available in an IBM® Support document. See Db2 13 for z/OS® IFCID flat file (DSNWMSGS)

Summary of incompatible changes for APPLCOMPAT levels

The following table summarizes incompatible changes that applications might encounter when you first run them at higher APPLCOMPAT levels in Db2 13. The APPLCOMPAT level column indicates the lowest application compatibility level at which applications might encounter the new behavior described in the APPLCOMPAT level new behavior column.

V10R1 is the lowest supported APPLCOMPAT level, so the Previous deprecated behavior column for rows with this value lists other settings, such as subsystem parameters, that enable applications to continue with the deprecated behavior until you can make appropriate adjustments. The QW0376FN trace code column contains the function code that is returned if an IFCID 0376 is enabled when applications run at lower APPLCOMPAT levels.

Incompatible change details for APPLCOMPAT levels in Db2 13

The following entries provide more details for incompatible changes that applications might encounter when you first run them at higher APPLCOMPAT levels in Db2 13.

New default values in existing rows for added ROW CHANGE TIMESTAMP columns

Starting at APPLCOMPAT level V13R1M503 or higher, Db2 uses a constant default value when a new ROW CHANGE TIMESTAMP column is added. When processing ALTER TABLE statements that specify ADD COLUMN for ROW CHANGE TIMESTAMP columns, Db2 now sets the corresponding value in the DEFAULTVALUE column value in the SYSIBM.SYSCOLUMNS catalog table to the timestamp of the ALTER TABLE statement. (Note that CREATE TABLE processing to define a ROW CHANGE TIMESTAMP column does not set the DEFAULTVALUE column value.)

Before this change, Db2 derives the default values for existing rows from the page header from the row, which is the RBA for standalone Db2 subsystems. In data sharing, the default is derived is based on an internal mapping table between the LRSN and a timestamp. As a result, inserts, deletes, or updates to any rows in a page can change the derived default row change timestamp column values for unchanged rows, leading to unpredictable results.

For more information, see the following related topics:

• ROW CHANGE expression
• SYSCOLUMNS catalog table
• ALTER TABLE statement
• PH51185 (functional code APAR)

Starting at function level 503 or higher, redirected recovery processing is also updated to allow different ROW CHANGE TIMESTAMP column default values between source and target tables. For more information, see Running a redirected recovery.

CREATE TABLESPACE uses MAXPARTITIONS 254 by default

At APPLCOMPAT level V13R1M500 or higher, CREATE TABLESPACE statements use MAXPARTITIONS 254 by default.

When MAXPARTITIONS 256 is explicitly specified, the default DSSIZE varies from 4 G to 32 G depending on the page size. However, starting with application compatibility level V12R1M504, when MAXPARTITIONS is not explicitly specified, Db2 12 use MAXPARTITIONS 256 by default, but the default DSSIZE is always 4 G regardless of the page size.

This apparent inconsistency avoided a risk of failure for existing statements, where the default data set size might be greater than 4 G depending on the page size. The statements might fail with the SQLCODE -904 error with the 00D70008 reason code if the data sets for the table space are not associated with a DFSMS data class that is specified with extended format and extended addressability.

With MAXPARTITIONS 254 as the default, the result is now consistent regardless of whether MAXPARTITONS is explicitly specified. The calculated default DSSIZE is always 4 G.

This change is potentially an incompatible change when applications first start running at APPLCOMPAT level V13R1M500 or higher. For more information about changes like this, see Incompatible changes for APPLCOMPAT levels in Db2 13.

See the MAXPARTITIONS and DSSIZE descriptions in CREATE TABLESPACE statement.

New built-in function names supported by pass-through to IBM Db2 Analytics Accelerator

Starting in APPPLCOMPAT level V12R1M507 or higher, the following built-in functions are supported passthrough-only built-in functions through IBM Db2 Analytics Accelerator. If your Db2 applications have unqualified references to existing user-defined functions with these names, they might start invoking new built-in functions instead if the names and signatures match, in certain situations.

• ADD_DAYS scalar function
• BTRIM scalar function
• DAYS_BETWEEN scalar function
• NEXT_MONTH scalar function
• Regression functions (REGR_AVGX, REGR_AVGY, REGR_COUNT, ...)
• ROUND_TIMESTAMP scalar function if invoked with a date expression

Actions to take: See Ensuring that Db2 executes the intended user-defined function.

Explicitly created table space changes

Starting in APPLCOMPAT level V12R1M506 or higher, the following changes take effect:

• Dropping a table that resides in an explicitly created universal table space no longer returns an error. Instead, the table space is implicitly dropped.
• Dropping an auxiliary table that resides in an explicitly created LOB table space no longer leaves the LOB table space in the database. Instead, the table space is implicitly dropped.

Actions to take:

• Any existing applications that use the DROP TABLESPACE statement to drop a universal table space with a table can now be changed to use the DROP TABLE statement instead.
• If an auxiliary table is dropped, any applications or tools that expect the LOB table space to remain for reuse must be modified accordingly.

New built-in function names supported by pass-through to IBM Db2 Analytics Accelerator

Starting in APPPLCOMPAT level V12R1M504 or higher, the following built-in functions are supported passthrough-only built-in functions through IBM Db2 Analytics Accelerator. If your Db2 applications have unqualified references to existing user-defined functions with these names, they might start invoking new built-in functions instead if the names and signatures match, in certain situations.

• CUME_DIST
• CUME_DIST aggregate function
• FIRST_VALUE
• LAG
• LAST_VALUE
• LEAD
• NTH_VALUE
• NTILE
• PERCENT_RANK
• PERCENT_RANK aggregate function
• RATIO_TO_REPORT
• REGEXP_COUNT scalar function
• REGEXP_INSTR scalar function
• REGEXP_LIKE scalar function
• REGEXP_REPLACE scalar function
• REGEXP_SUBSTR scalar function

Actions to take: Ensuring that Db2 executes the intended user-defined function.

CURRENT_TIMEZONE and CURRENT_SERVER in column names

Starting in APPPLCOMPAT level V12R1M504 or higher, if your Db2 environment contains existing columns or variables with names such as CURRENT_TIMEZONE or CURRENT_SERVER, you must modify your applications to delimit these column names.

Actions to take: You can use the following queries to identify columns and variables with the names.

Object type	Example query
Columns	SELECT * FROM SYSIBM.SYSCOLUMNS WHERE NAME IN('CURRENT_SERVER','CURRENT_TIMEZONE');
Variables	SELECT * FROM SYSIBM.SYSVARIABLES WHERE NAME IN('CURRENT_SERVER','CURRENT_TIMEZONE');

Based on the result, you can then check for dependencies, for example by querying the SYSIBM.SYSPACKDEP catalog table, to identify any static applications that depend on the nondelimited forms of these names.

Creation of deprecated objects prevented

Starting with APPPLCOMPAT level V12R1M504 or higher, SQL statements that explicitly or implicitly create table spaces for base tables always create always create universal table spaces (UTS), which are either partition-by-growth (PBG) or partition-by-range (PBR) table spaces. Also, errors can be returned for certain SQL statements that previously succeeded, as side effects of base tables always being created in a UTS.

• CREATE AUX TABLE statements for LOB columns can start failing with the SQLCODE -769 error because the PART clause is required when the base table resides in a PBG table space.
• DROP TABLE statements can fail with the SQLCODE -669 error because the DROP TABLE statement is not supported for tables in PBG table spaces. The DROP TABLESPACE statement must be used instead. (APPLCOMPAT level V12R1M506 resolves this situation.)
• CREATE TABLE statements can start failing with the SQLCODE -646 error if they attempt to create more than one table in a the same table space. Each UTS contains only a single table.

Starting in APPLCOMPAT level 504 or higher, the following SQL statements return errors instead of creating deprecated objects:

• CREATE SYNONYM statements
• CREATE TABLE statements that specify existing non-UTS table spaces
• CREATE TABLE and ALTER TABLE statements that specify ORGANIZE BY HASH

Actions to take: Update your applications to use non-deprecated object types.

Tip: Although it is best to use non-deprecated types for all new database objects, you can still use a lower APPLCOMPAT level to create the deprecated table spaces if necessary, such as for recovery situations. For more information, see Creating non-UTS table spaces (deprecated).

Result change for system-period temporal tables defined with ON DELETE ADD EXTRA ROW

Starting at application compatibility level V12R1M503 or higher, rows that contain a null value in the history table column that corresponds to a DATA CHANGE OPERATION column in a system-period temporal table are considered part of the intermediate result of a query that contains a period specification, even if the system-period temporal table is defined with the ON DELETE ADD EXTRA ROW attribute. Queries that run at lower application compatibility levels continue to omit the rows that contain null values from the intermediate result set and might return the incomplete results.

The behavior change occurs for queries that run at application compatibility level V12R1M503 or higher if all the following conditions are met:

• The query references a system-period temporal table (STT) with FOR SYSTEM_TIME FROM value1 TO value2 (or BETWEEN value1 AND value2) period specification. The STT can be directly referenced or indirectly referenced through views.
• The STT is defined with the ON DELETE ADD EXTRA ROW clause.
• The STT is defined with the DATA CHANGE OPERATION column.
• During migration to use the temporal auditing feature, the altered table contains existing rows.
• The DATA CHANGE OPERATION column is populated with the default NULL value for the existing rows.
• When the query is executed, the historical table contains records with NULL values in the column associated with a DATA CHANGE OPERATION column in the STT.

Actions to take: Start a trace for IFCID 0376. In the trace output, function code 1215031 or 1202 identifies the affected queries.

The SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY stored procedure is not supported when APPLCOMPAT is V12R1M503 or higher

Starting at application compatibility level V12R1M503 or higher, Db2 returns an error for any user or application that invokes the SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY stored procedure.

Actions to take: Start a trace for IFCID 0376. In the trace output, function code 1215032 or 1203 identifies applications that use the SYSPROC.SET_MAINT_MODE_RECORD_NO_TEMPORALHISTORY stored procedure. Modify such applications to set value of the SYSIBMADM.REPLICATION_OVERRIDE built-in global variable to 'Y' instead. For more information, see SYSIBMADM.REPLICATION_OVERRIDE built-in global variable.

GRAPHIC and VARGRAPHIC supported as built-in functions

Starting at APPLCOMPAT level V12R1M502 or higher, if your environment contains existing user-defined functions named GRAPHIC or VARGRAPHIC, applications with unqualified references to the existing user-defined functions might start invoking the built-in function instead of the user-defined function in certain situations.

Actions to take: See Ensuring that Db2 executes the intended user-defined function.

SUBSTR built-in function change for invalid input and the SQLCODE -138 error

Starting in function level V12R1M500 or higher (and not controlled by the APPLCOMPAT level), the SUBSTR_COMPATIBILITY subsystem parameter specifies whether calls to the SUBSTR built-in function consistently return the SQLCODE -138 error for invalid arguments that do not meet the requirements described in SUBSTR scalar function. That is, if SUBSTR_COMPATIBILITY is set to CURRENT, Db2 always enforces the documented rules for the SUBSTR built-in function and returns an SQL error code for invalid input. For more information about rules for valid input the SUBSTR built-in function, see . The behavior with the CURRENT setting improves compatibility with Db2 on other platforms.

With the default SUBSTR_COMPATIBILITY set to PREVIOUS, Db2 sometimes incorrectly returns a result for invalid input instead of issuing an appropriate error message. The default value is PREVIOUS. For more information, see SUBSTR COMPATIBILITY field (SUBSTR_COMPATIBILITY subsystem parameter).

For more information about the introduction of this change in Db2 12, see the APAR closing text for PH36071.

Actions to take: Before you set the SUBSTR_COMPATIBILITY subsystem parameter to CURRENT, you might need to modify some of your applications to handle this change. If you enable a trace for IFCID 0376, the function identifier '14' value in the QW0376FN field of the trace output identifies applications that might be affected by this change.

KEEPDYNAMIC(YES) bind option support for ROLLBACK

Starting at APPLCOMPAT level V12R1M500 or higher, the KEEPDYNAMIC(YES) bind option affects both COMMIT and ROLLBACK statements. With KEEPDYNAMIC(YES), the dynamic SQL statements in the package are retained after COMMIT or ROLLBACK, and those statements can run again without another PREPARE. This changes introduces the following differences:

• An OPEN statement without a preceding PREPARE statement does not receive the SQLCODE -514 error.
• An EXECUTE statement without a preceding PREPARE statement does not receive the SQLCODE -518 error.

At lower APPLCOMPAT levels, the KEEPDYNAMIC(YES) bind option applies only to COMMIT statements. After a ROLLBACK statement, another PREPARE is required so that the dynamic SQL statements can run. That is, a package that was bound with KEEPDYNAMIC(YES) was required to prepare dynamic SQL statements again after a ROLLBACK was issued.

Actions to take: Review packages that use the KEEPDYNAMIC(YES) bind option. You can make dynamic SQL programs that are bound with KEEPDYNAMIC(YES) run more efficiently by removing PREPARE statements that prepare SQL statements again following execution of ROLLBACK statements. Do not take this action until you are certain that you no longer need to run the programs at APPLCOMPAT level V11R1 or earlier. For more information, see: Methods for keeping prepared statements after the point of commit or rollback and KEEPDYNAMIC bind option.

POWER built-in function returns a warning SQLCODE for out of range DOUBLE results

Starting at APPLCOMPAT level V12R1M500 or higher, calls to the POWER® built-in function with DOUBLE arguments with results that are out of range return the SQLCODE +802 warning in some cases. For example, the following query returns the SQLCODE +802 warning:

SELECT POWER(DOUBLE(2.0E38), DOUBLE(2.0))
 FROM SYSIBM.SYSDUMMY1;

In lower APPLCOMPAT levels, Db2 issues -802 in these situations.

Actions to take: Adjust error processing to account for the change in the returned SQLCODE from an error to a warning. If you enable a trace for IFCID 0376, the function identifier '1201' value in the QW0376FN field of the trace output identifies applications that might be affected by this change.

CAST(string AS TIMESTAMP) result changes

Starting with APPLCOMPAT level V11R1 or higher, the result of CAST(string AS TIMESTAMP) is changed in some cases. That is, 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. An invalid representation of an 8-byte or 13-byte string in CAST(string AS TIMESTAMP) results in the SQLCODE -180 error. For example, the result of the first following SELECT statement is 2013-01-01-00.00.00.000000, which is the correct result. The result of the second SELECT statement is the SQLCODE -180 error, because a Store Clock value is not valid input to CAST(string AS TIMESTAMP).

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

In lower APPLCOMPAT levels, when Db2 executed CAST(string AS TIMESTAMP), Db2 interprets 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. For example, the result 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.

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

Actions to take: If you enable a trace for IFCID 0376, the function identifier '1109' value in the QW0376FN field of the trace output identifies applications that might be affected by this change.

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, the following statements return the correct result, 2013-01-01-20.37.04.246928.

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

CURRENT CLIENT special register lengths increased

Starting with APPLCOMPAT level V11R1 or higher, the following special register values might return values of greater length, the values are not padded to the supported maximum length, and any trailing blanks are removed.

Special register	New maximum length	Previous maximum length	QW0376FN function code
CURRENT CLIENT_ACCTNG	255 bytes	200 bytes	'1104'
CURRENT CLIENT_APPLNAM	255 bytes	32 bytes	'1105'
CURRENT CLIENT_USERID	128 bytes	16 bytes	'1106'
CURRENT CLIENT_WKSTNAME	255 bytes	18 bytes	'1107'

Actions to take: If you enable a trace for IFCID 0376, the following function level identifier values in the QW0376FN field of the trace output identify applications that might be affected by this change, as shown in the preceding table: '1104', '1105', '1106', or '1107'.

ASUTIME limit changes for RLF enforcement of dynamic SQL

Starting with APPLCOMPAT level V11R1 or higher, the resource limit facility (RLF) enforces the ASUTIME limit that is specified for each package when multiple packages are called in the same thread, and the ASUTIME limit that is specified for the top-level calling package is not considered. If the dynamic SQL statements in a package use more ASUTIME than the limit specified for that package, Db2 returns the SQLCODE -905 error. Because the limit is enforced for each package, applications might return more the SQLCODE -905 errors more often.

Also, the APPLCOMPAT level of each package controls the new behavior regardless of the APPLCPOMPAT level of a calling package. That is, the RLF enforces the ASUTIME limit specified for the dynamic SQL statements in any package that is itself bound out APPLCOMPAT level V11R1 or higher, even if it was called by a package that was bound at V10R1.

At lower APPLCOMPAT level V10R, Db2 issues the SQLCODE -905 error only when the limit of the top-level calling package for the entire threads is encountered.

Actions to take: Review ASUTIME limits on routines and packages that use dynamic SQL and adjust as necessary. If you enable a trace for IFCID 0376, the function identifier '1103' value in the QW0376FN field of the trace output identifies applications that might be affected by this change.

XPath processing continues with errors on filtered results

Starting with APPLCOMPAT level V11R1 or higher, XPath processing might return fewer errors on predicate expressions with an explicit cast or an operation with an invalid value. Examples of XPath expressions that are likely to return fewer errors include the following situations:

• 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

For more information about using XPath expressions, see XPath expressions for filtering output and Selecting XML data.

Actions to take: Review error processing for applications that use XPath processing. If you enable a trace for IFCID 0376, the function identifier '1102' value in the QW0376FN field of the trace output identifies applications that might be affected by this change.

XML document nodes implicitly added for insert and update of XML columns

Starting with APPLCOMPAT V11R1 or higher, when an application inserts or updates an XML column, Db2 implicitly adds an XML document node if the XML document does not already contain one.

At lower function levels, Db2 issues the SQLCODE -20345 error in this situation, unless the application also invokes the XMLDOCUMENT function before the insert or update.

Actions to take: Review your applications for use of the XMLDOCUMENT function. If you enable a trace for IFCID 0376, the function identifier '1101' value in the QW0376FN field of the trace output identifies applications that might be affected by this change.