db2 z/os V10 - Indexable Predicate not evaluated on index

Andre LeBlanc

db2 z/os V10 - Indexable Predicate not evaluated on index
I’m perplexed as to why DB2 is not applying all possible predicates on the index to avoid tablespace access whenever possible.
I get millions of getpages on the tablespace even though no rows qualify based on information available on the index.

I have an index with the following columns:
DOC_ID,
DOC_VER,
DOC_DTTM,
DOC_TCDE

I have the following query that should be resolve all predicates on the index:

SELECT * FROM T1
where DOC_ID = ? --> matching
AND DOC_VER= ? --> matching
and DOC_TCDE = ? --> screening
and DOC_ID > 0 --> stage 1 ?

However, the access path shows matching on the first two predicates, screening on the third, and stage 1 on the fourth.

I’m seeing a lot of situations where DB2 does not apply a predicate on the index even though it could be matching or screening.
Instead, it’s applied as stage 1 during the fetching of data from the tablespace.

Could there be a DSNZPARM that influences this behaviour and discourages index screening or matching? I’m at a loss.

Regards,

André LeBlanc


From: Terry Purcell [mailto:[login to unmask email]
Sent: March 3, 2016 2:31 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2


Hi Roy,

Given your question "I have always wondered why, when the DB2 optimizer is cost based, it cannot use a user created index on the catalog for BIND/REBIND/PREPARE…". The optimizer knows the most efficient way to access the catalog to read in all information necessary for the bind/rebind/prepare of a given statement. Therefore, the "access path" to read each catalog object is fixed (hard coded/not up for debate). There would be no value to add a cost based optimization step to evaluate each alternative access to the catalog. It would merely result in increased cost for BIND/REBIND/PREPARE.

Hope this eases your suffering.

Regards

Terry Purcell

In Reply to Roy Boxwell:
Then I misunderstood you!

If BP0 is just the catalog then it certainly looks like PREPAREs are counted in the access to the catalog (I did not know this) but, as far as I am aware, creating Indexes will not help you as the access plans are hard coded for these “system” style access’s. I have always wondered why, when the DB2 optimizer is cost based, it cannot use a user created index on the catalog for BIND/REBIND/PREPARE… Perhaps you could open a PMR and then post the answer??

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de>

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

From: fabio scatolini [mailto:[login to unmask email]
Sent: Monday, February 29, 2016 3:05 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2


perhaps we have not understood, in BP0 there are only the catalog tables.
It is not correct this practice ???
If not, what is the correct one ????
What I meant is that if the BP0 there are many pages you have visited, then it could be that access to the catalog tables are not indexed.
It 'a nonsense ???
Thank you

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

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

Mark Doyle

db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Andre LeBlanc)
Because you don't specify a value for DOC_DTTM, I would expect that DB2
would stop screening on the first two columns. If you can stand the
index resign (probably not) and you flip the order of the last two
columns of the index, then you would get the screening you expect.

Mark


On 3/8/2017 12:44 PM, LeBlanc, André-ITB wrote:
>
> I’m perplexed as to why DB2 is not applying all possible predicates on
> the index to avoid tablespace access whenever possible.
>
> I get millions of getpages on the tablespace even though no rows
> qualify based on information available on the index.
>
> I have an index with the following columns:
>
> DOC_ID,
>
> DOC_VER,
>
> DOC_DTTM,
>
> DOC_TCDE
>
> I have the following query that should be resolve all predicates on
> the index:
>
> SELECT * FROM T1
>
> where DOC_ID = ? àmatching
>
> AND DOC_VER= ? àmatching
>
> and DOC_TCDE = ? àscreening
>
> and DOC_ID > 0 àstage 1 ?
>
> However, the access path shows matching on the first two predicates,
> screening on the third, and stage 1 on the fourth.
>
> I’m seeing a lot of situations where DB2 does not apply a predicate on
> the index even though it could be matching or screening.
>
> Instead, it’s applied as stage 1 during the fetching of data from the
> tablespace.
>
> Could there be a DSNZPARM that influences this behaviour and
> discourages index screening or matching? I’m at a loss.
>
> Regards,
>
> André LeBlanc
>
> *From:*Terry Purcell [mailto:[login to unmask email]
> *Sent:* March 3, 2016 2:31 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: getpage of consumption for access to the
> catalog db2
>
> Hi Roy,
>
> Given your question "I have always wondered why, when the DB2
> optimizer is cost based, it cannot use a user created index on the
> catalog for BIND/REBIND/PREPARE…". The optimizer knows the most
> efficient way to access the catalog to read in all information
> necessary for the bind/rebind/prepare of a given statement. Therefore,
> the "access path" to read each catalog object is fixed (hard coded/not
> up for debate). There would be no value to add a cost based
> optimization step to evaluate each alternative access to the catalog.
> It would merely result in increased cost for BIND/REBIND/PREPARE.
>
> Hope this eases your suffering.
>
> Regards
>
> Terry Purcell
>
> In Reply to Roy Boxwell:
>
> Then I misunderstood you!
>
> If BP0 is just the catalog then it certainly looks like PREPAREs
> are counted in the access to the catalog (I did not know this)
> but, as far as I am aware, creating Indexes will not help you as
> the access plans are hard coded for these “system” style access’s.
> I have always wondered why, when the DB2 optimizer is cost based,
> it cannot use a user created index on the catalog for
> BIND/REBIND/PREPARE… Perhaps you could open a PMR and then post
> the answer??
>
> Roy Boxwell
>
> SOFTWARE ENGINEERING GMBH and SEGUS Inc.
> -Product Development-
>
> Heinrichstrasse 83-85
> 40239 Duesseldorf/Germany
> Tel. +49 (0)211 96149-675
> Fax +49 (0)211 96149-32
> Email: [login to unmask email]<mailto:[login to unmask email]>
> <mailto:[login to unmask email]%3e>
> http://www.seg.dehttp://www.seg.de <http://www.seg.dehttp/www.seg.de>
>
> Software Engineering GmbH
> Amtsgericht Düsseldorf, HRB 37894
> Geschäftsführung: Gerhard Schubert
>
> From: fabio scatolini [mailto:[login to unmask email]
> Sent: Monday, February 29, 2016 3:05 PM
> To: [login to unmask email] <mailto:[login to unmask email]>
> Subject: [DB2-L] - RE: getpage of consumption for access to the
> catalog db2
>
>
> perhaps we have not understood, in BP0 there are only the catalog
> tables.
> It is not correct this practice ???
> If not, what is the correct one ????
> What I meant is that if the BP0 there are many pages you have
> visited, then it could be that access to the catalog tables are
> not indexed.
> It 'a nonsense ???
> Thank you
>
> -----End Original Message-----
>
> -----End Original Message-----
>
> -----End Original Message-----

Andre LeBlanc

db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Mark Doyle)
According to IBM knowledge Center, I should be matching on the first two and screening on the DOC_TCDE since I’m missing a predicate on the 3rd column of the index :

In index screening, predicates are specified on index key columns but are not part of the matching columns.
Those predicates improve the index access by reducing the number of rows that qualify while searching the index.
For example, with an index on T(C1,C2,C3,C4) in the following SQL statement, C3>0 and C4=2 are index screening predicates.
SELECT * FROM T
WHERE C1 = 1
AND C3 > 0 AND C4 = 2
AND C5 = 8;

The predicates can be applied on the index, but they are not matching predicates.
However, C5=8 is not an index screening predicate, and DB2® must evaluated that predicate when data is retrieved. The value of the MATCHCOLS column of PLAN_TABLE is 1.
What I don’t understand is why the DOC_ID>0 is not evaluated at the same time as DOC_ID=?. I’m finding 10 million rows with DOC_ID = 0 that all get rejected after 10 million fetches from the tablespace.
André

From: Mark Doyle [mailto:[login to unmask email]
Sent: March 8, 2017 2:17 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index


Because you don't specify a value for DOC_DTTM, I would expect that DB2 would stop screening on the first two columns. If you can stand the index resign (probably not) and you flip the order of the last two columns of the index, then you would get the screening you expect.

Mark

On 3/8/2017 12:44 PM, LeBlanc, André-ITB wrote:
I’m perplexed as to why DB2 is not applying all possible predicates on the index to avoid tablespace access whenever possible.
I get millions of getpages on the tablespace even though no rows qualify based on information available on the index.

I have an index with the following columns:
DOC_ID,
DOC_VER,
DOC_DTTM,
DOC_TCDE

I have the following query that should be resolve all predicates on the index:

SELECT * FROM T1
where DOC_ID = ? --> matching
AND DOC_VER= ? --> matching
and DOC_TCDE = ? --> screening
and DOC_ID > 0 --> stage 1 ?

However, the access path shows matching on the first two predicates, screening on the third, and stage 1 on the fourth.

I’m seeing a lot of situations where DB2 does not apply a predicate on the index even though it could be matching or screening.
Instead, it’s applied as stage 1 during the fetching of data from the tablespace.

Could there be a DSNZPARM that influences this behaviour and discourages index screening or matching? I’m at a loss.

Regards,

André LeBlanc



From: Terry Purcell [mailto:[login to unmask email]
Sent: March 3, 2016 2:31 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2


Hi Roy,

Given your question "I have always wondered why, when the DB2 optimizer is cost based, it cannot use a user created index on the catalog for BIND/REBIND/PREPARE…". The optimizer knows the most efficient way to access the catalog to read in all information necessary for the bind/rebind/prepare of a given statement. Therefore, the "access path" to read each catalog object is fixed (hard coded/not up for debate). There would be no value to add a cost based optimization step to evaluate each alternative access to the catalog. It would merely result in increased cost for BIND/REBIND/PREPARE.

Hope this eases your suffering.

Regards

Terry Purcell

In Reply to Roy Boxwell:
Then I misunderstood you!

If BP0 is just the catalog then it certainly looks like PREPAREs are counted in the access to the catalog (I did not know this) but, as far as I am aware, creating Indexes will not help you as the access plans are hard coded for these “system” style access’s. I have always wondered why, when the DB2 optimizer is cost based, it cannot use a user created index on the catalog for BIND/REBIND/PREPARE… Perhaps you could open a PMR and then post the answer??

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de>

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

From: fabio scatolini [mailto:[login to unmask email]
Sent: Monday, February 29, 2016 3:05 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2


perhaps we have not understood, in BP0 there are only the catalog tables.
It is not correct this practice ???
If not, what is the correct one ????
What I meant is that if the BP0 there are many pages you have visited, then it could be that access to the catalog tables are not indexed.
It 'a nonsense ???
Thank you

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

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

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

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

Sam Baugh

db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Andre LeBlanc)
I'm guessing you're testing this by running query with DOC_ID = 0. Perhaps
predicate should be 'AND ? > 0' or even better, add logic in whatever
program language to only execute the query if host variable for DOC_ID is >
0.

On Wed, Mar 8, 2017 at 1:42 PM, LeBlanc, André-ITB <[login to unmask email]>
wrote:

> According to IBM knowledge Center, I should be matching on the first two
> and screening on the DOC_TCDE since I’m missing a predicate on the 3rd
> column of the index :
>
>
>
> In index screening, predicates are specified on index key columns but are
> not part of the matching columns.
>
> Those predicates improve the index access by reducing the number of rows
> that qualify while searching the index.
>
> For example, with an index on T(C1,C2,C3,C4) in the following SQL
> statement, C3>0 and C4=2 are index screening predicates.
>
> SELECT * FROM T
>
> WHERE C1 = 1
>
> AND C3 > 0 AND C4 = 2
>
> AND C5 = 8;
>
>
>
> The predicates can be applied on the index, but they are not matching
> predicates.
>
> However, C5=8 is not an index screening predicate, and DB2® must evaluated
> that predicate when data is retrieved. The value of the MATCHCOLS column of
> PLAN_TABLE is 1.
>
> What I don’t understand is why the DOC_ID>0 is not evaluated at the same
> time as DOC_ID=?. I’m finding 10 million rows with DOC_ID = 0 that all get
> rejected after 10 million fetches from the tablespace.
>
> André
>
>
>
> *From:* Mark Doyle [mailto:[login to unmask email]
> *Sent:* March 8, 2017 2:17 PM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated
> on index
>
>
>
> Because you don't specify a value for DOC_DTTM, I would expect that DB2
> would stop screening on the first two columns. If you can stand the index
> resign (probably not) and you flip the order of the last two columns of the
> index, then you would get the screening you expect.
>
> Mark
>
>
>
> On 3/8/2017 12:44 PM, LeBlanc, André-ITB wrote:
>
> I’m perplexed as to why DB2 is not applying all possible predicates on the
> index to avoid tablespace access whenever possible.
>
> I get millions of getpages on the tablespace even though no rows qualify
> based on information available on the index.
>
>
>
> I have an index with the following columns:
>
> DOC_ID,
>
> DOC_VER,
>
> DOC_DTTM,
>
> DOC_TCDE
>
>
>
> I have the following query that should be resolve all predicates on the
> index:
>
>
>
> SELECT * FROM T1
>
> where DOC_ID = ? à matching
>
> AND DOC_VER= ? à matching
>
> and DOC_TCDE = ? à screening
>
> and DOC_ID > 0 à stage 1 ?
>
>
>
> However, the access path shows matching on the first two predicates,
> screening on the third, and stage 1 on the fourth.
>
>
>
> I’m seeing a lot of situations where DB2 does not apply a predicate on the
> index even though it could be matching or screening.
>
> Instead, it’s applied as stage 1 during the fetching of data from the
> tablespace.
>
>
>
> Could there be a DSNZPARM that influences this behaviour and discourages
> index screening or matching? I’m at a loss.
>
>
>
> Regards,
>
>
>
> André LeBlanc
>
>
>
>
> *From:* Terry Purcell [mailto:[login to unmask email] <[login to unmask email]>]
>
> *Sent:* March 3, 2016 2:31 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: getpage of consumption for access to the catalog
> db2
>
>
>
> Hi Roy,
>
> Given your question "I have always wondered why, when the DB2 optimizer is
> cost based, it cannot use a user created index on the catalog for
> BIND/REBIND/PREPARE…". The optimizer knows the most efficient way to access
> the catalog to read in all information necessary for the
> bind/rebind/prepare of a given statement. Therefore, the "access path" to
> read each catalog object is fixed (hard coded/not up for debate). There
> would be no value to add a cost based optimization step to evaluate each
> alternative access to the catalog. It would merely result in increased cost
> for BIND/REBIND/PREPARE.
>
> Hope this eases your suffering.
>
> Regards
>
> Terry Purcell
>
> In Reply to Roy Boxwell:
>
> Then I misunderstood you!
>
> If BP0 is just the catalog then it certainly looks like PREPAREs are
> counted in the access to the catalog (I did not know this) but, as far as I
> am aware, creating Indexes will not help you as the access plans are hard
> coded for these “system” style access’s. I have always wondered why, when
> the DB2 optimizer is cost based, it cannot use a user created index on the
> catalog for BIND/REBIND/PREPARE… Perhaps you could open a PMR and then post
> the answer??
>
> Roy Boxwell
>
> SOFTWARE ENGINEERING GMBH and SEGUS Inc.
> -Product Development-
>
> Heinrichstrasse 83-85
> 40239 Duesseldorf/Germany
> Tel. +49 (0)211 96149-675
> Fax +49 (0)211 96149-32
> Email: [login to unmask email]<mailto:[login to unmask email]> <[login to unmask email]%3e>
> http://www.seg.dehttp://www.seg.de <http://www.seg.dehttp/www.seg.de>
>
> Software Engineering GmbH
> Amtsgericht Düsseldorf, HRB 37894
> Geschäftsführung: Gerhard Schubert
>
> From: fabio scatolini [mailto:[login to unmask email]
> <[login to unmask email]>
> Sent: Monday, February 29, 2016 3:05 PM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2
>
>
> perhaps we have not understood, in BP0 there are only the catalog tables.
> It is not correct this practice ???
> If not, what is the correct one ????
> What I meant is that if the BP0 there are many pages you have visited,
> then it could be that access to the catalog tables are not indexed.
> It 'a nonsense ???
> Thank you
>
> -----End Original Message-----
>
>
>
> -----End Original Message-----
>
>
>
> -----End Original Message-----
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>

Andre LeBlanc

db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Sam Baugh)
It’s actually executed in a left outer join where rows with 0 and other values are being joined, so I can’t avoid it. The access path is the same whether run as a single select or as part of the outer join.
What’s worse is that when I tried to add another join predicate on a 5th index column it’s also being treated as stage 1 and not screening.

Thanks
André

From: Sam Baugh [mailto:[login to unmask email]
Sent: March 8, 2017 3:08 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index

I'm guessing you're testing this by running query with DOC_ID = 0. Perhaps predicate should be 'AND ? > 0' or even better, add logic in whatever program language to only execute the query if host variable for DOC_ID is > 0.

On Wed, Mar 8, 2017 at 1:42 PM, LeBlanc, André-ITB <[login to unmask email]<mailto:[login to unmask email]>> wrote:
According to IBM knowledge Center, I should be matching on the first two and screening on the DOC_TCDE since I’m missing a predicate on the 3rd column of the index :

In index screening, predicates are specified on index key columns but are not part of the matching columns.
Those predicates improve the index access by reducing the number of rows that qualify while searching the index.
For example, with an index on T(C1,C2,C3,C4) in the following SQL statement, C3>0 and C4=2 are index screening predicates.
SELECT * FROM T
WHERE C1 = 1
AND C3 > 0 AND C4 = 2
AND C5 = 8;

The predicates can be applied on the index, but they are not matching predicates.
However, C5=8 is not an index screening predicate, and DB2® must evaluated that predicate when data is retrieved. The value of the MATCHCOLS column of PLAN_TABLE is 1.
What I don’t understand is why the DOC_ID>0 is not evaluated at the same time as DOC_ID=?. I’m finding 10 million rows with DOC_ID = 0 that all get rejected after 10 million fetches from the tablespace.
André

From: Mark Doyle [mailto:[login to unmask email]<mailto:[login to unmask email]>]
Sent: March 8, 2017 2:17 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index


Because you don't specify a value for DOC_DTTM, I would expect that DB2 would stop screening on the first two columns. If you can stand the index resign (probably not) and you flip the order of the last two columns of the index, then you would get the screening you expect.

Mark

On 3/8/2017 12:44 PM, LeBlanc, André-ITB wrote:
I’m perplexed as to why DB2 is not applying all possible predicates on the index to avoid tablespace access whenever possible.
I get millions of getpages on the tablespace even though no rows qualify based on information available on the index.

I have an index with the following columns:
DOC_ID,
DOC_VER,
DOC_DTTM,
DOC_TCDE

I have the following query that should be resolve all predicates on the index:

SELECT * FROM T1
where DOC_ID = ? --> matching
AND DOC_VER= ? --> matching
and DOC_TCDE = ? --> screening
and DOC_ID > 0 --> stage 1 ?

However, the access path shows matching on the first two predicates, screening on the third, and stage 1 on the fourth.

I’m seeing a lot of situations where DB2 does not apply a predicate on the index even though it could be matching or screening.
Instead, it’s applied as stage 1 during the fetching of data from the tablespace.

Could there be a DSNZPARM that influences this behaviour and discourages index screening or matching? I’m at a loss.

Regards,

André LeBlanc


From: Terry Purcell [mailto:[login to unmask email]
Sent: March 3, 2016 2:31 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2


Hi Roy,

Given your question "I have always wondered why, when the DB2 optimizer is cost based, it cannot use a user created index on the catalog for BIND/REBIND/PREPARE…". The optimizer knows the most efficient way to access the catalog to read in all information necessary for the bind/rebind/prepare of a given statement. Therefore, the "access path" to read each catalog object is fixed (hard coded/not up for debate). There would be no value to add a cost based optimization step to evaluate each alternative access to the catalog. It would merely result in increased cost for BIND/REBIND/PREPARE.

Hope this eases your suffering.

Regards

Terry Purcell

In Reply to Roy Boxwell:
Then I misunderstood you!

If BP0 is just the catalog then it certainly looks like PREPAREs are counted in the access to the catalog (I did not know this) but, as far as I am aware, creating Indexes will not help you as the access plans are hard coded for these “system” style access’s. I have always wondered why, when the DB2 optimizer is cost based, it cannot use a user created index on the catalog for BIND/REBIND/PREPARE… Perhaps you could open a PMR and then post the answer??

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de>

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

From: fabio scatolini [mailto:[login to unmask email]
Sent: Monday, February 29, 2016 3:05 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2


perhaps we have not understood, in BP0 there are only the catalog tables.
It is not correct this practice ???
If not, what is the correct one ????
What I meant is that if the BP0 there are many pages you have visited, then it could be that access to the catalog tables are not indexed.
It 'a nonsense ???
Thank you

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

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

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

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

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

Joe Geller

RE: db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Andre LeBlanc)

Andre,

Tell me about the statistics on the table and on the column DOC_ID.  What is the table cardinality, what is the column cardinality, and do you have distribution statistics for DOC_ID?

One possibility:

Without distribution statistics, DB2 will assume a uniform distribution.  If, for example, there are 100 million rows in the table and 98 million rows have non-zero DOC_ID, then DB2 will assume an average of just over 1 row per DOC_ID.  It will not know that there are 1 million rows with DOC_ID=0. 

If the query is SELECT * (as you show) or even selecting any non-indexed column, then DB2 will have to go to the data row to get the data that qualifies through the index.  I don't know the current situation, but I do know that it used to be a little cheaper for DB2 to apply predicates to the data than to the index entries.  Since DB2 is assuming that most entries will qualify DOC_ID>0 and it has to go to the data anyway, it might be cheaper (in most cases) to apply that predicate to the data.

If in your real query this is part of a join and is the inner table, then I am guessing that DOC_ID comes from the outer table.  In which case, why not have a predicate on the DOC_ID on the outer table? Then it will be checked before the join access.

Joe



In Reply to Andre LeBlanc:

It’s actually executed in a left outer join where rows with 0 and other values are being joined, so I can’t avoid it. The access path is the same whether run as a single select or as part of the outer join.
What’s worse is that when I tried to add another join predicate on a 5th index column it’s also being treated as stage 1 and not screening.

Thanks
André

From: Sam Baugh [mailto:[login to unmask email]
Sent: March 8, 2017 3:08 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index

I'm guessing you're testing this by running query with DOC_ID = 0. Perhaps predicate should be 'AND ? > 0' or even better, add logic in whatever program language to only execute the query if host variable for DOC_ID is > 0.

On Wed, Mar 8, 2017 at 1:42 PM, LeBlanc, André-ITB <[login to unmask email]<mailto:[login to unmask email]>> wrote:
According to IBM knowledge Center, I should be matching on the first two and screening on the DOC_TCDE since I’m missing a predicate on the 3rd column of the index :

In index screening, predicates are specified on index key columns but are not part of the matching columns.
Those predicates improve the index access by reducing the number of rows that qualify while searching the index.
For example, with an index on T(C1,C2,C3,C4) in the following SQL statement, C3>0 and C4=2 are index screening predicates.
SELECT * FROM T
WHERE C1 = 1
AND C3 > 0 AND C4 = 2
AND C5 = 8;

The predicates can be applied on the index, but they are not matching predicates.
However, C5=8 is not an index screening predicate, and DB2® must evaluated that predicate when data is retrieved. The value of the MATCHCOLS column of PLAN_TABLE is 1.
What I don’t understand is why the DOC_ID>0 is not evaluated at the same time as DOC_ID=?. I’m finding 10 million rows with DOC_ID = 0 that all get rejected after 10 million fetches from the tablespace.
André

From: Mark Doyle [mailto:[login to unmask email]<mailto:[login to unmask email]>]
Sent: March 8, 2017 2:17 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index


Because you don't specify a value for DOC_DTTM, I would expect that DB2 would stop screening on the first two columns. If you can stand the index resign (probably not) and you flip the order of the last two columns of the index, then you would get the screening you expect.

Mark

On 3/8/2017 12:44 PM, LeBlanc, André-ITB wrote:
I’m perplexed as to why DB2 is not applying all possible predicates on the index to avoid tablespace access whenever possible.
I get millions of getpages on the tablespace even though no rows qualify based on information available on the index.

I have an index with the following columns:
DOC_ID,
DOC_VER,
DOC_DTTM,
DOC_TCDE

I have the following query that should be resolve all predicates on the index:

SELECT * FROM T1
where DOC_ID = ? --> matching
AND DOC_VER= ? --> matching
and DOC_TCDE = ? --> screening
and DOC_ID > 0 --> stage 1 ?

However, the access path shows matching on the first two predicates, screening on the third, and stage 1 on the fourth.

I’m seeing a lot of situations where DB2 does not apply a predicate on the index even though it could be matching or screening.
Instead, it’s applied as stage 1 during the fetching of data from the tablespace.

Could there be a DSNZPARM that influences this behaviour and discourages index screening or matching? I’m at a loss.

Regards,

André LeBlanc


From: Terry Purcell [mailto:[login to unmask email]
Sent: March 3, 2016 2:31 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2


Hi Roy,

Given your question "I have always wondered why, when the DB2 optimizer is cost based, it cannot use a user created index on the catalog for BIND/REBIND/PREPARE…". The optimizer knows the most efficient way to access the catalog to read in all information necessary for the bind/rebind/prepare of a given statement. Therefore, the "access path" to read each catalog object is fixed (hard coded/not up for debate). There would be no value to add a cost based optimization step to evaluate each alternative access to the catalog. It would merely result in increased cost for BIND/REBIND/PREPARE.

Hope this eases your suffering.

Regards

Terry Purcell

In Reply to Roy Boxwell:
Then I misunderstood you!

If BP0 is just the catalog then it certainly looks like PREPAREs are counted in the access to the catalog (I did not know this) but, as far as I am aware, creating Indexes will not help you as the access plans are hard coded for these “system” style access’s. I have always wondered why, when the DB2 optimizer is cost based, it cannot use a user created index on the catalog for BIND/REBIND/PREPARE… Perhaps you could open a PMR and then post the answer??

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de>

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

From: fabio scatolini [mailto:[login to unmask email]
Sent: Monday, February 29, 2016 3:05 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2


perhaps we have not understood, in BP0 there are only the catalog tables.
It is not correct this practice ???
If not, what is the correct one ????
What I meant is that if the BP0 there are many pages you have visited, then it could be that access to the catalog tables are not indexed.
It 'a nonsense ???
Thank you

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

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

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

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

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

Walter Jani&#223;en

AW: db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Andre LeBlanc)
Hi

Although you got several answers I still have problems to believe, what I am seeing. Did you really see that for the same column in one query you get matching and stage1? I have an explanation it I would be stage2! You didn’t tell us, which datatype DOK_ID is. Assuming it is CHAR, then DOK_ID > 0 is stage2, because implicit casting comes into play. Till V9 you would have got a syntax error, which was good, but today it works, but the performance is awful. I already talked to several people from IBM and already raised 3 RFEs, but unfortunately there is still no solution for this type of predicate.

The other reason being, I sometimes don’t see matching, if a sparse index is used. But here, I don’t know what type of predicate it is: STAGE1 or SCREENING.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: LeBlanc, André-ITB [mailto:[login to unmask email]
Gesendet: Mittwoch, 8. März 2017 19:45
An: '[login to unmask email]'
Betreff: [DB2-L] - db2 z/os V10 - Indexable Predicate not evaluated on index

I’m perplexed as to why DB2 is not applying all possible predicates on the index to avoid tablespace access whenever possible.
I get millions of getpages on the tablespace even though no rows qualify based on information available on the index.

I have an index with the following columns:
DOC_ID,
DOC_VER,
DOC_DTTM,
DOC_TCDE

I have the following query that should be resolve all predicates on the index:

SELECT * FROM T1
where DOC_ID = ? --> matching
AND DOC_VER= ? --> matching
and DOC_TCDE = ? --> screening
and DOC_ID > 0 --> stage 1 ?

However, the access path shows matching on the first two predicates, screening on the third, and stage 1 on the fourth.

I’m seeing a lot of situations where DB2 does not apply a predicate on the index even though it could be matching or screening.
Instead, it’s applied as stage 1 during the fetching of data from the tablespace.

Could there be a DSNZPARM that influences this behaviour and discourages index screening or matching? I’m at a loss.

Regards,

André LeBlanc

From: Terry Purcell [mailto:[login to unmask email]
Sent: March 3, 2016 2:31 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2


Hi Roy,

Given your question "I have always wondered why, when the DB2 optimizer is cost based, it cannot use a user created index on the catalog for BIND/REBIND/PREPARE…". The optimizer knows the most efficient way to access the catalog to read in all information necessary for the bind/rebind/prepare of a given statement. Therefore, the "access path" to read each catalog object is fixed (hard coded/not up for debate). There would be no value to add a cost based optimization step to evaluate each alternative access to the catalog. It would merely result in increased cost for BIND/REBIND/PREPARE.

Hope this eases your suffering.

Regards

Terry Purcell

In Reply to Roy Boxwell:
Then I misunderstood you!

If BP0 is just the catalog then it certainly looks like PREPAREs are counted in the access to the catalog (I did not know this) but, as far as I am aware, creating Indexes will not help you as the access plans are hard coded for these “system” style access’s. I have always wondered why, when the DB2 optimizer is cost based, it cannot use a user created index on the catalog for BIND/REBIND/PREPARE… Perhaps you could open a PMR and then post the answer??

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de>

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

From: fabio scatolini [mailto:[login to unmask email]
Sent: Monday, February 29, 2016 3:05 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2


perhaps we have not understood, in BP0 there are only the catalog tables.
It is not correct this practice ???
If not, what is the correct one ????
What I meant is that if the BP0 there are many pages you have visited, then it could be that access to the catalog tables are not indexed.
It 'a nonsense ???
Thank you

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

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

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

  • image001.png (2.6k)

Peter Vanroose

RE: db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Andre LeBlanc)

I could see a possible explanation for the stage-1 (although it should still be an in-index filtering).
That would be when your column DOC_ID is not numeric but textual (char/varchar).
In that case, "DOC_ID > 0" is not indexable since it needs to cast the lhs to INT before comparing.
If DOC_ID is indeed (var)char, you could consider replacing the condition by
" AND DOC_ID NOT LIKE '-%' AND DOC_ID <> '0' " or something in that sense (depending on the guarantees on the layout of the digits, e.g. zero-leading or blank-leading or not).

In Reply to Andre LeBlanc:

I have an index with the following columns:
DOC_ID, DOC_VER, DOC_DTTM, DOC_TCDE
I have the following query that should resolve all predicates on this index:
SELECT * FROM T1
where DOC_ID = ? --> matching
AND DOC_VER= ? --> matching
and DOC_TCDE = ? --> screening
and DOC_ID > 0 --> stage 1 ?

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/

Walter Jani&#223;en

AW: db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Peter Vanroose)
Hi Peter

You said: In that case, "DOC_ID > 0" is not indexable since it needs to cast the lhs to INT before comparing.
But it’s not casted to INT, but instead to DECFLOAT, therefore some comparisons are true, which – I think – no one would expect to be equal:
If the CHAR-column contains e.g. ‘ 1.0E0’ and ‘1.0’, they are both equal to 1, but not equal to ‘1’.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Peter Vanroose [mailto:[login to unmask email]
Gesendet: Samstag, 11. März 2017 17:51
An: [login to unmask email]
Betreff: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index


I could see a possible explanation for the stage-1 (although it should still be an in-index filtering).
That would be when your column DOC_ID is not numeric but textual (char/varchar).
In that case, "DOC_ID > 0" is not indexable since it needs to cast the lhs to INT before comparing.
If DOC_ID is indeed (var)char, you could consider replacing the condition by
" AND DOC_ID NOT LIKE '-%' AND DOC_ID <> '0' " or something in that sense (depending on the guarantees on the layout of the digits, e.g. zero-leading or blank-leading or not).

In Reply to Andre LeBlanc:
I have an index with the following columns:
DOC_ID, DOC_VER, DOC_DTTM, DOC_TCDE
I have the following query that should resolve all predicates on this index:
SELECT * FROM T1
where DOC_ID = ? --> matching
AND DOC_VER= ? --> matching
and DOC_TCDE = ? --> screening
and DOC_ID > 0 --> stage 1 ?

-- Peter Vanroose
ABIS Training & Consulting,
Leuven, Belgium.
http://www.abis.be/<http://www.abis.be/html/enDB2Calendar.html>

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

  • image001.png (2.6k)

Andre LeBlanc

db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Joe Geller)
Thank you all for your feedback. I’ve been fighting a lot of fires and didn’t have the opportunity to respond until now.

I initially thought there must be a data type mismatch and that the column was not numeric but textual. However, the column is in fact a BIGINT.
Even if I replace the “DOC_ID > 0” with “DOC_ID > ?”, it’s still stage 1.

I see this exact same behaviour when I run a single table select or a left outer join. The “> 0”is always stage 1 and never evaluated on the index.

The table has an xml column that I need, but the path is not changed whether I select * or just the xml column.
I don’t want to create an index that has all the join predicates and the xml column to avoid the TS fetch.
I simply want to avoid going to the tablespace until all predicates are evaluated on the index since the value 0 is repeated over 10 million times.

I simplified the query even more to focus on the index usage in this specific case. Why is the index not used to evaluate the DOC_ID > 0?

SELECT xml_col FROM T1
WHERE DOC_ID > 0
AND DOC_ID = ?

table T1
NAME VALUE
-------------------------- ---------------------------
Name T1
Type T
Table Number 1
Qualifying Rows 5.0148
Base Table Type T
Rows 1.60668525E8
Pages 3724378.0
Compressed Row Percentage 100
Occupied Page Percentage 99
Record Length 179
Columns 15
Primary Key Columns 7
Status X
Parents 0
Children 0
Encoding Scheme E
DASD Storage 3.2476752E7
Average Row Length 1146.0
Partition Key Columns 1
Dependent MQTs 0
Append N
RUNSTATS TIMESTAMP 2017-03-11 13:32:27.177324
Explain Time 2017-03-14 12:06:39.31
Versioning Schema
Versioning Table

Column DOC_ID:
NAME VALUE
--------------------------------- -------------------------------------
Name DOC_ID
Cardinality 3.2038667E7
Second High Key 62082128
Second Low Key 1202
Sequence 1
Type BIGINT
Status
Length 8
Scale 0
Nullable N
Updatable Y
Key Sequence 1
Max Length 0
Default N
Partition Key Sequence 0
Partition Key Ordering
CCSID 0
Default Value
RUNSTATS Format
Timestamp 2017-03-11 19:50:59.853913
Field Procedure N

Coldist:
VALUE FREQUENCY
-------------------- -------------------------------------
0 0.06536011
45622961 2.8607075E-5
51343109 2.5168009E-5
37150260 7.2885855E-6
60933510 6.884355E-6
60936844 6.809728E-6
61643427 6.3992784E-6
60978737 6.1194264E-6
29264278 5.80848E-6
58092889 4.8196703E-6

ixscan:

NAME VALUE
----------------------- -----------------------
Input RIDs 1.60668525E8
Index Leaf Pages 5019716
Matching Predicates Filter Factor
DOC_ID=(EXPR) 3.1212294970828225E-8
Scanned Leaf Pages 1
Output RIDs 6
Type I
Matching Columns 1
Matching Filter Factor 3.1212295E-8
Total Filter Factor 3.1212295E-8

matching predicates:

NAME VALUE
----------------------- -----------------------
Filter Factor 3.1212294970828225E-8
Type EQUAL
Stage MATCHING
Order 1
Marker Y
Boolean Term Y
Join N
After Join
LHS Text DOC_ID
RHS Text VALUE
Direct Access N
Keyfield Y
Added N
Redundant N
LHS Query Block Number 1
LHS Table Number 1
RHS Query Block Number 0
RHS Table Number 0
Predicate Number 3
Predicate Text DOC_ID=(EXPR)



fetch:

NAME VALUE
----------------------- -----------------------
Input Cardinality 6
Scanned Rows 6
Stage 1 Predicates Filter Factor
DOC_ID>0 0.9346
Stage 1 Returned Rows 5.0148
Stage 2 Returned Rows 5.0148
Output Cardinality 5.0148
Cumulative Total Cost 5.8023
Cumulative IO Cost 2.8726
Cumulative CPU Cost 35618.12
Stage 1 Columns 1
Page Range
Prefetch
Direct Access
Table Space Lock Mode IS


Stage 1 Predicates:

NAME VALUE
----------------------- -----------------------
Filter Factor 0.9346
Type RANGE
Stage STAGE1
Order 2
Marker N
Boolean Term Y
Join N
After Join
LHS Text DOC_ID
RHS Text VALUE
Direct Access N
Keyfield Y
Added N
Redundant N
LHS Query Block Number 1
LHS Table Number 1
RHS Query Block Number 0
RHS Table Number 0
Predicate Number 2
Predicate Text DOC_ID>0


Regards

André LeBlanc

From: Joe Geller [mailto:[login to unmask email]
Sent: March 9, 2017 9:15 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index


Andre,

Tell me about the statistics on the table and on the column DOC_ID. What is the table cardinality, what is the column cardinality, and do you have distribution statistics for DOC_ID?

One possibility:

Without distribution statistics, DB2 will assume a uniform distribution. If, for example, there are 100 million rows in the table and 98 million rows have non-zero DOC_ID, then DB2 will assume an average of just over 1 row per DOC_ID. It will not know that there are 1 million rows with DOC_ID=0.

If the query is SELECT * (as you show) or even selecting any non-indexed column, then DB2 will have to go to the data row to get the data that qualifies through the index. I don't know the current situation, but I do know that it used to be a little cheaper for DB2 to apply predicates to the data than to the index entries. Since DB2 is assuming that most entries will qualify DOC_ID>0 and it has to go to the data anyway, it might be cheaper (in most cases) to apply that predicate to the data.

If in your real query this is part of a join and is the inner table, then I am guessing that DOC_ID comes from the outer table. In which case, why not have a predicate on the DOC_ID on the outer table? Then it will be checked before the join access.

Joe


In Reply to Andre LeBlanc:
It’s actually executed in a left outer join where rows with 0 and other values are being joined, so I can’t avoid it. The access path is the same whether run as a single select or as part of the outer join.
What’s worse is that when I tried to add another join predicate on a 5th index column it’s also being treated as stage 1 and not screening.

Thanks
André

From: Sam Baugh [mailto:[login to unmask email]
Sent: March 8, 2017 3:08 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index

I'm guessing you're testing this by running query with DOC_ID = 0. Perhaps predicate should be 'AND ? > 0' or even better, add logic in whatever program language to only execute the query if host variable for DOC_ID is > 0.

On Wed, Mar 8, 2017 at 1:42 PM, LeBlanc, André-ITB <[login to unmask email]<mailto:[login to unmask email]>><mailto:[login to unmask email]%3e%3e> wrote:
According to IBM knowledge Center, I should be matching on the first two and screening on the DOC_TCDE since I’m missing a predicate on the 3rd column of the index :

In index screening, predicates are specified on index key columns but are not part of the matching columns.
Those predicates improve the index access by reducing the number of rows that qualify while searching the index.
For example, with an index on T(C1,C2,C3,C4) in the following SQL statement, C3>0 and C4=2 are index screening predicates.
SELECT * FROM T
WHERE C1 = 1
AND C3 > 0 AND C4 = 2
AND C5 = 8;

The predicates can be applied on the index, but they are not matching predicates.
However, C5=8 is not an index screening predicate, and DB2® must evaluated that predicate when data is retrieved. The value of the MATCHCOLS column of PLAN_TABLE is 1.
What I don’t understand is why the DOC_ID>0 is not evaluated at the same time as DOC_ID=?. I’m finding 10 million rows with DOC_ID = 0 that all get rejected after 10 million fetches from the tablespace.
André

From: Mark Doyle [mailto:[login to unmask email]<mailto:[login to unmask email]%3cmailto:[login to unmask email]%3e]>
Sent: March 8, 2017 2:17 PM
To: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index


Because you don't specify a value for DOC_DTTM, I would expect that DB2 would stop screening on the first two columns. If you can stand the index resign (probably not) and you flip the order of the last two columns of the index, then you would get the screening you expect.

Mark

On 3/8/2017 12:44 PM, LeBlanc, André-ITB wrote:
I’m perplexed as to why DB2 is not applying all possible predicates on the index to avoid tablespace access whenever possible.
I get millions of getpages on the tablespace even though no rows qualify based on information available on the index.

I have an index with the following columns:
DOC_ID,
DOC_VER,
DOC_DTTM,
DOC_TCDE

I have the following query that should be resolve all predicates on the index:

SELECT * FROM T1
where DOC_ID = ? --> matching
AND DOC_VER= ? --> matching
and DOC_TCDE = ? --> screening
and DOC_ID > 0 --> stage 1 ?

However, the access path shows matching on the first two predicates, screening on the third, and stage 1 on the fourth.

I’m seeing a lot of situations where DB2 does not apply a predicate on the index even though it could be matching or screening.
Instead, it’s applied as stage 1 during the fetching of data from the tablespace.

Could there be a DSNZPARM that influences this behaviour and discourages index screening or matching? I’m at a loss.

Regards,

André LeBlanc


From: Terry Purcell [mailto:[login to unmask email]
Sent: March 3, 2016 2:31 AM
To: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2


Hi Roy,

Given your question "I have always wondered why, when the DB2 optimizer is cost based, it cannot use a user created index on the catalog for BIND/REBIND/PREPARE…". The optimizer knows the most efficient way to access the catalog to read in all information necessary for the bind/rebind/prepare of a given statement. Therefore, the "access path" to read each catalog object is fixed (hard coded/not up for debate). There would be no value to add a cost based optimization step to evaluate each alternative access to the catalog. It would merely result in increased cost for BIND/REBIND/PREPARE.

Hope this eases your suffering.

Regards

Terry Purcell

In Reply to Roy Boxwell:
Then I misunderstood you!

If BP0 is just the catalog then it certainly looks like PREPAREs are counted in the access to the catalog (I did not know this) but, as far as I am aware, creating Indexes will not help you as the access plans are hard coded for these “system” style access’s. I have always wondered why, when the DB2 optimizer is cost based, it cannot use a user created index on the catalog for BIND/REBIND/PREPARE… Perhaps you could open a PMR and then post the answer??

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e%3cmailto:[login to unmask email]%3e%3cmailto:[login to unmask email]%3e%3e>
http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de%3chttp:/www.seg.dehttp/www.seg.de%3e>

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

From: fabio scatolini [mailto:[login to unmask email]
Sent: Monday, February 29, 2016 3:05 PM
To: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2


perhaps we have not understood, in BP0 there are only the catalog tables.
It is not correct this practice ???
If not, what is the correct one ????
What I meant is that if the BP0 there are many pages you have visited, then it could be that access to the catalog tables are not indexed.
It 'a nonsense ???
Thank you

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

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

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

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

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

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

Sam Baugh

db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Andre LeBlanc)
What happens if you replace the "DOC_ID > 0" with "cast(? as bigint) > 0"
and run with the same parameter value used for DOC_ID = ?. Test with both
parameters set to 0 and both values set to an existing ID value.

SELECT xml_col FROM T1

WHERE cast(? as bigint) > 0
AND DOC_ID = ?

On Tue, Mar 14, 2017 at 11:41 AM, LeBlanc, André-ITB <[login to unmask email]>
wrote:

> Thank you all for your feedback. I’ve been fighting a lot of fires and
> didn’t have the opportunity to respond until now.
>
>
>
> I initially thought there must be a data type mismatch and that the column
> was not numeric but textual. However, the column is in fact a BIGINT.
>
> Even if I replace the “DOC_ID > 0” with “DOC_ID > ?”, it’s still stage 1.
>
>
>
> I see this exact same behaviour when I run a single table select or a left
> outer join. The “> 0”is always stage 1 and never evaluated on the index.
>
>
>
> The table has an xml column that I need, but the path is not changed
> whether I select * or just the xml column.
>
> I don’t want to create an index that has all the join predicates and the
> xml column to avoid the TS fetch.
>
> I simply want to avoid going to the tablespace until all predicates are
> evaluated on the index since the value 0 is repeated over 10 million times.
>
>
>
> I simplified the query even more to focus on the index usage in this
> specific case. Why is the index not used to evaluate the DOC_ID > 0?
>
>
>
> SELECT xml_col FROM T1
>
> WHERE DOC_ID > 0
>
> AND DOC_ID = ?
>
>
>
> table T1
>
> NAME VALUE
>
> -------------------------- ---------------------------
>
> Name T1
>
> Type T
>
> Table Number 1
>
> Qualifying Rows 5.0148
>
> Base Table Type T
>
> Rows 1.60668525E8
>
> Pages 3724378.0
>
> Compressed Row Percentage 100
>
> Occupied Page Percentage 99
>
> Record Length 179
>
> Columns 15
>
> Primary Key Columns 7
>
> Status X
>
> Parents 0
>
> Children 0
>
> Encoding Scheme E
>
> DASD Storage 3.2476752E7
>
> Average Row Length 1146.0
>
> Partition Key Columns 1
>
> Dependent MQTs 0
>
> Append N
>
> RUNSTATS TIMESTAMP 2017-03-11 13:32:27.177324
>
> Explain Time 2017-03-14 12:06:39.31
>
> Versioning Schema
>
> Versioning Table
>
>
>
> Column DOC_ID:
>
> NAME VALUE
>
>
> --------------------------------- -------------------------------------
>
>
> Name DOC_ID
>
> Cardinality 3.2038667E7
>
>
> Second High Key 62082128
>
>
> Second Low Key 1202
>
>
> Sequence 1
>
>
> Type BIGINT
>
>
> Status
>
>
> Length 8
>
>
> Scale 0
>
>
> Nullable N
>
>
> Updatable Y
>
>
> Key Sequence 1
>
>
> Max Length 0
>
>
> Default N
>
>
> Partition Key Sequence 0
>
>
> Partition Key Ordering
>
>
> CCSID 0
>
>
> Default Value
>
>
> RUNSTATS Format
>
>
> Timestamp 2017-03-11
> 19:50:59.853913
>
> Field Procedure N
>
>
>
> Coldist:
>
> VALUE FREQUENCY
>
> -------------------- -------------------------------------
>
> 0 0.06536011
>
> 45622961 2.8607075E-5
>
> 51343109 2.5168009E-5
>
> 37150260 7.2885855E-6
>
> 60933510 6.884355E-6
>
> 60936844 6.809728E-6
>
> 61643427 6.3992784E-6
>
> 60978737 6.1194264E-6
>
> 29264278 5.80848E-6
>
> 58092889 4.8196703E-6
>
>
>
> ixscan:
>
>
>
> NAME VALUE
>
> ----------------------- -----------------------
>
> Input RIDs 1.60668525E8
>
> Index Leaf Pages 5019716
>
> Matching Predicates Filter Factor
>
> DOC_ID=(EXPR) 3.1212294970828225E-8
>
> Scanned Leaf Pages 1
>
> Output RIDs 6
>
> Type I
>
> Matching Columns 1
>
> Matching Filter Factor 3.1212295E-8
>
> Total Filter Factor 3.1212295E-8
>
>
>
> matching predicates:
>
>
>
> NAME VALUE
>
> ----------------------- -----------------------
>
> Filter Factor 3.1212294970828225E-8
>
> Type EQUAL
>
> Stage MATCHING
>
> Order 1
>
> Marker Y
>
> Boolean Term Y
>
> Join N
>
> After Join
>
> LHS Text DOC_ID
>
> RHS Text VALUE
>
> Direct Access N
>
> Keyfield Y
>
> Added N
>
> Redundant N
>
> LHS Query Block Number 1
>
> LHS Table Number 1
>
> RHS Query Block Number 0
>
> RHS Table Number 0
>
> Predicate Number 3
>
> Predicate Text DOC_ID=(EXPR)
>
>
>
>
>
>
>
> fetch:
>
>
>
> NAME VALUE
>
> ----------------------- -----------------------
>
> Input Cardinality 6
>
> Scanned Rows 6
>
> Stage 1 Predicates Filter Factor
>
> DOC_ID>0 0.9346
>
> Stage 1 Returned Rows 5.0148
>
> Stage 2 Returned Rows 5.0148
>
> Output Cardinality 5.0148
>
> Cumulative Total Cost 5.8023
>
> Cumulative IO Cost 2.8726
>
> Cumulative CPU Cost 35618.12
>
> Stage 1 Columns 1
>
> Page Range
>
> Prefetch
>
> Direct Access
>
> Table Space Lock Mode IS
>
>
>
>
>
> Stage 1 Predicates:
>
>
>
> NAME VALUE
>
> ----------------------- -----------------------
>
> Filter Factor 0.9346
>
> Type RANGE
>
> Stage STAGE1
>
> Order 2
>
> Marker N
>
> Boolean Term Y
>
> Join N
>
> After Join
>
> LHS Text DOC_ID
>
> RHS Text VALUE
>
> Direct Access N
>
> Keyfield Y
>
> Added N
>
> Redundant N
>
> LHS Query Block Number 1
>
> LHS Table Number 1
>
> RHS Query Block Number 0
>
> RHS Table Number 0
>
> Predicate Number 2
>
> Predicate Text DOC_ID>0
>
>
>
>
>
> Regards
>
>
>
> André LeBlanc
>
>
>
> *From:* Joe Geller [mailto:[login to unmask email]
> *Sent:* March 9, 2017 9:15 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated
> on index
>
>
>
> Andre,
>
> Tell me about the statistics on the table and on the column DOC_ID. What
> is the table cardinality, what is the column cardinality, and do you have
> distribution statistics for DOC_ID?
>
> One possibility:
>
> Without distribution statistics, DB2 will assume a uniform distribution.
> If, for example, there are 100 million rows in the table and 98 million
> rows have non-zero DOC_ID, then DB2 will assume an average of just over 1
> row per DOC_ID. It will not know that there are 1 million rows with
> DOC_ID=0.
>
> If the query is SELECT * (as you show) or even selecting any non-indexed
> column, then DB2 will have to go to the data row to get the data that
> qualifies through the index. I don't know the current situation, but I do
> know that it used to be a little cheaper for DB2 to apply predicates to the
> data than to the index entries. Since DB2 is assuming that most entries
> will qualify DOC_ID>0 and it has to go to the data anyway, it might be
> cheaper (in most cases) to apply that predicate to the data.
>
> If in your real query this is part of a join and is the inner table, then
> I am guessing that DOC_ID comes from the outer table. In which case, why
> not have a predicate on the DOC_ID on the outer table? Then it will be
> checked before the join access.
>
> Joe
>
>
>
> In Reply to Andre LeBlanc:
>
> It’s actually executed in a left outer join where rows with 0 and other
> values are being joined, so I can’t avoid it. The access path is the same
> whether run as a single select or as part of the outer join.
> What’s worse is that when I tried to add another join predicate on a 5th
> index column it’s also being treated as stage 1 and not screening.
>
> Thanks
> André
>
> From: Sam Baugh [mailto:[login to unmask email] <[login to unmask email]>
> Sent: March 8, 2017 3:08 PM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on
> index
>
> I'm guessing you're testing this by running query with DOC_ID = 0. Perhaps
> predicate should be 'AND ? > 0' or even better, add logic in whatever
> program language to only execute the query if host variable for DOC_ID is >
> 0.
>
> On Wed, Mar 8, 2017 at 1:42 PM, LeBlanc, André-ITB <[login to unmask email]<
> mailto:[login to unmask email]>> <[login to unmask email]%3e%3e> wrote:
> According to IBM knowledge Center, I should be matching on the first two
> and screening on the DOC_TCDE since I’m missing a predicate on the 3rd
> column of the index :
>
> In index screening, predicates are specified on index key columns but are
> not part of the matching columns.
> Those predicates improve the index access by reducing the number of rows
> that qualify while searching the index.
> For example, with an index on T(C1,C2,C3,C4) in the following SQL
> statement, C3>0 and C4=2 are index screening predicates.
> SELECT * FROM T
> WHERE C1 = 1
> AND C3 > 0 AND C4 = 2
> AND C5 = 8;
>
> The predicates can be applied on the index, but they are not matching
> predicates.
> However, C5=8 is not an index screening predicate, and DB2® must evaluated
> that predicate when data is retrieved. The value of the MATCHCOLS column of
> PLAN_TABLE is 1.
> What I don’t understand is why the DOC_ID>0 is not evaluated at the same
> time as DOC_ID=?. I’m finding 10 million rows with DOC_ID = 0 that all get
> rejected after 10 million fetches from the tablespace.
> André
>
> From: Mark Doyle [mailto:[login to unmask email]
> <[login to unmask email]%3cmailto:[login to unmask email]%3e]>
> Sent: March 8, 2017 2:17 PM
> To: [login to unmask email]<mailto:[login to unmask email]>
> <[login to unmask email]%3e>
> Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on
> index
>
>
> Because you don't specify a value for DOC_DTTM, I would expect that DB2
> would stop screening on the first two columns. If you can stand the index
> resign (probably not) and you flip the order of the last two columns of the
> index, then you would get the screening you expect.
>
> Mark
>
> On 3/8/2017 12:44 PM, LeBlanc, André-ITB wrote:
> I’m perplexed as to why DB2 is not applying all possible predicates on the
> index to avoid tablespace access whenever possible.
> I get millions of getpages on the tablespace even though no rows qualify
> based on information available on the index.
>
> I have an index with the following columns:
> DOC_ID,
> DOC_VER,
> DOC_DTTM,
> DOC_TCDE
>
> I have the following query that should be resolve all predicates on the
> index:
>
> SELECT * FROM T1
> where DOC_ID = ? --> matching
> AND DOC_VER= ? --> matching
> and DOC_TCDE = ? --> screening
> and DOC_ID > 0 --> stage 1 ?
>
> However, the access path shows matching on the first two predicates,
> screening on the third, and stage 1 on the fourth.
>
> I’m seeing a lot of situations where DB2 does not apply a predicate on the
> index even though it could be matching or screening.
> Instead, it’s applied as stage 1 during the fetching of data from the
> tablespace.
>
> Could there be a DSNZPARM that influences this behaviour and discourages
> index screening or matching? I’m at a loss.
>
> Regards,
>
> André LeBlanc
>
>
> From: Terry Purcell [mailto:[login to unmask email] <[login to unmask email]>
> Sent: March 3, 2016 2:31 AM
> To: [login to unmask email]<mailto:[login to unmask email]>
> <[login to unmask email]%3e>
> Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2
>
>
> Hi Roy,
>
> Given your question "I have always wondered why, when the DB2 optimizer is
> cost based, it cannot use a user created index on the catalog for
> BIND/REBIND/PREPARE…". The optimizer knows the most efficient way to access
> the catalog to read in all information necessary for the
> bind/rebind/prepare of a given statement. Therefore, the "access path" to
> read each catalog object is fixed (hard coded/not up for debate). There
> would be no value to add a cost based optimization step to evaluate each
> alternative access to the catalog. It would merely result in increased cost
> for BIND/REBIND/PREPARE.
>
> Hope this eases your suffering.
>
> Regards
>
> Terry Purcell
>
> In Reply to Roy Boxwell:
> Then I misunderstood you!
>
> If BP0 is just the catalog then it certainly looks like PREPAREs are
> counted in the access to the catalog (I did not know this) but, as far as I
> am aware, creating Indexes will not help you as the access plans are hard
> coded for these “system” style access’s. I have always wondered why, when
> the DB2 optimizer is cost based, it cannot use a user created index on the
> catalog for BIND/REBIND/PREPARE… Perhaps you could open a PMR and then post
> the answer??
>
> Roy Boxwell
>
> SOFTWARE ENGINEERING GMBH and SEGUS Inc.
> -Product Development-
>
> Heinrichstrasse 83-85
> 40239 Duesseldorf/Germany
> Tel. +49 (0)211 96149-675
> Fax +49 (0)211 96149-32
> Email: [login to unmask email]<mailto:[login to unmask email]>
> <[login to unmask email]%3e%3cmailto:[login to unmask email]%3e%3cmailto:[login to unmask email]%3e%3e>
> http://www.seg.dehttp://www.seg.de
> <http://www.seg.dehttp/www.seg.de%3chttp:/www.seg.dehttp/www.seg.de%3e>
>
> Software Engineering GmbH
> Amtsgericht Düsseldorf, HRB 37894
> Geschäftsführung: Gerhard Schubert
>
> From: fabio scatolini [mailto:[login to unmask email]
> <[login to unmask email]>
> Sent: Monday, February 29, 2016 3:05 PM
> To: [login to unmask email]<mailto:[login to unmask email]>
> <[login to unmask email]%3e>
> Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2
>
>
> perhaps we have not understood, in BP0 there are only the catalog tables.
> It is not correct this practice ???
> If not, what is the correct one ????
> What I meant is that if the BP0 there are many pages you have visited,
> then it could be that access to the catalog tables are not indexed.
> It 'a nonsense ???
> Thank you
>
> -----End Original Message-----
>
> -----End Original Message-----
>
> -----End Original Message-----
>
> -----End Original Message-----
>
> -----End Original Message-----
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>

Joe Geller

RE: db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Sam Baugh)

So you tested single table access with DOC_ID=0 AND DOC_ID>0  with doc_id=0 indexable but doc_id>0 applied at the Fetch?  This raises and interesting question.  There are clearly 0 rows that satisfy this. Aside from the filter factors, does the Optimizer know that?   If instead of > you had <> do you get a different access path?  There have been times where DB2 could do things with = predicates that it couldn't with range predicates. 

Could you check that?  Terry or Pat, what does the Optimizer know?

Joe


In Reply to Sam Baugh:

What happens if you replace the "DOC_ID > 0" with "cast(? as bigint) > 0"
and run with the same parameter value used for DOC_ID = ?. Test with both
parameters set to 0 and both values set to an existing ID value.

SELECT xml_col FROM T1

WHERE cast(? as bigint) > 0
AND DOC_ID = ?

On Tue, Mar 14, 2017 at 11:41 AM, LeBlanc, André-ITB <[login to unmask email]>
wrote:

> Thank you all for your feedback. I’ve been fighting a lot of fires and
> didn’t have the opportunity to respond until now.
>
>
>
> I initially thought there must be a data type mismatch and that the column
> was not numeric but textual. However, the column is in fact a BIGINT.
>
> Even if I replace the “DOC_ID > 0” with “DOC_ID > ?”, it’s still stage 1.
>
>
>
> I see this exact same behaviour when I run a single table select or a left
> outer join. The “> 0”is always stage 1 and never evaluated on the index.
>
>
>
> The table has an xml column that I need, but the path is not changed
> whether I select * or just the xml column.
>
> I don’t want to create an index that has all the join predicates and the
> xml column to avoid the TS fetch.
>
> I simply want to avoid going to the tablespace until all predicates are
> evaluated on the index since the value 0 is repeated over 10 million times.
>
>
>
> I simplified the query even more to focus on the index usage in this
> specific case. Why is the index not used to evaluate the DOC_ID > 0?
>
>
>
> SELECT xml_col FROM T1
>
> WHERE DOC_ID > 0
>
> AND DOC_ID = ?
>
>
>
> table T1
>
> NAME VALUE
>
> -------------------------- ---------------------------
>
> Name T1
>
> Type T
>
> Table Number 1
>
> Qualifying Rows 5.0148
>
> Base Table Type T
>
> Rows 1.60668525E8
>
> Pages 3724378.0
>
> Compressed Row Percentage 100
>
> Occupied Page Percentage 99
>
> Record Length 179
>
> Columns 15
>
> Primary Key Columns 7
>
> Status X
>
> Parents 0
>
> Children 0
>
> Encoding Scheme E
>
> DASD Storage 3.2476752E7
>
> Average Row Length 1146.0
>
> Partition Key Columns 1
>
> Dependent MQTs 0
>
> Append N
>
> RUNSTATS TIMESTAMP 2017-03-11 13:32:27.177324
>
> Explain Time 2017-03-14 12:06:39.31
>
> Versioning Schema
>
> Versioning Table
>
>
>
> Column DOC_ID:
>
> NAME VALUE
>
>
> --------------------------------- -------------------------------------
>
>
> Name DOC_ID
>
> Cardinality 3.2038667E7
>
>
> Second High Key 62082128
>
>
> Second Low Key 1202
>
>
> Sequence 1
>
>
> Type BIGINT
>
>
> Status
>
>
> Length 8
>
>
> Scale 0
>
>
> Nullable N
>
>
> Updatable Y
>
>
> Key Sequence 1
>
>
> Max Length 0
>
>
> Default N
>
>
> Partition Key Sequence 0
>
>
> Partition Key Ordering
>
>
> CCSID 0
>
>
> Default Value
>
>
> RUNSTATS Format
>
>
> Timestamp 2017-03-11
> 19:50:59.853913
>
> Field Procedure N
>
>
>
> Coldist:
>
> VALUE FREQUENCY
>
> -------------------- -------------------------------------
>
> 0 0.06536011
>
> 45622961 2.8607075E-5
>
> 51343109 2.5168009E-5
>
> 37150260 7.2885855E-6
>
> 60933510 6.884355E-6
>
> 60936844 6.809728E-6
>
> 61643427 6.3992784E-6
>
> 60978737 6.1194264E-6
>
> 29264278 5.80848E-6
>
> 58092889 4.8196703E-6
>
>
>
> ixscan:
>
>
>
> NAME VALUE
>
> ----------------------- -----------------------
>
> Input RIDs 1.60668525E8
>
> Index Leaf Pages 5019716
>
> Matching Predicates Filter Factor
>
> DOC_ID=(EXPR) 3.1212294970828225E-8
>
> Scanned Leaf Pages 1
>
> Output RIDs 6
>
> Type I
>
> Matching Columns 1
>
> Matching Filter Factor 3.1212295E-8
>
> Total Filter Factor 3.1212295E-8
>
>
>
> matching predicates:
>
>
>
> NAME VALUE
>
> ----------------------- -----------------------
>
> Filter Factor 3.1212294970828225E-8
>
> Type EQUAL
>
> Stage MATCHING
>
> Order 1
>
> Marker Y
>
> Boolean Term Y
>
> Join N
>
> After Join
>
> LHS Text DOC_ID
>
> RHS Text VALUE
>
> Direct Access N
>
> Keyfield Y
>
> Added N
>
> Redundant N
>
> LHS Query Block Number 1
>
> LHS Table Number 1
>
> RHS Query Block Number 0
>
> RHS Table Number 0
>
> Predicate Number 3
>
> Predicate Text DOC_ID=(EXPR)
>
>
>
>
>
>
>
> fetch:
>
>
>
> NAME VALUE
>
> ----------------------- -----------------------
>
> Input Cardinality 6
>
> Scanned Rows 6
>
> Stage 1 Predicates Filter Factor
>
> DOC_ID>0 0.9346
>
> Stage 1 Returned Rows 5.0148
>
> Stage 2 Returned Rows 5.0148
>
> Output Cardinality 5.0148
>
> Cumulative Total Cost 5.8023
>
> Cumulative IO Cost 2.8726
>
> Cumulative CPU Cost 35618.12
>
> Stage 1 Columns 1
>
> Page Range
>
> Prefetch
>
> Direct Access
>
> Table Space Lock Mode IS
>
>
>
>
>
> Stage 1 Predicates:
>
>
>
> NAME VALUE
>
> ----------------------- -----------------------
>
> Filter Factor 0.9346
>
> Type RANGE
>
> Stage STAGE1
>
> Order 2
>
> Marker N
>
> Boolean Term Y
>
> Join N
>
> After Join
>
> LHS Text DOC_ID
>
> RHS Text VALUE
>
> Direct Access N
>
> Keyfield Y
>
> Added N
>
> Redundant N
>
> LHS Query Block Number 1
>
> LHS Table Number 1
>
> RHS Query Block Number 0
>
> RHS Table Number 0
>
> Predicate Number 2
>
> Predicate Text DOC_ID>0
>
>
>
>
>
> Regards
>
>
>
> André LeBlanc
>
>
>
> *From:* Joe Geller [mailto:[login to unmask email]
> *Sent:* March 9, 2017 9:15 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated
> on index
>
>
>
> Andre,
>
> Tell me about the statistics on the table and on the column DOC_ID. What
> is the table cardinality, what is the column cardinality, and do you have
> distribution statistics for DOC_ID?
>
> One possibility:
>
> Without distribution statistics, DB2 will assume a uniform distribution.
> If, for example, there are 100 million rows in the table and 98 million
> rows have non-zero DOC_ID, then DB2 will assume an average of just over 1
> row per DOC_ID. It will not know that there are 1 million rows with
> DOC_ID=0.
>
> If the query is SELECT * (as you show) or even selecting any non-indexed
> column, then DB2 will have to go to the data row to get the data that
> qualifies through the index. I don't know the current situation, but I do
> know that it used to be a little cheaper for DB2 to apply predicates to the
> data than to the index entries. Since DB2 is assuming that most entries
> will qualify DOC_ID>0 and it has to go to the data anyway, it might be
> cheaper (in most cases) to apply that predicate to the data.
>
> If in your real query this is part of a join and is the inner table, then
> I am guessing that DOC_ID comes from the outer table. In which case, why
> not have a predicate on the DOC_ID on the outer table? Then it will be
> checked before the join access.
>
> Joe
>
>
>
> In Reply to Andre LeBlanc:
>
> It’s actually executed in a left outer join where rows with 0 and other
> values are being joined, so I can’t avoid it. The access path is the same
> whether run as a single select or as part of the outer join.
> What’s worse is that when I tried to add another join predicate on a 5th
> index column it’s also being treated as stage 1 and not screening.
>
> Thanks
> André
>
> From: Sam Baugh [mailto:[login to unmask email] <[login to unmask email]>
> Sent: March 8, 2017 3:08 PM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on
> index
>
> I'm guessing you're testing this by running query with DOC_ID = 0. Perhaps
> predicate should be 'AND ? > 0' or even better, add logic in whatever
> program language to only execute the query if host variable for DOC_ID is >
> 0.
>
> On Wed, Mar 8, 2017 at 1:42 PM, LeBlanc, André-ITB <[login to unmask email]<
> mailto:[login to unmask email]>> <[login to unmask email]%3e%3e> wrote:
> According to IBM knowledge Center, I should be matching on the first two
> and screening on the DOC_TCDE since I’m missing a predicate on the 3rd
> column of the index :
>
> In index screening, predicates are specified on index key columns but are
> not part of the matching columns.
> Those predicates improve the index access by reducing the number of rows
> that qualify while searching the index.
> For example, with an index on T(C1,C2,C3,C4) in the following SQL
> statement, C3>0 and C4=2 are index screening predicates.
> SELECT * FROM T
> WHERE C1 = 1
> AND C3 > 0 AND C4 = 2
> AND C5 = 8;
>
> The predicates can be applied on the index, but they are not matching
> predicates.
> However, C5=8 is not an index screening predicate, and DB2® must evaluated
> that predicate when data is retrieved. The value of the MATCHCOLS column of
> PLAN_TABLE is 1.
> What I don’t understand is why the DOC_ID>0 is not evaluated at the same
> time as DOC_ID=?. I’m finding 10 million rows with DOC_ID = 0 that all get
> rejected after 10 million fetches from the tablespace.
> André
>
> From: Mark Doyle [mailto:[login to unmask email]
> <[login to unmask email]%3cmailto:[login to unmask email]%3e]>
> Sent: March 8, 2017 2:17 PM
> To: [login to unmask email]<mailto:[login to unmask email]>
> <[login to unmask email]%3e>
> Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on
> index
>
>
> Because you don't specify a value for DOC_DTTM, I would expect that DB2
> would stop screening on the first two columns. If you can stand the index
> resign (probably not) and you flip the order of the last two columns of the
> index, then you would get the screening you expect.
>
> Mark
>
> On 3/8/2017 12:44 PM, LeBlanc, André-ITB wrote:
> I’m perplexed as to why DB2 is not applying all possible predicates on the
> index to avoid tablespace access whenever possible.
> I get millions of getpages on the tablespace even though no rows qualify
> based on information available on the index.
>
> I have an index with the following columns:
> DOC_ID,
> DOC_VER,
> DOC_DTTM,
> DOC_TCDE
>
> I have the following query that should be resolve all predicates on the
> index:
>
> SELECT * FROM T1
> where DOC_ID = ? --> matching
> AND DOC_VER= ? --> matching
> and DOC_TCDE = ? --> screening
> and DOC_ID > 0 --> stage 1 ?
>
> However, the access path shows matching on the first two predicates,
> screening on the third, and stage 1 on the fourth.
>
> I’m seeing a lot of situations where DB2 does not apply a predicate on the
> index even though it could be matching or screening.
> Instead, it’s applied as stage 1 during the fetching of data from the
> tablespace.
>
> Could there be a DSNZPARM that influences this behaviour and discourages
> index screening or matching? I’m at a loss.
>
> Regards,
>
> André LeBlanc
>
>
> From: Terry Purcell [mailto:[login to unmask email] <[login to unmask email]>
> Sent: March 3, 2016 2:31 AM
> To: [login to unmask email]<mailto:[login to unmask email]>
> <[login to unmask email]%3e>
> Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2
>
>
> Hi Roy,
>
> Given your question "I have always wondered why, when the DB2 optimizer is
> cost based, it cannot use a user created index on the catalog for
> BIND/REBIND/PREPARE…". The optimizer knows the most efficient way to access
> the catalog to read in all information necessary for the
> bind/rebind/prepare of a given statement. Therefore, the "access path" to
> read each catalog object is fixed (hard coded/not up for debate). There
> would be no value to add a cost based optimization step to evaluate each
> alternative access to the catalog. It would merely result in increased cost
> for BIND/REBIND/PREPARE.
>
> Hope this eases your suffering.
>
> Regards
>
> Terry Purcell
>
> In Reply to Roy Boxwell:
> Then I misunderstood you!
>
> If BP0 is just the catalog then it certainly looks like PREPAREs are
> counted in the access to the catalog (I did not know this) but, as far as I
> am aware, creating Indexes will not help you as the access plans are hard
> coded for these “system” style access’s. I have always wondered why, when
> the DB2 optimizer is cost based, it cannot use a user created index on the
> catalog for BIND/REBIND/PREPARE… Perhaps you could open a PMR and then post
> the answer??
>
> Roy Boxwell
>
> SOFTWARE ENGINEERING GMBH and SEGUS Inc.
> -Product Development-
>
> Heinrichstrasse 83-85
> 40239 Duesseldorf/Germany
> Tel. +49 (0)211 96149-675
> Fax +49 (0)211 96149-32
> Email: [login to unmask email]<mailto:[login to unmask email]>
> <[login to unmask email]%3e%3cmailto:[login to unmask email]%3e%3cmailto:[login to unmask email]%3e%3e>
> http://www.seg.dehttp://www.seg.de
> <http://www.seg.dehttp/www.seg.de%3chttp:/www.seg.dehttp/www.seg.de%3e>
>
> Software Engineering GmbH
> Amtsgericht Düsseldorf, HRB 37894
> Geschäftsführung: Gerhard Schubert
>
> From: fabio scatolini [mailto:[login to unmask email]
> <[login to unmask email]>
> Sent: Monday, February 29, 2016 3:05 PM
> To: [login to unmask email]<mailto:[login to unmask email]>
> <[login to unmask email]%3e>
> Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2
>
>
> perhaps we have not understood, in BP0 there are only the catalog tables.
> It is not correct this practice ???
> If not, what is the correct one ????
> What I meant is that if the BP0 there are many pages you have visited,
> then it could be that access to the catalog tables are not indexed.
> It 'a nonsense ???
> Thank you
>
> -----End Original Message-----
>
> -----End Original Message-----
>
> -----End Original Message-----
>
> -----End Original Message-----
>
> -----End Original Message-----
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>

Andre LeBlanc

db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Joe Geller)
Of course if I hard code DOC_ID=0 and DOC_ID>0 it will prune both predicates and return no data. At least one of the predicates must use host variables.

I had already tried with <>0 with no success.
Here’s how the stage 1 predicate for <> 0:
NAME VALUE
--------------------------------- -------------------------------------------
Filter Factor 0.9346
Type EQUAL
Stage STAGE1
Order 2
Marker N
Boolean Term Y
Join N
After Join
LHS Text DOC_ID
RHS Text VALUE
Direct Access N
Keyfield N
Added N
Redundant N
LHS Query Block Number 1
LHS Table Number 1
RHS Query Block Number 0
RHS Table Number 0
Predicate Number 2
Predicate Text DOC_ID<>0

Thanks
André

From: Joe Geller [mailto:[login to unmask email]
Sent: March 15, 2017 6:24 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index


So you tested single table access with DOC_ID=0 AND DOC_ID>0 with doc_id=0 indexable but doc_id>0 applied at the Fetch? This raises and interesting question. There are clearly 0 rows that satisfy this. Aside from the filter factors, does the Optimizer know that? If instead of > you had <> do you get a different access path? There have been times where DB2 could do things with = predicates that it couldn't with range predicates.

Could you check that? Terry or Pat, what does the Optimizer know?

Joe

In Reply to Sam Baugh:
What happens if you replace the "DOC_ID > 0" with "cast(? as bigint) > 0"
and run with the same parameter value used for DOC_ID = ?. Test with both
parameters set to 0 and both values set to an existing ID value.

SELECT xml_col FROM T1

WHERE cast(? as bigint) > 0
AND DOC_ID = ?

On Tue, Mar 14, 2017 at 11:41 AM, LeBlanc, André-ITB
wrote:

> Thank you all for your feedback. I’ve been fighting a lot of fires and
> didn’t have the opportunity to respond until now.
>
>
>
> I initially thought there must be a data type mismatch and that the column
> was not numeric but textual. However, the column is in fact a BIGINT.
>
> Even if I replace the “DOC_ID > 0” with “DOC_ID > ?”, it’s still stage 1.
>
>
>
> I see this exact same behaviour when I run a single table select or a left
> outer join. The “> 0”is always stage 1 and never evaluated on the index.
>
>
>
> The table has an xml column that I need, but the path is not changed
> whether I select * or just the xml column.
>
> I don’t want to create an index that has all the join predicates and the
> xml column to avoid the TS fetch.
>
> I simply want to avoid going to the tablespace until all predicates are
> evaluated on the index since the value 0 is repeated over 10 million times.
>
>
>
> I simplified the query even more to focus on the index usage in this
> specific case. Why is the index not used to evaluate the DOC_ID > 0?
>
>
>
> SELECT xml_col FROM T1
>
> WHERE DOC_ID > 0
>
> AND DOC_ID = ?
>
>
>
> table T1
>
> NAME VALUE
>
> -------------------------- ---------------------------
>
> Name T1
>
> Type T
>
> Table Number 1
>
> Qualifying Rows 5.0148
>
> Base Table Type T
>
> Rows 1.60668525E8
>
> Pages 3724378.0
>
> Compressed Row Percentage 100
>
> Occupied Page Percentage 99
>
> Record Length 179
>
> Columns 15
>
> Primary Key Columns 7
>
> Status X
>
> Parents 0
>
> Children 0
>
> Encoding Scheme E
>
> DASD Storage 3.2476752E7
>
> Average Row Length 1146.0
>
> Partition Key Columns 1
>
> Dependent MQTs 0
>
> Append N
>
> RUNSTATS TIMESTAMP 2017-03-11 13:32:27.177324
>
> Explain Time 2017-03-14 12:06:39.31
>
> Versioning Schema
>
> Versioning Table
>
>
>
> Column DOC_ID:
>
> NAME VALUE
>
>
> --------------------------------- -------------------------------------
>
>
> Name DOC_ID
>
> Cardinality 3.2038667E7
>
>
> Second High Key 62082128
>
>
> Second Low Key 1202
>
>
> Sequence 1
>
>
> Type BIGINT
>
>
> Status
>
>
> Length 8
>
>
> Scale 0
>
>
> Nullable N
>
>
> Updatable Y
>
>
> Key Sequence 1
>
>
> Max Length 0
>
>
> Default N
>
>
> Partition Key Sequence 0
>
>
> Partition Key Ordering
>
>
> CCSID 0
>
>
> Default Value
>
>
> RUNSTATS Format
>
>
> Timestamp 2017-03-11
> 19:50:59.853913
>
> Field Procedure N
>
>
>
> Coldist:
>
> VALUE FREQUENCY
>
> -------------------- -------------------------------------
>
> 0 0.06536011
>
> 45622961 2.8607075E-5
>
> 51343109 2.5168009E-5
>
> 37150260 7.2885855E-6
>
> 60933510 6.884355E-6
>
> 60936844 6.809728E-6
>
> 61643427 6.3992784E-6
>
> 60978737 6.1194264E-6
>
> 29264278 5.80848E-6
>
> 58092889 4.8196703E-6
>
>
>
> ixscan:
>
>
>
> NAME VALUE
>
> ----------------------- -----------------------
>
> Input RIDs 1.60668525E8
>
> Index Leaf Pages 5019716
>
> Matching Predicates Filter Factor
>
> DOC_ID=(EXPR) 3.1212294970828225E-8
>
> Scanned Leaf Pages 1
>
> Output RIDs 6
>
> Type I
>
> Matching Columns 1
>
> Matching Filter Factor 3.1212295E-8
>
> Total Filter Factor 3.1212295E-8
>
>
>
> matching predicates:
>
>
>
> NAME VALUE
>
> ----------------------- -----------------------
>
> Filter Factor 3.1212294970828225E-8
>
> Type EQUAL
>
> Stage MATCHING
>
> Order 1
>
> Marker Y
>
> Boolean Term Y
>
> Join N
>
> After Join
>
> LHS Text DOC_ID
>
> RHS Text VALUE
>
> Direct Access N
>
> Keyfield Y
>
> Added N
>
> Redundant N
>
> LHS Query Block Number 1
>
> LHS Table Number 1
>
> RHS Query Block Number 0
>
> RHS Table Number 0
>
> Predicate Number 3
>
> Predicate Text DOC_ID=(EXPR)
>
>
>
>
>
>
>
> fetch:
>
>
>
> NAME VALUE
>
> ----------------------- -----------------------
>
> Input Cardinality 6
>
> Scanned Rows 6
>
> Stage 1 Predicates Filter Factor
>
> DOC_ID>0 0.9346
>
> Stage 1 Returned Rows 5.0148
>
> Stage 2 Returned Rows 5.0148
>
> Output Cardinality 5.0148
>
> Cumulative Total Cost 5.8023
>
> Cumulative IO Cost 2.8726
>
> Cumulative CPU Cost 35618.12
>
> Stage 1 Columns 1
>
> Page Range
>
> Prefetch
>
> Direct Access
>
> Table Space Lock Mode IS
>
>
>
>
>
> Stage 1 Predicates:
>
>
>
> NAME VALUE
>
> ----------------------- -----------------------
>
> Filter Factor 0.9346
>
> Type RANGE
>
> Stage STAGE1
>
> Order 2
>
> Marker N
>
> Boolean Term Y
>
> Join N
>
> After Join
>
> LHS Text DOC_ID
>
> RHS Text VALUE
>
> Direct Access N
>
> Keyfield Y
>
> Added N
>
> Redundant N
>
> LHS Query Block Number 1
>
> LHS Table Number 1
>
> RHS Query Block Number 0
>
> RHS Table Number 0
>
> Predicate Number 2
>
> Predicate Text DOC_ID>0
>
>
>
>
>
> Regards
>
>
>
> André LeBlanc
>
>
>
> *From:* Joe Geller [mailto:[login to unmask email]
> *Sent:* March 9, 2017 9:15 AM
> *To:* [login to unmask email]<mailto:[login to unmask email]>
> *Subject:* [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated
> on index
>
>
>
> Andre,
>
> Tell me about the statistics on the table and on the column DOC_ID. What
> is the table cardinality, what is the column cardinality, and do you have
> distribution statistics for DOC_ID?
>
> One possibility:
>
> Without distribution statistics, DB2 will assume a uniform distribution.
> If, for example, there are 100 million rows in the table and 98 million
> rows have non-zero DOC_ID, then DB2 will assume an average of just over 1
> row per DOC_ID. It will not know that there are 1 million rows with
> DOC_ID=0.
>
> If the query is SELECT * (as you show) or even selecting any non-indexed
> column, then DB2 will have to go to the data row to get the data that
> qualifies through the index. I don't know the current situation, but I do
> know that it used to be a little cheaper for DB2 to apply predicates to the
> data than to the index entries. Since DB2 is assuming that most entries
> will qualify DOC_ID>0 and it has to go to the data anyway, it might be
> cheaper (in most cases) to apply that predicate to the data.
>
> If in your real query this is part of a join and is the inner table, then
> I am guessing that DOC_ID comes from the outer table. In which case, why
> not have a predicate on the DOC_ID on the outer table? Then it will be
> checked before the join access.
>
> Joe
>
>
>
> In Reply to Andre LeBlanc:
>
> It’s actually executed in a left outer join where rows with 0 and other
> values are being joined, so I can’t avoid it. The access path is the same
> whether run as a single select or as part of the outer join.
> What’s worse is that when I tried to add another join predicate on a 5th
> index column it’s also being treated as stage 1 and not screening.
>
> Thanks
> André
>
> From: Sam Baugh [mailto:[login to unmask email]
> Sent: March 8, 2017 3:08 PM
> To: [login to unmask email]<mailto:[login to unmask email]>
> Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on
> index
>
> I'm guessing you're testing this by running query with DOC_ID = 0. Perhaps
> predicate should be 'AND ? > 0' or even better, add logic in whatever
> program language to only execute the query if host variable for DOC_ID is >
> 0.
>
> On Wed, Mar 8, 2017 at 1:42 PM, LeBlanc, André-ITB <[login to unmask email]<
<mailto:[login to unmask email]%3c%0b>> mailto:[login to unmask email]>><mailto:[login to unmask email]%3e%3e> wrote:
> According to IBM knowledge Center, I should be matching on the first two
> and screening on the DOC_TCDE since I’m missing a predicate on the 3rd
> column of the index :
>
> In index screening, predicates are specified on index key columns but are
> not part of the matching columns.
> Those predicates improve the index access by reducing the number of rows
> that qualify while searching the index.
> For example, with an index on T(C1,C2,C3,C4) in the following SQL
> statement, C3>0 and C4=2 are index screening predicates.
> SELECT * FROM T
> WHERE C1 = 1
> AND C3 > 0 AND C4 = 2
> AND C5 = 8;
>
> The predicates can be applied on the index, but they are not matching
> predicates.
> However, C5=8 is not an index screening predicate, and DB2® must evaluated
> that predicate when data is retrieved. The value of the MATCHCOLS column of
> PLAN_TABLE is 1.
> What I don’t understand is why the DOC_ID>0 is not evaluated at the same
> time as DOC_ID=?. I’m finding 10 million rows with DOC_ID = 0 that all get
> rejected after 10 million fetches from the tablespace.
> André
>
> From: Mark Doyle [mailto:[login to unmask email]
> <[login to unmask email]%3cmailto:[login to unmask email]%3e]><mailto:[login to unmask email]%3e]%3e>
> Sent: March 8, 2017 2:17 PM
> To: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
>
> Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on
> index
>
>
> Because you don't specify a value for DOC_DTTM, I would expect that DB2
> would stop screening on the first two columns. If you can stand the index
> resign (probably not) and you flip the order of the last two columns of the
> index, then you would get the screening you expect.
>
> Mark
>
> On 3/8/2017 12:44 PM, LeBlanc, André-ITB wrote:
> I’m perplexed as to why DB2 is not applying all possible predicates on the
> index to avoid tablespace access whenever possible.
> I get millions of getpages on the tablespace even though no rows qualify
> based on information available on the index.
>
> I have an index with the following columns:
> DOC_ID,
> DOC_VER,
> DOC_DTTM,
> DOC_TCDE
>
> I have the following query that should be resolve all predicates on the
> index:
>
> SELECT * FROM T1
> where DOC_ID = ? --> matching
> AND DOC_VER= ? --> matching
> and DOC_TCDE = ? --> screening
> and DOC_ID > 0 --> stage 1 ?
>
> However, the access path shows matching on the first two predicates,
> screening on the third, and stage 1 on the fourth.
>
> I’m seeing a lot of situations where DB2 does not apply a predicate on the
> index even though it could be matching or screening.
> Instead, it’s applied as stage 1 during the fetching of data from the
> tablespace.
>
> Could there be a DSNZPARM that influences this behaviour and discourages
> index screening or matching? I’m at a loss.
>
> Regards,
>
> André LeBlanc
>
>
> From: Terry Purcell [mailto:[login to unmask email]
> Sent: March 3, 2016 2:31 AM
> To: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
>
> Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2
>
>
> Hi Roy,
>
> Given your question "I have always wondered why, when the DB2 optimizer is
> cost based, it cannot use a user created index on the catalog for
> BIND/REBIND/PREPARE…". The optimizer knows the most efficient way to access
> the catalog to read in all information necessary for the
> bind/rebind/prepare of a given statement. Therefore, the "access path" to
> read each catalog object is fixed (hard coded/not up for debate). There
> would be no value to add a cost based optimization step to evaluate each
> alternative access to the catalog. It would merely result in increased cost
> for BIND/REBIND/PREPARE.
>
> Hope this eases your suffering.
>
> Regards
>
> Terry Purcell
>
> In Reply to Roy Boxwell:
> Then I misunderstood you!
>
> If BP0 is just the catalog then it certainly looks like PREPAREs are
> counted in the access to the catalog (I did not know this) but, as far as I
> am aware, creating Indexes will not help you as the access plans are hard
> coded for these “system” style access’s. I have always wondered why, when
> the DB2 optimizer is cost based, it cannot use a user created index on the
> catalog for BIND/REBIND/PREPARE… Perhaps you could open a PMR and then post
> the answer??
>
> Roy Boxwell
>
> SOFTWARE ENGINEERING GMBH and SEGUS Inc.
> -Product Development-
>
> Heinrichstrasse 83-85
> 40239 Duesseldorf/Germany
> Tel. +49 (0)211 96149-675
> Fax +49 (0)211 96149-32
> Email: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
> <[login to unmask email]%3e%3cmailto:[login to unmask email]%3e%3cmailto:[login to unmask email]%3e%3e><mailto:[login to unmask email]%3e%3cmailto:[login to unmask email]%3e%3e%3e>
> http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de>
> <http://www.seg.dehttp/www.seg.de%3chttp:/www.seg.dehttp/www.seg.de%3e><http://www.seg.dehttp/www.seg.de%3chttp:/www.seg.dehttp/www.seg.de%3e%3e>
>
> Software Engineering GmbH
> Amtsgericht Düsseldorf, HRB 37894
> Geschäftsführung: Gerhard Schubert
>
> From: fabio scatolini [mailto:[login to unmask email]
>
> Sent: Monday, February 29, 2016 3:05 PM
> To: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
>
> Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2
>
>
> perhaps we have not understood, in BP0 there are only the catalog tables.
> It is not correct this practice ???
> If not, what is the correct one ????
> What I meant is that if the BP0 there are many pages you have visited,
> then it could be that access to the catalog tables are not indexed.
> It 'a nonsense ???
> Thank you
>
> -----End Original Message-----
>
> -----End Original Message-----
>
> -----End Original Message-----
>
> -----End Original Message-----
>
> -----End Original Message-----
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>

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

Terry Purcell

RE: db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Andre LeBlanc)

Sorry for delay in replying - I see this discussion has gone a couple of days.

To Joe's question - what does the optimizer know? Although there are some targeted cases where predicates are combined or simplified - this isn't one of them.

You can see from the discussion that DB2 does prune some cases of mutually exclusive predicates - this occurs mostly when it is with literals - because when binding/preparing we are able to compare the actual values.

When the optimizer is choosing the access path, there is a host variable involved in this example - so it can't know that the predicates are mutually exclusive. That determination needs to happen at execution time when the literal values are known. At execution time (with the literals supplied) - the optimizer isn't re-evaluating the access path.

However, as compared with bindtime - there is less pruning that occurs at runtime (and in this case there is no pruning) - since it is limited to situations where there are multiple query blocks - specifically UNION ALL queries as an example.

Why doesn't DB2 (runtime) prune here? Because it has never been raised before as an interesting customer requirement - despite the fact that we have historically seen a handful of cases like this.

For this type of situation, the optimizer will choose the best predicates as index matching (since matching is possible) and apply the other predicate after.

I do believe it would be interesting for DB2 support to study this example to see why the predicate can't be index screening. That would require a PMR.

Regards
Terry Purcell

Joe Geller

RE: db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Terry Purcell)

Terry,

Am I correct in that it is slightly less costly (CPU) to evaluate a predicate against the data row than in the index?  I believe that used to be the case.  If so, if the Optimizer's estimate for the predicate is that almost all rows would satisfy the predicate and the data row would have to be accessed, might the cost therefore be less to apply the predicate to the data row instead of as an index screening predicate?

Joe

In Reply to Terry Purcell:

Sorry for delay in replying - I see this discussion has gone a couple of days.

To Joe's question - what does the optimizer know? Although there are some targeted cases where predicates are combined or simplified - this isn't one of them.

You can see from the discussion that DB2 does prune some cases of mutually exclusive predicates - this occurs mostly when it is with literals - because when binding/preparing we are able to compare the actual values.

When the optimizer is choosing the access path, there is a host variable involved in this example - so it can't know that the predicates are mutually exclusive. That determination needs to happen at execution time when the literal values are known. At execution time (with the literals supplied) - the optimizer isn't re-evaluating the access path.

However, as compared with bindtime - there is less pruning that occurs at runtime (and in this case there is no pruning) - since it is limited to situations where there are multiple query blocks - specifically UNION ALL queries as an example.

Why doesn't DB2 (runtime) prune here? Because it has never been raised before as an interesting customer requirement - despite the fact that we have historically seen a handful of cases like this.

For this type of situation, the optimizer will choose the best predicates as index matching (since matching is possible) and apply the other predicate after.

I do believe it would be interesting for DB2 support to study this example to see why the predicate can't be index screening. That would require a PMR.

Regards
Terry Purcell

Andre LeBlanc

db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Andre LeBlanc)
Thanks Terry. I was very happy to see you’d replied.
Do I understand correctly that DB2 will not apply “not equals” or “greater/less than” predicates on an index column if a matching predicate is already applied?

That would be bad news. We have a lot of situations where one value needs to be avoided at all costs since every value in the column is unique except for one value that is repeated 10 million times.
Index matching is required in all situations except for that filler value. Often 0 is used as a filler and SEQUENCE generates all other values.
I do not approve a this design, but that’s the situation I have to work with once it hits prod.

Can you recommend a better way to write a sql when the join predicate is a column where some of the values may be unique while others may be filler to be avoided at all cost?

Select *
from t1 left Join t2
on t1.a = t2.a
and t2.a <> 0
where t1.b = ?

Regards
André LeBlanc

From: Joe Geller [mailto:[login to unmask email]
Sent: March 17, 2017 9:54 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index


Terry,

Am I correct in that it is slightly less costly (CPU) to evaluate a predicate against the data row than in the index? I believe that used to be the case. If so, if the Optimizer's estimate for the predicate is that almost all rows would satisfy the predicate and the data row would have to be accessed, might the cost therefore be less to apply the predicate to the data row instead of as an index screening predicate?

Joe

In Reply to Terry Purcell:

Sorry for delay in replying - I see this discussion has gone a couple of days.

To Joe's question - what does the optimizer know? Although there are some targeted cases where predicates are combined or simplified - this isn't one of them.

You can see from the discussion that DB2 does prune some cases of mutually exclusive predicates - this occurs mostly when it is with literals - because when binding/preparing we are able to compare the actual values.

When the optimizer is choosing the access path, there is a host variable involved in this example - so it can't know that the predicates are mutually exclusive. That determination needs to happen at execution time when the literal values are known. At execution time (with the literals supplied) - the optimizer isn't re-evaluating the access path.

However, as compared with bindtime - there is less pruning that occurs at runtime (and in this case there is no pruning) - since it is limited to situations where there are multiple query blocks - specifically UNION ALL queries as an example.

Why doesn't DB2 (runtime) prune here? Because it has never been raised before as an interesting customer requirement - despite the fact that we have historically seen a handful of cases like this.

For this type of situation, the optimizer will choose the best predicates as index matching (since matching is possible) and apply the other predicate after.

I do believe it would be interesting for DB2 support to study this example to see why the predicate can't be index screening. That would require a PMR.

Regards
Terry Purcell

From: LeBlanc, André-ITB [mailto:[login to unmask email]
Sent: March 14, 2017 12:42 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index

Thank you all for your feedback. I’ve been fighting a lot of fires and didn’t have the opportunity to respond until now.

I initially thought there must be a data type mismatch and that the column was not numeric but textual. However, the column is in fact a BIGINT.
Even if I replace the “DOC_ID > 0” with “DOC_ID > ?”, it’s still stage 1.

I see this exact same behaviour when I run a single table select or a left outer join. The “> 0”is always stage 1 and never evaluated on the index.

The table has an xml column that I need, but the path is not changed whether I select * or just the xml column.
I don’t want to create an index that has all the join predicates and the xml column to avoid the TS fetch.
I simply want to avoid going to the tablespace until all predicates are evaluated on the index since the value 0 is repeated over 10 million times.

I simplified the query even more to focus on the index usage in this specific case. Why is the index not used to evaluate the DOC_ID > 0?

SELECT xml_col FROM T1
WHERE DOC_ID > 0
AND DOC_ID = ?

table T1
NAME VALUE
-------------------------- ---------------------------
Name T1
Type T
Table Number 1
Qualifying Rows 5.0148
Base Table Type T
Rows 1.60668525E8
Pages 3724378.0
Compressed Row Percentage 100
Occupied Page Percentage 99
Record Length 179
Columns 15
Primary Key Columns 7
Status X
Parents 0
Children 0
Encoding Scheme E
DASD Storage 3.2476752E7
Average Row Length 1146.0
Partition Key Columns 1
Dependent MQTs 0
Append N
RUNSTATS TIMESTAMP 2017-03-11 13:32:27.177324
Explain Time 2017-03-14 12:06:39.31
Versioning Schema
Versioning Table

Column DOC_ID:
NAME VALUE
--------------------------------- -------------------------------------
Name DOC_ID
Cardinality 3.2038667E7
Second High Key 62082128
Second Low Key 1202
Sequence 1
Type BIGINT
Status
Length 8
Scale 0
Nullable N
Updatable Y
Key Sequence 1
Max Length 0
Default N
Partition Key Sequence 0
Partition Key Ordering
CCSID 0
Default Value
RUNSTATS Format
Timestamp 2017-03-11 19:50:59.853913
Field Procedure N

Coldist:
VALUE FREQUENCY
-------------------- -------------------------------------
0 0.06536011
45622961 2.8607075E-5
51343109 2.5168009E-5
37150260 7.2885855E-6
60933510 6.884355E-6
60936844 6.809728E-6
61643427 6.3992784E-6
60978737 6.1194264E-6
29264278 5.80848E-6
58092889 4.8196703E-6

ixscan:

NAME VALUE
----------------------- -----------------------
Input RIDs 1.60668525E8
Index Leaf Pages 5019716
Matching Predicates Filter Factor
DOC_ID=(EXPR) 3.1212294970828225E-8
Scanned Leaf Pages 1
Output RIDs 6
Type I
Matching Columns 1
Matching Filter Factor 3.1212295E-8
Total Filter Factor 3.1212295E-8

matching predicates:

NAME VALUE
----------------------- -----------------------
Filter Factor 3.1212294970828225E-8
Type EQUAL
Stage MATCHING
Order 1
Marker Y
Boolean Term Y
Join N
After Join
LHS Text DOC_ID
RHS Text VALUE
Direct Access N
Keyfield Y
Added N
Redundant N
LHS Query Block Number 1
LHS Table Number 1
RHS Query Block Number 0
RHS Table Number 0
Predicate Number 3
Predicate Text DOC_ID=(EXPR)



fetch:

NAME VALUE
----------------------- -----------------------
Input Cardinality 6
Scanned Rows 6
Stage 1 Predicates Filter Factor
DOC_ID>0 0.9346
Stage 1 Returned Rows 5.0148
Stage 2 Returned Rows 5.0148
Output Cardinality 5.0148
Cumulative Total Cost 5.8023
Cumulative IO Cost 2.8726
Cumulative CPU Cost 35618.12
Stage 1 Columns 1
Page Range
Prefetch
Direct Access
Table Space Lock Mode IS


Stage 1 Predicates:

NAME VALUE
----------------------- -----------------------
Filter Factor 0.9346
Type RANGE
Stage STAGE1
Order 2
Marker N
Boolean Term Y
Join N
After Join
LHS Text DOC_ID
RHS Text VALUE
Direct Access N
Keyfield Y
Added N
Redundant N
LHS Query Block Number 1
LHS Table Number 1
RHS Query Block Number 0
RHS Table Number 0
Predicate Number 2
Predicate Text DOC_ID>0


Regards

André LeBlanc

From: Joe Geller [mailto:[login to unmask email]
Sent: March 9, 2017 9:15 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index


Andre,

Tell me about the statistics on the table and on the column DOC_ID. What is the table cardinality, what is the column cardinality, and do you have distribution statistics for DOC_ID?

One possibility:

Without distribution statistics, DB2 will assume a uniform distribution. If, for example, there are 100 million rows in the table and 98 million rows have non-zero DOC_ID, then DB2 will assume an average of just over 1 row per DOC_ID. It will not know that there are 1 million rows with DOC_ID=0.

If the query is SELECT * (as you show) or even selecting any non-indexed column, then DB2 will have to go to the data row to get the data that qualifies through the index. I don't know the current situation, but I do know that it used to be a little cheaper for DB2 to apply predicates to the data than to the index entries. Since DB2 is assuming that most entries will qualify DOC_ID>0 and it has to go to the data anyway, it might be cheaper (in most cases) to apply that predicate to the data.

If in your real query this is part of a join and is the inner table, then I am guessing that DOC_ID comes from the outer table. In which case, why not have a predicate on the DOC_ID on the outer table? Then it will be checked before the join access.

Joe


In Reply to Andre LeBlanc:
It’s actually executed in a left outer join where rows with 0 and other values are being joined, so I can’t avoid it. The access path is the same whether run as a single select or as part of the outer join.
What’s worse is that when I tried to add another join predicate on a 5th index column it’s also being treated as stage 1 and not screening.

Thanks
André

From: Sam Baugh [mailto:[login to unmask email]
Sent: March 8, 2017 3:08 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index

I'm guessing you're testing this by running query with DOC_ID = 0. Perhaps predicate should be 'AND ? > 0' or even better, add logic in whatever program language to only execute the query if host variable for DOC_ID is > 0.

On Wed, Mar 8, 2017 at 1:42 PM, LeBlanc, André-ITB <[login to unmask email]<mailto:[login to unmask email]>><mailto:[login to unmask email]%3e%3e> wrote:
According to IBM knowledge Center, I should be matching on the first two and screening on the DOC_TCDE since I’m missing a predicate on the 3rd column of the index :

In index screening, predicates are specified on index key columns but are not part of the matching columns.
Those predicates improve the index access by reducing the number of rows that qualify while searching the index.
For example, with an index on T(C1,C2,C3,C4) in the following SQL statement, C3>0 and C4=2 are index screening predicates.
SELECT * FROM T
WHERE C1 = 1
AND C3 > 0 AND C4 = 2
AND C5 = 8;

The predicates can be applied on the index, but they are not matching predicates.
However, C5=8 is not an index screening predicate, and DB2® must evaluated that predicate when data is retrieved. The value of the MATCHCOLS column of PLAN_TABLE is 1.
What I don’t understand is why the DOC_ID>0 is not evaluated at the same time as DOC_ID=?. I’m finding 10 million rows with DOC_ID = 0 that all get rejected after 10 million fetches from the tablespace.
André

From: Mark Doyle [mailto:[login to unmask email]<mailto:[login to unmask email]%3cmailto:[login to unmask email]%3e]>
Sent: March 8, 2017 2:17 PM
To: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index


Because you don't specify a value for DOC_DTTM, I would expect that DB2 would stop screening on the first two columns. If you can stand the index resign (probably not) and you flip the order of the last two columns of the index, then you would get the screening you expect.

Mark

On 3/8/2017 12:44 PM, LeBlanc, André-ITB wrote:
I’m perplexed as to why DB2 is not applying all possible predicates on the index to avoid tablespace access whenever possible.
I get millions of getpages on the tablespace even though no rows qualify based on information available on the index.

I have an index with the following columns:
DOC_ID,
DOC_VER,
DOC_DTTM,
DOC_TCDE

I have the following query that should be resolve all predicates on the index:

SELECT * FROM T1
where DOC_ID = ? --> matching
AND DOC_VER= ? --> matching
and DOC_TCDE = ? --> screening
and DOC_ID > 0 --> stage 1 ?

However, the access path shows matching on the first two predicates, screening on the third, and stage 1 on the fourth.

I’m seeing a lot of situations where DB2 does not apply a predicate on the index even though it could be matching or screening.
Instead, it’s applied as stage 1 during the fetching of data from the tablespace.

Could there be a DSNZPARM that influences this behaviour and discourages index screening or matching? I’m at a loss.

Regards,

André LeBlanc


From: Terry Purcell [mailto:[login to unmask email]
Sent: March 3, 2016 2:31 AM
To: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2


Hi Roy,

Given your question "I have always wondered why, when the DB2 optimizer is cost based, it cannot use a user created index on the catalog for BIND/REBIND/PREPARE…". The optimizer knows the most efficient way to access the catalog to read in all information necessary for the bind/rebind/prepare of a given statement. Therefore, the "access path" to read each catalog object is fixed (hard coded/not up for debate). There would be no value to add a cost based optimization step to evaluate each alternative access to the catalog. It would merely result in increased cost for BIND/REBIND/PREPARE.

Hope this eases your suffering.

Regards

Terry Purcell

In Reply to Roy Boxwell:
Then I misunderstood you!

If BP0 is just the catalog then it certainly looks like PREPAREs are counted in the access to the catalog (I did not know this) but, as far as I am aware, creating Indexes will not help you as the access plans are hard coded for these “system” style access’s. I have always wondered why, when the DB2 optimizer is cost based, it cannot use a user created index on the catalog for BIND/REBIND/PREPARE… Perhaps you could open a PMR and then post the answer??

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e%3cmailto:[login to unmask email]%3e%3cmailto:[login to unmask email]%3e%3e>
http://www.seg.dehttp://www.seg.de<http://www.seg.dehttp/www.seg.de%3chttp:/www.seg.dehttp/www.seg.de%3e>

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

From: fabio scatolini [mailto:[login to unmask email]
Sent: Monday, February 29, 2016 3:05 PM
To: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Subject: [DB2-L] - RE: getpage of consumption for access to the catalog db2


perhaps we have not understood, in BP0 there are only the catalog tables.
It is not correct this practice ???
If not, what is the correct one ????
What I meant is that if the BP0 there are many pages you have visited, then it could be that access to the catalog tables are not indexed.
It 'a nonsense ???
Thank you

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

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

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

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

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

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

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

Peter Vanroose

RE: db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Andre LeBlanc)

Andre,

In such cases you can always "force" in-index predicate filtering (at the cost of an extra query block, possibly with materialisation, so look out for that):
delegate the filtering to a non-correlated subquery which returns just in-index columns, so the subquery, being index-only, is normally evaluated in-index (possibly with index screening). In the outer query the table appears again, now filtered though the subquery.

In your example:

Select *
from t1 Left Join
(Select * from t2 where a in (Select a from t2 where a<>0) ) t22
on t1.a = t22.a
where t1.b = ?
In reply to Andre Leblanc:

Do I understand correctly that DB2 will not apply “not equals” or “greater/less than” predicates on an index column if a matching predicate is already applied?

That would be bad news. We have a lot of situations where one value needs to be avoided at all costs since every value in the column is unique except for one value that is repeated 10 million times.
Index matching is required in all situations except for that filler value. Often 0 is used as a filler and SEQUENCE generates all other values.
I do not approve a this design, but that’s the situation I have to work with once it hits prod.

Can you recommend a better way to write a sql when the join predicate is a column where some of the values may be unique while others may be filler to be avoided at all cost?

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/

Terry Purcell

RE: db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Andre LeBlanc)

Hi Andre & Joe,

To Joe's questions - yes, there is lower cost to applying a predicate on a data row than as a screening predicate on an index entry. However, in this case the issue is that applying the predicate against the index entry would then result in fewer data rows/pages needing to be accessed.

Which leads to Andre's questions - DB2 will only apply one predicate on each column as matching (some exceptions including >= AND <= which are combined into one BETWEEN predicate) - and the others are applied "later". When is later? I haven't had to chance to investigate further - it will take some discovery which will take some time - and that is why I was hoping for a PMR so that support could reconstruct the testcase. I can add that to my to-do list........when? Not sure.

So how to resolve this now for Andre? Given the LEFT OUTER JOIN query you have, the reason this issue is being exposed is that we only have a predicate on the right side, and DB2 doesn't do predicate transitive closure for the ON clause predicate in your example - so you will need to code the same predicate onto T1. Once you do that - this issue should disappear because DB2 wont attempt the join from left to right if it determines that the ON clause is false before the join.

Select *
from t1 left Join t2
on t1.a = t2.a
and t2.a <> 0
and t1.a <> 0     <-- add this predicate
where t1.b = ?

Test the above and report back if you are able. You have to be careful with adding/duplicating ON/WHERE clause predicates in outer joins to ensure you get the same result - but the above rewrite is correct given your query example.

Regards
Terry Purcell

Joe Geller

RE: db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Terry Purcell)

Terry's predicate on t1 should solve the problem and makes the most sense for a solution because the > 0 predicate is done on the outer table.  But, Andre, could you try something for me?  Instead of the literal 0 (t2.a > 0), use a parameter marker (or host variable).  Do the Explain and see if that makes a difference. 

That would indicate if the problem is with the filter factor.  t2.a > ? will have a much lower filter factor than t2.a > 0 since even though there are many rows with 0, there are many more without. From you statistics data, in fact, the coldist data shows that 0 is 6% of the rows, which means that >0 is 94% (filter factor .94).  This column (DOC_ID in the actual table) has a cardinality of 32,000,000.  col > ? has a filter factor based on the column cardinality.  For 32,000,000 it would be only 1/3000 or .00033 (see the Managing Performance manual - chapter 29 for V11).

Joe

In Reply to Terry Purcell:

Hi Andre & Joe,

To Joe's questions - yes, there is lower cost to applying a predicate on a data row than as a screening predicate on an index entry. However, in this case the issue is that applying the predicate against the index entry would then result in fewer data rows/pages needing to be accessed.

Which leads to Andre's questions - DB2 will only apply one predicate on each column as matching (some exceptions including >= AND <= which are combined into one BETWEEN predicate) - and the others are applied "later". When is later? I haven't had to chance to investigate further - it will take some discovery which will take some time - and that is why I was hoping for a PMR so that support could reconstruct the testcase. I can add that to my to-do list........when? Not sure.

So how to resolve this now for Andre? Given the LEFT OUTER JOIN query you have, the reason this issue is being exposed is that we only have a predicate on the right side, and DB2 doesn't do predicate transitive closure for the ON clause predicate in your example - so you will need to code the same predicate onto T1. Once you do that - this issue should disappear because DB2 wont attempt the join from left to right if it determines that the ON clause is false before the join.

Select *
from t1 left Join t2
on t1.a = t2.a
and t2.a <> 0
and t1.a <> 0     where t1.b = ?

Test the above and report back if you are able. You have to be careful with adding/duplicating ON/WHERE clause predicates in outer joins to ensure you get the same result - but the above rewrite is correct given your query example.

Regards
Terry Purcell

Andre LeBlanc

db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Joe Geller)
Hi Joe/Terry,

I already addressed these points on my March 14th response.

When I already tried replacing the “> 0” with “> ?” the filter factor changed from 0.9346 to 0.0003, but it was still applied as stage1 during the fetch from the TS.

Even if I replace the “DOC_ID > 0” with “DOC_ID > ?”, it’s still stage 1.

Terry,
I also explained that the issue isn’t with the left outer join since the behaviour is the same for a simple select.
The only reason I mentioned the left outer join was because solutions were proposed to work around the problem that could not apply to the left outer join scenario.

In this case, I can’t apply this predicate to the right side of the left outer join since I do want to return results with 0 from the right side.
I did an explain of the change you suggested and it gave an extra stage 2 predicate on the left table:

Select *
from t1 left Join t2
on t1.a = t2.a
and t2.a <> 0
and t1.a <> 0 where t1.b = ?

Here’s how the FETCH on t2 is processed.

NAME VALUE
----------------------- --------------
Input Cardinality 2
Scanned Rows 2
Stage 1 Predicates Filter Factor
T2.DOC_ID<>0 0.9363
Stage 1 Returned Rows 1.4588
Stage 2 Predicates Filter Factor
T1.DOC_ID<>0 1
Stage 2 Returned Rows 1.4588
Output Cardinality 1.4588
Cumulative Total Cost N/A
Cumulative IO Cost N/A
Cumulative CPU Cost N/A
Stage 1 Columns 1
Page Range
Prefetch
Direct Access
Table Space Lock Mode N


Regards
André LeBlanc

From: Joe Geller [mailto:[login to unmask email]
Sent: March 19, 2017 10:08 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index


Terry's predicate on t1 should solve the problem and makes the most sense for a solution because the > 0 predicate is done on the outer table. But, Andre, could you try something for me? Instead of the literal 0 (t2.a > 0), use a parameter marker (or host variable). Do the Explain and see if that makes a difference.

That would indicate if the problem is with the filter factor. t2.a > ? will have a much lower filter factor than t2.a > 0 since even though there are many rows with 0, there are many more without. From you statistics data, in fact, the coldist data shows that 0 is 6% of the rows, which means that >0 is 94% (filter factor .94). This column (DOC_ID in the actual table) has a cardinality of 32,000,000. col > ? has a filter factor based on the column cardinality. For 32,000,000 it would be only 1/3000 or .00033 (see the Managing Performance manual - chapter 29 for V11).

Joe

In Reply to Terry Purcell:

Hi Andre & Joe,

To Joe's questions - yes, there is lower cost to applying a predicate on a data row than as a screening predicate on an index entry. However, in this case the issue is that applying the predicate against the index entry would then result in fewer data rows/pages needing to be accessed.

Which leads to Andre's questions - DB2 will only apply one predicate on each column as matching (some exceptions including >= AND <= which are combined into one BETWEEN predicate) - and the others are applied "later". When is later? I haven't had to chance to investigate further - it will take some discovery which will take some time - and that is why I was hoping for a PMR so that support could reconstruct the testcase. I can add that to my to-do list........when? Not sure.

So how to resolve this now for Andre? Given the LEFT OUTER JOIN query you have, the reason this issue is being exposed is that we only have a predicate on the right side, and DB2 doesn't do predicate transitive closure for the ON clause predicate in your example - so you will need to code the same predicate onto T1. Once you do that - this issue should disappear because DB2 wont attempt the join from left to right if it determines that the ON clause is false before the join.

Select *
from t1 left Join t2
on t1.a = t2.a
and t2.a <> 0
and t1.a <> 0 where t1.b = ?

Test the above and report back if you are able. You have to be careful with adding/duplicating ON/WHERE clause predicates in outer joins to ensure you get the same result - but the above rewrite is correct given your query example.

Regards
Terry Purcell

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

Joe Geller

RE: db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Andre LeBlanc)

Andre,

In regards to Terry's solution to the left outer join, you said

"I can’t apply this predicate to the right side of the left outer join since I do want to return results with 0 from the right side."

Did you mean you can't apply the predicate (t1.a <> 0) to the left side?

Anyway, there is a different in whether the predicate is in the Where clause of the ON clause as Terry suggested.  If you had it in the Where clause you would not get rows where the ID was 0 (and that is not what you want).  But if the ON clause it is part of the join criteria - i.e. find the t2 rows where the ON clause is TRUE.  Here, if t1.a = 0, t2 will not be returned, but t1 will be returned (with NULLs for the right hand side of the join).  I think that is what you want.

Joe

In Reply to Andre LeBlanc:

Hi Joe/Terry,

I already addressed these points on my March 14th response.

When I already tried replacing the “> 0” with “> ?” the filter factor changed from 0.9346 to 0.0003, but it was still applied as stage1 during the fetch from the TS.

Even if I replace the “DOC_ID > 0” with “DOC_ID > ?”, it’s still stage 1.

Terry,
I also explained that the issue isn’t with the left outer join since the behaviour is the same for a simple select.
The only reason I mentioned the left outer join was because solutions were proposed to work around the problem that could not apply to the left outer join scenario.

In this case, I can’t apply this predicate to the right side of the left outer join since I do want to return results with 0 from the right side.
I did an explain of the change you suggested and it gave an extra stage 2 predicate on the left table:

Select *
from t1 left Join t2
on t1.a = t2.a
and t2.a <> 0
and t1.a <> 0 where t1.b = ?

Here’s how the FETCH on t2 is processed.

NAME VALUE
----------------------- --------------
Input Cardinality 2
Scanned Rows 2
Stage 1 Predicates Filter Factor
T2.DOC_ID<>0 0.9363
Stage 1 Returned Rows 1.4588
Stage 2 Predicates Filter Factor
T1.DOC_ID<>0 1
Stage 2 Returned Rows 1.4588
Output Cardinality 1.4588
Cumulative Total Cost N/A
Cumulative IO Cost N/A
Cumulative CPU Cost N/A
Stage 1 Columns 1
Page Range
Prefetch
Direct Access
Table Space Lock Mode N


Regards
André LeBlanc

From: Joe Geller [mailto:[login to unmask email]
Sent: March 19, 2017 10:08 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index


Terry's predicate on t1 should solve the problem and makes the most sense for a solution because the > 0 predicate is done on the outer table. But, Andre, could you try something for me? Instead of the literal 0 (t2.a > 0), use a parameter marker (or host variable). Do the Explain and see if that makes a difference.

That would indicate if the problem is with the filter factor. t2.a > ? will have a much lower filter factor than t2.a > 0 since even though there are many rows with 0, there are many more without. From you statistics data, in fact, the coldist data shows that 0 is 6% of the rows, which means that >0 is 94% (filter factor .94). This column (DOC_ID in the actual table) has a cardinality of 32,000,000. col > ? has a filter factor based on the column cardinality. For 32,000,000 it would be only 1/3000 or .00033 (see the Managing Performance manual - chapter 29 for V11).

Joe

In Reply to Terry Purcell:

Hi Andre & Joe,

To Joe's questions - yes, there is lower cost to applying a predicate on a data row than as a screening predicate on an index entry. However, in this case the issue is that applying the predicate against the index entry would then result in fewer data rows/pages needing to be accessed.

Which leads to Andre's questions - DB2 will only apply one predicate on each column as matching (some exceptions including >= AND <= which are combined into one BETWEEN predicate) - and the others are applied "later". When is later? I haven't had to chance to investigate further - it will take some discovery which will take some time - and that is why I was hoping for a PMR so that support could reconstruct the testcase. I can add that to my to-do list........when? Not sure.

So how to resolve this now for Andre? Given the LEFT OUTER JOIN query you have, the reason this issue is being exposed is that we only have a predicate on the right side, and DB2 doesn't do predicate transitive closure for the ON clause predicate in your example - so you will need to code the same predicate onto T1. Once you do that - this issue should disappear because DB2 wont attempt the join from left to right if it determines that the ON clause is false before the join.

Select *
from t1 left Join t2
on t1.a = t2.a
and t2.a <> 0
and t1.a <> 0 where t1.b = ?

Test the above and report back if you are able. You have to be careful with adding/duplicating ON/WHERE clause predicates in outer joins to ensure you get the same result - but the above rewrite is correct given your query example.

Regards
Terry Purcell

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

Andre LeBlanc

db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Joe Geller)
You’re correct Joe, I meant to say left, not right. Thank you.
My problem here is that I understand all of the concepts on applying predicates in the where clause vs the ON clause, the difference between host variables and explicitly coded values, how DB2 calculates filter factors and determines access path…. Everything that was suggested is exactly what I did before asking for help. But that’s a good thing. It’s reassuring to see that I actually know what I’m doing, because I was started to wonder.
The only option I didn’t try was Terry’s last suggestion which ended up being stage2, so I’m back to square one.

I’ll follow Terry’s recommendation and open a PMR with the simple query and ask:

Why is the index not used to evaluate the DOC_ID > 0 after matching on DOC_ID = ?.

SELECT xml_col FROM T1
WHERE DOC_ID > 0
AND DOC_ID = ?

Can DB2 process a range predicate as index screening if it already has a matching predicate on the same index column? If the answer is no, can I request this enhancement be made to DB2 z/OS?

Thanks to everyone for your help. I really appreciate it!

Regards,
André LeBlanc

From: Joe Geller [mailto:[login to unmask email]
Sent: March 20, 2017 3:26 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index


Andre,

In regards to Terry's solution to the left outer join, you said

"I can’t apply this predicate to the right side of the left outer join since I do want to return results with 0 from the right side."

Did you mean you can't apply the predicate (t1.a <> 0) to the left side?

Anyway, there is a different in whether the predicate is in the Where clause of the ON clause as Terry suggested. If you had it in the Where clause you would not get rows where the ID was 0 (and that is not what you want). But if the ON clause it is part of the join criteria - i.e. find the t2 rows where the ON clause is TRUE. Here, if t1.a = 0, t2 will not be returned, but t1 will be returned (with NULLs for the right hand side of the join). I think that is what you want.

Joe

In Reply to Andre LeBlanc:
Hi Joe/Terry,

I already addressed these points on my March 14th response.

When I already tried replacing the “> 0” with “> ?” the filter factor changed from 0.9346 to 0.0003, but it was still applied as stage1 during the fetch from the TS.

Even if I replace the “DOC_ID > 0” with “DOC_ID > ?”, it’s still stage 1.

Terry,
I also explained that the issue isn’t with the left outer join since the behaviour is the same for a simple select.
The only reason I mentioned the left outer join was because solutions were proposed to work around the problem that could not apply to the left outer join scenario.

In this case, I can’t apply this predicate to the right side of the left outer join since I do want to return results with 0 from the right side.
I did an explain of the change you suggested and it gave an extra stage 2 predicate on the left table:

Select *
from t1 left Join t2
on t1.a = t2.a
and t2.a <> 0
and t1.a <> 0 where t1.b = ?

Here’s how the FETCH on t2 is processed.

NAME VALUE
----------------------- --------------
Input Cardinality 2
Scanned Rows 2
Stage 1 Predicates Filter Factor
T2.DOC_ID<>0 0.9363
Stage 1 Returned Rows 1.4588
Stage 2 Predicates Filter Factor
T1.DOC_ID<>0 1
Stage 2 Returned Rows 1.4588
Output Cardinality 1.4588
Cumulative Total Cost N/A
Cumulative IO Cost N/A
Cumulative CPU Cost N/A
Stage 1 Columns 1
Page Range
Prefetch
Direct Access
Table Space Lock Mode N


Regards
André LeBlanc

From: Joe Geller [mailto:[login to unmask email]
Sent: March 19, 2017 10:08 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index


Terry's predicate on t1 should solve the problem and makes the most sense for a solution because the > 0 predicate is done on the outer table. But, Andre, could you try something for me? Instead of the literal 0 (t2.a > 0), use a parameter marker (or host variable). Do the Explain and see if that makes a difference.

That would indicate if the problem is with the filter factor. t2.a > ? will have a much lower filter factor than t2.a > 0 since even though there are many rows with 0, there are many more without. From you statistics data, in fact, the coldist data shows that 0 is 6% of the rows, which means that >0 is 94% (filter factor .94). This column (DOC_ID in the actual table) has a cardinality of 32,000,000. col > ? has a filter factor based on the column cardinality. For 32,000,000 it would be only 1/3000 or .00033 (see the Managing Performance manual - chapter 29 for V11).

Joe

In Reply to Terry Purcell:

Hi Andre & Joe,

To Joe's questions - yes, there is lower cost to applying a predicate on a data row than as a screening predicate on an index entry. However, in this case the issue is that applying the predicate against the index entry would then result in fewer data rows/pages needing to be accessed.

Which leads to Andre's questions - DB2 will only apply one predicate on each column as matching (some exceptions including >= AND <= which are combined into one BETWEEN predicate) - and the others are applied "later". When is later? I haven't had to chance to investigate further - it will take some discovery which will take some time - and that is why I was hoping for a PMR so that support could reconstruct the testcase. I can add that to my to-do list........when? Not sure.

So how to resolve this now for Andre? Given the LEFT OUTER JOIN query you have, the reason this issue is being exposed is that we only have a predicate on the right side, and DB2 doesn't do predicate transitive closure for the ON clause predicate in your example - so you will need to code the same predicate onto T1. Once you do that - this issue should disappear because DB2 wont attempt the join from left to right if it determines that the ON clause is false before the join.

Select *
from t1 left Join t2
on t1.a = t2.a
and t2.a <> 0
and t1.a <> 0 where t1.b = ?

Test the above and report back if you are able. You have to be careful with adding/duplicating ON/WHERE clause predicates in outer joins to ensure you get the same result - but the above rewrite is correct given your query example.

Regards
Terry Purcell

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

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

Joe Geller

RE: db2 z/os V10 - Indexable Predicate not evaluated on index
(in response to Andre LeBlanc)

Andre,

Test out Terry's solution for the join.  It is stage 2, but it is applied to the outer table.  If there are 10 million rows with 0 on the inner table, they won't be accessed.

Joe

In Reply to Andre LeBlanc:

You’re correct Joe, I meant to say left, not right. Thank you.
My problem here is that I understand all of the concepts on applying predicates in the where clause vs the ON clause, the difference between host variables and explicitly coded values, how DB2 calculates filter factors and determines access path…. Everything that was suggested is exactly what I did before asking for help. But that’s a good thing. It’s reassuring to see that I actually know what I’m doing, because I was started to wonder.
The only option I didn’t try was Terry’s last suggestion which ended up being stage2, so I’m back to square one.

I’ll follow Terry’s recommendation and open a PMR with the simple query and ask:

Why is the index not used to evaluate the DOC_ID > 0 after matching on DOC_ID = ?.

SELECT xml_col FROM T1
WHERE DOC_ID > 0
AND DOC_ID = ?

Can DB2 process a range predicate as index screening if it already has a matching predicate on the same index column? If the answer is no, can I request this enhancement be made to DB2 z/OS?

Thanks to everyone for your help. I really appreciate it!

Regards,
André LeBlanc

From: Joe Geller [mailto:[login to unmask email]
Sent: March 20, 2017 3:26 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index


Andre,

In regards to Terry's solution to the left outer join, you said

"I can’t apply this predicate to the right side of the left outer join since I do want to return results with 0 from the right side."

Did you mean you can't apply the predicate (t1.a <> 0) to the left side?

Anyway, there is a different in whether the predicate is in the Where clause of the ON clause as Terry suggested. If you had it in the Where clause you would not get rows where the ID was 0 (and that is not what you want). But if the ON clause it is part of the join criteria - i.e. find the t2 rows where the ON clause is TRUE. Here, if t1.a = 0, t2 will not be returned, but t1 will be returned (with NULLs for the right hand side of the join). I think that is what you want.

Joe

In Reply to Andre LeBlanc:
Hi Joe/Terry,

I already addressed these points on my March 14th response.

When I already tried replacing the “> 0” with “> ?” the filter factor changed from 0.9346 to 0.0003, but it was still applied as stage1 during the fetch from the TS.

Even if I replace the “DOC_ID > 0” with “DOC_ID > ?”, it’s still stage 1.

Terry,
I also explained that the issue isn’t with the left outer join since the behaviour is the same for a simple select.
The only reason I mentioned the left outer join was because solutions were proposed to work around the problem that could not apply to the left outer join scenario.

In this case, I can’t apply this predicate to the right side of the left outer join since I do want to return results with 0 from the right side.
I did an explain of the change you suggested and it gave an extra stage 2 predicate on the left table:

Select *
from t1 left Join t2
on t1.a = t2.a
and t2.a <> 0
and t1.a <> 0 where t1.b = ?

Here’s how the FETCH on t2 is processed.

NAME VALUE
----------------------- --------------
Input Cardinality 2
Scanned Rows 2
Stage 1 Predicates Filter Factor
T2.DOC_ID<>0 0.9363
Stage 1 Returned Rows 1.4588
Stage 2 Predicates Filter Factor
T1.DOC_ID<>0 1
Stage 2 Returned Rows 1.4588
Output Cardinality 1.4588
Cumulative Total Cost N/A
Cumulative IO Cost N/A
Cumulative CPU Cost N/A
Stage 1 Columns 1
Page Range
Prefetch
Direct Access
Table Space Lock Mode N


Regards
André LeBlanc

From: Joe Geller [mailto:[login to unmask email]
Sent: March 19, 2017 10:08 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: db2 z/os V10 - Indexable Predicate not evaluated on index


Terry's predicate on t1 should solve the problem and makes the most sense for a solution because the > 0 predicate is done on the outer table. But, Andre, could you try something for me? Instead of the literal 0 (t2.a > 0), use a parameter marker (or host variable). Do the Explain and see if that makes a difference.

That would indicate if the problem is with the filter factor. t2.a > ? will have a much lower filter factor than t2.a > 0 since even though there are many rows with 0, there are many more without. From you statistics data, in fact, the coldist data shows that 0 is 6% of the rows, which means that >0 is 94% (filter factor .94). This column (DOC_ID in the actual table) has a cardinality of 32,000,000. col > ? has a filter factor based on the column cardinality. For 32,000,000 it would be only 1/3000 or .00033 (see the Managing Performance manual - chapter 29 for V11).

Joe

In Reply to Terry Purcell:

Hi Andre & Joe,

To Joe's questions - yes, there is lower cost to applying a predicate on a data row than as a screening predicate on an index entry. However, in this case the issue is that applying the predicate against the index entry would then result in fewer data rows/pages needing to be accessed.

Which leads to Andre's questions - DB2 will only apply one predicate on each column as matching (some exceptions including >= AND <= which are combined into one BETWEEN predicate) - and the others are applied "later". When is later? I haven't had to chance to investigate further - it will take some discovery which will take some time - and that is why I was hoping for a PMR so that support could reconstruct the testcase. I can add that to my to-do list........when? Not sure.

So how to resolve this now for Andre? Given the LEFT OUTER JOIN query you have, the reason this issue is being exposed is that we only have a predicate on the right side, and DB2 doesn't do predicate transitive closure for the ON clause predicate in your example - so you will need to code the same predicate onto T1. Once you do that - this issue should disappear because DB2 wont attempt the join from left to right if it determines that the ON clause is false before the join.

Select *
from t1 left Join t2
on t1.a = t2.a
and t2.a <> 0
and t1.a <> 0 where t1.b = ?

Test the above and report back if you are able. You have to be careful with adding/duplicating ON/WHERE clause predicates in outer joins to ensure you get the same result - but the above rewrite is correct given your query example.

Regards
Terry Purcell

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

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