SQL changes in Db2 12 application compatibility levels

You can use this information to plan for SQL changes in Db2 12 application compatibility levels.

Important: Db2 12 introduces continuous delivery of new capabilities and enhancements in function levels. Most new capabilities become available only after activation of the Db2 12 function level that introduces them, or when applications run with the corresponding application compatibility level. For more information, see Activating Db2 12 function levels.

Important: Start of change

Applications must run with V12R1M500 application compatibility or higher to use most of these new SQL capabilities. For more information, see Application compatibility levels in Db2 12. End of change

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

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: COMPRESS YES FIXEDLENGTH COMPRESS YES HUFFMAN	No
V12R1M509	CREATE TABLE	The following new clauses are added: COMPRESS YES FIXEDLENGTH COMPRESS YES HUFFMAN IN ACCELERATOR can now specify an alias that represents multiple accelerators.	No
V12R1M509	CREATE TABLESPACE	The following new clauses are added: COMPRESS YES FIXEDLENGTH COMPRESS YES HUFFMAN	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: OR REPLACE SPECIFIC	No
V12R1M507	CREATE PROCEDURE (SQL native)	The following new clauses are added: OR REPLACE SPECIFIC	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.

Start of change 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. End of change

SQL element	Change introduced	Incompatible change?
ALTER FUNCTION (compiled SQL scalar)	New clause: CONCENTRATE STATEMENTS.	No
ALTER INDEX	A new clauses are added: PADDED USING APPLICATION COMPATIBILITY The following clauses are changed: ADD COLUMN `column-name` (can now specify DECFLOAT columns) COMPRESS 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: CCSID EXCLUSIVE and INCLUSIVE clauses of the PERIOD BUSINESS_TIME clause PERIOD BUSINESS_TIME clause for constraints The following clauses are changed: ADD PERIOD FOR ADD PARTITION	No
ALTER TABLESPACE	The following new clauses are added: PAGENUM INSERT ALGORITHM The following clauses are changed: COMPRESS DSSIZE	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: CONCENTRATE STATEMENTS WRAPPED	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: CONCENTRATE STATEMENTS WRAPPED	No
CREATE TABLE	The following new clauses are added: CCSID on a CHAR, GRAPHIC, CLOB, or DBCLOB column EXCLUSIVE and INCLUSIVE clauses of the PERIOD BUSINESS_TIME clause PAGENUM PERIOD BUSINESS_TIME for constraints The following clauses are changed: DSSIZE PERIOD FOR	No
CREATE TABLESPACE	The following new clauses are added: PAGENUM INSERT ALGORITHM The following clauses are changed: COMPRESS DSSIZE NUMPARTS 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: BETWEEN `value-1` AND `value-2` clause of the `period-clause` FETCH FIRST `n` ROWS ONLY	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 `EXPLAIN PACKAGE: copy`.	Yes
FETCH	New clause: `target-variable`.	No
fullselect	New clause: offset-clause. The following clauses are changed: fetch-clause order-by-clause	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: `signal-statement` ELSE IGNORE The following clauses are changed: AS `correlation-name` `assignment-clause` WHEN `matching-condition` THEN `modification-operation` USING (see `table-reference`)	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: INTO fetch-clause	No
SET assignment-statement statement	Changed clause: DEFAULT.	No
subselect	New clause: offset-clause The following clauses are changed: `correlation-clause` of collection-derived-table in a table-reference of a FROM clause fetch-clause A user-defined function that is defined with MODIFIES SQL DATA can be invoked.	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: Assignment clause source INTO	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.

Start of change 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. End of change

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

Begin general-use programming interface information. 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

Begin general-use programming interface information. 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.

Existing Predicate	New Syntax Alternative
IS NULL	ISNULL
IS NOT NULL	NOTNULL

End general-use programming interface information.

New and changed built-in functions

Db2 12 introduces or changes the following built-in functions.

Important information about existing user-defined functions: When a new application compatibility level introduces a new or changed built-in function that has the same name and signature as an existing user-defined function, unqualified references to the user-defined function might resolve incorrectly. Applications that have unqualified references to the user-defined function might fail. To avoid this situation, modify applications to explicitly qualify references to user-defined functions with the same name and signature as the new or changed built-in functions.

Begin general-use programming interface information.

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. ADD_DAYS scalar function BTRIM scalar function DAYS_BETWEEN scalar function NEXT_MONTH scalar function REGR_AVGX REGR_AVGY REGR_COUNT REGR_INTERCEPT or REGR_ICPT REGR_R2 REGR_SLOPE REGR_SXX REGR_SXY REGR_SYY ROUND_TIMESTAMP scalar function if invoked with a date expression	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 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 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 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. CUME_DIST CUME_DIST aggregate function FIRST_VALUE LAG LAST_VALUE LEAD NTH_VALUE NTILE PERCENT_RANK PERCENT_RANK aggregate function RATIO_TO_REPORT REGEXP_COUNT scalar function REGEXP_INSTR scalar function REGEXP_LIKE scalar function REGEXP_REPLACE scalar function REGEXP_SUBSTR scalar function	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: UNI_60 UNI_90
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: UNI_60 UNI_90	No
V12R1M500	UPPER scalar function	The following locales can now be specified: UNI_60 UNI_90	No
V12R1M500	WRAP scalar function	New built-in function.	No
V12R1M100	BLOCKING_THREADS table function	New built-in function.

End general-use programming interface information.

New and changed Db2-supplied stored procedures

Begin general-use programming interface information. 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

End general-use programming interface information.

Built-in global variables

Begin general-use programming interface information. Db2 12 introduces the following built-in global variables:

End general-use programming interface information.

New reserved words in Db2 12

Begin general-use programming interface information. Db2 12 introduces the following SQL reserved words:

FL 504 CURRENT_SERVER
FL 504 CURRENT_TIMEZONE
FL 500 LIMIT
FL 500 OFFSET

For the complete list, see Reserved words in Db2 for z/OS.

End general-use programming interface information.