This Blog
Syndication
Recent Posts
Tags
News
Calendar
Archives
Pacific Mainframe Community
-
Undercover Whitepaper - DB2 V8 Changing Column Defaults
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 Processa. SQL to update default column value to new valueb. Stop Database that this table resides inc. DB2 utility repair rebuild this DBDd. Start Database4. 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
-
New z10 launched - I want one!!
IBM have just released their new z10 - first real upgrade to their mainframe for 3 years - here's an article . 50% faster than the z9, and up to 70% more grunt - as the late Big Kev would say "I'm Excited"!!
Is anyone out there getting one of these beauties soon?!
-
Have Pacific Businesses been neglectful of the Mainframe in Preference to "Toy Computers"?!
Alex asked for a discussion topic on this theme. His opening gambit is "I believe a topic that would raise interesting discussion is the change we have all seen over the past year or two of the non-stop reduction of "costs", seemingly regardless of the inevitable and disastrous impact this will ultimately have on services. With all the focus being on distributed systems ("toy computers"), management appear to have forgotten that it really is mainframes that provide the basis for the modern world we live in. Take away or reduce the stable and reliable mainframe services and much of what we rely on will cease to function.Why don't they understand this and what do we need to do to make them realise the danger they are playing with?Alex" .
Nice opening delivery Alex. I actually think perceptions are turning around for the better. It may be happening slower in some shops than others, but I reckon we are in for a fun ride over the next few years. Oh - and if your own management are losing touch, suggest they visit this blog as it gets more active and we could revise their thinking eh?
-
Developing applications in REXX and CLISTs
Gary from SA has also asked for this topic to be opened. I suspect this may get some interesting responses - ranging from "Don't" through to the benefits of REXX over Clist or vice-versa.
Personally, I love REXX - it's quick, flexible and powerful - since moving to REXX from Clist I've never looked back. But use it for a production application? Hmmm
-
Web Services and the Mainframe
A friend of mine in South Australia has asked if I could open up this topic. Gary is an IDMS guru (bless his heart) and I suspect is going to be a regular contributor to Bruce.
Gary is interested in knowing how much interest there is around our community in Web Services (or SOA) on the Big Iron, who is doing it, success stories, difficulties, which CA products (or other) are being used, security issues, etc. So - over to you share your knowledge...
-
Bruce on Bruce
G'day and Welcome to my blog!
I'm glad you've found your way here and I hope this will be the first of many visits for you. This blog is provided specifically for mainframers in Australia & NZ to discuss issues that concern you, and to share ideas and help each other.
With something new like this, it will take a little time to build up the interaction . Your contributions will help of course. If you don't see a topic you care to comment on, you can ask me to start up a new topic of your choosing (provided it's about mainframes) and I'll happily do that for you. It doesn't have to be about CA products - CA simply host this site for the mainframe community here in the Pacific region.
Do you like my mug? I've got some to give away to the most interesting and active contributors to my blog. So rip into it! :-)
I'd like to hear from you and really hope you get involved. This blog is for you, so enjoy it and get value from it. Oh, and please, keep yourself nice hey?
Cheers now,
Bruce
Print
Email
Rate