Mar 1 ’03
An Overview of DB2 V8 for z/OS zData Perspectives
At conferences and regional user group meetings, IBM has been leaking out details about the next version of DB2 for z/OS. This article provides a concise overview of some features and functions IBM has been touting. Remember, though, that this is merely an overview of some of the features you can expect in DB2 Version 8; more details will become available later this year.
A major impact of V8 will be the requirement to run a zSeries machine and z/OS 1.3. DB2 V8 won’t support old hardware, nor will it support OS/390. Additionally, DB2 customers must migrate to V7 before converting to V8. There’ll be no IBM-supported capability to jump from V6 (or predecessors) directly to V8 without first migrating to V7.
Owing to these architectural requirements, DB2 will have the ability to support large virtual memory. This next version of DB2 will be able to surmount the limitation of two gigabytes (2GB) of real storage that was imposed due to S/390’s 31-bit addressing. Theoretically, with 64-bit addressing, DB2 could have up to 16 exabytes of virtual storage address support to be used by a single DB2 address space. Now there’s some room for growth!
Broader usage of Unicode is another architectural highlight of DB2 V8. V7 delivered support for Unicode-encoded data, but V8 forces its use. If you don’t use Unicode today, you will when you move to V8. The DB2 system catalog will be implemented using Unicode. In fact, the DB2 catalog has some dramatic changes, including some table spaces with larger page sizes and long names.
Actually, support of long DB2 object names is another significant architectural change in V8. DB2 V8 significantly increases the maximum length of most DB2 object names. For example, instead of being limited to 18-byte table names, you’ll be able to use up to 128 bytes to name your DB2 tables. The same limit applies to most DB2 objects and special registers, including views, aliases, indexes, collections, schemas, triggers, and distinct types. The limit for columns is 30 bytes, a table space is still 8 bytes, and packages are still 8 bytes, unless it’s a trigger package, which can be 128 bytes. This brings considerable flexibility, but also a lot of reworking of DB2 catalog tables.
One such reworking requires the use of table spaces with 8K, 16K, and 32K page sizes. So the system catalog in DB2 V8 will require use of the BP8K0, BP16K0, and BP32K buffer pools.
As with each new version, DB2 V8 offers new functionality that helps DBAs administer and manage their databases and subsystems. This release contains many enhancements to the DB2 objects DBAs must manage:
- Sequence objects
- Variable length index keys
- Expanded partitions
- New types of partitioned indexes
- New partition management
- Materialized query tables (also known as automated summary tables).
Also, index keys can comprise up to 2,000 bytes, so more data can be indexed using a single index. Each of these features delivers more functionality but also presents implementation and maintenance challenges.
Another useful administration feature of DB2 V8 is schema evolution. Today, there are many types of DB2 changes that require the DBA to DROP and then re-CREATE the object to implement the change. Schema evolution lets the DBA make more types of changes to database objects using native DB2 features. For example, DBAs can:
- Add and rotate partitions of partitioned table spaces
- Expand the length of numeric and character columns using the ALTER statement.
Basically, schema evolution provides more support for various changes to occur directly, using ALTER statements.
Other administration highlights include:
- Support for up to 4,096 partitions per partitioned table space
- Row-level security
- Session variables (for global security)
- Data Partitioned Secondary Indexes (DPSIs).
DPSIs are significant because they’re geared to resolve one of the biggest management headaches DB2 DBAs encounter — dealing with Non-Partitioned Indexes (NPIs) on tables in a partitioned table space. A DPSI is basically a partitioned NPI.
The new security features are interesting, too! With row-level security, DB2 can support applications that need a more granular security scheme. For example, you might want to set up an authorization scenario so employees can see their own data, but no one else’s. To complicate matters, you might also want each employee’s immediate manager to be able to see his payroll information and all of his employee’s data — and so on up through the organization chart. Setting up such a security scheme is next to impossible with current DB2 versions, but it’s straightforward using row-level security in DB2 V8.
Finally, it looks like we’ll be able to have partitioning and clustering independent of one another. In other words, the clustering index key can be different from the partitioning index key.
Programming and Development
Numerous SQL and programming features are being added to DB2 V8 that will make the job of programming both easier and simultaneously more complex. This may sound like a paradox, but it’s true. Great new features will make programming simpler once they’re learned. However, it will take time and effort to train the legions of DB2 developers on this new functionality, including when and how best to use it.
Some V8 SQL improvements include:
- The ability to get diagnostic information
- True SEQUENCEs
- Dynamic scrollable cursors
- Scalar fullselect
- Multiple DISTINCT clauses
- Qualified column names on the SET clause of INSERT and UPDATE statements
- The ability to mix EBCDIC, ASCII, and Unicode columns in a single SQL statement
- The ability to SELECT from an INSERT statement.
V8 will also significantly change SQL system limits. Besides long name support for database objects, V8 expands the maximum length of SQL statements up to 2 megabytes. This major change permits more complex SQL statements to be written, optimized, and run within DB2. Additionally, V8 increases the length of literals and predicates to 32K, and will support joining up to 255 tables in a single SQL statement. This last one has been promised before, but should be delivered in V8.
The ability to SELECT from an INSERT statement is an intriguing feature. In some cases, it’s possible today to perform actions on an inserted row before it gets saved to disk. For example, a BEFORE TRIGGER might change data before it’s even recorded to disk. But the application program won’t have any knowledge of this change that occurs in the trigger.
Identity columns and user-defined defaults have similar effects. What if the program needs to know the final column values? Today, this is difficult and inefficient to implement. The SELECT FROM INSERT syntax in DB2 V8 solves this problem. It lets you insert the row and retrieve the values of the columns with a single SQL statement. It performs well because it performs both the INSERT and SELECT as one operation.
Consider Figure 1. The data is inserted as specified in the VALUES clause, and retrieved as specified in the SELECT. Without the ability to select COL5, the program would have no knowledge of the value supplied to COL5 because it was assigned using CURRENT DATE. With this new syntax, the program can retrieve the CURRENT DATE value that was just inserted into COL5 without adding overhead.
Also, 64-bit virtual addressing will greatly increase the amount of memory available to DB2. IBM is making major enhancements to the internal SQL control-block structures, so DB2 will use memory more efficiently. So more memory, used more efficiently, should translate into more efficient execution. As with every previous version, IBM seeks to improve application performance. DB2 V8 optimization enhancements are expected to include:
- Sophisticated query rewrite capabilities to support materialized query tables
- Sparse indexing to improve star join performance
- Support for parallel sort
- Better support for queries with data type and length mismatches (which would have caused less efficient access paths in previous releases).
For Java programmers, DB2 V8 offers expanded functionality in support for both Type 2 and Type 4 Java drivers. Both will be updated to support the Java Database Connectivity (JDBC)/SQLJ 3.0 standard. The standard enhances support for SAVEPOINTs and WITHHOLD cursors and provides improvements to connection pooling, among a long list of other expanded features.
Finally, DB2 V8 provides more XML support that is being pushed into the DB2 engine. This should include support for some built-in XML publishing functions such as XMLELEMENT and XML2CLOB, among others.
DB2 V8 is the most significant new version of DB2 ever, in terms of new functionality and the amount of new engine code IBM is writing. This should result in a bevy of improvements that will make our databases and applications more reliable, available, and efficient. As users of DB2, that should excite us all! Z