V8: Online Schema Evolution
Making changes to database structures can
be one of the biggest causes of database downtime. This
is so because many of the most common changes require
DBAs to create convoluted scripts that capture DDL from
the system catalog, unload data, DROP objects, and then
re-CREATE the objects with the desired changes. Such
changes can be tedious and error-prone unless you are
using a software tool to manage the process.
Of course, not all database changes
require such draconian measures. Some changes can be
made using a simple ALTER to modify specific
characteristics of a DB2 object. And database changes
are more flexible with DB2 V8 due to online schema
evolution. Online schema evolution begins the process of
making more types of database change possible without
requiring objects to be dropped and re-created. IBM also
refers to this feature as simple schema evolution,
online schema changes, or just schema changes.
Online schema evolution, over
time, will allow us to alter DB2 database structures
with minimal or no outages. Of course, this is the
long-term goal. We are in the first phases of schema
evolution with DB2 V8 - and remember, “evolution” is
a very lengthy process.
Of course, DB2 has offered some degree of
online change management even prior to V8. For example,
adding a column to the end of a table or renaming a
table are both simple to accomplish. But online schema
evolution introduces new capabilities for managing
database changes. What exactly can be changed with DB2
You can extend the length of a column
to a greater size (but not to a smaller length).
You can switch the data type of a
column within character data types (CHAR, VARCHAR);
within numeric data types (SMALLINT, INTEGER, FLOAT,
REAL, FLOAT, DOUBLE, DECIMAL); and within graphic
data types (GRAPHIC, VARGRAPHIC). But you cannot
change character to numeric or graphic; numeric to
character or graphic; or graphic to numeric or
The previous data type changes are
permitted even for columns that are part of an index
or referenced within a view.
You can alter identity column
You can add a column to an index.
You can change the clustering index
for a table.
You can make many changes to
partitioned table spaces and indexes that were
previously not allowed. For example, you can drop
the partitioning index, create a table without a
partitioning index, add a partition to the end of a
table to extend the limit key value, rotate
partitions, and even automatically re-balance
You can better support indexes built
on VARCHAR columns. Prior to V8 all indexes on
variable columns were padded to their maximum size
in the index. Now you can CREATE or ALTER an index
to specify non-padded variable keys.
You can better support utility
processing for database objects in utility-pending
states (REORG pending, RECOVER pending, REBUILD
But there is no such thing as a free
lunch. When making online schema changes the
modification is made to the system catalog details right
away, but not to the underlying data. Every time the
data is accessed DB2 will have to transform it from its
old type and length to its new type and length. This
adds overhead to queries and will cause performance to
Let’s consider a simple example. You can
use the SET DATATYPE clause of the ALTER TABLE statement
to change the data type and length of a column. For
ALTER TABLE DSN8810.EMP
After the ALTER runs successfully, DB2
creates a new “version” of the table space. The
definition of the data type is stored in the DB2 Catalog
and immediately applies to the data. (Up to 256
concurrent versions of a table space and up to 16
concurrent versions of an index can be maintained by
DB2.) But remember, the existing data is not changed or
reformatted on disk. Instead, when data is accessed, the
changed column(s) will be materialized in the new
Updating or inserting data will cause the
row to be saved using the format of the new data type.
When the object is reorganized or rebuilt, the data will
be converted to the format of the latest version
specified in the DB2 Catalog. This technique allows DB2
to offer the greatest availability to users, albeit with
some performance degradation.
Keep in mind that we just covered online
schema change at a high-level. Space limits prohibit a
complete discussion of the other types of changes
supported by DB2 V8. Suffice it to say, it becomes
easier to make changes to database structures under DB2
Version 8 – but this ease of use does not come free.
You will need to manage versions and your queries will
pay the cost to transform data until it is modified,
reorganized or rebuilt.