A UDCL change is the most complex of all. To implement the change, you must drop and then re- create the object. But, of course, before you drop an object, you must save the contents by unloading the data and then reload the contents into the new table after the object is re-created. This process is called “UDCL” denoting the steps you have to take: Unload, Drop, Create and Load.
This might sound straightforward, but there can be massive implications. First, the original data type and the target data type might be a mismatch (otherwise it is a simple conversion and a type 2 change). Because we want to use the LOAD utility for the load step, we need the data conversion to occur during the UNLOAD process. In many cases the UNLOAD and LOAD utilities will not be of help, because they provide only limited conversions. So we need to write a customized unload conversion program. The DROP has a cascading effect - all objects related to the dropped object are also dropped. (Note: Referential Integrity constraint is also regarded a related object; a child table will lose its foreign key definition). When the object is re-created, the related objects like indexes, triggers and constraints must be re-created (using DDL saved in the unload step). That is, if it is possible at all (think of a drop of column and the effect it could have on indexes referencing that column). Finally, all security related to the dropped objects is removed from the DB2 catalog. So security privileges must be reestablished after all objects are re-created (using DCL saved in the unload step).
A “simple” change like removing a column from a table can easily result into a script of hundreds of lines (SQL and utilities). Some changes can be so complex that they are almost impossible, even for an experienced DBA and even a small scripting mistake can have a massive impact.
Page |43
Examples:
Application used an integer column to record a date in DDMMYYYY format. Decision is made to convert the column to a DATE column to allow DB2 functions related to dates. Because of changes in the law, an application has obsolete data that is no longer needed.
The columns in use for this must be removed.
A new release of an application was scheduled for the weekend but final tests revealed a performance problem so severe that decision is made to return to the previous version. All of these changes require an in-depth analysis and an implementation script.
Risk High to very high. A small mistake can result in bad performance or a loss of data or data corruption.
Time needed High to very high. The many drop and re-creates plus their follow-up actions can cause an implementation script to run a long time. In DB2 10 the time needed for these scripts will increase because of the changes made in the catalog. Creating the best possible script is imperative. If the UNLOAD utility cannot unload the data, you need to unload the data with a SQL application. SQL is much slower than a utility.
Audit Keep the script and the execution of the script. Make sure that you know what the situation was before the change so you can undo the change (see below). Undo In the last example, you need to return the objects to their previous state. Under
normal circumstances you compare the new (desired) situation to the existing catalog state and then create your script to move forward. In the last example, you must compare the existing catalog state to the old situation to go backwards. This means that you must store all of the old situation information somewhere. After a change implementation, it is wise store your complete current situation (often referred to as a baseline), so you can make these reverse comparisons can be made and you can return to the previous baseline.
How BMC can help
For all three types of changes, you will need to prepare, deploy, audit and (hopefully never) undo the change. Depending on the number of changes and objects, and the amount of data to handle with utilities, this can take up quite a long time. Manual preparation for any type of change increases the risk of making mistakes and running into performance, or even worse, availability problems. The more data you need to handle (type 3 but also type 1 and 2), the more time you will need for the deployment of changes. All this affects the lifecycle of the application and negatively affects the go- to-market time of new business services.
BMC CHANGE MANAGER for DB2 completely manages preparation, deployment, auditing and, if needed, undoing of any type (1,2,3) of change.
Page |44
BMC CHANGE MANAGER for DB2 simplifies and automates change management and ensures the integrity of structures and data. With BMC CHANGE MANAGER for DB2, you can extract changes to a data structure on one DB2 subsystem and then apply them to the corresponding data structures on other DB2 subsystems, preserve data and local modifications, and fall back to a previous version of the database if needed. You can even synchronize database schema versions across multiple subsystems.
BMC CHANGE MANAGER for DB2 automates change management in three phases:
In the specification phase, BMC CHANGE MANAGER for DB2 lets you define all of the information needed to make changes to affected tables, indexes and other objects, or lets the built-in Compare feature determine which DB2 objects need changes.
After you create change or migration requests in a work ID, the Analysis component checks the requests for validity with the DB2 catalog, develops an optimal implementation strategy, and generates a worklist – the script. The worklist contains the DDL,DB2 utility commands, AMS commands, and security commands that are necessary for implementing the requests. In addition, the Analysis component propagates changes to dependent structures.
The Execution component (deployment) executes the commands that are contained in the worklist that the Analysis component generates for change or migrate requests. The tasks that are required to implement a worklist vary depending on the type of change. Execution can take a long time when many objects with a lot of data must be handled – especially for type 3 changes.
BMC CHANGE MANAGER for DB2 also supports mass changes with CM/PILOT, which acts like a wizard for defining and executing mass changes. It enables you to specify changes once and apply them multiple times, to multiple objects and to multiple databases. You set up tasks that can be performed immediately or at a later time by someone else or through job scheduling. With
CM/PILOT you can ensure that the change management task is performed the same way every time it is executed.
Unavailability during the deployment
In almost all cases, there is a period of unavailability during the deployment of a change script. Even for type 1 changes, DB2 forces a restricted status upon the in many cases. If we are very aware of what we are doing, we might lift that status very rapidly.
Page |45
In the example type 1 change where a table column needed a domain and the domain should be enforced, we established a Referential Integrity constraint. If the parent table is populated using select distinct of the foreign key column (in the child table), then the CHECK condition and the execution of the CHECK utility is not needed. In this case, we can quickly remove that restriction with REPAIR NOCHECKPEND. But if we look at a complex type 3 change, the object is dropped and then re-created, causing a long period of unavailability; related objects can be placed in a restricted status for a period of time during this process. When creating online change (type 2) IBM strived toward 100% availability, hence the name online. At the price of performance degradation, this is a success. But for most online changes, the application itself must be changed and a new version of the program or programs must be deployed. It is very difficult (if even possible) to create a scenario that results in 100% availability when application code is involved. Often we assume it can be done by using data sharing, but this not true. In data sharing, all members share the same catalog and suffer the same consequences of a change deployment.
How BMC can help
BMC Software can reduce the window of unavailability during the deployment of a change
dramatically. For type 3 changes, you can unload and load the data with BMC UNLOAD PLUS for DB2 and BMC LOADPLUS for DB2, which are high speed alternatives to native IBM utilities. If available, zIIP engines are used to move workload from away from the busy general purpose processors.
In addition to the speed of BMC UNLOAD PLUS for DB2 and BMC LOADPLUS for DB2, you can use the worklist parallelism. With worklist parallelism you can deploy multiple changes in parallel (using multiple initiators on the same LPAR or on multiple LPARs within the same sysplex) instead of sequentially. This saves a lot of time when you need to unload and load a lot of data for many objects. Worklist parallelism is a component of BMC Database Administration for DB2.
Keep your data healthy
Every change to the data structures has an impact on the health of the data. To ensure that you can always assess your data’s condition, you must include two important items in your change scripts: statistics and a new recovery point. During the change deployment process, the existing statistics can be partially wiped out (for example, during a type 2 change, statistics like HIGH2KEY, LOW2KEY and frequency are removed from the catalog), and (re)created structures have no
Page |46
statistics at all. This means that in many scripts you need to run RUNSTATS after the loads or reorgs. New statistics could trigger a rebind. Of course, rebinds are advisable anyway because the packages were invalidated by the script.
The second important piece regarding health is to establish a new recovery point. Depending on the nature of your change, a recovery with an older image copy might be impossible, if they even exist. Sometimes you can combine utilities in a smart way (for example, do a LOAD and COPY together); other times you need to include a separate COPY into the script to keep the data healthy from a recovery point of view. Be aware that if you deploy special scenarios, like hardware related backup and recovery, you must be extra careful. You must establish a new recovery point before you can do a recovery using this special scenario (an example would be IBM’s recovery expert system backup).
How BMC can help
Together with the BMCSTATS feature of BMC DASD MANAGER for DB2, BMC LOADPLUS for DB2 can gather up-to-date object statistics during the load. The BMCSTATS utility is executed as part of the load process, and it performs much faster than IBM RUNSTATS while using less CPU to gather statistics. If zIIP engines are available, the BMCSTATS code is eligible to run on those specialty processors. All of this helps you to speed up the deployment of changes.
Risk of altering scripts
It is very tempting to create a script in a non-production environment and then deploy that script with a small change (manual edit). Even though you have used your script in the non-production environment, there is no guarantee that the two environments are 100% identical. If they are not, you may need to compensate for the differences and you may need to edit the script even further. Before you know it, you are looking at an untested script again with an increased risk when the script is executed.
How BMC can help
BMC CHANGE MANAGER for DB2 can creates a valid script for any environment based on a single change request. No editing is needed.
Page |47
Deployment and impact of changes
All change types (1, 2 and 3) involve DDL statements. To keep the definitions of a database stable, DB2 locks the database descriptor (DBD) in an exclusive mode during DDL execution. This has negative effect on running applications. Before DB2 10, the catalog was not suited for running multiple DDL streams concurrently, so often changes needed to be implemented serially. Almost every DDL change has an impact on the programs using the data structures. Even for changes type 1 and 2, DB2 will regenerate certain views and invalidate packages and the dynamic statement referencing the changed structures. To deploy a successful change, you need to do an in- depth analysis and create a script that not only includes the change itself, but also all steps to fix the side effects, like RUNSTATS and REBIND. It is wise to review the health of your system after each change. Determine if any tablespaces are in a restricted status (issue a -DIS DB(*) SP(*) LIMIT(*) RESTRICT and –DIS DB(*) SP(*) LIMIT(*) ADVISORY) and query the catalog for plans and/or packages that need rebinding (see Chapter 5, “Managing the DB2 catalog”).
A special undo scenario
If you are able to hold all production programs until the changes are implemented and tested, you might have a unique opportunity to undo your changes in a 100% guaranteed and fast way: bring the whole system (including all data) back in time. There are several scenarios: DB2 has supported system backup/restore since Version 8, but shutting down DB2 and using disk snapshot or decoupling mirror disks can create nice scenarios that guarantee that the whole system can be restored as it was before the change implementation.
Is an audit needed?
Auditing is not only the domain of the company’s auditor, although it would be wise for them to be included in the change process. While implementing changes, the system is vulnerable. For
example, when you are unloading and loading data, mistakes are easily made, especially when you have to restart your script after an error. This is the perfect moment for someone to insert malicious code or alter data. An extra file concatenated to the load file would allow for inserting data into a normally restricted table. Therefore, it is wise to build in audit checks to detect mistakes and attempts to alter or damage the system.
Page |48
Here are a few tricks: count the number of rows in the tables using select count(*) from table - are they the same before and after the change? If not, can they be explained? You can compare the program source programs; compare the changed lines in the programs that are part of the change and carefully look at SQL and logic that is changed. As always, it is wise to have someone review these changes (“another pair of eyes” principle).
What’s new in DB2 10
In the releases prior to DB2 10, IBM made it possible to ALTER most table attributes. Through the online change feature, you could change most attributes, as long as the change did not result in data loss. The versioning feature of DB2 would take care of the necessary conversions and the next REORG would implement the physical changes. What could not be changed were the more physical attributes of a tablespace in which a table resides. Until DB2 10 the only way to change tablespace attributes like segsize, pagesize or the tablespace type like segmented and universal tablespace was with the UDCL method (type 3 change).
DB2 10 allows you to change these physical attributes of a tablespace, but the approach for this is not versioning because data conversions are not needed for this change (making it a type 1 change). Instead, DB2 will remember what change was requested and make that change during the next REORG. So there is a major difference with versioning, after the ALTER TABLESPACE statement the change is not immediately reflected in the catalog’s table describing the object. Instead, your request becomes a pending change and is stored in the new catalog table
SYSIBM.SYSPENDINGDDL. A simple select on this table would reveal the changes that are pending. If you want to undo the ALTER statement before the REORG, it is possible using the new ALTER TABLESPACE DROP PENDING statement. Be aware that some changes are one-way changes and cannot be undone after the REORG (e.g. you can convert to universal tablespace, but you go back to the original type). The following changes can be implemented this way:
Altering the tablespace PAGESIZE (use a different size buffer pool) Altering the tablespace DSSIZE
Altering the tablespace SEGSIZE Altering the tablespace type by:
o Changing a simple or segmented tablespace (with one table!) to a partition-by- growth universal tablespace
Page |49
o Changing a partitioned tablespace to a partition-by-growth universal tablespace (do an un-partition)
o Changing a partitioned tablespace to a range-partitioned universal tablespace o Changing a partition-by-growth universal tablespace to use hashing access (new
DB2 10 feature)
Altering the MEMBER CLUSTER structure for a (universal) tablespace
Altering a tablespace with LOBs to have “small LOBs” inline (new DB2 10 feature) Altering the index PAGESIZE
Altering the index to have include columns (new DB2 10 feature)
Despite the many new ALTER features, many change scenarios still require a UDCL script. It is very unlikely that DB2 will ever ALTER support for extensive conversion (e.g. char to date) or data truncation.
How BMC can help
If you are running DB2 V8 or 9, BMC can help you with the physical changes described above. By exploiting page oriented hardware exploitive processes, the BMC Recovery Management for DB2 High Speed Structure Change (HSSC) feature can dramatically reduce the downtime and CPU consumption to effect physical structure changes to DB2 table spaces. You can use it with BMC CHANGE MANAGER for DB2 to further automate the process. A change that may take hours using standard unload/drop/create/load process can be transformed in minutes, with minimal resource waste. HSSC is available with BMC Recovery Management for DB2 version 9.2 and later. The HSSC process allows you to perform the following transformations:
Convert a simple or segmented table space to UTS PBG (for example, when a table space is nearing the 64 GB limit).
Convert a non-large partitioned table space to large partitioned table space (for example, convert a 4 byte RID to a 5 byte RID).
Convert a partitioned by range table space to a UTS PBR (providing the benefits of a segmented table space to a partitioned table).
Convert a partitioned by range table space to a UTS PBG (dropping the partitioning index requirement).
Page |50
Change the table space SEGSIZE and/or DSSIZE. Add or drop compression from indexes
Change index PIECESIZE.
You can use the HSSC process in two ways: SHRLEVEL REFERENCE and SHRLEVEL
CHANGE. Each method provides more availability and uses fewer CPU resources than traditional unload/drop/create/load techniques.
In both methods, the transformation is done using a shadow - or new - object. The source object being transformed is the original object. At the end of the process, the new table is renamed to the original table; you do not need to change application SQL.
Page |51