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

Harry Garagoski

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


Terry gave me some usefull information.

The project made a requirement for the vendor. The vendor will solve
this problem. Hopefully with an UNION.

Thanks for your participation


Regards,

Harry Garagoski




Van:
Walter Janißen <[login to unmask email]>
Aan:
[login to unmask email]
Datum:
03-12-2009 12:22
Onderwerp:
[DB2-L] AW: [DB2-L] Betr: Re: [DB2-L] Is this access path correct?
Verzonden door:
IDUG DB2-L <[login to unmask email]>



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.



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

Norbert Wolf

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

of course the solution with UNION will be the end of the performance
problem. And if the vendor will change the SQL it will work fine. Really? I think
the solution must happen inside the DB2 optimizer, because this is a
common sql problem. The optimizer must be able to transform the SQL (
under the cover to a UNION solution) and then we can call him an intelligent
optimizer.

kind regards

Norbert Wolf

Datev eG

_____________________________________________________________________

* 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