Blog
DB2utor

Categories

July 19, 2010

DB2 10 Online Schema Evolution

The first time I wrote about online schema, I was discussing the changes with DB2 8 and the enhancements delivered with DB2 9.

That was almost three years ago. Today, further refining online schema--also known as data definition on demand (DDOD)--remains a high priority for IBM's DB2 architects. With DB2 10, the focus is on providing greater availability and manageability.

Through enhancements to ALTER statements, you'll be able to change indexes and table spaces without having to unload the data, drop and recreate the objects, regenerate all of the security authorizations, recreate the views and reload the data. In DB2 10, the changes will be materialized when the altered objects are reorganized. These online schema enhancements are expected to apply to these types of changes:

•    Altering the page size of a table space (when a different size bufferpool is named).
•    Altering the data set size of a table space (DSSIZE parameter).
•    Altering the segment size of a table space (SEGSIZE parameter).
•    Altering the table space type by:
o    changing a simple table space with only one table to a partition-by-growth universal table space
o    changing a segmented table space with only one table to a partition-by-growth universal table space
o    changing a classic partitioned table space to a partition-by-growth universal table space
o    changing a classic partitioned table space to a range-partitioned universal table space.
•    Altering the MEMBER CLUSTER structure for a table space.
•    Altering the index page size.

A really nice feature in DB2 10 is the capability to undo the pending changes to a table space or any object in the table space. This will be made possible through the ALTER TABLESPACE DROP PENDING CHANGES statement. If you've already materialized the changes, you could perform a compensating ALTER and then schedule a REORG.

A quick reminder that DB2 10 is still in beta. So in this case, or whenever I discuss the upcoming release, individual features could be changed or dropped prior to general availability.