SQL enhancements
The Db2 version 11.5 Mod Pack 4 release includes five new scalar functions.
Attention: The enhancements in this topic are included in the
Db2 11.5.4 mod pack release, which is available for the following products:
- Db2 on-premises
- The single container deployments of Db2 Warehouse and IBM Integrated Analytics System (IIAS)
- The container micro-service deployment of Db2 on Red Hat OpenShift
SQL compatibility enhancements
The following table lists SQL compatibility enhancements in the Db2
11.5
Mod Pack 4 release that provide compatibility with PureData System for Analytics (Netezza):
Enhancement | Description |
---|---|
TRUNCATE TABLE without IMMEDIATE clause support asynchronous reclaim at the end of the transaction | In column-organized tables, the TRUNCATE statement can contain an IMMEDIATE clause as optional. When the IMMEDIATE clause is not specified, the truncate operation is processed and can be undone. The statement can be anywhere in the transaction scope, and can be undone before the transaction completes. The truncated table is immediately available for use in the same unit of work. A ROLLBACK statement is allowed to execute after a TRUNCATE statement without the IMMEDIATE option, and the truncate operation is undone. For example, if another data change operation is done on the table after the TRUNCATE without the IMMEDIATE statement, and if then the ROLLBACK statement is executed, the truncate operation is also undone. Storage can be automatically reclaimed through a background asynchronous task. |
Lock avoidance for catalog tables for external user queries only | In previous versions of Db2, enabling CC (Currently Committed), DB2_SKIPINSERTED, DB2_EVALUNCOMMITTED, and DB2_SKIPDELETED is not supported for user-initiated catalog table scans. This restriction is lifted by the option to change the locking behavior, thus improving the concurrency for user-initiated catalog scans. The isolation levels that are supported depend on the optimization. You can set the DB2COMPOPT registry variable with the LOCKAVOID_EXT_CATSCANS option to enable catalog scans on external queries. This registry variable setting does not impact the behavior of internal queries on the Db2 catalog tables. |
New/enhanced built-in scalar functions | The following scalar functions have been added to Db2:
The scalar function TO_SINGLE_BYTE, is now available on all database code pages. Previously, this function was restricted to databases created in Unicode and IBM-943 code page. |
New Netezza TIMESTAMP string support | With this enhancement, the Netezza timestamp format (MM-DD-YYYY HH24:MM:SS) is recognized in Db2. |
WITH clause in nested-table-reference and derived table usage | The query body of a common-table expression (WITH clause) can now contain additional nested-table-reference and derived table usage, except for subqueries in predicates. For these subqueries, a WITH clause in nested-table-references and derived tables is not possible. |
NULL ordering | In Db2, NULL values are considered higher than any other values. By enabling NULL ordering, NULLS are considered as the smallest values in sorting. You can enable this new option by setting the DB2_REVERSE_NULL_ORDER registry variable to DB2_REVERSE_NULL_ORDER=TRUE. By default, the DB2_REVERSE_NULL_ORDER registry variable is set to FALSE. |
External Table with COMPRESS GZIP option does not need data object with .gz extension | When you use the COMPRESS GZIP option, you can now choose to specify the value with or without the .gz extension for the DATAOBJECT or FILE_NAME option. |
Changed behavior of DECIMAL scalar function empty string in NPS mode | In NPS mode, casting an empty string to DECIMAL now returns 0. |
DAYS_BETWEEN, WEEK_BETWEEN, MONTHS_BETWEEN, HOURS_BETWEEN, MINUTES_BETWEEN, SECONDS_BETWEEN scalar functions behavior change in NPS mode | In NPS compatibility mode, the DAYS_BETWEEN, WEEK_BETWEEN, MONTHS_BETWEEN, HOURS_BETWEEN, MINUTES_BETWEEN, SECONDS_BETWEEN scalar functions always return a positive number. |
SQL enhancement for transactional databases
The following enhancement applies to row-organized tables.
Enhancement | Description |
---|---|
|
|