SQL changes in Db2 12 application compatibility levels
You can use this information to plan for SQL changes in Db2 12 application compatibility levels.
SQL statement changes in Db2 12 function level 501 and higher
The following SQL changes take effect in Db2 12 function level 501 or higher for applications that run at the specified application compatibility level or higher.
Any attempt to use the capabilities in the following table at a lower application compatibility than the specified level results in an error condition, such as SQL code -4743 or others.
APPLCOMPAT level | SQL element | Change introduced | Incompatible change? |
---|---|---|---|
V12R1M509 | ALTER TABLESPACE | The following new clauses are added:
|
No |
V12R1M509 | CREATE TABLE | The following new clauses are added:
IN ACCELERATOR can now specify an alias that represents multiple accelerators. |
No |
V12R1M509 | CREATE TABLESPACE | The following new clauses are added:
|
No |
V12R1M509 | DELETE | A restriction against specifying FOR PORTION OF BUSINESS_TIME if the target table is a parent in a temporal referential constraint is removed. | No |
V12R1M508 | ALTER TABLESPACE | New clause: MOVE TABLE. | No |
V12R1M507 | CREATE PROCEDURE (external) | The following new clauses are added:
|
No |
V12R1M507 | CREATE PROCEDURE (SQL native) | The following new clauses are added:
|
No |
V12R1M506 | DROP | Changed clause: TABLE. | No |
V12R1M505 | ALTER TABLE | The following clauses are now supported for DECFLOAT columns: PRIMARY KEY and UNIQUE. | No |
V12R1M505 | CREATE INDEX | column-name can now specify DECFLOAT columns. | No |
V12R1M505 | CREATE TABLE | The following clauses now support DECFLOAT columns: PRIMARY KEY and UNIQUE. | No |
V12R1M505 | CREATE TRIGGER (basic) | Changed clause: WHEN search-condition. | No |
V12R1M505 | CREATE TRIGGER (advanced) | Changed clause: WHEN search-condition. | No |
V12R1M503 | ALTER TABLE | New clause: ON DELETE ADD EXTRA ROW. | No |
V12R1M502 | ALTER STOGROUP statement | New clause: KEY LABEL. | No |
V12R1M502 | ALTER TABLE | New clause: KEY LABEL. | No |
V12R1M502 | CREATE STOGROUP | New clause: KEY LABEL. | No |
V12R1M502 | CREATE TABLE | New clause: KEY LABEL. | No |
SQL statement changes in Db2 12 function level 500
The following SQL changes take effect in Db2 12 for applications that run at application compatibility V12R1M500 or higher.
Any attempt to use SQL capabilities in the following table at a lower application compatibility than V12R1M500 results in an error condition, such as SQL code -4743 or others.
SQL element | Change introduced | Incompatible change? |
---|---|---|
ALTER FUNCTION (compiled SQL scalar) | New clause: CONCENTRATE STATEMENTS. | No |
ALTER INDEX | A new clauses are added:
The following clauses are changed:
Altering to use index compression for indexes in universal table spaces is now a pending change that places the index in advisory REORG-pending (AREOR) status |
Yes |
ALTER PROCEDURE (SQL native) | New clause: CONCENTRATE STATEMENTS. | No |
ALTER TABLE | The following new clauses are added:
The following clauses are changed:
|
No |
ALTER TABLESPACE | The following new clauses are added:
The following clauses are changed:
|
No |
ALTER TRIGGER statement (advanced trigger) | New statement. | No |
ALTER TRIGGER (basic) | Equivalent to ALTER TRIGGER in prior releases. | No |
COMMENT statement | Changed clause: TRIGGER trigger-name VERSION trigger-version-id. | No |
CREATE FUNCTION (compiled SQL scalar) | The following new clauses are added:
|
No |
CREATE FUNCTION (inline SQL scalar) | New clause: WRAPPED. | No |
CREATE FUNCTION (SQL table) | New clause: WRAPPED. | No |
CREATE INDEX | New clause: DSSIZE. CREATE INDEX statements that omit the USING clause at the table space or index level now fail with SQLCODE -204, if the storage group specified when the containing database was created does not exist. |
Yes |
CREATE PROCEDURE (SQL native) | The following new clauses are added:
|
No |
CREATE TABLE | The following new clauses are added:
The following clauses are changed:
|
No |
CREATE TABLESPACE |
The following new clauses are added:
The following clauses are changed:
CREATE TABLESPACE statements that omit the USING clause at the table space or index level now fail with SQLCODE -204, if the storage group specified when the containing database was created does not exist. |
Yes |
CREATE TRIGGER (basic) | New clause: WRAPPED. | No |
CREATE TRIGGER (advanced) | New statement. | No |
CREATE VARIABLE | Changed clause: data-type. | No |
DELETE | The following new clauses are added:
|
No |
EXECUTE | Changed clause: USING. | No |
EXECUTE IMMEDIATE statement | Changed clause: variable. | No |
EXPLAIN | New clause: STABILIZED DYNAMIC QUERY STMTID. When Db2 processes the SQL statement EXPLAIN PACKAGE or EXPLAIN STABILIZED DYNAMIC QUERY, the HINT_USED column in the PLAN_TABLE is populated with |
Yes |
FETCH | New clause: target-variable. | No |
fullselect | New clause: offset-clause. The following clauses are changed: |
No |
GRANT statement (table or view privileges) | New clause: UNLOAD. | No |
GRANT statement (system privileges) | New clause: BINDAGENT. | No |
MERGE |
The MERGE statement now includes the delete operation as part of the merge process. The following new clauses are added:
The following clauses are changed:
|
No |
OPEN | Changed clause: USING. | No |
PREPARE statement | New clause: offset-clause. Changed clause: fetch-clause. |
No |
SELECT INTO statement | New clause: offset-clause The following clauses are changed:
|
No |
SET assignment-statement statement | Changed clause: DEFAULT. | No |
subselect | New clause: offset-clause The following clauses are changed:
|
No |
TRANSFER OWNERSHIP statement | New statement. | No |
UPDATE statement | New clause: BETWEEN value-1 AND value-2 clause of the period-clause. | No |
VALUES INTO | The following clauses are changed:
|
No |
compound-statement for SQL routines | New clause: ATOMIC. Changed clause: DEFAULT or CONSTANT. |
No |
For more information about these changes, see Function level 500 (installation or migration - October 2016).
SQL changes in Db2 12 function level 100
The following SQL changes take effect in Db2 12 for applications that run at application compatibility V12R1M100 or higher.
Any attempt to use SQL capabilities in the following table at a lower application compatibility than V12R1M100 results in an error condition, such as SQL code -4743 or others.
SQL element | Change introduced | Incompatible change? |
---|---|---|
REVOKE statement (table or view privileges) | New clause: UNLOAD. | No |
For more information about these changes, see Function level 100 (migration).
Special register changes
Db2 12 introduces the following changes to special registers:
- FL 504 New alternative spelling for certain special registers, as shown in the following table.
Existing Special Register New Syntax Alternative CURRENT CLIENT_ACCTNG CLIENT ACCTNG CURRENT CLIENT_APPLNAME CLIENT APPLNAME CURRENT CLIENT_USERID CLIENT USERID CURRENT CLIENT_WRKSTNNAME CLIENT WRKSTNNAME CURRENT SERVER CURRENT_SERVER CURRENT TIME ZONE CURRENT TIMEZONE
CURRENT_TIMEZONE
Predicate changes
Db2 12 introduces the following changes to SQL predicates:
- FL 504 Alternative spellings for ISNULL and NOT NULL predicates, as shown in the following table.
Existing Predicate New Syntax Alternative IS NULL ISNULL IS NOT NULL NOTNULL - FL 500 Predicates involving row-value-expressions can use the following additional comparison operators: <, >, <=, and >=. See Basic predicate for details.
New and changed built-in functions
Db2 12 introduces or changes the following built-in functions.
APPLCOMPAT level | Function name | Change introduced | Incompatible change? |
---|---|---|---|
V12R1M507 | Various | The following functions are newly supported in Db2 for z/OS as passthrough-only expressions, which are passed through to IBM Db2 Analytics Accelerator for z/OS. | No |
V12R1M506 | HASH scalar function | New built-in function. | No |
V12R1M506 | CHARACTER_LENGTH or CHAR_LENGTH scalar function | CHAR_LENGTH is now supported as an alternative function name. | No |
V12R1M506 | CLOB or TO_CLOB scalar function | TO_CLOB is now supported as an alternative function name. | No |
V12R1M506 | COVAR_POP or COVARIANCE or COVAR aggregate function | COVAR_POP is now supported as an alternative function name. | No |
V12R1M506 | LEFT or STRLEFT scalar function | STRLEFT is now supported as an alternative function name. | No |
V12R1M506 | POWER or POW scalar function | POW is now supported as an alternative function name. | No |
V12R1M506 | POSSTR or STRPOS scalar function | STRPOS is now supported as an alternative function name. | No |
V12R1M506 | RANDOM or RAND scalar function | RANDOM is now supported as an alternative function name. | No |
V12R1M506 | RIGHT or STRRIGHT scalar function | STRRIGHT is now supported as an alternative function name. | No |
V12R1M506 | TIMESTAMP_FORMAT or TO_TIMESTAMP scalar function | TO_TIMESTAMP is now supported as an alternative function name. | No |
V12R1M505 | DECRYPT_DATAKEY_INTEGER, DECRYPT_DATAKEY_BIGINT, DECRYPT_DATAKEY_DECIMAL, DECRYPT_DATAKEY_VARCHAR, DECRYPT_DATAKEY_CLOB, DECRYPT_DATAKEY_VARGRAPHIC, DECRYPT_DATAKEY_DBCLOB, and DECRYPT_DATAKEY_BIT | New built-in functions. | No |
V12R1M505 | ENCRYPT_DATAKEY | New built-in function. | No |
V12R1M504 | Various | The following functions are newly supported in Db2 for z/OS as passthrough-only expressions, which are passed through to IBM Db2 Analytics Accelerator for z/OS. | No |
V12R1M502 | GRAPHIC scalar function | The first argument now accepts numeric data types, including SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE, FLOAT, and DECFLOAT. | No |
V12R1M502 | VARGRAPHIC scalar function | The first argument accepts numeric data types, including SMALLINT, INTEGER, BIGINT, DECIMAL, REAL, DOUBLE, FLOAT, and DECFLOAT. | No |
V12R1M501 | LISTAGG aggregate function | New built-in function. | No |
V12R1M500 | ARRAY_AGG aggregate function | Newly supported built-in function when used for associative array aggregation. | No |
V12R1M500 | GENERATE_UNIQUE_BINARY | New built-in function. | No |
V12R1M500 | HASH_CRC32, HASH_MD5, HASH_SHA1, and HASH_SHA256 | New built-in functions. | No |
V12R1M500 | LOWER scalar function | The following locales can now be specified:
|
|
V12R1M500 | PERCENTILE_CONT | New built-in function. | No |
V12R1M500 | PERCENTILE_DISC | New built-in function. | No |
V12R1M500 | TRANSLATE scalar function | The following locales can now be specified:
|
No |
V12R1M500 | UPPER scalar function | The following locales can now be specified:
|
No |
V12R1M500 | WRAP scalar function | New built-in function. | No |
V12R1M100 | BLOCKING_THREADS table function | New built-in function. |
New and changed Db2-supplied stored procedures
Db2 12 introduces or changes the following Db2-supplied stored procedures:
- FL 500 CREATE_WRAPPED stored procedure is new
- FL 500 DSNUTILV stored procedure is new
- FL 500 DSNACCOX stored procedure: Several of the default values have changed
- FL 100 XSR_COMPLETE stored procedure can now run in a 64-bit JVM
Built-in global variables
Db2 12 introduces the following built-in global variables:
New reserved words in Db2 12
Db2 12 introduces the following SQL reserved words:
For the complete list, see Reserved words in Db2 for z/OS.