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:

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):
Table 1. SQL compatibility enhancements in 11.5 Mod Pack 4
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:
  • ASCII_STR: returns an ASCII version of the string. ASCIISTR can be specified as a synonym for ASCII_STR
  • NCHR: returns a Unicode character with the specified UTF-32 Unicode code point. UNICHR can be specified as a synonym for NCHR
  • TO_MULTI_BYTE: returns a Unicode string in which the single-byte characters in string-expression are converted to their multi-byte (full width) equivalents.
  • UNICODE_STR: returns a string in Unicode UTF-8 format or UTF-16 format, depending on the specified option. The string represents a Unicode encoding of the input string. UNISTR can be specified as a synonym for UNICODE_STR

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.

Table 2. General SQL enhancements in 11.5 Mod Pack 4
Enhancement Description
  • New SKIP LOCKED DATA clause for row-organized tables
  • The SKIP LOCKED DATA clause specifies that rows locked by other transactions are skipped. This occurs when incompatible locks that would block the progress of the statement are held on the rows.