V10R1 application compatibility level

When you set the application compatibility level to V10R1, applications that attempt to use functions and features that are introduced in Db2 11 or later might behave differently or receive an error.

Start of change In Db2 12, you can continue to run individual applications with some of the features and behavior of DB2® 10. That is, your applications can continue to experience V10R1 behavior while in Db2 12, regardless of whether new function is activated. Then, you can migrate each application to a new application compatibility value separately until all are migrated. End of change If application compatibility is set to V10R1 and you attempt to use the new functions of a later version, SQL might behave differently or result in a negative SQL codes, such as -4743 and others.

Begin program-specific programming interface information.

You can run package level accounting or monitor traces with IFCID 0239 and review field QPACINCOMPAT, which indicates an SQL incompatible change. If a trace is started for IFCID 0376, and application compatibility is set for a previous version, details about features and functions that have a change in behavior are written in field QW0376FN.

End program-specific programming interface information.

Start of change A migrated Db2 12 environment behaves with V11R1 application compatibility until function level 500 or higher is activated. End of change Application and SQL incompatibilities are described in the migration information for each version.

The following table shows examples of many of the new capabilities of Db2 11 features and functions that are controlled by application compatibility, and the results if you specify V10R1. If a behavior difference is traced, then the IFCID trace function code is shown.

Also, the new SQL capabilities of later Db2 releases cannot be used at application compatibility level V10R1. For lists of these SQL capabilities see:

Tip:

For best results, configure your development environment to use the lowest application compatibility level that the application will run at in the production environment. For dynamic SQL, remember to consider the application compatibility levels of client and NULLID packages. If you develop and test applications at a higher application compatibility level and try to run them at a lower level in production, you are likely to encounter SQL code -4743 and other errors when you deploy the applications to production. End of change

Table 1. Behavior of V10R1 application compatibility
Feature or Function	Result with V10R1 application compatibility	IFCID 0376 trace function code
An SQL statement in a client application includes an unsupported conversion (from a string type to a numeric type or from a numeric type to a string type), and implicit casting is disabled (DDF_COMPATIBILITY is set to SP_PARMS_NJV or to DISABLE_IMPCAST_NJV).	SQLCODE -301	7¹
A client application executes an SQL CALL statement to execute a Db2 for z/OS® stored procedure. The DDF_COMPATIBILITY subsystem parameter is set to SP_PARMS_NJV for client applications other than Java™ applications, or SP_PARMS_JV for Java applications.	The data types of the data that is returned from the SQL CALL statement match the data types of the CALL statement arguments. This behavior is compatible with the behavior before Version 10.	8¹
A client application accesses Db2 11 from an IBM® Data Server Driver for JDBC and SQLJ client. The DDF_COMPATIBILITY subsystem parameter is set to IGNORE_TZ for Java applications.	The Db2 server ignores the TIMEZONE portion, appended by the IBM Data Server Driver for JDBC and SQLJ, of the value in the TIMESTAMP WITH TIMEZONE input to a TIMESTAMP target. This behavior is compatible with the behavior before DB2 10.	9
BIF_COMPATIBILITY is set to V9_TRIM, and input `string-expression` is EBCDIC mixed data for the RTRIM, LTRIM, or STRIP built-in function.	The DB2 9 version of SYSIBM.LTRIM(`string-expression`), SYSIBM.RTRIM(`string-expression`), or SYSIBM.STRIP(`string-expression`) is executed.	10
An implicit insert or update of an XML document node	SQLCODE -20345	1101
A predicate expression with an explicit cast or an operation with an invalid value that does not affect the results of XPath processing	SQLCODE -20345	1102
How the resource limit facility uses ASUTIME value for nested routines	SQLCODE -905 is issued only when the ASUTIME limit of the top-level calling package is encountered.	1103
The lengths of values that are returned from CURRENT CLIENT_USERID, CURRENT CLIENT_WRKSTNNAME, CURRENT CLIENT_APPLNAME, or CURRENT CLIENT_ACCTNG special register are longer than the DB2 10 limits.	The special register values are truncated to the DB2 10 maximum lengths and padded with blanks	1104, 1105, 1106, 1107
A CAST(string as TIMESTAMP) specification with an input string of length of 8 or an input string of length 13	An explicit cast specification from string as TIMESTAMP interprets an 8-byte character string as a Store Clock value and a 13-byte string as a GENERATE_UNIQUE value. CAST result might be incorrect.	1109
Invocation of the SPACE or VARCHAR built-in function when the result is defined as VARCHAR(32765), VARCHAR(32766), or VARCHAR(32767)	No error	1110, 1111
Subsystem parameter XML_RESTRICT_EMPTY_TAG is set to YES, and an empty XML element is serialized as <`emptyElement`></`emptyElement`>	No error	1112
Specification of bind option DBPROTOCOL(DRDACBF)	DSNT298I
A period specification that follows the name of a view in the FROM clause of a query	SQLCODE -4743
A period clause that follows the name of a target view in an UPDATE or DELETE statement	SQLCODE -4743
A SET CURRENT TEMPORAL SYSTEM_TIME statement	SQLCODE -4743
A SET CURRENT TEMPORAL BUSINESS_TIME statement	SQLCODE -4743
A reference to a global variable	SQLCODE -4743
Use of array operations and built-in functions such as Use of the UNNEST collection-derived-table Use of the ARRAY_FIRST, ARRAY_LAST, ARRAY_NEXT, ARRAY_PRIOR, ARRAY_AGG, TRIM_ARRAY, CARDINALITY, MAX_CARDINALITY built-in functions A SET assignment-statement of an array element as a target table A CAST specification with a parameter marker as the source and an array as the data type	SQLCODE -4743
An aggregate function that contains the keyword DISTINCT and references a column that is defined with a column mask	SQLCODE -20478
An SQL statement contains the GROUP BY clause and references a column that is defined with a column mask	SQLCODE -20478
An SQL statement contains the set operator UNION ALL or UNION DISTINCT and references a column that is defined with a column mask	SQLCODE -20478
A reference to an alias for a sequence object	SQLCODE -4743
A reference to an unqualified sequence that is not resolved to a public alias	SQLCODE -204
A SELECT with a table function reference that includes a typed correlation clause	SQLCODE -4743
A `table-reference`, `collection-derived-table`, or `xmltable-expression` that does not include a correlation-clause.	SQLCODE -4743
A CALL statement that specifies an autonomous procedure	SQLCODE -4743
The following datetime assignments: A valid string representation of a timestamp to a date column A valid string representation of a timestamp to a time column A valid string representation of a date to a timestamp column	SQLCODE -180
Notes: To find details about the incompatible parameters, examine the contents of fields QW0376SC_Var, QW0376PR_Var, and QW0376INC_Var. See the DSNWMSGS file for more information.