DB2’s plan_table contains some pretty useful information when looking at the access paths of SQL statements. It seems with each release of DB2 there are more and more access path options open to DB2, making these tables increasingly important – and more complicated. And with each release of DB2, more columns are added to the plan_table to cater for these new options. So it’s important to keep the plan_table definitions current with the changes in each DB2 release so you get the Explain information you need.
Each release of DB2 has introduced new columns into the plan_table. DB2 V7 (no one’s still on this version, right?) had 51 columns in the plan_table. V8 added another 7 (58), V9 added one more (59) and V10 another 5, taking the current GA total to 64. And I’d be astounded if DB2 V11 didn’t add at least one more. So maintaining the table definitions is an on-going task. But how to achieve it with minimal disruption and effort, making sure you cover all the plan_tables out there in your estate?
Well, if you have BMC’s Change Manager for DB2 this task just got a whole lot easier. A component of Change Manager called CM/Pilot can find the plan_table objects out there that don’t conform to the latest ‘n’ greatest definition, build the instructions to get them up-to-date and optionally run them. And it doesn’t matter what DB2 version of plan_table definition it finds; it’ll upgrade them all, whether they’re current V9 ones, old V8 ones or, heaven forbid, even older. What it does is use a pre-created (by you) DB2 table that looks the way you ultimately want them all to look, and builds the instructions to do it.
There’s a library provided when Change Manager is installed called DBCNTL. In it there are loads of sample CM/Pilot scripts to perform a whole bunch of useful tasks – and one is to upgrade plan_table definitions. You need member ACMDMLU6, which is rather cryptic-looking but makes sense if you read the imbedded comments. You need to go into Change Manager and select CM/Pilot. Create a Taskid of type ‘DML Trigger’ and bring up the panel to edit the DML. Ordinarily you’d use this to specify the kind of change you want to achieve, but we don’t care in this case; we’re just using it to get to the ISPF edit screen so we can copy in member ACMDMLU6 – remembering to replace whatever you entered to bring the screen up! You need to modify it slightly to tell it the name of the pre-created table above but other than that it’s all good. Then just Analyze the Taskid to build the Workid and Worklist that will do the work, go into Change Manager and run the pre-analysed Worklist – job done! The data will be retained because the new columns are ALTERed to the end of the table.
And that’s it! All plan_tables are now at the latest version, data retained, and took you all of 10 minutes. Think you’ve earned a drink, don’t you?