January 07, 2014

ALTER Operations Restricted in DB2 11

DB2 11 introduces new restrictions that limit the types of ALTER operations that can be conducted on an object.

The restrictions pertain to the IBM solution known as Online Schema Evolution. They generally involve having a mixture of alters that are immediate, as well as alters that place an object in REORG-pending status. A sample scenario is presented in PMR58791, "Workaround for ALTER operations that can no longer be done together in Version 11."

My takeaway from this is that, whenever possible, DBAs should perform all ALTER operations that take effect immediately prior to performing the ALTER operations that put the object in REORG-pending status.

For related information, see my previous posts about Online Schema Evolution. This one, from back in September 2007, covers enhancements in DB2 9. In July 2010 I wrote about the updates in DB2 10.