Overview of DB2 for z/OS Version 8
Craig S. Mullins
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.
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.
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!
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.
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
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.
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
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.
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.
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.
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.
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.
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.
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.
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
INSERT (COL1, COL2, COL5, COL7) INTO SAMPLE_TABLE
VALUES('JONES', 'CHARLES', CURRENT DATE, 'HOURLY');
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.
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
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
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.
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).
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.