SQL compatibility enhancements

Db2 11.5.2 contains enhancements that reduce the time and complexity of enabling applications written for other relational database products to run in Db2 environments.

Attention: The enhancements in this topic are included in the Db2 11.5.2 mod pack release, which is available only for single container deployments of Db2 Warehouse and IBM Integrated Analytics System (IIAS).
The following table displays a list of enhancements in the container-only release of 11.5.2 that provide compatibility with PureData System for Analytics (Netezza):
Table 1. SQL compatibility enhancements in 11.5.2
Enhancement Description
WITH clauses can now be nested

The query body of a common-table expression (that is, a WITH clause) can now contain additional common-table expressions.
A TRUNCATE TABLE statement no longer requires an IMMEDIATE clause

For column-organized tables, the IMMEDIATE clause is now optional for a TRUNCATE statement. When the IMMEDIATE clause is not specified, the TRUNCATE operation can be stopped. This means that the operation can be stopped at any point in the transaction's scope before it completes. The truncated table is then immediately available for use within the same unit of work.

For a TRUNCATE statement that is issued without the IMMEDIATE clause, you can issue a ROLLBACK statement to undo the TRUNCATE operation, even if another data-changing operation was issued after the original TRUNCATE statement. This will undo everything, including the truncate operation. After this is done, you can reclaim storage manually by running the REORG RECLAIM operation, or you can wait for the health monitor trigger to reclaim storage automatically.
LFINSTRING specifies how to interpret unescaped line-feed characters in string data in an external table
When you create an external table that uses a text file format, a new option called LFINSTRING lets you specify how unescaped line-feed (sometimes called LF or newline) characters in string data are to be interpreted for that table:
  • If set to TRUE, an unescaped LF character is interpreted as a record delimiter only if it is in the last field of a record; otherwise, it is treated as data.
  • If set to FALSE, an unescaped LF character is interpreted as a record delimiter regardless of its position. This is the default.
This option is not supported for unload operations, and applies only to line-feed characters, not to carriage-return line-feed (CRLF) characters.