DB2 Version 5 includes the following SQL enhancements:
All enhancements comply with the SQL92 Entry Level standards. Features from higher levels of SQL92 and the future SQL3 have also been added.
See the SQL Reference for details about the enhancements described in this section.
A left, right, and full outer join operation is now supported using SQL92 syntax; that is, a join operation whose result includes unmatched rows in addition to matching rows.
The following functionality has been added for authorization support:
A single-precision floating-point data type using the keyword REAL is now supported.
A CREATE SCHEMA statement and a DROP SCHEMA statement are now supported. This allows privileges to be associated with the schema to control which users can create, alter, or drop objects in the schema.
SQL users can now access data which is not stored in the relational format and make full use of the query capabilities of the relational database.
It is often difficult if not impossible to subject data from non-relational data stores to relational operations. User-defined table functions are an extension to SQL that address this issue. A table function is an external user-defined function that constructs a derived table. The program for the function can access data from the various sources and format it into a tabular form that is returned from the table function. Once the table function is written, it can be used in the FROM clause of queries. Table functions can be used not only to subject this external data to the power of SQL, but also to capture external data permanently into relational tables.
See the Embedded SQL Programming Guide for details.
Unique constraint support has been added as follows:
The GROUP BY clause has been extended to support "super groups". One type of super group is a "ROLLUP group"; a result set that contains "sub-total" and "overall total" rows in addition to the regular grouped rows. Another type of super group is a "CUBE group"; a result set that contains "cross-tabulation" rows in addition to all the rows that would be in a ROLLUP group for the same columns.
Support is now provided for renaming an existing table while maintaining current authorizations and indexes from the source table on the renamed table.
Friendly arithmetic and conversion allow a query to proceed and provide some returned results even though some data items could not be evaluated. This function enhances compatibility with DB2 for OS/390.
The following built-in functions are now available:
Returns the number of rows or values in a set of rows or values for tables with a large number of rows.
Returns a unique value that can be used to provide unique values in a table.
Up to 500 columns in a table are now supported.