SQL compatibility enhancements

If you work with relational database products other than Db2 database products, Db2 11.5 contains enhancements that reduce the time and complexity of enabling applications that were written for other relational database products to run in Db2 environments.
The following table displays a list of SQL compatibility enhancements in version 11.5:
Table 1. SQL compatibility enhancements in version 11.5
Enhancement Description
New scalar functions improve compatibility with other vendors With the release of Db2 11.5, we have improved SQL compatibility with Netezza and Db2 for z/OS with two new scalar functions:
  • DATETIME, which returns a timestamp from a value or a pair of values.
  • ISNULL, which returns the first non-null expression in a list of two expressions.
DROP TABLE now supports an IF EXISTS clause With this optional clause, no error message is shown if the specified table name does not exist.
Tools for exporting client information captured by table functions

The DBMS_APPLICATION_INFO module provides useful procedures that set custom client info exported through some of the table functions in the database. This feature helps identify the targeted sessions upon executing the procedure. The READ procedures return the current values of the sessions while the SET procedures register the value of the current sessions. The ACTION, MODULE and CLIENT_INFO attributes used with the procedures set or read the information on the action, module, and client info defined from the user actions.

The schema for all procedures and functions in this modules is SYSIBMADM

The DBMS_APPLICATION_INFO module includes the following routines:
  • The READ_CLIENT_INFO procedure: reads and returns the value of the client information field from the current session.
  • The READ_MODULE procedure: reads and returns the value of the module and actions fields from the current session.
  • The SET_CLIENT_INFO procedure: sets and registers the value of the client information field from the current session.
  • The SET_ACTION procedure: sets and registers the action name within the current module.
  • The SET_SESSION_LONGOPS procedure: sets and registers a row in the SYSTOOLS.SESSION_LONGOPS table, to store progress information for long operations.
Manipulate data of type VARBINARY

The UTL_RAW module provides a set of routines for manipulating binary data of the data type VARBINARY. The routines perform various functions including data conversion, casting, comparison, concatenation, substring, xrange and translation.

The UTL_RAW module includes the following built-in routines:
  • The BIT_AND operation: runs a bitwise logical AND operation against the values in input1 with input2 and returns the result.
  • The BIT_OR operation: runs a bitwise logical OR operation against the values in input1 with input2 and returns the result.
  • The BIT_XOR operation: runs a bitwise logical EXCLUSIVE OR operation against the values in input1 and input2 and returns the result.
  • The BIT_COMPLEMENT operation: runs a bitwise logical COMPLEMENT operation against the values in input1 and returns the result.
  • The COMPARE operation: runs a COMPARE of the values in input1 against the values in input2.
  • The CAST_TO_RAW function: casts a VARCHAR value to a VARBINARY value.
  • The CAST_TO_VARCHAR2 function: casts a VARBINARY value to a VARCHAR2 value.
  • The CAST_FROM_NUMBER function: casts a DECFLOAT value to a VARBINARY value.
  • The CAST_TO_NUMBER function: casts a VARBINARY value to a DECFLOAT value.
  • The CONCAT function: concatenates up to twelve (12) VARBINARY values into a single value.
  • The COPIES function: returns the concatenated results of the VARBINARY value a specified number times.
  • The LENGTH function: returns the length of a VARBINARY value.
  • The REVERSE function: reverses the order of digits of a VARBINARY value.
  • The SUBSTR function: returns a specified portion of a VARBINARY value.
  • The XRANGE function: returns all valid one-byte values that exists in a specified interval, concatenated as a single VARBINARY value.
  • The CAST_FROM_BINARY_DOUBLE function: casts a DOUBLE value to a VARBINARY value.
  • The CAST_FROM_BINARY_FLOAT function: casts a FLOAT value to a VARBINARY value.
  • The CAST_FROM_BINARY_INTEGER function: casts an INTEGER value to a VARBINARY value.
  • The CAST_TO_BINARY_DOUBLE function: casts a VARBINARY value to a DOUBLE value.
  • The CAST_FROM_BINARY_FLOAT function: casts a VARBINARY value to a FLOAT value.
  • The CAST_FROM_BINARY_INTEGER function: casts a VARBINARY value to an INTEGER value.