Skip to content

Personal tools
You are here: Home » DB2 » DB2 Mainframe Articles Archive » Database Change Management and Schema Evolution in DB2 for z/OS Version 8
Seeking new owner for this high-traffic site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3554

Database Change Management and Schema Evolution in DB2 for z/OS Version 8

by Craig S. Mullins
This article is adapted from the upcoming edition of Craig’s book, DB2 Developer’s Guide, 5th edition. This new edition, which will be available in May 2004, updates the book to include coverage of DB2 Version 7 and Version 8.

When building DB2 database applications, we create databases, table spaces, tables, indexes, and so on, and then we build the application programs to access the DB2 data. When complete, everything is moved into production and we all live happily ever after, right? Well, eventually we will need to change the database to meet changing business needs. And then what?

One of the bigger causes of database downtime is caused when changes are made to DB2 database structures. This is so because many of the most common changes required of DB2 objects cannot be supported with simple ALTERs. Instead, DBAs are required to DROP the object and then re-CREATE it with the desired changes.

Such changes can be tedious and error-prone. Suppose you need to extend the length of a column in a table, perhaps because business conditions have changed necessitating longer values. For example, (for V7 and all previous releases) to change a CHAR(10) column to CHAR(15), the following steps need to occur:

      1. Unload the data, extract the DDL and authorizations for the table you are about to change and all dependent objects, (indexes, views, synonyms, triggers, and so on).
      2. Drop the table.
      3. Modify the DDL for the table change the length of the particular column from 10 to 15.
      4. Run the CREATE statement to re-create the table.
      5. Run the CREATE statements for the dependent objects (indexes, views, synonyms, triggers, etc.).
      6. Re-build the authorizations for the table by running the appropriate GRANT statements.
      7. Re-create all dependent objects and re-build their authorizations.
      8. Reload the data taking care to build the LOAD statement properly because there is a new column right smack dab in the middle of the other columns.
      9. Don’t forget to run RUNSTATS to gather statistics for optimization and run COPY to backup the data in its new format after it is loaded.
      10. REBIND all affected plans and packages.
      11. Test everything to make sure your changes were implemented correctly.

You can see how this intensive manual process can be very time-consuming. And the data is unavailable for the duration of this procedure. Furthermore, if you miss any single item or step the resulting database structures will not be accurate and problems will arise.

DB2 V8 and online schema evolution does not do away with the need for such products. Automation and control over database change is still required by production DBAs.
Of course, not all database changes require such drastic steps. Many changes can be made using a simple ALTER to change specific aspects of a DB2 object. Sometimes the object will need to be stopped and started for the change to take effect; sometimes not. Online schema evolution begins the process of making more types of database change possible without requiring objects to be dropped and re-created.

DB2 V8 begins the process of making it easier to implement database changes with fewer steps and less downtime. IBM calls the changes being made to DB2 to facilitate simpler and quicker database changes online schema evolution. For example, as of V8, you can change a CHAR column to a larger size simply using ALTER. The remainder of this article will focus on the improved schema changes supported by DB2 Version 8.


Third party ISV tools are available that make the process of modifying DB2 database structures easier. These products provide a simple menu-driven interface that allows the user to indicate the changes to be made. The tool is used to automate the DROP and re-CREATE process. An example of such a product is BMC Software’s Change Manager.

DB2 database changes are more flexible with DB2 V8 due to online schema evolution. IBM also refers to this feature as simple schema evolution, online schema changes, or just schema changes.

IBM introduced online schema evolution with DB2 V8 with the intention of, over time, allowing DB2 databases to be altered without causing an outage. Of course, this is the long-term goal of online schema evolution. We are in the first phases of this evolution with DB2 V8 - and remember, evolution is a very lengthy process. It took many millions of years for life to evolve on Earth. The point is, it will take a long time for online schema evolution to enable every type of database change to be carried out without downtime.


For literal-minded readers, it most likely will not take millions of years for online schema evolution to support most types of database changes. I am simply using biological evolution as a metaphor for schema evolution.

Online Change Management Prior to V8

Of course, DB2 has offered some degree of online change management even prior to V8. For example, DB2 has provided the following capabilities for quite some time now:

      • You can add a column to the end of a table without having to stop access to the table or perhaps even modify any programs that access that table.
      • A table can be renamed without dropping and re-creating the table.
      • You can use ALTER to extend the length of a VARCHAR column to a greater size (but not to a smaller length).
      • Application changes can be introduced and managed using package versioning.
      • For a DB2 data sharing complex changes to the DB2 engine code of a single member via PTFs while other members remain active.
      • The REORG and LOAD RESUME utilities can be run online while concurrent workloads are being run against the data being reorganized or loaded.

And these are just a few of the capabilities of DB2 change management. But some changes are not quite so easy.

Online Schema Changes for V8

Online schema evolution for DB2 V8 introduces some nice, new capabilities for managing database changes. What exactly can be changed today, with DB2 Version 8?

You can extend the length of a CHAR 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 character.

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 columns.
      • You can add a column to an index.
      • You can change the clustering index for a table.
      • You can make many changes to partitioned and partitioning 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 re-balance partitions during a REORG.
      • You can better create and support indexes on variable length 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 pending).

Changing the Data Type of a Column

Sometimes it becomes necessary to change the data type of a column for an existing DB2 table. Prior to V8 this required dropping and re-creating the table, but as of V8 a column’s data type may be changed if the data can be converted from the old type to the new without losing significance. Essentially this means that the new column definition has to allow for “larger” values than the current column definition.

The ALTER TABLE statement can be used to change the data type of a column as indicated in Table 1. If the combination is not shown in this table, it is not supported - that is, you cannot use ALTER to make such a change.

Current Date Type             Supported New Data Type(s)       




DECIMAL(n,m) DECIMAL(n+x,m+y)
CHAR(n) CHAR(n+x)





Table 1: Data Type Changes Supported By ALTER.

To change a data type under DB2 V8 you will use the SET DATATYPE clause of the ALTER TABLE statement. For example


This is possible because the current data type of EDLEVEL was SMALLINT, so it can be changed to a DECIMAL with a scale of 5 or greater.

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. Versioning is discussed in more depth later in this article.

Keep in mind, though, that the existing data is not changed or reformatted on disk. Instead, when data is retrieved, 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 with minimal performance degradation.

Limitations on Changing a Data Type

Keep in mind that the data type can be changed only for character and numeric data types. You cannot change the data type of ROWID, DATE, TIME, TIMESTAMP, or FOR BIT DATA columns, nor can you change the length of a LOB column.

Additionally, you cannot change the data type or length of a column under the following circumstances:

      • The column is part of a materialized query table.
      • The column is part of a referential constraint.
      • The column is defined as an IDENTITY column.
      • The column has a FIELDPROC defined on it.

There is an EDITPROC or VALIDPROC defined on the table in which the column resides.
For each of the above bulleted items you will need to DROP and re-CREATE the table to modify the data type or change its length.

Impact of Changing a Data Type

When changing the data type for a column you need to be aware of the impact the change will have on other DB2 facilities and database objects. For example, when any column in a table has its data type changed, the plans, packages, and cached dynamic statements that reference the changed table are invalidated.

After changing a data type or length using ALTER, be sure to analyze all of the application programs that reference the column - using either static or dynamic SQL. You can query SYSIBM.SYSPLANDEP and SYSIBM.SYSPACKDEP to find which plans and packages reference the changed column’s table using static SQL. But you will have to use other means such as your data dictionary or a SQL performance monitor to find dynamic SQL dependencies. As you examine the programs that are potentially impacted, pay particular attention to the host variables that are used in conjunction with the column. You will probably have to change the definition of the host variable to conform to the new definition of the column. Failure to do so can cause data to be truncated. For example, if a column is changed from CHAR(x) to CHAR(x+y), the processing application truncates the last y bytes unless the application is changed to accommodate the longer column.

Statistics in the DB2 Catalog are also an issue. Any distribution statistics for the column in SYSIBM.SYSCOLDIST and SYSIBM.SYSCOLDISTSTATS will be invalidated when its data type changes. Additionally, the STATSTIME column in SYSIBM.SYSCOLUMNS will be set to January 1, 0001. This tells the optimizer to ignore the distribution frequency statistics. Be sure to run the RUNSTATS utility to repopulate the catalog with accurate column and index statistics as soon as possible after changing a column data type or length.

Table spaces, indexes, and views are the obvious database objects that will need to be modified when a data types is changed. Check constraints are impacted, as well.

Every data type change requires the column’s table space to be modified. Upon completion of a data type change, the table space will be placed in an AREO* exception state, which stands for Advisory REORG Pending. Users can continue to access the data while the table space is in the AREO* state, but there will be a performance impact because the columns will need to be converted from the old format to the format of the new data type. Additionally, when the data is modified, the entire row will be logged. Performance will continue to suffer until the table space is reorganized.

Indexes need to be changed if the column whose data type has changed participates in an index. The availability of the index depends upon the data type of the column being changed.

The index will be immediately available for use if a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC column is altered to increase its length. Altering a numeric data type to increase its length will result in delayed availability for the index. This includes columns defined as SMALLINT, INTEGER, DECIMAL, NUMERIC, FLOAT, REAL, or DOUBLE. The index is not immediately available because changes to numeric data would create severe performance problems. Instead, the index is placed into RBPD exception state, which stands for REBUILD Pending. If an entire index is rebuilt from the data, all the keys are converted to the latest format. The utilities that can be used to rebuild an index include REBUILD INDEX, REORG TABLESPACE, and LOAD REPLACE.

If the data type changes (for example, from SMALLINT to DECIMAL), reorganizing the index will reformat the index keys to the latest version unless the index is in ARBDP (Advisory Rebuild Pending). An index in the ARBDP exception state requires access to the data is to determine the length of the index key.

Views that reference an impacted column will be immediately regenerated. DB2 will examine the DB2 Catalog to perform this regeneration. Affected views are retrieved from the SYSIBM.SYSVIEWDEP table and then SYSIBM.SYSVTREE and SYSIBM.SYSVLTREE are used to review and modify the parse tree for the views. Keep in mind that a view can be created on another view, so this process may be recursive.

Finally, when check constraints exist on a column whose data type or length has change, the constraints will be regenerated.

Changing an Index

Prior to DB2 V8, the aspects of an index that could be altered were limited to mostly storage characteristics. With V8 online schema evolution additional index attributes can be changed: you can use ALTER to add columns to an index, change the clustering specification, and modify the manner in which varying length index keys are treated.

Adding Columns to an Index

To add a column to an index under DB2 V8 you will use the ADD COLUMN clause of the ALTER INDEX statement. For example, to add a column to the XDEPT2 index (currently defined on the MGRNO column only):


Running this ALTER statement adds the ADMRDEPT column to the existing XDEPT2 index on the DSN8810.DEPT table. The new column will be appended to the end of the existing index key; you cannot change the existing order of a key or append a column to the beginning of the index key.

What is the impact of adding a column to an index? Well, if the column is added to both the table and the index in the same unit of work then the index is immediately available for use and it is put in the AREO* exception state. If the column was not added to the table in the same unit of work, the index is put into the RBDP exception state. This would be the state of the example we just reviewed. Finally, if the index was created specifying DEFINE NO, then no exception state is set and a new version of the index is not created; the index is simply changed in the DB2 Catalog awaiting eventual definition.

Changing Clustering

Sometimes it becomes necessary to adjust clustering; that is, the manner in which DB2 attempts to store data physically on disk. You might wish to change how data is clustered because access patterns have changed, the initial clustering specification was wrong, or for any other possible reason.

Prior to V8 changing clustering required dropping the clustering index and recreating it without the CLUSTER keyword. To change clustering in DB2 V8 you can use the ALTER INDEX statement to specify either CLUSTER or NO CLUSTER. For example:


Running this ALTER statement will change the XPROJAC1 index such that it no longer controls clustering. Keep in mind, though, that simply removing explicit clustering may not change the clustering specification for the table space. Until another clustering index is specified for the table DB2 will continue to use the index that was just changed as the implicit clustering index.


If no explicit clustering index is specified for a table, the first index created on each table will be used as the implicit clustering index. 

When the clustering index is changed, new INSERT statements will cause data to be placed using the new clustering order. However, existing data is not immediately re-clustered. Existing data will not be affected until the next time the table space is reorganized.

Of course, you are still restricted to having only one clustering index at any one point in time. So, you will need to order and time the execution of your ALTER INDEX statements so that there is never a state when two clustering indexes exist at the same time. For example, to change the clustering index from IX2 to IX5 you would issue the following sequence of ALTER statements:



You can use the ALTER statement to re-specify the clustering of your existing partitioned table spaces if you so desire. Prior to DB2 V8, the partitioning index for partitioned tables also had to be the clustering index. This is no longer the case. You can now specify a partitioning key that is not also the clustering key for a table.

Changing the Treatment of Variable Index Keys

Prior to DB2 V8, specifying a variable length column in an index caused DB2 to pad the data to its maximum length in the index key. This is no longer a requirement because DB2 V8 allows you to specify PADDED or NOT PADDED to control whether the index key should be padded to its maximum length. This specification can be made when the index is defined using CREATE INDEX or changed using ALTER INDEX.


The default is PADDED when you migrate from V7 to V8 in order to maintain compatibility with past implementations. However, for new V8 installations the default is NOT PADDED.

A new DB2 V8 DSNZPARM named DEFIXPD can be used to change the default.

When changing an index from PADDED to NOT PADDED the index is placed in the ARBDP exception state and a value of 'N' is placed in the PADDED column of SYSIBM.SYSINDEXES. The index must be rebuilt because DB2 cannot determine the accurate length of the index key without accessing the table space. The index is not available for use until it has been rebuilt, thereby setting all of the keys to varying length, and resetting the pending state.


Be aware that recovery to a point-in-time may cause the ARBDP exception state to be set (if that point in time was before the index was rebuilt).

You can also change an index from NOT PADDED to PADDED using ALTER INDEX. If the index has varying length columns, it is placed in the AREO* exception state and a value of 'Y' is placed in the PADDED column of SYSIBM.SYSINDEXES. The index is available for use but performance will suffer. The index can be rebuilt or reorganized to pad the keys to the maximum length and reset the pending state.

Whenever the padding attribute of the index is changed DB2 creates a new version of the index in the DB2 Catalog.

Changing Table Space Partitioning Specifications

Historically, one of the biggest impediments to managing DB2 database systems has been administering partitioned table spaces. Prior to DB2 V8, it was either difficult or impossible to modify the structure and many of the parameters of a partitioned table space. Exacerbating this problem is the fact that most partitioned table spaces are the largest, most critical, table spaces in the system with the highest availability requirements. Fortunately, DB2 V8 removes many of the barriers to managing partitioned table spaces.

With DB2 V8 you gain the ability to immediately add partitions, rotate partitions, and change the partitioning key values. In order to gain this flexibility, though, you will need to change from index-controlled partitioning to table-controlled partitioned tables. Then you can use the ALTER TABLE statement to modify most of the partitioning specifications.

Adding Partitions

To add a partition to an existing table space the ALTER TABLE statement has been augmented with the ADD PART parameter. For example, consider a table space that is partitioned having one fiscal quarter worth of data per partition. Eventually, you may run out of partitions and need to add one. Assume that the last partition holds data up to the third quarter of 2004, but now you need to add data past this date. You can based on to add a partition to the EMP table:

ADD PART VALUES('12-31-2004');

Of course, this assumes that your fourth quarter ends in December.

You do not specify a partition number when you add a partition. DB2 will determine the next partition number to be used by examining information in the DB2 Catalog.

Along with adding a new data partition, a new partition is added for each partitioning index. This can include both the partitioning index and data partitioned secondary indexes (DPSIs), as well.

You can add partitions up to the maximum limit; the maximum number of partitions depends on the DSSIZE parameter and page size of the table space.

You cannot specify attributes like PRIQTY and SECQTY; instead, DB2 uses the values in use for the previous logical partition. Before you begin to use the new partition, you should execute an ALTER TABLESPACE statement to provide accurate space parameters for the new partition.

If you are using STOGROUPs the next data set is automatically allocated for the table space and each partitioned index. When your DB2 objects are user managed (VCAT), you must pre-define the data sets using VSAM IDCAMS.

Each newly added partition will be immediately available for use, but you must stop the table space and partitioned index before adding the partition. When adding a partition the table will be quiesced and all related plans, packages and cached statements will be invalidated. This is required because certain access paths might be optimized to read only certain partitions. Automatic rebinds will occur if AUTO REBIND is enable, but rebinding manually is usually a better approach to avoid performance problems as applications wait to rebind before execution.

Rotating Partitions

If the requirement to add a partition can be satisfied by allowing an existing partition to be reused, you might be able to rotate the partition. Rotating partitions allows old data to “roll off,” but the partition is kept for new data. This is a good option in the following situations:

      • A year of data is kept in 13 partitions
      • Data is stored with a quarter in each partition but only the last 20 quarters (5 years) are needed online; this might be any number of quarter or years
      • Any time old data is periodically archived and only a limited number of partitions need to be active

Partition rotation is implemented using the ALTER TABLE ALTER PART ROTATE FIRST TO LAST statement. When rotating, if you specify the RESET parameter, the data rows in the oldest (or logically first) partition are deleted, and a new table space high boundary is set so that partition becomes the last logical partition in sequence. This partition will then be ready to hold the new data as it is added. The partition that was rolled off is immediately available after the ALTER succeeds; a REORG is not required.


When specifying RESET the existing data in the oldest partition is deleted and SYSIBM.SYSCOPY and SYSLGRNX rows associated with the partition being reset will be deleted, too.

The aftermath of rotating a partition can be confusing. This is especially the case if you are trying to match partitions to physical data sets. The .A001 data set is now the last logical partition, not the first. You will need to use the new LOGICAL_PART column in the SYSIBM.SYSTABLEPART table to match partitions to data sets. The DISPLAY command will list the status of table space partitions by logical partition.

Also, steps need to be taken if you need to keep the rolled off data for archival purposes. Be sure to unload the data immediately before rotating the partition using either the UNLOAD utility or a user-written program.

If this REUSE option is specified, a logical reset of the partition is done instead of deleting and redefining data sets. Existing extents for the partition will be kept.

Changing Partition Boundaries

DB2 V6 introduced the ability to modify limit keys for partitions. DB2 V8 adds the same capability for table-based partitioning with the ALTER TABLE ALTER PART VALUES statement. The affected data partitions are placed into the REORP pending state until they have been reorganized.

Rebalancing partitions

You can rebalance partitions when running DB2 V8, too. Unlike the schema changes previously discussed, partition rebalancing is accomplished using the REORG utility instead of the ALTER statement. When reorganizing a table space you can specify a new parameter, REBALANCE, indicating that new partition boundaries should be set for the range of partitions being reorganized. DB2 will rebalance the data such that it is evenly distributed across the partitions. Rebalancing is most practical when the data is not skewed greatly.


When many duplicate values occur in the columns that define the partition boundaries DB2 may not be able to evenly balance the data effectively.

Yes, you are reading this right. Running a REORG with the REBALANCE option can change the limit key for partition boundaries. The REORG will set new partition boundaries so that all the rows participating in the reorganization are evenly distributed across the partitions being reorganized. DB2 will update the SYSIBM.SYSTABLEPART and SYSIBM.SYSINDEXPART tables to record the new limit key values.

Using REORG to rebalance partitions has its advantages. Using this approach your partitions will not be placed in the REORP exception state as would be the case if you changed the partition boundaries using ALTER TABLE.

Keep the following restrictions in mind when considering whether to rebalance your partitions using REORG:

      • You cannot specify the REBALANCE keyword if you are reorganizing a table space using the SHRLEVEL CHANGE option.
      • You cannot specify the REBALANCE keyword with any of the following keywords: SCOPE PENDING, OFFPOSLIMIT, INDREFLIMIT, REPORTONLY, UNLOAD ONLY, and UNLOAD EXTERNAL.
      • You cannot rebalance a partitioned table space that also has LOB columns.
      • When the clustering sequence does not match the partitioning sequence, REORG must be run twice. The first REORG moves the rows to the right partition; the second REORG sorts the data into clustering sequence. After the first REORG DB2 places the table space in the AREO* exception state - meaning that DB2 recommends running another REORG (to cluster the data).


After rebalancing is complete, plans, packages, and the dynamic statement cache records that reference the reorganized object will be invalidated.

Versioning for Online Schema Changes

To support online schema changes DB2 has been enhanced to support multiple versions of DB2 objects. As certain schema changes are made, DB2 creates a new version to refer to the new structure. Multiple versions can exist at one time, each version referring to the object at a different stage of its life.

Issuing an ALTER for an existing DB2 object or column can cause a new format to be needed for tables, table spaces, and/or indexes. DB2 needs to know about the old format and the new format because the all of the underlying data for an object (as well as its image copies) cannot be changed immediately to match the format of the latest version. By supporting multiple versions with different formats over time for tables and indexes, maximum data availability is achieved.

DB2 references the version information to appropriately store and use the data in its correct format. Versioning is tracked and recorded in the OLDEST_VERSION and CURRENT_VERSION columns in the following DB2 Catalog tables:








SYSIBM.SYSOBDS is a new V8 DB2 Catalog table that contains one row for each table space and index that can be recovered to an image copy that was made before the first version was generated.

But the version information relevant to the data also is stored in system pages embedded in the table space or index page set. The system pages are stored along with the data so the data can be properly interpreted. Doing so makes table spaces and indexes self-defining. Additionally, with the version information embedded in the page set data can be accessed or unloaded from an image copy without DB2 being up.


DB2 supported versioning prior to V8. When an indexed VARCHAR column in a table is enlarged a new index version is created and tracked using the IOFACTOR column of SYSIBM.SYSINDEXES.

In DB2 V8, the first ALTER that creates a new index version switches to DB2 V8 versioning by setting the OLDEST_VERSION and CURRENT_VERSION columns to the existing versions in the index.

The OLDEST_VERSION is the oldest format of the data in the object itself or any image copy still registered in SYSIBM.SYSCOPY. There is an upper bound for version numbers. A table space can have up to 256 different active versions; an index can have up to 16 different active versions.  A version is active if it is used on any page within a page set (table space or index) or is in use in an existing image copy still registered in the DB2 Catalog.


When the upper bound is hit for a version and a new version must be created, the version number will wrap back to the beginning[md]starting again at version 1 (not zero). So it is possible that the CURRENT_VERSION is a lower number than the OLDEST_VERSION.

Of course, DB2 will not wrap if version number 1 is still an active version. If the maximum number of active versions is reached, a –4702 SQLCODE will be returned and the ALTER will fail.

An object that is never altered will remain at version zero.

When is a New Version Generated?

A new version is created for the table or index that is impacted whenever the following types of changes are made:

      • ALTER TABLE table-name ALTER COLUMN column-name
      • SET DATA TYPE altered-data-type
      • ALTER INDEX index-name NOT PADDED
      • ALTER INDEX index-name PADDED
      • ALTER INDEX index-name ADD COLUMN column-name
      • Multiple ALTER COLUMN SET DATA TYPE statements in the same unit of work are included in one new schema version.

Inactivating Versions

For table spaces, and indexes defined as COPY YES, the MODIFY utility needs to be run to update the LOW_VERSION in the DB2 Catalog and reclaim the version by making it inactive. If there are entries for a COPY, REORG, or REPAIR VERSIONS remaining in SYSIBM.SYSCOPY for the table space, MODIFY updates LOW_VERSION to be the lowest value of LOW_VERSION found from matching SYSCOPY rows. If no SYSCOPY rows remain for the object, MODIFY sets LOW_VERSION to the lowest version data row or key that exists in the active page set.

For indexes defined as COPY NO, running a REORG, REBUILD, or LOAD utility that resets the entire index will update the LOW_VERSION in SYSIBM.SYSINDEXES to be the same as HIGH_VERSION.

Database Exception States for Online Schema Changes

Exception states are used by DB2 to alert administrators and users to a database condition that needs to be managed or improved. Exception states have been used in all past releases of DB2 but two new states have been created to support online schema changes. Throughout this article we have discussed these new database exception states, but we will review them here for easy reference:

Advisory Reorg (AREO*):[md]indicates that the specified table space, index, or partition needs to be reorganized for performance to improve.

Advisory Rebuild Pending (ARBDP):[md]indicates that the specified index needs to be rebuilt to improve performance and to allow DB2 to choose the index for index only access.

As with any DB2 database exception state, these states will appear when you issue the DISPLAY command to monitor the status of your database objects.

Online Schema Change Implementation Considerations

Keep in mind that existing access paths can become inefficient when a new version of an object is created. Therefore, making an online schema change might cause performance to degrade. Just because IBM has made it easier to implement changes to database objects does not mean that changes can be made indiscriminately and without planning.

Be sure to treat every database change as a potential impact to performance and availability. Whenever possible, schedule schema changes as close to a scheduled reorganization as possible. This will minimize the potential performance impact.

Certain types of online schema changes are more invasive than others. For example, rotating partitions can be very time-consuming. When rotating partitions of a partitioned table, the reset operation requires that the keys for deleted rows also be deleted from all NPIs. Because each NPI must be scanned to delete the keys this activity can consume an inordinate amount of elapsed time to complete.

Individual delete row processing is required for referential integrity relationships, when DATA CAPTURE is enabled, or when there are delete triggers. In such scenarios be sure to factor in additional time to delete data a row at a time.


DB2 V8 offers a bevy of new options for changing DB2 tables and structures with less impact to availability. New capabilities are sure to be introduced in future releases as IBM improves and evolves DB2’s online schema change facilities.


Craig Mullins is an independent consultant and president of Mullins Consulting, Inc. Craig has extensive experience in the field of database management having worked as an application developer, a DBA, and an instructor with multiple database management systems including DB2, Sybase, and SQL Server. Craig is also the author of the DB2 Developer’s Guide, the industry-leading book on DB2 for z/OS, and Database Administration: Practices and Procedures, the industry’s only book on heterogeneous DBA procedures. You can contact Craig via his web site at

Contributors : Craig S. Mullins
Last modified 2006-01-16 04:38 AM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!

Powered by Plone