[DB2 V8 CM] NPGTHRSH

Walter Janißen

[DB2 V8 CM] NPGTHRSH
Hi

Probably a question for Terry. I have some problems in understanding the
effect of NPGTHRSH. In our installation this value is set to 10. But for
the following query, DB2 chooses a tablespace scan with additional sort:

SELECT SAMMEL_BUCH_REF, INS_LNR, UPD_NR, SPERR_KZ,
GRDBU_SATZ_ANZ, GRDBU_DT
FROM DB2.NBTB0033
ORDER BY SAMMEL_BUCH_REF, INS_LNR

The values for CARDF and NPAGESF are both 0.

If I change CARDF to 2, I get a non matching index scan without a sort as
desired. Why do I get a tablespace scan in the first case? My understaning
of NPGTHRSH is, if a table has less than NPGTHRSH (and 0 is certainly less)
then the index is choses otherwise the access path is cost-based. Or is 0 a
special case and I have to pay attention, if a runstats is executed against
this table and the table is unfortunately empty?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

[login to unmask email]

Re: [DB2 V8 CM] NPGTHRSH
(in response to Walter Janißen)
Hi ,Walter

It seems really strange. Here is my wild guess. Are you using data
sharing, and set different member with
different NPGTHRSH value? Then you bind/explain this SQL in a wrong
member, which may be possible.







Walter Janißen <[login to unmask email]>
???: DB2 Data Base Discussion List <[login to unmask email]>
2006-01-12 16:09
??? ? DB2 Database Discussion list at IDUG

???: [login to unmask email]
??:
??: [DB2-L] [DB2 V8 CM] NPGTHRSH


Hi

Probably a question for Terry. I have some problems in understanding the
effect of NPGTHRSH. In our installation this value is set to 10. But for
the following query, DB2 chooses a tablespace scan with additional sort:

SELECT SAMMEL_BUCH_REF, INS_LNR, UPD_NR, SPERR_KZ,
GRDBU_SATZ_ANZ, GRDBU_DT
FROM DB2.NBTB0033
ORDER BY SAMMEL_BUCH_REF, INS_LNR

The values for CARDF and NPAGESF are both 0.

If I change CARDF to 2, I get a non matching index scan without a sort as
desired. Why do I get a tablespace scan in the first case? My understaning
of NPGTHRSH is, if a table has less than NPGTHRSH (and 0 is certainly
less)
then the index is choses otherwise the access path is cost-based. Or is 0
a
special case and I have to pay attention, if a runstats is executed
against
this table and the table is unfortunately empty?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm


Walter Jani&#223;en

Re: [DB2 V8 CM] NPGTHRSH
(in response to hhuang@DCCSH.ICBC.COM.CN)
William

I did my explains all in the same member.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mae Bruce

Re: [DB2 V8 CM] NPGTHRSH
(in response to Walter Janißen)
With V8, there is a new feature called volatile tables, which will favor index access. According to the Admin guide:

Defining a table as volatile has a similar effect on a query to setting the NPGTHRSH subsystem parameter to favor matching index access for all qualified tables. (See Using a subsystem parameter for information on the settings for NPGTHRSH.) However, the effect of NPGTHRSH is subsystem-wide, and index access might not be appropriate for many queries. Defining tables as volatile lets you limit the set of queries that favor index access to queries that involve the volatile tables.

I don't know if this is available in CM. And altho this doesn't really answer your question, it is an option.

Mae Bruce

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Walter Janißen
Sent: Thursday, January 12, 2006 2:10 AM
To: [login to unmask email]
Subject: [DB2-L] [DB2 V8 CM] NPGTHRSH

Hi

Probably a question for Terry. I have some problems in understanding the effect of NPGTHRSH. In our installation this value is set to 10. But for the following query, DB2 chooses a tablespace scan with additional sort:

SELECT SAMMEL_BUCH_REF, INS_LNR, UPD_NR, SPERR_KZ, GRDBU_SATZ_ANZ, GRDBU_DT FROM DB2.NBTB0033 ORDER BY SAMMEL_BUCH_REF, INS_LNR

The values for CARDF and NPAGESF are both 0.

If I change CARDF to 2, I get a non matching index scan without a sort as desired. Why do I get a tablespace scan in the first case? My understaning of NPGTHRSH is, if a table has less than NPGTHRSH (and 0 is certainly less) then the index is choses otherwise the access path is cost-based. Or is 0 a special case and I have to pay attention, if a runstats is executed against this table and the table is unfortunately empty?


****************************
CONFIDENTIALITY STATEMENT:
This e-mail and any attachments are intended only for those to which it is addressed and may contain information which is privileged, confidential and prohibited from disclosure and unauthorized use under applicable law. If you are not the intended recipient of this e-mail, you are hereby notified that any use, dissemination, or copying of this e-mail or the information contained in this e-mail is strictly prohibited by the sender. If you have received this transmission in error, please return the material received to the sender and delete all copies from your system.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Patrick Bossman

Re: [DB2 V8 CM] NPGTHRSH
(in response to Mae Bruce)
Hi Walter,
NPGTHRSH establishes a preference for matching index access. Even if a
table meets the NPGTHRSH criteria, non-matching index scan and R-scan
compete on cost. NPGTHRSH drives optimizer to prefer matching index access
over R-scan and non-matching index scan.

http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/dsnagh15/5.8.8.9.1?SHELF=&DT=20050721100022&CASE=
n>=1
If data access statistics have been collected for all tables, DB2 favors
matching index access for tables for which the total number of pages on
which rows of the table appear (NPAGES) is less than n.

With regards to why optimizer would choose R-scan + sort over non-matching
index scan that avoids the sort for an empty table, I'd suggest a pmr to get
it looked at.

Best regards,
Pat Bossman

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Jani&#223;en

Re: [DB2 V8 CM] NPGTHRSH
(in response to Patrick Bossman)
On Thu, 12 Jan 2006 10:21:33 -0600, Bruce, Mae <[login to unmask email]> wrote:

Hi Mae

If you look into the archives, we have had some discussions about VOLATILE-
tables. And as Terry mentioned and I painfully detected, there are some
restrictions with VOLATILE:

- Update can lead to a tablespace scan, if you update some of the columns,
which are in the only index

- Delete without a WHERE-clause behaves like a delete in a non-segmented
tablespace

So this is not an option for that table.

>With V8, there is a new feature called volatile tables, which will favor
index access. According to the Admin guide:
>
>Defining a table as volatile has a similar effect on a query to setting
the NPGTHRSH subsystem parameter to favor matching index access for all
qualified tables. (See Using a subsystem parameter for information on the
settings for NPGTHRSH.) However, the effect of NPGTHRSH is subsystem-wide,
and index access might not be appropriate for many queries. Defining tables
as volatile lets you limit the set of queries that favor index access to
queries that involve the volatile tables.
>
>I don't know if this is available in CM. And altho this doesn't really
answer your question, it is an option.
>
>Mae Bruce
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Walter Janißen
>Sent: Thursday, January 12, 2006 2:10 AM
>To: [login to unmask email]
>Subject: [DB2-L] [DB2 V8 CM] NPGTHRSH
>
>Hi
>
>Probably a question for Terry. I have some problems in understanding the
effect of NPGTHRSH. In our installation this value is set to 10. But for
the following query, DB2 chooses a tablespace scan with additional sort:
>
>SELECT SAMMEL_BUCH_REF, INS_LNR, UPD_NR, SPERR_KZ, GRDBU_SATZ_ANZ,
GRDBU_DT FROM DB2.NBTB0033 ORDER BY SAMMEL_BUCH_REF, INS_LNR
>
>The values for CARDF and NPAGESF are both 0.
>
>If I change CARDF to 2, I get a non matching index scan without a sort as
desired. Why do I get a tablespace scan in the first case? My understaning
of NPGTHRSH is, if a table has less than NPGTHRSH (and 0 is certainly less)
then the index is choses otherwise the access path is cost-based. Or is 0 a
special case and I have to pay attention, if a runstats is executed against
this table and the table is unfortunately empty?
>
>
>****************************
CONFIDENTIALITY STATEMENT:
This e-mail and any attachments are intended only for those to which it is
addressed and may contain information which is privileged, confidential and
prohibited from disclosure and unauthorized use under applicable law. If
you are not the intended recipient of this e-mail, you are hereby notified
that any use, dissemination, or copying of this e-mail or the information
contained in this e-mail is strictly prohibited by the sender. If you have
received this transmission in error, please return the material received to
the sender and delete all copies from your system.
>
>---------------------------------------------------------------------------
------
>Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at DB2-L-
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

Hi

I

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm