SQL changes in Db2 11
This version of Db2 for z/OS® provides new and changed SQL statements.
New SQL statements in Db2 11
| 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. |
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.
| SQL statement | Description of enhancements and notes |
|---|---|
| ALTER FUNCTION (SQL scalar) |
|
| ALTER PROCEDURE (SQL native) |
|
| ALTER TABLE |
|
| ALTER TABLESPACE |
|
| COMMENT |
|
| CREATE FUNCTION (SQL scalar) |
|
| CREATE INDEX |
|
| CREATE PROCEDURE (external) |
|
| CREATE PROCEDURE (SQL native) |
|
| CREATE TABLESPACE |
|
| DECLARE GLOBAL TEMPORARY TABLE |
|
| DROP |
|
| EXECUTE |
|
| FETCH |
|
| GRANT (function or procedure privileges) |
|
| GRANT (type or JAR privileges) |
|
| OPEN |
|
| REVOKE (function or procedure privileges) |
|
| REVOKE (type or JAR privileges) |
|
| SELECT INTO |
|
| SET PATH |
|
| SQL statement with subselect |
|
| VALUES INTO |
|
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.
| 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.
|
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.