z/OS: GTTs and Stats (DB2 v10 CM)

Nick CIANCI

z/OS: GTTs and Stats (DB2 v10 CM)

Howdy
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??


Regards,

Nick CIANCI
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 ...>

Walter Jani&#223;en

AW: z/OS: GTTs and Stats (DB2 v10 CM)
(in response to Nick CIANCI)
Hi Nick

I guess you are talking about DGTTs and not CGTTs. The problem with DGTTs is that each SQL dealing with DGTTS is treated as dynamic. So you can't see the access path and AFAIK these types of SQL aren't stored in the DSC either. But unfortunately I can't answer your question. Hopefully someone from the lab jumps in.

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Spezialisten
Technische Anwendungsarchitektur
Victoriaplatz 2
40477 Düsseldorf
[login to unmask email]

Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996
-----Ursprüngliche Nachricht-----
Von: Nick Cianci [mailto:[login to unmask email]
Gesendet: Freitag, 21. Juni 2013 03:00
An: [login to unmask email]
Betreff: [DB2-L] - z/OS: GTTs and Stats (DB2 v10 CM)


Howdy
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??


Regards,

Nick CIANCI
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 ...>


-----End Original Message-----

Adam Baldwin

RE: z/OS: GTTs and Stats (DB2 v10 CM)
(in response to Nick CIANCI)

Hi Nick. If it is DGTTs that you're talking about then this could be worth looking at:

http://it.toolbox.com/blogs/db2zos/some-thoughts-on-declared-global-temporary-tables-from-pat-bossman-14009

Pat Bossman explains what stats are collected when and gives some pointers re determining the access path.

 

Cheers, Adam

Roman Glodowski

RE: z/OS: GTTs and Stats (DB2 v10 CM)
(in response to Adam Baldwin)

Interesting.

I'm curious if these informations regarding DGTT are still up to date.

I know one shop, where batch proccesing (trust me: really MASSIVE batch) is based on DGTTs.

Is this reasonable? It looks like statistics about DGTTs available are somewhat basic.



In Reply to Adam Baldwin:

Hi Nick. If it is DGTTs that you're talking about then this could be worth looking at:

http://it.toolbox.com/blogs/db2zos/some-thoughts-on-declared-global-temporary-tables-from-pat-bossman-14009

Pat Bossman explains what stats are collected when and gives some pointers re determining the access path.

 

Cheers, Adam

Patrick Bossman

RE: z/OS: GTTs and Stats (DB2 v10 CM)
(in response to Roman Glodowski)

It's still accurate.

 

Best regards,

Pat Bossman

In Reply to Roman Glodowski:

Interesting.

I'm curious if these informations regarding DGTT are still up to date.

I know one shop, where batch proccesing (trust me: really MASSIVE batch) is based on DGTTs.

Is this reasonable? It looks like statistics about DGTTs available are somewhat basic.



In Reply to Adam Baldwin:

Hi Nick. If it is DGTTs that you're talking about then this could be worth looking at:

http://it.toolbox.com/blogs/db2zos/some-thoughts-on-declared-global-temporary-tables-from-pat-bossman-14009

Pat Bossman explains what stats are collected when and gives some pointers re determining the access path.

 

Cheers, Adam