Changing DB2 for z/OS schema and object definitions has always been risky. Changes require outages, and it’s challenging to find time to implement them. The bigger problem, however, is ensuring the changes don’t introduce errors or performance problems. Many types of changes are risky, including application enhancements, DB2 upgrades or fixes, or schema changes. While some changes are simple and others complicated, all require careful planning, a backup plan, and a fallback plan.
Implementing change requires some downtime. When planning changes, factor in required downtime. Leave a cushion for unplanned downtime that may occur as a byproduct of an application- or data-related change.
When you change an application and its underlying DB2 schema, you must stop the application, make the change, test the application, and then resume operations. Unfortunately, many users omit the testing step. Or, if they do test, they lack a fallback plan to undo the changes. One effective way to undo an application change is to stop DB2 and snap all disks containing DB2 data (including system and catalog files) and the program files. If the test is unsuccessful, you can snap the disks back and it looks as if the change never occurred (a go-back-in-time scenario). With the proper snapshot technology in place, this provides a quick, low-impact scenario and reduces risk.
Let's review several types of changes:
- Program-only changes with no data structure (DB2 schema) changes required
- Data structure changes with minimal impact
- Data structure changes using online change
- High-impact changes using Unload, Drop, Create, Load (UDCL).
Let’s say some program logic must be altered or extended, but the data structures (tables, indexes, views, etc.) don’t require changes. This can happen when the program contains only dynamic SQL and DB2 is unaware of the change. Dynamic statements previously cached from the old application version will eventually be deleted from cache with no application impact. Such changes usually occur quickly with no real impact to the user.
New dynamic SQL statements representing extended program logic, accessing the same unchanged underlying data structure in DB2, won’t cause any outage. However, that doesn’t preclude performance problems from occurring after this type of change is applied. Testing dynamic SQL (new or changed) before promotion into a production environment is vitally important.
When application programs use static SQL, the situation is more complex. DB2 is involved because you must BIND the programs. By using package versioning, you can preserve the package of the old program. If you carefully prepare the change and can switch the programs dynamically, this type of change can have zero application impact. However, the preparation process includes binding the program Database Request Module (DBRM) into a new version. BIND always has an impact because it places exclusive locks on several DB2 resources. You must ensure you perform BINDs at quiet points, when the application and DB2 utility (backups, reorgs, etc.) activity is relatively low. The fallback scenario is the reverse of the implementation: Switching the programs back to the old package version is all that’s required because the old package is intact. This simple fallback should work unless the application change compromised data integrity, which means you need to fix the data in your DB2 tables first, before falling back to the prior version of your program.
DB2 Data Structure Changes With Minimal Impact
DB2 has a golden rule: “If it’s missing, then it must be NULL.” This enables DB2 to have an ALTER TABLE ADD COLUMN function. The columns added using this statement are, by default, NULLABLE, because the change is reflected only in the DB2 catalog. There are no dependencies on the newly created columns, and DB2 won’t invalidate any packages. Does this mean adding a column to a table has no impact? The classic answer is “it depends.” If your program does a SELECT * FROM TABLE…, the newly added columns will show up, but the program may not expect them. Depending on the programming language and interface used, there could be a problem. DB2 9 for z/OS has syntax (EXPLICITLY HIDDEN) that can be added to a column definition. Columns with this attribute won’t show up in a SELECT * FROM TABLE…, but they can be retrieved when named explicitly.