Now that the kids have finally handed in their Beijing Olympics collage, I've had a bit of time to myself again..this one is a really sneaky piece on DB2 and a bit of a treat. And if you think this is the sauce, wait 'til you hear what I've got to say on migrating straight from 1.4 to 1.8
or 1.9 on z/OS. You'd be as mad as a monkey on a merry-go-round if you thought 1.7 was the only way to go. That'll be up here in the coming weeks. In the meantime, happy hunting:
It all started in a previous life, whilst working for an outsourcer (unnamed to protect the guilty). I sat amidst the din of empty vessels – an inselberg in a sea of ineptitude. The noise of the construction site receded into the background as I focused on the day-to-day requirements of my customers (and the masses departed for the numerous coffee excursions – leaving me in peace). I had a full day ahead of me; but in the dusty recesses of my mind the answer to a question posed to me the previous day… was evolving. How can the default value of a column in a local Table on ‘DB2 for z/OS’ version 8 – be altered? Was it possible? The DB2 manuals stated that it was NOT. I tested this ‘map against the road’ (general semantics terminology) and found that the manuals and reality coincided. One of my customers had a requirement to change the default value of a column at the start of each fiscal year. I was positive that I could deliver a solution to replace the high cost (computing resources), time consuming (approx. 180 minutes) and risky process of dropping and re-creating the table. Not to mention all of the ancillary processes that need to be undertaken to protect Data, Security, Integrity, Accessibility and Performance.
I had an hour left after completing all of the outstanding (normally an oxymoron for outsourcing) customer tasks. Could I do it and be in time for my 13:00 hours meeting? I would give it a bloody good try. It is lunch time and soon the roar will subside as the herd move out toward the feed troughs. What was the issue anyway? This was just an entry in the DB2 Catalog. Not exactly – the catalog is only the repository for the definition of a table – the true structure is stored as part of the DBD (Data Base Descriptor) in the DB2 Directory. This DBD is huge topic and one that I will defer for another time. Let’s focus on the task at hand - how could I modify the DBD for the default column value of a table? To understand this it is important to understand how a table is created. What happens under the covers of DB2 when the ‘Create Table …’ command is successfully completed (SQLCODE >= 0). The DB2 Catalog tables are updated with rows inserted as meta data pertaining to the type and structure of the DB2 Table and the DBD for the Database that the table resides in is modified (refer to DB2 UDB for z/OS V8 Administration Guide: SC18-7413-05, DB2 UDB for z/OS V8 Utility Guide and Reference: SC18-7427-05 and DB2 UDB for z/OS V8 SQL Reference: SC18-7426-05).
So the trick is to somehow update both the DB2 Catalog and the Directory without causing issues with the integrity of either of these two structures (as they are interrelated). Since the default column value is only applied when an SQL Insert or a Load utility is initiated without a value for this column - modifying this value has no impact on existing rows. If I was able to update the DB2 Catalog and then resynchronize the DBD this would be the answer. Now that sounds simple doesn’t it! Here goes – a high-level how to is described below.
1. Create a modified DB2 ZPARM to Update DB2 Catalog tables.
2. Stop DB2 and (RE)Start DB2 with new ZPARM.
3. Batch JCL Process
a. SQL to update default column value to new value
b. Stop Database that this table resides in
c. DB2 utility repair rebuild this DBD
d. Start Database
4. STOP and (RE)-START DB2 (normal)
Wow that was about 1-2 minutes all in batch with a little automation. Did I get this finished in time for my 13:00 hours meeting – not exactly – I was 10 minutes late. Drop me a line anytime if you would like to get hold of the SQL and JCL required to support the afore mentioned process. It's just out of the oven and ready to go.
Cheers now,
Bruce