SQL compatibility enhancements
If you work with relational database products other than Db2 database products,
Db2version 11.1
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 11.1:
Enhancement | Description |
---|---|
BINARY and VARBINARY data types support binary string data. | BINARY and VARBINARY data types allow binary string data to be stored and manipulated without the overhead of using a BLOB type. A binary string is a sequence of bytes that are used to store as pictures, sound, or mixed media. BINARY and VARBINARY data types are compatible with each other and are compatible with the BLOB data type. Binary string data types are not compatible with character string data types, except those character strings that are defined as FOR BIT DATA. Support for BINARY and VARBINARY data types enhances compatibility with other relational database management systems. For more information, see Binary strings. |
Regular expression support in scalar functions. | The following scalar functions provide support for regular expressions to enhance your search ability: |
Extensions that enhance SQL compatibility. |
|
Support for outer joins that use the outer join operator, which is the plus sign (+). | Support for the outer join operator enhances cross-vendor support. See Introduction to Db2 compatibility features and DB2_COMPATIBILITY_VECTOR registry variable |
New built-in aggregate and scalar functions. | The following new built-in functions increase functionality and compatibility
with other relational database management systems,
|
New CREATE FUNCTION statement for user-defined aggregate functions. | The new CREATE FUNCTION (aggregate interface) statement allows you to create your own aggregate functions, by using your choice of programming language. |
OFFSET option for a FETCH FIRST clause. | Use the offset-clause to set the number of rows that are skipped during a subselect retrieval. |
Syntax alternatives | The following SQL
syntax alternatives can now be used:
|
New data types | The following additional data types can
now be used:
|
OVERLAPS predicate | Use the OVERLAPS predicate to determine whether two chronological periods overlap. |
DATASLICEID pseudo column | Use the DATASLICEID pseudo column to return the database partition numbers of any number of rows. |
Synonyms | The following synonyms can be used to call the
following functions:
|
Compatibility features for Netezza® Performance Server (NPS®) | You can use the SQL_COMPAT global
variable to activate the following optional NPS compatibility features:
|