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.

In Db2 13, 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 13, regardless of whether new function is activated. Then, you can migrate each application to a new application compatibility value separately until all are migrated. 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.

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: Start of changeFor 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 71
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. 81
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:
  1. Begin program-specific programming interface information.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.End program-specific programming interface information.