Nov 1 ’03
z/Data Perspectives: DB2 V8: Online Schema Evolution
Making changes to database structures can be one of the biggest causes of database downtime. 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.
Database changes are more flexible with DB2 Version 8 due to online schema evolution. Over time, online schema evolution 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.
However, online schema evolution introduces new capabilities for managing database changes. For example, with DB2 V8 you can:
- Extend the length of a column to a greater size (but not to a smaller length)
- 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). However, you can’t change character to numeric or graphic, numeric to character or graphic, or graphic to numeric or character. The previous data type changes are permitted even for columns that are part of an index or referenced within a view.
- Alter identity column characteristics
- Add a column to an index
- Change the clustering index for a table
- Make many changes to partitioned tablespaces 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 automatically rebalance partitions.
- 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.
- Better support utility processing for database objects in utility- pending states (REORG pending, RECOVER pending, REBUILD pending).
However, there is no such thing as a free lunch. When making online schema changes, the modification is immediately made to the system catalog, 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, causing performance to degrade.
Let’s consider the following code example. Here you can use the SET DATATYPE clause of the ALTER TABLE statement to change the data type and length of a column:
ALTER TABLE DSN8810.EMP
ALTER COLUMN EDLEVEL SET DATATYPE DECIMAL(7,0);
After the ALTER runs successfully, DB2 creates a new “version” of the tablespace. The definition of the data type is stored in the DB2 Catalog and immediately applies to the data. (DB2 can maintain up to 256 concurrent versions of a tablespace and up to 16 concurrent versions of an index.) However, remember that 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 format.
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 this is online schema change at a high level. Space constraints 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 V8, 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. Z