Betr: Re: [DB2-L] Is this access path correct?

Harry Garagoski

Betr: Re: [DB2-L] Is this access path correct?
Hello Scott,

Sorry no difference. Same access path


Regards,

Harry Garagoski






Van:
"Hodgin, Scott" <[login to unmask email]>
Aan:
[login to unmask email]
Datum:
02-12-2009 14:49
Onderwerp:
Re: [DB2-L] Is this access path correct?
Verzonden door:
IDUG DB2-L <[login to unmask email]>



Out of curiosity, what does the plan say when you do this?

SELECT DISTINCT COLA, COLB , COLC, COLD, COLE
FROM CM_BK
WHERE
COLF iN
(?,?)
OR
COLG IN
(?,?,?,?,?,?,?,?)

Sometimes if you have hardcodes values, you give DB2 _too much_
information


Scott Hodgin
Database Administrator, MCITP
South Carolina Farm Bureau Insurance Company
[login to unmask email]


From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Harry Garagoski
Sent: Wednesday, December 02, 2009 8:10 AM
To: [login to unmask email]
Subject: [DB2-L] Is this access path correct?


We have a query like this on a 300.000 row table and there is a index
on COLF (unique) and on COLG (duplicate (not much))

SELECT DISTINCT COLA, COLB , COLC, COLD, COLE
FROM CM_BK
WHERE
COLF iN
('1111410007081000','1111410007081000')
OR
COLG IN
('111410009H8','111410009H81',
'111410009H0','111410009H08','111410009H081',
'111410009H0810','111410009H08100','111410009H081000')



According to the documentation : If 2 indexable predicates are combined
with an OR, the result is indexable.
All the statistics are run, but the query's acces type = R with sort
(Distinct). So it takes to long to run.


QB_PN_O M_AT_MC_I TB_IX F_UJOG_UJOG TLM_P_QBLOCK
BINDTIME
---------+---------+---------+---------+---------+---------+---------+---------

01 01 0 0 R 00 N CM_BK S NNNN NNNN IS SELECT
0912011622
01 02 0 3 00 N NNNN YNNN SELECT
0912011622

Has any one an idea?


Regards,

Harry Garagoski



------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.




The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.



------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Isaac Yassin

Re: Betr: Re: [DB2-L] Is this access path correct?
(in response to Harry Garagoski)
Hi

Another index on COLG ?



Isaac Yassin

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Harry Garagoski
Sent: Wednesday, December 02, 2009 4:13 PM
To: [login to unmask email]
Subject: [DB2-L] Betr: Re: [DB2-L] Is this access path correct?




Hello Scott,

Sorry no difference. Same access path


Regards,

Harry Garagoski







Van:

"Hodgin, Scott" <[login to unmask email]>


Aan:

[login to unmask email]


Datum:

02-12-2009 14:49


Onderwerp:

Re: [DB2-L] Is this access path correct?


Verzonden door:

IDUG DB2-L <[login to unmask email]>



_____




Out of curiosity, what does the plan say when you do this?

SELECT DISTINCT COLA, COLB , COLC, COLD, COLE
FROM CM_BK
WHERE
COLF iN
(?,?)
OR
COLG IN
(?,?,?,?,?,?,?,?)

Sometimes if you have hardcodes values, you give DB2 _too much_ information


Scott Hodgin
Database Administrator, MCITP
South Carolina Farm Bureau Insurance Company
<mailto:[login to unmask email]> [login to unmask email]




_____


From: IDUG DB2-L [ <mailto:[login to unmask email]> mailto:[login to unmask email] On Behalf Of Harry Garagoski
Sent: Wednesday, December 02, 2009 8:10 AM
To: [login to unmask email]
Subject: [DB2-L] Is this access path correct?


We have a query like this on a 300.000 row table and there is a index on COLF (unique) and on COLG (duplicate (not much))

SELECT DISTINCT COLA, COLB , COLC, COLD, COLE
FROM CM_BK
WHERE
COLF iN
('1111410007081000','1111410007081000')
OR
COLG IN
('111410009H8','111410009H81',
'111410009H0','111410009H08','111410009H081',
'111410009H0810','111410009H08100','111410009H081000')



According to the documentation : If 2 indexable predicates are combined with an OR, the result is indexable.
All the statistics are run, but the query's acces type = R with sort (Distinct). So it takes to long to run.


QB_PN_O M_AT_MC_I TB_IX F_UJOG_UJOG TLM_P_QBLOCK BINDTIME
---------+---------+---------+---------+---------+---------+---------+---------
01 01 0 0 R 00 N CM_BK S NNNN NNNN IS SELECT 0912011622
01 02 0 3 00 N NNNN YNNN SELECT 0912011622

Has any one an idea?


Regards,

Harry Garagoski






_____

< http://www.idug.org/db2-north-america-conference/index.html > IDUG - The Worldwide DB2 User Community!

< http://www.idug.org/db2-north-america-conference/index.html > IDUG - The Worldwide DB2 User Community!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please < http://www.idug.org/register > register here.


_____

I am using the Free version of SPAMfighter < http://www.spamfighter.com/len > .
We are a community of 6 million users fighting spam.
SPAMfighter has removed 4596 of my spam emails to date.
The Professional version does not have this message.


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L
Attachments

  • import1 (14.4k)

Harry Garagoski

Betr: Re: [DB2-L] Is this access path correct?
(in response to Isaac Yassin)
Hello Roy,


COLG = VARCHAR and
we are running V9




Index for COLF :



Sel Column Name Seq No O Col Type Length Scale Null Def FP Col
Card
* * * * * * * * * *
--- ------------------ ------ - -------- ------ ------ ---- --- --
-----------
COLF 1 A CHAR 16 0 N N N 290607



Details for index : ABC

On table : CM_BK
Index space ID (ISOBID) : 64 Index type : Type 2
index
Index subpage size(bytes): 4 Max. ds piece size (KB) : 2097152

Number of columns in key : 1 Clustering ratio : 100
Is it a clustering index : Yes Table clustered by index: Yes
Unique rule : Primary index
Close rule for data set : Y - The data sets are closed after use
Erase rule for data set : N - Data set is NOT erased when dropped
Can the index be copied : No Created TS:
2009-06-19-07.34.32.186212
Oldest data version : 0 Altered TS:
2009-06-19-07.34.32.186212
Current data version : 0 Page size : 4K
Created in DB2 Version : M - DB2 V9 Index keys padded : N/A
Key target count : 0 Unique count : 0
Extended index type : Simple index
Index compression active : No
Oldest data version : 0 Altered TS:
2009-06-19-07.34.32.186212
Current data version : 0 Page size : 4K
Created in DB2 Version : M - DB2 V9 Index keys padded : N/A
Key target count : 0 Unique count : 0
Extended index type : Simple index
Index compression active : No

Statistical data : RUNSTATS timestamp: 2009-12-02-16.38.17.274174
Allocated space (KB) : 10224 (Float: 1.022400000000000E+04)
First key column count : 290607 (Float: 2.906070000000000E+05)
Number of distinct keys : 290607 (Float: 2.906070000000000E+05)
Number of leaf pages : 2169
Number of index levels : 3
Average key length : 16
Data repeat factor : 8.542000000000000E+03


Index for COLG :

Sel Column Name Seq No O Col Type Length Scale Null Def FP Col
Card
* * * * * * * * * *
--- ------------------ ------ - -------- ------ ------ ---- --- --
-----------
CM_STR_VORD_ID 1 A VARCHAR 25 0 N 1 N 290607




Details for index : XYZ On table : CM_BK

Index space name : CM123TMP Buffer pool used : BP2
Index space ID (ISOBID) : 172 Index type : Type 2
index
Index subpage size(bytes): 4 Max. ds piece size (KB) : 2097152

Number of columns in key : 1 Clustering ratio : 0: No
stats
Is it a clustering index : No Table clustered by index: No
Unique rule : Non unique - duplicates allowed
Close rule for data set : Y - The data sets are closed after use
Erase rule for data set : N - Data set is NOT erased when dropped
Can the index be copied : No Created TS:
2009-12-01-15.40.51.571636
Oldest data version : 0 Altered TS:
2009-12-01-15.40.51.571636
Current data version : 0 Page size : 4K
Created in DB2 Version : M - DB2 V9 Index keys padded : Padded

Key target count : 0 Unique count : 0
Extended index type : Simple index
Index compression active : No
Oldest data version : 0 Altered TS:
2009-12-01-15.40.51.571636
Current data version : 0 Page size : 4K
Created in DB2 Version : M - DB2 V9 Index keys padded : Padded

Key target count : 0 Unique count : 0
Extended index type : Simple index
Index compression active : No

Statistical data : RUNSTATS timestamp: 2009-12-02-16.38.17.274174
Allocated space (KB) : 19584 (Float: 1.958400000000000E+04)
First key column count : 290607 (Float: 2.906070000000000E+05)
Number of distinct keys : 290607 (Float: 2.906070000000000E+05)
Number of leaf pages : 3159
Number of index levels : 3
Average key length : 25
Data repeat factor : 2.898030000000000E+05




Regards,

Harry Garagoski





Van:
Roy Boxwell <[login to unmask email]>
Aan:
[login to unmask email]
Datum:
02-12-2009 15:28
Onderwerp:
Re: [DB2-L] Is this access path correct?
Verzonden door:
IDUG DB2-L <[login to unmask email]>




are colg or even the colf VARCHAR by any chance?? and remember STATS STATS
STATS and STATS

ps What version of DB2 are you running?



Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

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



Harry Garagoski <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
02.12.2009 14:10

Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
[DB2-L] Is this access path correct?









We have a query like this on a 300.000 row table and there is a index
on COLF (unique) and on COLG (duplicate (not much))

SELECT DISTINCT COLA, COLB , COLC, COLD, COLE
FROM CM_BK
WHERE
COLF iN
('1111410007081000','1111410007081000')
OR
COLG IN
('111410009H8','111410009H81',
'111410009H0','111410009H08','111410009H081',
'111410009H0810','111410009H08100','111410009H081000')



According to the documentation : If 2 indexable predicates are combined
with an OR, the result is indexable.
All the statistics are run, but the query's acces type = R with sort
(Distinct). So it takes to long to run.


QB_PN_O M_AT_MC_I TB_IX F_UJOG_UJOG TLM_P_QBLOCK
BINDTIME
---------+---------+---------+---------+---------+---------+---------+---------

01 01 0 0 R 00 N CM_BK S NNNN NNNN IS SELECT
0912011622
01 02 0 3 00 N NNNN YNNN SELECT
0912011622

Has any one an idea?


Regards,

Harry Garagoski




------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.





The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.



------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Harry Garagoski

Betr: Re: [DB2-L] Is this access path correct?
(in response to Harry Garagoski)
No, Its the product works.

If I split the query in separate predicates both use the index but when
you join the predicates
to one query with an OR it changes to a TS-scan




Regards,

Harry Garagoski





Van:
Balachandran Chandrasekaran1 <[login to unmask email]>
Aan:
[login to unmask email]
Datum:
02-12-2009 15:35
Onderwerp:
Re: [DB2-L] Is this access path correct?
Verzonden door:
IDUG DB2-L <[login to unmask email]>




I just noticed this.

COLF iN
('1111410007081000','1111410007081000')

Any reason why the same value is mentioned twice ?




Harry Garagoski <[login to unmask email]>
Sent by: IDUG DB2-L <[login to unmask email]>
02/12/2009 18:40

Please respond to
IDUG DB2-L <[login to unmask email]>


To
[login to unmask email]
cc

Subject
[DB2-L] Is this access path correct?









We have a query like this on a 300.000 row table and there is a index
on COLF (unique) and on COLG (duplicate (not much))

SELECT DISTINCT COLA, COLB , COLC, COLD, COLE
FROM CM_BK
WHERE
COLF iN
('1111410007081000','1111410007081000')
OR
COLG IN
('111410009H8','111410009H81',
'111410009H0','111410009H08','111410009H081',
'111410009H0810','111410009H08100','111410009H081000')



According to the documentation : If 2 indexable predicates are combined
with an OR, the result is indexable.
All the statistics are run, but the query's acces type = R with sort
(Distinct). So it takes to long to run.


QB_PN_O M_AT_MC_I TB_IX F_UJOG_UJOG TLM_P_QBLOCK
BINDTIME
---------+---------+---------+---------+---------+---------+---------+---------

01 01 0 0 R 00 N CM_BK S NNNN NNNN IS SELECT
0912011622
01 02 0 3 00 N NNNN YNNN SELECT
0912011622

Has any one an idea?


Regards,

Harry Garagoski




------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.





The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.



------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Harry Garagoski

Betr: Re: [DB2-L] Is this access path correct?
(in response to Harry Garagoski)
Index for COLF :



Sel Column Name Seq No O Col Type Length Scale Null Def FP Col
Card
* * * * * * * * * *
--- ------------------ ------ - -------- ------ ------ ---- --- --
-----------
COLF 1 A CHAR 16 0 N N N 290607



Details for index : ABC

On table : CM_BK
Index space ID (ISOBID) : 64 Index type : Type 2
index
Index subpage size(bytes): 4 Max. ds piece size (KB) : 2097152

Number of columns in key : 1 Clustering ratio : 100
Is it a clustering index : Yes Table clustered by index: Yes
Unique rule : Primary index
Close rule for data set : Y - The data sets are closed after use
Erase rule for data set : N - Data set is NOT erased when dropped
Can the index be copied : No Created TS:
2009-06-19-07.34.32.186212
Oldest data version : 0 Altered TS:
2009-06-19-07.34.32.186212
Current data version : 0 Page size : 4K
Created in DB2 Version : M - DB2 V9 Index keys padded : N/A
Key target count : 0 Unique count : 0
Extended index type : Simple index
Index compression active : No
Oldest data version : 0 Altered TS:
2009-06-19-07.34.32.186212
Current data version : 0 Page size : 4K
Created in DB2 Version : M - DB2 V9 Index keys padded : N/A
Key target count : 0 Unique count : 0
Extended index type : Simple index
Index compression active : No

Statistical data : RUNSTATS timestamp: 2009-12-02-16.38.17.274174
Allocated space (KB) : 10224 (Float: 1.022400000000000E+04)
First key column count : 290607 (Float: 2.906070000000000E+05)
Number of distinct keys : 290607 (Float: 2.906070000000000E+05)
Number of leaf pages : 2169
Number of index levels : 3
Average key length : 16
Data repeat factor : 8.542000000000000E+03


Index for COLG :

Sel Column Name Seq No O Col Type Length Scale Null Def FP Col
Card
* * * * * * * * * *
--- ------------------ ------ - -------- ------ ------ ---- --- --
-----------
CM_STR_VORD_ID 1 A VARCHAR 25 0 N 1 N 290607




Details for index : XYZ On table : CM_BK

Index space name : CM123TMP Buffer pool used : BP2
Index space ID (ISOBID) : 172 Index type : Type 2
index
Index subpage size(bytes): 4 Max. ds piece size (KB) : 2097152

Number of columns in key : 1 Clustering ratio : 0: No
stats
Is it a clustering index : No Table clustered by index: No
Unique rule : Non unique - duplicates allowed
Close rule for data set : Y - The data sets are closed after use
Erase rule for data set : N - Data set is NOT erased when dropped
Can the index be copied : No Created TS:
2009-12-01-15.40.51.571636
Oldest data version : 0 Altered TS:
2009-12-01-15.40.51.571636
Current data version : 0 Page size : 4K
Created in DB2 Version : M - DB2 V9 Index keys padded : Padded

Key target count : 0 Unique count : 0
Extended index type : Simple index
Index compression active : No
Oldest data version : 0 Altered TS:
2009-12-01-15.40.51.571636
Current data version : 0 Page size : 4K
Created in DB2 Version : M - DB2 V9 Index keys padded : Padded

Key target count : 0 Unique count : 0
Extended index type : Simple index
Index compression active : No

Statistical data : RUNSTATS timestamp: 2009-12-02-16.38.17.274174
Allocated space (KB) : 19584 (Float: 1.958400000000000E+04)
First key column count : 290607 (Float: 2.906070000000000E+05)
Number of distinct keys : 290607 (Float: 2.906070000000000E+05)
Number of leaf pages : 3159
Number of index levels : 3
Average key length : 25
Data repeat factor : 2.898030000000000E+05




Regards,

Harry Garagoski





Van:
Balachandran Chandrasekaran1 <[login to unmask email]>
Aan:
[login to unmask email]
Datum:
02-12-2009 15:02
Onderwerp:
Re: [DB2-L] Is this access path correct?
Verzonden door:
IDUG DB2-L <[login to unmask email]>




Hi Harry,

also, give us the cardinality, frequency information of these indexes and
the columns... from SYSIBM.SYSCOLDIST (If collected), SYSIBM.SYSCOLUMNS
for these two indexes..

Thanks
Balachandran Chandrasekaran



Harry Garagoski <[login to unmask email]>
Sent by: IDUG DB2-L <[login to unmask email]>
02/12/2009 18:40

Please respond to
IDUG DB2-L <[login to unmask email]>


To
[login to unmask email]
cc

Subject
[DB2-L] Is this access path correct?









We have a query like this on a 300.000 row table and there is a index
on COLF (unique) and on COLG (duplicate (not much))

SELECT DISTINCT COLA, COLB , COLC, COLD, COLE
FROM CM_BK
WHERE
COLF iN
('1111410007081000','1111410007081000')
OR
COLG IN
('111410009H8','111410009H81',
'111410009H0','111410009H08','111410009H081',
'111410009H0810','111410009H08100','111410009H081000')



According to the documentation : If 2 indexable predicates are combined
with an OR, the result is indexable.
All the statistics are run, but the query's acces type = R with sort
(Distinct). So it takes to long to run.


QB_PN_O M_AT_MC_I TB_IX F_UJOG_UJOG TLM_P_QBLOCK
BINDTIME
---------+---------+---------+---------+---------+---------+---------+---------

01 01 0 0 R 00 N CM_BK S NNNN NNNN IS SELECT
0912011622
01 02 0 3 00 N NNNN YNNN SELECT
0912011622

Has any one an idea?


Regards,

Harry Garagoski




------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.





The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.



------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Roy Boxwell

Re: Betr: Re: [DB2-L] Is this access path correct?
(in response to Harry Garagoski)
varchar and PADDED is not a good mix in this case. I would try the
following steps

Create a new COLG index as NOT PADDED, Runstat it with FREQVAL and EXPLAIN
the query again



Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

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




Harry Garagoski <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
03.12.2009 08:32
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
[DB2-L] Betr: Re: [DB2-L] Is this access path correct?







Hello Roy,


COLG = VARCHAR and
we are running V9




Index for COLF :



Sel Column Name Seq No O Col Type Length Scale Null Def FP Col
Card
* * * * * * * * * *
--- ------------------ ------ - -------- ------ ------ ---- --- --
-----------
COLF 1 A CHAR 16 0 N N N 290607



Details for index : ABC

On table : CM_BK
Index space ID (ISOBID) : 64 Index type : Type 2
index
Index subpage size(bytes): 4 Max. ds piece size (KB) : 2097152

Number of columns in key : 1 Clustering ratio : 100
Is it a clustering index : Yes Table clustered by index: Yes
Unique rule : Primary index
Close rule for data set : Y - The data sets are closed after use
Erase rule for data set : N - Data set is NOT erased when dropped
Can the index be copied : No Created TS:
2009-06-19-07.34.32.186212
Oldest data version : 0 Altered TS:
2009-06-19-07.34.32.186212
Current data version : 0 Page size : 4K
Created in DB2 Version : M - DB2 V9 Index keys padded : N/A
Key target count : 0 Unique count : 0

Extended index type : Simple index
Index compression active : No
Oldest data version : 0 Altered TS:
2009-06-19-07.34.32.186212
Current data version : 0 Page size : 4K
Created in DB2 Version : M - DB2 V9 Index keys padded : N/A

Key target count : 0 Unique count : 0
Extended index type : Simple index
Index compression active : No

Statistical data : RUNSTATS timestamp: 2009-12-02-16.38.17.274174
Allocated space (KB) : 10224 (Float: 1.022400000000000E+04)
First key column count : 290607 (Float: 2.906070000000000E+05)
Number of distinct keys : 290607 (Float: 2.906070000000000E+05)
Number of leaf pages : 2169
Number of index levels : 3
Average key length : 16
Data repeat factor : 8.542000000000000E+03


Index for COLG :

Sel Column Name Seq No O Col Type Length Scale Null Def FP Col
Card
* * * * * * * * * *
--- ------------------ ------ - -------- ------ ------ ---- --- --
-----------
CM_STR_VORD_ID 1 A VARCHAR 25 0 N 1 N 290607




Details for index : XYZ On table : CM_BK

Index space name : CM123TMP Buffer pool used : BP2

Index space ID (ISOBID) : 172 Index type : Type 2
index
Index subpage size(bytes): 4 Max. ds piece size (KB) : 2097152

Number of columns in key : 1 Clustering ratio : 0: No
stats
Is it a clustering index : No Table clustered by index: No
Unique rule : Non unique - duplicates allowed
Close rule for data set : Y - The data sets are closed after use
Erase rule for data set : N - Data set is NOT erased when dropped
Can the index be copied : No Created TS:
2009-12-01-15.40.51.571636
Oldest data version : 0 Altered TS:
2009-12-01-15.40.51.571636
Current data version : 0 Page size : 4K
Created in DB2 Version : M - DB2 V9 Index keys padded : Padded

Key target count : 0 Unique count : 0
Extended index type : Simple index
Index compression active : No
Oldest data version : 0 Altered TS:
2009-12-01-15.40.51.571636
Current data version : 0 Page size : 4K
Created in DB2 Version : M - DB2 V9 Index keys padded : Padded

Key target count : 0 Unique count : 0
Extended index type : Simple index
Index compression active : No

Statistical data : RUNSTATS timestamp: 2009-12-02-16.38.17.274174
Allocated space (KB) : 19584 (Float: 1.958400000000000E+04)
First key column count : 290607 (Float: 2.906070000000000E+05)
Number of distinct keys : 290607 (Float: 2.906070000000000E+05)
Number of leaf pages : 3159
Number of index levels : 3
Average key length : 25
Data repeat factor : 2.898030000000000E+05




Regards,

Harry Garagoski




Van:
Roy Boxwell <[login to unmask email]>
Aan:
[login to unmask email]
Datum:
02-12-2009 15:28
Onderwerp:
Re: [DB2-L] Is this access path correct?
Verzonden door:
IDUG DB2-L <[login to unmask email]>





are colg or even the colf VARCHAR by any chance?? and remember STATS STATS
STATS and STATS

ps What version of DB2 are you running?



Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

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


Harry Garagoski <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
02.12.2009 14:10

Bitte antworten an
IDUG DB2-L <[login to unmask email]>



An
[login to unmask email]
Kopie

Thema
[DB2-L] Is this access path correct?











We have a query like this on a 300.000 row table and there is a index
on COLF (unique) and on COLG (duplicate (not much))

SELECT DISTINCT COLA, COLB , COLC, COLD, COLE
FROM CM_BK
WHERE
COLF iN
('1111410007081000','1111410007081000')
OR
COLG IN
('111410009H8','111410009H81',
'111410009H0','111410009H08','111410009H081',
'111410009H0810','111410009H08100','111410009H081000')



According to the documentation : If 2 indexable predicates are combined
with an OR, the result is indexable.
All the statistics are run, but the query's acces type = R with sort
(Distinct). So it takes to long to run.


QB_PN_O M_AT_MC_I TB_IX F_UJOG_UJOG TLM_P_QBLOCK
BINDTIME
---------+---------+---------+---------+---------+---------+---------+---------

01 01 0 0 R 00 N CM_BK S NNNN NNNN IS SELECT
0912011622
01 02 0 3 00 N NNNN YNNN SELECT
0912011622

Has any one an idea?


Regards,

Harry Garagoski




------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.





The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.


------------------------------------------------------------------------
De Belastingdienst gebruikt e-mail niet voor de uitwisseling van
vertrouwelijke informatie met de burger of voor de bekendmaking
van beslissingen. De Belastingdienst hanteert conventies voor het
gebruik van e-mail. Deze zijn te vinden op www.belastingdienst.nl.





The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L