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

Harry Garagoski

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

I tried this already but the result was also negative.

I had a answer of Terry Purcell that i is simple not possible.

This will result in a requirement toe the vendor.



regards,

Harry Garagoski



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




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.



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: AW: [DB2-L] Betr: Re: [DB2-L] Is this access path correct?
(in response to Harry Garagoski)
I just hope its not SAP ...... :)



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




Walter Janißen <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
03.12.2009 12:20
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

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






Hi

I think, you can create as much indexes as you want, you won't get a
matching index access for this query (as Terry already pointed out). May
be you can get a non-matching index-only access.

To get a better access path, you have to rewrite that query as I already
suggested, but you said, you can't, because it is a generated query. So
make the vendor change their tool, to generate better queries or get rid
of that tool.

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40477 Düsseldorf
Tel.: +49 211 477-2928
Fax: +49 211 477-2615
mailto:[login to unmask email]

Vorsitzender des Aufsichtsrates: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.

Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996


Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Roy Boxwell
Gesendet: Donnerstag, 3. Dezember 2009 10:49
An: [login to unmask email]
Betreff: Re: [DB2-L] Betr: Re: [DB2-L] Is this access path correct?


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.



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.

_____________________________________________________________________

* 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] Betr: Re: [DB2-L] Is this access path correct?
(in response to Roy Boxwell)
good luck!



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

_____________________________________________________________________

* 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