V11R1 application compatibility level

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

When new function is activated in your Db2 12 environment, you can run individual applications with some of the features and behavior of Db2 11. That is, your applications can continue to experience V11R1 behavior after new function is activated in Db2 12 or later. Then, you can migrate each application to a new application compatibility value separately until all are migrated. If application compatibility level is set to V11R1 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.

For examples of newer SQL capabilities that cannot be used at application compatibility V11R1, see the following topics:

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

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.

A migrated Db2 12 environment behaves with V11R1 application compatibility until function level 500 or higher is activated.

The following table shows some features and functions that are controlled by application compatibility, and the results if you specify V11R1. If a behavior difference is traced, then the IFCID trace function code is shown.

Table 1. Behavior of V11R1 application compatibility
Feature or Function	Result with V11R1 application compatibility	IFCID 0376 trace function code
The POWER built-in function returns a result with the DOUBLE data type. The result is out of range.	SQLCODE -802	1201
CURRENT_SERVER or CURRENT_TIMEZONE is used as a column name or variable name.	SQLCODE -206	1204

SQL changes in application compatibility level V11R1

The following SQL capabilities are available in Db2 11 new-function mode or later for applications that run at application compatibility level V11R1 or higher.

Start of change Any attempt to use the capabilities in the following table at a lower application compatibility level than V11R1 results in an error condition, such as SQL code -4743 or others. For more restrictions that apply at lower application compatibility levels, see V11R1 application compatibility level. End of change

New SQL statements in Db2 11

Begin general-use programming interface information.

Table 2. New SQL statements in Db2 11
SQL statement	Description
CREATE	The CREATE TYPE (array) SQL statement defines an array type at the current server.
CREATE VARIABLE	The CREATE VARIABLE statement creates a global variable at the current server.
SET CURRENT ACCELERATOR	The SET CURRENT ACCELERATOR changes the value of the CURRENT ACCELERATOR special register.
SET CURRENT APPLICATION COMPATIBILITY	The SET CURRENT APPLICATION COMPATIBILITY statement changes the value of the CURRENT APPLICATION COMPATIBILITY special register.
SET CURRENT TEMPORAL BUSINESS_TIME	The SET CURRENT TEMPORAL BUSINESS_TIME statement changes the value of the CURRENT TEMPORAL BUSINESS_TIME special register.
SET CURRENT TEMPORAL SYSTEM_TIME	The SET CURRENT TEMPORAL SYSTEM_TIME statement changes the value of the CURRENT TEMPORAL SYSTEM_TIME special register.
SET assignment-statement	The SET `assignment-statement` statement is a reclassification of the documentation of the SET `host-variable` and SET `transition-variable` statements into a single statement.

End general-use programming interface information.

SQL statement changes in Db2 11

The following table shows the changes to existing SQL statements that applications can use in application compatibility level V11R1 or higher.

Begin general-use programming interface information.

Table 3. Changes to existing SQL statements in Db2 11
SQL statement	Description of enhancements and notes
ALTER FUNCTION (SQL scalar)	New clauses: BUSINESS_TIME SENSITIVE SYSTEM_TIME SENSITIVE ARCHIVE SENSITIVE APPLCOMPAT Changed clauses: `data-type`, `data-type2` can include `array-type-name`.
ALTER PROCEDURE (SQL native)	New clauses: BUSINESS_TIME SENSITIVE SYSTEM_TIME SENSITIVE ARCHIVE SENSITIVE APPLCOMPAT Changed clauses: `data-type` can include `array-type-name`.
ALTER TABLE	New clauses: DROP COLUMN ENABLE ARCHIVE DISABLE ARCHIVE Changed clauses: ALTER PARTITION clauses that change limit key values now result in pending definition changes.
ALTER TABLESPACE	Changed clauses: PCTFREE can now include FOR UPDATE `smallint`.
COMMENT	Changed clauses: `data-type` can include `array-type-name`.
CREATE FUNCTION (SQL scalar)	New clauses: BUSINESS_TIME SENSITIVE SYSTEM_TIME SENSITIVE ARCHIVE SENSITIVE APPLCOMPAT Changed clauses: `data-type` can include `array-type-name`.
CREATE INDEX	New clauses: INCLUDE NULL KEYS EXCLUDE NULL KEYS
CREATE PROCEDURE (external)	Changed clauses: `data-type` can include `array-type-name`.
CREATE PROCEDURE (SQL native)	New clauses: BUSINESS_TIME SENSITIVE SYSTEM_TIME SENSITIVE ARCHIVE SENSITIVE APPLCOMPAT Changed clauses: `data-type` can include `array-type-name`.
CREATE TABLESPACE	Changed clauses: PCTFREE can now include FOR UPDATE `smallint`.
DECLARE GLOBAL TEMPORARY TABLE	New clauses: LOGGED NOT LOGGED
DROP	Changed clauses: `data-type` can include `array-type-name`.
EXECUTE	Changed clauses: The object of the USING clause can be an SQL variable, SQL parameter, global variable, or host variable.
FETCH	Changed clauses: The object of the INTO clause can be a host variable, an SQL parameter, an SQL variable, a transition variable, or an array element.
GRANT (function or procedure privileges)	Changed clauses: `data-type` can include `array-type-name`.
GRANT (type or JAR privileges)	Changed clauses: The object of the TYPE clause can be a distinct type or an array type.
OPEN	Changed clauses: The object of the USING clause can be an SQL variable, SQL parameter, global variable, or host variable.
REVOKE (function or procedure privileges)	Changed clauses: `data-type` can include `array-type-name`.
REVOKE (type or JAR privileges)	Changed clauses: The object of the TYPE clause can be a distinct type or an array type.
SELECT INTO	Changed clauses: The object of the INTO clause can be a host variable, a global variable, an SQL parameter, an SQL variable, a transition variable, or an array element.
SET PATH	Changed clauses: The SYSTEM PATH now includes the schemas "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM".
SQL statement with subselect	Changed clauses: `collection-derived-table` is added to `table-reference` in the FROM clause of a `subselect`. Other changes: A user-defined function that is defined with MODIFIES SQL DATA can be invoked in a subselect.
VALUES INTO	Changed clauses: The object of the INTO clause can be a host variable, a global variable, an SQL parameter, an SQL variable, a transition variable, or an array element.

End general-use programming interface information.

New built-in functions in Db2 11

Db2 11 introduces new built-in functions that improve the power of the SQL language. The following table shows the new built-in functions.

Begin general-use programming interface information.

Table 4. New built-in functions in Db2 11
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.