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 V11.1:
Table 1. SQL compatibility enhancements in V11.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:
  • LIMIT ... OFFSET is a syntax alternative for a FETCH FIRST ... OFFSET clause.
  • ISNULL and NOTNULL are syntax alternatives for the IS NULL and IS NOT NULL predicates.
  • DISTRIBUTE ON is a syntax alternative for the DISTRIBUTE BY clause.
New data types The following additional data types can now be used:
  • INT2
  • INT4
  • INT8
  • FLOAT4
  • FLOAT8
  • BPCHAR
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:
  • COVAR_POP for COVARIANCE
  • LOG for LN
  • POW for POWER®
  • RANDOM for RAND
  • STDDEV_POP for STDDEV
  • STRPOS for POSSTR
  • STRLEFT for LEFT
  • STRRIGHT for RIGHT
  • VAR_POP for VARIANCE
  • VAR_SAMP for VARIANCE_SAMP
Compatibility features for Netezza® Performance Server (NPS®) You can use the SQL_COMPAT global variable to activate the following optional NPS compatibility features:
Double-dot notation
When operating in NPS compatibility mode, you can use double-dot notation to specify a database object.
TRANSLATE parameter syntax
The syntax of the TRANSLATE parameter depends on whether NPS compatibility mode is being used.
Operators
Which symbols are used to represent operators in expressions depends on whether NPS compatibility mode is being used.
Grouping by SELECT clause columns
When operating in NPS compatibility mode, you can specify the ordinal position or exposed name of a SELECT clause column when grouping the results of a query.
Routines written in NZPLSQL
When operating in NPS compatibility mode, the NZPLSQL language can be used in addition to the SQL PL language.