Blog
DB2utor

Categories

September 24, 2007

Evolution of Online Schema Change to Database Definition on Demand

One of the top reasons for taking a database outage is making structural changes to support application enhancements. The reason is that many changes to the structure (i.e., removing a column, changing the data type) cannot be handled with a simple ALTER statement but instead require the DBA to write script to unload the data, drop and re-create with the needed changes, reload the data and then perform upgrade scripts and utilities as needed. These scripts can be complex and error prone unless you’re using a software tool to manage the process.

IBM recognizes the plight DBAs have in having to take outages and is continually improving DB2 to reduce the need to take down the database to make changes. These improvements started in DB2 V8 with online schema evolution and have been extended in DB2 9 with Database Definition on Demand (DDOD). 

Prior to DB2 V8, you could make some basic changes to a table like ALTER ADD COLUMN. You could even rename a table; however, a long list of restrictions like having a trigger defined or having a view defined would prevent you from using the rename. If you want to change a column data type or length, then you must take the table and the applications using the table down in order to drop and rebuild the table with the data-type changes. You would also need to write a program to convert the unloaded data into the new format to be reloaded.

Online schema evolution, also known as online schema change or simply schema change, allows you to make changes to the database with a minimal or no outage. Let’s examine what DB2 version 8 allows and then take a look at further improvements in version 9:

  • Column Size or Data Type can be changed using the following statement:
    ALTER TABLE table-name ALTER COLUMN column-name
         SET DATA TYPE altered-data-type;
    The size cannot be less than the existing size. See the SQL Reference Guide for other restrictions.
  • The alter table can also be used to change the following attributes of an IDENTIY column:
    o SET GENERATE ALWAYS or BY DEFAULT
    o RESTART WITH numeric-constant
    o SET INCREMENT BY numeric-constant
    o SET MINVALUE or NO MINVALUE
    o SET MAXVALUE or NO MAXVALUE
    o SET CYCLE or NO CYCLE
    o SET CACHE or NO CACHE
    o SET ORDER or NO ORDER
  • Add column to end of index
  • Change index VARCHAR columns from PADDED to NOT PADDED
  • Add partitions to the end of the partitioned table space
  • You can rotate partitions from first to last to support rolling off of old data as new data is being added
  • You can rebalance partitions using the REORG REBALANCE utility and continue to have applications access the data (no changing of data allowed)
  • You can drop partitioning index
  • You can change the clustering index
  • You can define clustering order different from partitioning order
  • You can create partitioned tables without having to create a partitioning index
  • You can partition secondary indexes

As you make alterations to the table and index, a new version of the object is stored in DB2. This is to ensure availability of the data to your applications. You can have up to 256 concurrent versions of the table space, which includes changes to the table structure, and up to 16 versions of an index. You can find the latest version of a given table by looking at sysibm.systablespace OLD_VERSION and CURRENT_VERSION. For index you can look at sysibm.sysindexes. Making changes to the table puts the table space into an advisory REORG-pending (AREO*) state. I mention this because the support of multiple versions isn’t free. DB2 has to format the data from what’s internal on the table space to what the current version definition is. When you REORG the table space and index space, the data will be converted and stored in the current version format.

DB2 9 continues on with the theme of improved availability and online schema evolution with the introduction of DDOD. The following features are provided in DB2 9 with DDOD:

  • Fast replacement of one table with another. By using ALTER TABLE ADD CLONE clone-table, you can populate the clone table and then use the EXCHANGE statement to swap the content of the base table with the clone table. Think of this as a fast online load.
  • RENAME COLUMN using ALTER TABLE RENAME COLUMN source-column to target-column; see SQL Reference Guide for restrictions.
  • RENAME INDEX source-index TO target-index
  • Table space that can automatically add partitions for growth (UTS Partition by Growth)
  • Improved ability to rebuild index online. REBUILD INDEX share level change will support INSERT and UPDATE activity as long as the index supports duplicates; otherwise you’ll receive -904 because uniqueness checking cannot be performed while the index space is in RBDP state.
  • Online reorganization with no build2 phase. The issue of rebuilding non-partitioning indexes has been removed. The rebuild of all indexes will occur early on in the process so that at the end of the REORG, DB2 can truly switch the datasets without having to wait.
  • Turn off logging with ALTER TABLESPACE NOT LOGGED
  • Alter table set column default
  • Create and alter STOGROUP SMS construct
    ALTER STOGROUP stogroup-name
    DATACLAS dc-name MGMTCLAS mc-name STORCLAS sc-name;
  • New DB2 command REFRESH DB2, EARLY; lets you refresh EARLY code when DB2 isn't active, without having to IPL.

As you can see from the online schema evolution available in V8 and DDOD enhancements in DB2 9, IBM is serious about improving availability and reducing total cost of ownership. So what’s missing? Let me know what you think should go into the next release to improve availability and simplify administration.