SQL consistency improvements

Increased consistency with SQL across the DB2® family is good news for programmers who write cross-platform applications because porting and writing application programs becomes easier.

The following SQL functions, statements, and clauses are consistent across the DB2 family.

SELECT FROM UPDATE or SELECT FROM DELETE function
The INSERT-within-SELECT feature that was introduced in DB2 Version 8 of DB2 for z/OS® has been expanded in DB2 9. DB2 9 supports the retrieval of columns from rows that are modified by a SELECT FROM DELETE or a SELECT FROM UPDATE statement.
INSTEAD OF triggers
The introduction of INSTEAD OF triggers reduces the complexity in your application programs.
BIGINT data type and function
This release of DB2 for z/OS introduces a new SQL data type and function, BIGINT (big integer). BIGINT supports big integers and extends the set of currently supported exact numeric data types (SMALLINT and INTEGER).
BINARY data type and function
This release of DB2 for z/OS provides expanded support for binary string data by introducing the BINARY data type and function.
File reference variables
This release of DB2 for z/OS adds support for two new file reference variables, LOB and XML.
INTERSECT keyword in a fullselect
The new INTERSECT keyword specifies the intersection set operator in a fullselect operation between two result tables.
EXCEPT keyword in a subselect
This release of DB2 for z/OS contributes to SQL consistency by providing the new EXCEPT keyword. Use the EXCEPT keyword to specify the difference set operator in a fullselect operation between two result tables.
Native support for SQL procedures
In this release of DB2 for z/OS, new support for native SQL procedures simplifies the definition and use of SQL procedures by eliminating the need for generating a C program.
Nested compound statements in native SQL procedures
Native SQL procedures support nested compound statements.
Expanded support for not logging table spaces
This release of DB2 for z/OS supports suspension of logging for base table spaces, XML table spaces, and the indexes that are associated with them.
OLAP specifications for RANK, DENSE_RANK, and ROW_NUMBER
Online analytical processing (OLAP) specifications provide the ability to return ranking, row numbering, and existing aggregate function information. This information is returned as a scalar value in the result of a query.
COLLATION_KEY function
The new COLLATION_KEY function supports culturally correct and case-insensitive collation of Unicode data.
Capability to create an expression-based index
In this release of DB2 for z/OS, an extension to the CREATE INDEX statement lets you create an expression-based index.
Automatic creation of a database, a table space, and all system-required objects
The CREATE TABLE statement now supports automatic (implicit) creation of a database or a table space.
IBM Spatial Support for DB2 for z/OS
IBM® Spatial Support for DB2 for z/OS allows you to generate and analyze spatial information about geographic features and to store and manage the data on which this information is based.

Parent topic: Compatibility and leadership with SQL