CA Community






This Blog

June 2008 - Posts

Undercover Whitepaper - DB2 V8 Changing Column Defaults

Published: June 26 2008, 05:15 PM | 3 Comment(s)
by Reg Harbeck

Hi Mainframer's,

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

Share this post:  EmailEmail

 

By: Reg Harbeck
Reg Harbeck is CA's Product Management Director for Mainframe Strategy. In the more than two decades since he received his Bachelor's Degree in Computer Science he has worked with operating systems, networks, security and applications on mainframes, UNIX, Linux, Windows and other platforms. Reg...
Read More..

The Business is the Computer

Published: June 03 2008, 10:11 PM | no comments
by Reg Harbeck

Scott McNealy, founder of Sun Microsystems, famously asserted that, "the network is the computer." In its time, this was an important and insightful statement, as islands of computing joined together to form the Internet.

 

Indeed, we tend to think of computing as something accessible anywhere in the world from our own interface, be it a workstation, a handheld, a laptop, or some other device. So, in a sense, his mantra will continue to ring true.

 

However, I think we're now entering an era where the network extends beyond even the Internet to include every other network we participate in, such as cell phone networks and social networks - and the original insight in McNealy's observation has become so ubiquitous in its application as to be irrelevant through its obviousness.

 

At the same time, a much older, more deeply-entrenched reality is beginning to reassert its relevance. Who paid for all of these computers, anyway, and what is it they're supposed to be doing?

 

The fact is that every computer purchased by a business or similar organization of any kind, or even intended for use in dealing with such entities, is paid for with business money and intended for use in supporting and furthering business.

 

Which means that the time has come for a reckoning of sorts - even a bit of "natural selection" one might say. Why do we have all these funny boxes everywhere that seem to do every sort of thing in every different way? What is their business purpose and value?

 

After all, what business in today's economy can afford to have an overabundance of machines that run at 20% of their capacity (when they're working) and take an apparently exponentiating number of support staff to take care of them? Where's the business value in that?

 

Of course, I'm not advocating some sort of revolution. Inertia is a powerful thing, and as long as legacy distributed systems perform a needed role, good for them. Still, the mainframe has quite a legacy of excellent performance according to business requirements as well.

 

So, I guess what I'm calling for is just a bit of an awakening to the fact that, while bells and whistles are cool, we need to wake up and realize that no amount of technological innovation is more important than the businesses that are paying for functional information systems. So, innovations must serve to survive.

 

Bringing us back, of course, to the mainframe. It's not about the technology (which is excellent and leading edge, mind you). It's about the business.

 

That's why I say the business is the computer. And you can quote me on that!

Share this post:  EmailEmail

 

By: Reg Harbeck
Reg Harbeck is CA's Product Management Director for Mainframe Strategy. In the more than two decades since he received his Bachelor's Degree in Computer Science he has worked with operating systems, networks, security and applications on mainframes, UNIX, Linux, Windows and other platforms. Reg...
Read More..

More Posts