SQL changes in Db2 11

This version of Db2 for z/OS® provides new and changed SQL statements.

New SQL statements in Db2 11

Begin general-use programming interface information.

Table 1. New SQL statements
SQL statement Description
CREATE TYPE (array) 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. For information about a clause with a changed default value, see Application and SQL release incompatibilities.

Begin general-use programming interface information.

Table 2. Changes to existing SQL statements
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. For more information, see Db2 environment, see ALTER statements that change limit keys are pending changes in Application and SQL release incompatibilities.
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 3. 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.

New reserved words in Db2 11

Db2 11 introduces the following SQL reserved words:

  • ARRAY
  • ARRAY_EXISTS
  • VERSIONING

For the complete list, see Reserved words.