with linux and notes conspiring to stuff up my explain output, I
won't try posting that here, but recently ...
We ran runstats (after a long hiatus) and discovered a few GTT's we didn't
know about, causing AP (Access Path) problems for us. Being a Static SQL
environment we were not expecting any AP changes until we bound, but with
GTT's we found that this is not the case. Also with GTTs there are no APs
recorded on the PLAN_TABLE and no entry in SYSPACKDEP that we can see.
However, we did observe a Status of "H" on SYSPACKSTMT.
In the particular SQL in question, DB2 joins a table to a GTT (access
reasonable) as a NTE (Nested Table) , and then uses the result to join to a
denormalised summary table (which is where the issue is)
The access path went bad we believe because the stat on a bad index (which
DB2 decided to use) went from 45 --> 97% Cluster Ratio and the number of
Leaf pages is very low (at < 900 pages about a 1/4 of the other indices).
The trouble though is (despite full Column Distribution stats being
collected) the Cardinality on the index and in particular the Match Cols of
3 was really really bad and low, and access to the table appears to have
been very very heavy.
What we were expecting though is that given a static set of stats that this
would be a fixed proposition ie the Access Path would either be good or
would be bad, until something changed. This was not the case, some
transaction flew through (Good Index) while with others (Bad index), well
20 minutes later we were still waiting. ... Further when we did an Explain
on the SQL we did not see any access to the bad index, regardless of
whether we used actual values in the SQL or parameter markers. We knew
that the bad index was being used, because we could see it getting hammered
in Platinum Detector.
We also noted that our predecessors had set the Stats for the GTT to very
low ie: 100, so we played with that and low and behold with the GTT having
CARDF at 100 we get the Good index; having a CARDF at 1000 the Bad index is
chosen. This is leading me to believe that the optimiser is not using the
stats on the catalog but rather somehow building a picture of the real
number of rows in the GTT at execution time.
My question to the list is in DB2 v10 (for z/OS) are catalog or the real
number of rows in a GTT used to evaluate the AP? If it is the real number
of rows, how does it work it out (RTS??) and when did that come into being
v8, v9 or v10??
DB2 Dinosaur Jockey
<the views expressed in this post are mine and by no way should be seen as
my employers or anyone else I am affliated with ...>