An Overview of DB2 for z/OS Version 8

By Craig S. Mullins

At conferences and regional user groups IBM has been steadily leaking out details about the next version of DB2 for z/OS. But there has been no official announcement of this latest and greatest iteration of DB2. This article will provide you with a concise overview of some of the features and functions that IBM has been touting.  Keep in mind, though, that this short article is merely an overview of some of the great features you can expect in DB2 V8; more details will become available over the course of 2003.

Architecture

One of the biggest impacts of V8 will be the requirement to be running a zSeries machine and z/OS v1.3 – DB2 V8 will not support old hardware nor will it support OS/390. Additionally, DB2 customers must migrate to V7 before converting to V8.  There will be no IBM-supported capability to jump from V6 (or an older version) 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 2GB 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 addressability to be used by a single DB2 address space. Now there is 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 do not use Unicode today, you will when you move to V8. This is so because 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 will 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 is a trigger package, which can be 128 bytes. This brings a lot of flexibility, but also a lot of reworking of the DB2 catalog tables. 

One such reworking requires the use of table spaces with 8K, 16K, and 32K page sizes. Therefore, the system catalog in DB2 V8 will require use of the BP8K0, BP16K0, and BP32K buffer pools.

Administration

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 that DBAs must manage including sequence objects, variable length index keys, expanded partitions, new types of partitioned indexes, new partition management, and materialized query tables (also known as automated summary tables). Also, index keys can comprise up to 2000 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 known as Schema Evolution. Today, there are many types of DB2 changes that require the DBA to DROP and then re-CREATE the object in order to implement the change. Schema evolution enables the DBA to make more types of changes to database objects using native DB2 features. For example, DBAs will be able to add and rotate partitions of partitioned table spaces and to expand the length of numeric and character columns using the ALTER statement. Basically, schema evolution provides more support for a variety of changes to be made directly using ALTER statements.

Other administration highlights include support for up to 4096 partitions per partitioned table space, row-level security, session variables (for global security), and Data Partitioned Secondary Indexes (usually shortened to DPSI and pronounced “dipsy”). DPSIs are significant because they are geared to resolve one of the biggest management headaches encountered by DB2 DBAs – 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 such that employees can see their own data but no one else’s. To complicate matters somewhat, you might also want each employee’s immediate manager to be able to see his payroll information as well as all of his employee’s data, and so on up through the org chart. Setting up such a security scheme is next to impossible with current DB2 versions, but it is straightforward using row level security in DB2 V8.

Finally, it looks like we will be able to have partitioning and clustering be independent of one another. In other words, the clustering index key can be different than 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, but at the same time, more complex. This may sound like a paradox, but it is true. Great new features will make programming simpler once they are learned, but it will take time and effort to train the legions of DB2 developers on this new functionality, and when and how best to use it.

For example, some of the V8 SQL improvements, will 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, and the ability to SELECT from an INSERT statement.

In addition, V8 will offer significant changes to the SQL system limits. Firstly, as we have already mentioned, DB2 will now offer long name support for database objects. But it does not stop there. DB2 V8 expands the maximum length of SQL statements to support up to 2 megabytes. This is a major change that permits much 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 new feature. To understand why we first need to present some background data. In some cases, it is 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 is even recorded to disk. But the application program will not have any knowledge of this change that is made 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 allows you to both insert the row and retrieve the values of the columns with a single SQL statement. It performs very well because it performs both the INSERT and the SELECT as a single operation. Consider the following example:

   SELECT COL5

   FROM 

   INSERT (COL1, COL2, COL5, COL7) INTO SAMPLE_TABLE

   VALUES('JONES', 'CHARLES', CURRENT DATE, 'HOURLY');

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, as noted in the initial architecture section, 64-bit virtual addressing will greatly increase the amount of memory available to DB2. And IBM is making major enhancements to the internal SQL control block structures, so that DB2 will use memory more efficiently. So more memory, used more efficiently, should translate into the more efficient execution of DB2 SQL.

And, as with every previous new DB2 version, IBM is making significant enhancements to improve application performance. DB2 V8 optimization enhancements are scheduled to include sophisticated query rewrite capabilities to support materialized query tables, sparse indexing to improve star join performance, support for parallel sort, and 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 the form of support for both Type 2 and Type 4 Java drivers. Both will be updated to support the JDBC/SQLJ 3.0 standard which brings enhanced support for things like SAVEPOINTs and WITH HOLD cursors, as well as improvements to connection pooling, and a long list of other expanded features.

And finally, in DB2 V8 more XML support 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).

Synopsis

DB2 V8 is the most significant new version of DB2 ever in terms of new functionality and the amount of new engine code that IBM is writing. This should result in a bevy of improvements that will make our databases and applications more reliable, more available, and more efficient. And as users of DB2 that should excite us all! Stay tuned to z/Journal for more in-depth coverage of DB2 V8 over the course of this year.

.