Hi all! This month I wish to go though a few of the interesting, annoying and odd things that I bumped into last year. Some were new for me and some were just interesting for me!
COMPRESS THIS!
One of my customers is now starting down the road of compressing their very, very large NPSI’s as the RECOVER utility is actually way faster than a REBUILD. Nothing new here, is there? But wait! What if they are using FLASH COPY?
It gets very, very ugly very, very quickly is what happens!
Why?
Remember how FLASH COPY works? It is sooooo blindingly fast because it does all the actual copy stuff „in the DS8000, or equivalent, box“ and *not* on your mainframe. This is really cool as you just shoot off a FLASH COPY and, as long as a few really basic rules are not broken, the copy is finished the moment it starts!
So, what about Indexes?
Firstly, if you want to do a FLASH COPY of a COMPRESS YES index you *cannot* also do a sequential copy. Further, remember that a FLASH COPY is a VSAM dataset and you cannot do a COPYTOCOPY of one of these either, meaning you have just one single VSAM dataset as a copy – This is, at least for me, a single point of failure and not good. But it gets much worse!
Really, how so?
Please now remember how index compression works… It is done purely „in memory“ in the bufferpool. This means that when you have an insert, delete, or key update in memory and it has not yet been externalized to disk that when you now do a FLASH COPY you are copying garbage… This is, to coin a phrase, „not good“ whereas a normal COPY index goes through the bufferpool and so a sequential copy is naturally ok!
Bottom Line
If using COMPRESS YES indexes in no way use FLASH COPY. Perhaps, at least for storage, dataset compression of the sequential copy datasets might save space…but then that defeats the purpose of COMPRESS YES on the index purely in the Db2 world which is primarily reducing I/O and secondarily reducing index page splits.
What about SYSTEM LEVEL BACKUPS?
Guess what? These are FLASH COPY as well! If you use SLBs and you have COMPRESS YES indexes you better be careful what you „replay“ and make sure to always REBUILD, or at least CHECK, all indexes after a RECOVER has been run!
Docu?
All of the above is documented of course but it is like Douglas Adams wrote „in the bottom of a locked filing cabinet stuck in a disused lavatory with a sign on the door in saying ‚Beware of the Shark.'“ – [The Shark is my idea geddit? Originally it was Leopard of course!]
Death By RUNSTATS
It is surprisingly easy to kill yourself with a simple RUNSTATS these days.
How?
Let’s say you have PROFILE on and you are using SYSSTATFEEDBACK for all your tables. I know you are as it is all on by default and who changes defaults?
And?
Now a third-party vendor sells you some software with ridiculously long VARCHAR fields containing possible NAMEs and ADDRESSes. In this case VARCHAR(2000) is being used. The SQL in question is using dynamic SQL with literals, not parameter markers, in the WHERE clause against these columns and SYSSTATFEEDBACK „sees“ the requirement for column groups as these columns have, naturally, no index and a column group is a „poor man’s“ index for frequencies and cardinalities, right?
So?
You end up with 23 column groups for a five partition table with over 120 million rows.
But what has that got to do with the Price of Beef?
So, dear, friends, what does our poor old RUNSTATS utility do now? It must farm out these column groups to DFSORT — and can you guess how it works out the allocation size??? You guessed it: 2000 + 8 for the maximum record size then multiplied by 23, for the number of column groups, then the result multiplied by the number of rows: 120,000,000. Do the math and you end up with a DFSORT storage requirement of over 5 PB (Yep that’s PETA bytes!), then the Storage Admin freaked out!
Easy fix: Delete all COLGROUP definitions for this table not backed by a real index. RUNSTATS DELETE PROFILE is a great help here! Then switch off SYSSTATFEEDBACK for this table and control *all* other column groups because as I like to say „where there is one, there are probably more“.
Bugblatter Beast of Traal
It is possible to wrap your head in a towel so the beast does not see you, but it is sometimes better to actually look for these things before they get really really bad. I hate to think how long this RUNSTATS was just „growing and growing and growing“ with no-one noticing that it was quite simply insane!
SYSSTATFEEDBACK is good but not that good!
Remember, it is doing the best it can based on the SQL usage and what the Db2 Optimizer thinks is missing or might improve performance. In this case, the excessive number of column groups and the excessive size of the groups was actually way more of a problem than a help!
Parameter Markers…
It would also have been fine if the developers had coded good SQL with parameter markers so that SYSSTATFEEDBACK would not have started the whole problem in the first place! As a secondary bonus, moving to parameter markers stops any SQL Injection attack vectors as you could do a lot of damage with a VARCHAR(2000) text field!!! Just using „A‘ OR ‚A‘ = ‚A“ would be nice and evil, wouldn’t it!!! Returns every single row because in the code it is stringed into a delimited string. So, if the customer gives A as input it builds this string:
SELECT all my columns FROM mytable WHERE ADDRESS = 'A' ;
Now add my injection code:
SELECT all my columns FROM mytable WHERE ADDRESS = 'A' OR 'A' = 'A' ;
What does that OR do? Yep – It is always true so every row is always returned…Not what you would want with 120 million rows…
Stale Stats?
A last bit about SYSSTATFEEDBACK is that it recommends STALE quite a lot and some of these bogus entries are, in fact, just created by a STALE recommendation so one other way of clearing them all out is to run a little SQL like the following:
-- THIS SQL WILL CORRECT THE PROBLEM OF BOGUS COLUMN COLGROUP CAUSING
-- EXCESSIVE SORT ALLOCATION AND FAILING RUNSTATS.
--
-- WHAT IT DOES IS:
--
-- 1) STOP SYSSTATFEEDBACK GENERATION FOR A GIVEN TABLE
-- 2) CLEAN UP SYSCOLDIST AND SYSCOLDISTSTATS "F" ENTRIES WHICH
-- ARE LISTED IN SYSSTATFEEDBACK WITH A "F" AND "STALE" ENTRY
-- 3) DELETE ALL "F" AND "STALE" ENTRIES FROM SYSSTATFEEDBACK
--
-- TWO VARIABLES WILL BE CREATED AND USE THE DEFAULT FOR NAME AND
-- CREATOR:
--
CREATE VARIABLE TAB_NAME VARCHAR(128)
DEFAULT 'MY_BAD_TABLE'
;
CREATE VARIABLE TAB_CREATOR VARCHAR(128)
DEFAULT 'MY_BAD_CREATOR'
;
--
-- STOP SYSSTATFEEDBACK PROCESSING FOR THIS TABLE
--
UPDATE SYSIBM.SYSTABLES
SET STATS_FEEDBACK = 'N'
WHERE CREATOR = TAB_CREATOR
AND NAME = TAB_NAME
AND TYPE = 'T'
;
COMMIT ;
--
-- DELETE ANY STALE COLDIST FREQ VALS FOR THIS TABLE
--
DELETE FROM SYSIBM.SYSCOLDIST A
WHERE A.TBOWNER = TAB_CREATOR
AND A.TBNAME = TAB_NAME
AND A.TYPE = 'F'
AND EXISTS (SELECT 1 FROM SYSIBM.SYSSTATFEEDBACK B
WHERE B.TBCREATOR = TAB_CREATOR
AND B.TBNAME = TAB_NAME
AND B.TYPE = 'F'
AND B.REASON = 'STALE'
AND B.TBCREATOR = A.TBOWNER
AND B.TBNAME = A.TBNAME
AND B.COLNAME = A.NAME)
;
COMMIT ;
--
-- DELETE ANY STALE COLDISTSTATS FREQ VALS FOR THIS TABLE
--
DELETE FROM SYSIBM.SYSCOLDISTSTATS A
WHERE A.TBOWNER = TAB_CREATOR
AND A.TBNAME = TAB_NAME
AND A.TYPE = 'F'
AND EXISTS (SELECT 1 FROM SYSIBM.SYSSTATFEEDBACK B
WHERE B.TBCREATOR = TAB_CREATOR
AND B.TBNAME = TAB_NAME
AND B.TYPE = 'F'
AND B.REASON = 'STALE'
AND B.TBCREATOR = A.TBOWNER
AND B.TBNAME = A.TBNAME
AND B.COLNAME = A.NAME)
;
COMMIT ;
--
-- DELETE ANY STALE SYSSTATFEEDBACK FREQ VALS FOR THIS TABLE
--
DELETE FROM SYSIBM.SYSSTATFEEDBACK
WHERE TBCREATOR = TAB_CREATOR
AND TBNAME = TAB_NAME
AND TYPE = 'F'
AND REASON = 'STALE'
;
COMMIT ;
--
-- DROP THE CREATED VARS FOR NEXT RUN
--
DROP VARIABLE TAB_NAME ;
DROP VARIABLE TAB_CREATOR ;
COMMIT ;
Take care out there!
Caveat Emptor!
Remember to always review DELETEs like this *before* you do them in production. Blindly deleting stuff is sometimes dangerous and hazardous to your career path!
I hope you found this info interesting on a cold and dark January day, at least here in Germany!
TTFN,
Roy Boxwell