Figure 1 shows the workflow for the UDCL process in DB2 for z/OS. Creating and testing UDCL scripts is difficult and time-consuming. You must know what you’re doing. Because UDCL will always be required and will only become more complex, change management tools are a prudent investment.

The time needed to implement most changes is much more than creating the simple Data Definition Language (DDL) command or creating and testing the UDCL script. It includes the time needed to analyze which objects will participate in the change and which follow-up actions you must take (such as REBIND or run RUNSTATS again). Sometimes, a change will affect only a single object and a few programs, but when a new version of a complete business application is involved, a change may affect hundreds of related objects. UDCL scripts quickly become extremely complex because they must identify and include dependencies. When re-creating the objects, you must be aware of the correct re-creation order; otherwise, the DDL (or worse, the application logic) could be changed.

Why Change Is Risky

Consider this scenario. You forgot an index; it was dropped but never re-created. You REBIND the packages and some transactions now have bad response times, which results in grumpy users. You need to debug and fix the situation, but at least no data is lost.

But what if you forgot a trigger? Triggers are part of the business logic, and now that essential logic is missing. Before the error is detected and fixed, many transactions could have been processed and data might have been corrupted. Correcting this issue could take days and affect your customers.

In scenarios such as these, no SQL error is given and no return code is set to tell you there’s a problem. Simple mistakes can turn into big disasters. Other serious risks are involved:

• When you REBIND an application, DB2 can opt for a different access path. Even if you haven’t made any mistakes in re-creating the objects and have run RUNSTATS correctly, DB2 could still take a different access path. Usually, the new access path performs as well or better than the original one, so most DBAs consider this an acceptable risk. But what if the new access path turns out to be more costly?
• If you implement a change and the implementation is rejected during testing or after the application is used in production, then you must back out the change completely. Many DBAs don’t create a back-out scenario, and they try to fix the error “on the fly.” When this proves impossible, they must manually back out the change without any guarantee of success.

Change management and SQL quality assurance tools can prevent mistakes such as these and reduce risk.

Often, even if you undo structure changes, no one looks after the data. The DBA may have no knowledge of the business processes that affect the data in the application. Has it been corrupted during the test or updated by users? The only way of undoing everything is to go back to a time when the subsystem and the application were in sync, and the only way to do this quickly is to use snapshot technology for all disks involved.

DB2 10 Improvements

IBM made many online change enhancements in DB2 10, including a DDL pending feature that accommodates changes to physical parameters such as pagesize, segsize, and dssize. Similar to the previous releases of DB2, the DB2 REORG utility will implement these parameter changes during the next REORG. Pending DDL is different from versioning, as there’s no resulting CPU overhead. With pending DDL, some changes will move from the high impact to the online change category, but there will still be many changes that require a UDCL scenario.

Be aware there are again changes to the optimizer calculating access paths (i.e., I/O parallelism for index updates) during BIND. So you will see different access paths for the same SQL in DB2 9 and DB2 10! When you’re in a transition phase with DB2 10 in test and DB2 9 still in production, you need to consider this when making changes.

Summary

Making changes in DB2 isn’t easy, and every change can involve risk. If your application is complex and you exploit many DB2 features, creating a UDCL script manually can be a nightmare. Whether or not you use tools, you must properly plan, test, and execute every change. Be prepared to fall back. Every good change strategy includes a fallback strategy.

This article is based on a white paper written by Klaas Brant for BMC Software. More about this topic can be found at http://documents.bmc.com/products/documents/30/43/183043/183043.pdf.