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:

Table 1. 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: REGEXP_COUNT REGEXP_EXTRACT REGEXP_INSTR REGEXP_LIKE REGEXP_MATCH_COUNT REGEXP_REPLACE REGEXP_SUBSTR
Extensions that enhance SQL compatibility.	Functions added to OLAP specification: NTH_VALUE CUME_DIST PERCENT_RANK offset clause added to subselect DISTINCT clause added to LISTAGG aggregate function
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, ADD_DAYS ADD_HOURS ADD_MINUTES ADD_SECONDS ADD_YEARS AGE BTRIM COLLATION_KEY COVARIANCE_SAMP CUME_DIST DATE_PART DATE_TRUNC DAYOFMONTH DAYS_BETWEEN DAYS_TO_END_OF_MONTH EXTRACT FIRST_DAY HASH HASH4 HASH8 HOURS_BETWEEN INTNAND , INTNOR, INTNXOR, and INTNNOT MEDIAN MINUTES_BETWEEN NEXT_MONTH NEXT_QUARTER NEXT_WEEK NEXT_YEAR NOW PERCENTILE_CONT PERCENTILE_DISC PERCENT_RANK RAWTOHEX SECONDS_BETWEEN STDDEV_SAMP THIS_MONTH THIS_QUARTER THIS_WEEK THIS_YEAR TO_HEX VARIANCE_SAMP WEEKS_BETWEEN WIDTH_BUCKET YEARSBETWEEN YMDBETWEEN
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.