This newsletter was inspired by a recent article I read in the “Enterprise Systems Magazine” called “Top 10 Ways to Waste Money on CPU”. Why not the Top 10 things to ignore?
DB2 z/OS things you could ignore but most definitely should not!
So here’s my little list, in no particular order, of things you could ignore but most definitely should not!
| 1 | SQL DELETE statements in mega-million tables | SQL DELETE statements in mega-million tables when a REORG DISCARD would kill two birds with one stone. (I love that phrase) Anyway, after 500,000 singleton deletes the tablespace probably needs a REORG anyway and so why not do two in one? A bit of a no-brainer really. |
| 2 | LOB columns | LOB columns, whose size would *easily* fit inside an inline LOB or even a VARCHAR. LOBs are still slow and cumbersome to use, but inline LOBs are great. If you can use ‘em – do so! |
| 3 | BP0 being used for *everything* by default… | BP0 being used for *everything* by default… Please split the BP s into groups!!! BP0 is only, and I mean ONLY, for the Catalog and Directory. That way you can actually keep the size low and spare some memory for other BPs. LOB and XML tablespaces get their own BP. Tables and Indexes are split. Sort gets its own. You get the idea ? |
| 4 | Utility jobs still based on 1990’s ideas | Utility jobs still based on 1990’s ideas. Are you still running a RUNSTATS to see if a REORG is needed? Are you running REORGs without inline RUNSTATS? Are your RUNSTATS using FREQVAL and, if required, HISTOGRAM? |
| 5 | Death by “indexiphication”. | Death by “indexiphication”. Do you have tables with more than three indexes? Do you have ten or more indexes? Time to look for INCLUDE usage and LASTUSED Timestamps here! |
| 6 | PLAN_TABLE explosion | PLAN_TABLE explosion. Do you have multiple PLAN_TABLEs in production? Are you REORGing, RUNSTATSing and Image Copying them on a regular basis? Are you purging them of rubbish data on a regular basis? |
| 7 | Are your ZPARMs up to date? | Have you checked the Rules of Thumb in regard to ZPARMS since they were last set back in the 80’s? Now is the time to do a review of all the ZPARMS to see where you can really get performance boosts. (For example the default SRTPOOL In DB2 10 is now 10,000k but in DB2 V8 and 9 it was just 2,000k) |
| 8 | Are you removing garbage from the DB2 Catalog and Directory ? | Are you removing garbage from the DB2 Catalog and Directory ? Do you really need all the packages and versions of those packages from 1989 these days? If a table gets RUNSTATSed that these ancient, never executed, packages uses then it should trigger a review of the access paths, which could, of course, flag up problems where no real problem exists. |
| 9 | COMMIT frequency. | You never need to check or change this do you… |
| 10 | Training | IDUG, Insight, and RUGs etc. you can never ever get enough info about how things work and how to make things better. |
One thing you should certainly NOT ignore, is my newsletter! I have lots of exciting topics coming up in 2015 and I’ll also let you know about our webinars.
Upcoming Newsletters
- SOUNDEX and other cool features part 4 – update for DB2 10 & all new for DB2 11
- BAD Data Day
- Overloaded Log
- A real CLUSTER Buster
As usual, any comments or questions are welcome!
TTFN
Roy Boxwell