Version 5
|
|
Version 5
|
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. See "BIGINT Data Type" for information on 64-bit integer support available in Version 5.2.
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.
See "CURRENT SCHEMA" for information on the Version 5.2 special register that identifies the schema name used to qualify unqualified object references.
SQL users can now access data that is not stored in the relational format and can 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.
See "APPEND Parameter for ALTER TABLE", "Increasing VARCHAR Column Length", and "Table-Level Locks" for Version 5.2 enhancements to the ALTER TABLE statement.
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. Use COUNT_BIG instead of COUNT when the value returned may exceed 2 billion.
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 on 4 KB pages. For
information on Version 5.2 enhancements that support tables with up to
1012 columns see "Multiple Page Size Support".
 |
Version 5.2
|
DB2 is being enhanced with a new predefined data type, DATALINK. A DATALINK value in a database represents an object stored in a storage system outside of the database system. DB2 will treat the DATALINK value as if it were stored in the database, even though it is not. These means that the value is robust in terms of integrity, access control, and recovery.
The DB2 extension, called the DB2 File Manager, is important because it enables the asset management of files stored on file servers outside of the database management system. Using DATALINKs and the DB2 File Manager means that external files can be backed up with the database and SQL Data Control Language statements can be used to control permission to those files (for example, GRANT and REVOKE).
Users can create indexes on text, images, and videos, and store those attributes in relational tables along with the DATALINK value. The DATALINK value is a pointer or a uniform resource locator (URL) to an external file. The DB2 File Manager allows DB2 to treat this external data as if it was stored in the database.
DB2 File Manager is not available for Extended Enterprise Edition systems. The DATALINK data type can not be used on Extended Enterprise Edition systems to reference files on a DB2 File Manager.
DATALINKs does not support Windows 3.1 clients. Clients on other operating systems must be at the Version 5.2 level of DB2 Universal Database, except for AIX clients who can be on Version 5 at FixPak U453782 or higher.
Detailed information on the DB2 File Manager technology and the DATALINK data type can be found in the following:
An SQL data type of BIGINT is available for supporting 64-bit integers. As platforms introduce native support for 64-bit integers, the processing of large numbers with BIGINT is more efficient than processing with DECIMAL, and more precise than DOUBLE or REAL. This new data type allows:
See "BIGINT", "BIGINT Data Type", the SQL Reference and the Administration Guide for details.
The length of an existing VARCHAR column in a table can be increased to up to 4000 bytes. The ALTER column-alteration parameter for the ALTER TABLE statement allows for increasing the size of a VARCHAR column length. See the SQL Reference for details.
A PCTFREE parameter has been added to the ALTER TABLE and CREATE INDEX statements. It is used to indicate the amount of free space left on each page. The free space is necessary to allow data to be inserted on a target page, instead of being appended to the end of a table. Free space is an important consideration when using clustering indexes (see "Clustering Indexes"). See the SQL Reference and the Administration Guide for details.
An INDEXFREESPACE parameter has been added to the LOAD command to specify the percentage of free space to leave on each index page when loading an index. See the Command Reference for details.
The Long Field Manager log records have been updated so that data capture capability can be extended to LONG VARCHAR/LONG VARGRAPHIC columns. See the API Reference for details.
An additional clause has been added to the DATA CAPTURE parameter of the ALTER TABLE statement to indicate when a LONG VARCHAR or LONG VARGRAPHIC column is included. See the SQL Reference for details.
Information can be stored on page sizes of 4 KB and 8 KB. A page size of 4 KB supports table spaces of up to 64 GB and tables of up to 500 columns (see "500 Table Columns"). A page size of 8 KB allows table spaces of 128 GB, tables of 1012 columns, and row lengths of 8101 bytes. Page size is set during buffer pool creation. Once the buffer pool page size is fixed, then table spaces and tables can be created. See the SQL Reference and the Administration Guide for details.
The CURRENT SCHEMA special register contains the default qualifier to be used for unqualified object references for dynamic SQL statements issued within a specific DB2 connection. The qualifier can be changed by the SET SCHEMA statement. See the SQL Reference for details.
The QUALIFIER option of the BIND command controls the schema name used to qualify unqualified database object references for static SQL statements. See the Command Reference for details.
| Note: | For compatibility with DB2 for OS/390, CURRENT SCHEMA is synonymous with the CURRENT SQLID special register. |
A new CREATE TYPE statement supports the definition of user-defined structured types that enhance DB2's object management capabilities. Subtyping is supported, so a structured type can either be created on its own or as a subtype of another structured type (thereby inheriting the attributes of that type). For example, an employee table might have some employees who are part of the subtype part-time. In this way, DB2 users can now create structured type hierarchies that are similar to class hierarchies in Java or C++. In addition, the creation of a structured type T also makes a corresponding reference type, REF(T), available for use as an attribute or column type when defining structured types, tables, and views.
The CREATE TABLE statement has been extended so that a structured type can be used as the basis for defining a table of objects of that type. Rows in such a table have an object ID column plus columns that correspond to each attribute of the type specified in the type-based version of the CREATE TABLE statement. To manage a table (or more properly, table hierarchy) that contains instances of a type plus one or more of its subtypes, the CREATE TABLE statement allows a typed table to be created UNDER another typed table; as a subtable of that table.
The CREATE VIEW statement has been similarly extended so that a structured type can be used as the basis for defining an object view or an object view hierarchy.
The SQL language has been extended with new functionality in its SELECT, INSERT, UPDATE, and DELETE statements to support queries and updates to table hierarchies (and subsets thereof), and the expression portion of SQL has been extended with support for a dereference operator (->) that enables users to traverse references using a C++ like path notation. More details on all of these new features can be found in the SQL Reference.
See "IMPORT and EXPORT" and "RUNSTATS" for commands that can be used on table hierarchies. See the Administration Guide and the SQL Reference for more information on structured types and table and view hierarchies. See "Defined Types" for information about the Call Level Interface (CLI) impact of these extensions.