[DB2-L] z/OS V8 and V9 SQL Optimizer weirdness

Walter Janißen

[DB2-L] z/OS V8 and V9 SQL Optimizer weirdness
Hi Roy

I was surprized, that the query with the RTRIM-function and LIKE returned a row at all. But, if you use OSC, you see, that DB2 indeed removes the RTRIM-function.It looks like:

SYSIBM.SYSTABLESPACE.DBNAME LIKE 'DSNDB06'

But, if that woulf be the real predicate, no row should qualify

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40477 Düsseldorf
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: Montag, 7. Dezember 2009 09:41
An: [login to unmask email]
Betreff: [DB2-L] z/OS V8 and V9 SQL Optimizer weirdness


Hi all!

OK, ok ,I know the SQL below is not 100% politically correct but please try it at your site and check the EXPLAIN output...

SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;
SELECT *
FROM SYSIBM.SYSTABLESPACE
WHERE RTRIM(DBNAME) = 'DSNDB06'
AND RTRIM(NAME) = 'SYSDDF';
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;
SELECT *
FROM SYSIBM.SYSTABLESPACE
WHERE RTRIM(DBNAME) LIKE 'DSNDB06'
AND RTRIM(NAME) LIKE 'SYSDDF';
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;



What I have found is that the rule "= is better than LIKE" does not seem to work in this case. The first query is always a tablespace scan and the second wacko sql is using the index correctly albeit just the first column. If you look in the predicate table what you see is even odder as it looks to me as if the RTRIM is being *removed* from the SQL LIKE code before execution and is therefore no longer STAGE2.

Has anyone else hit this problem or can someone say "Silly Roy, Apply PK65472, rebind the plan and all is well" by the way I ran this with no COLDIST stats and with all possible COLDIST stats and no change in access path occurred. You can even change it to be SYSPKAGE and it is the same.... of course adding a % to the end of the literals changes nothing either...

any ideas?!?!??!?!



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

________________________________

[ http://www.idug.org/images/M_images/idug%20org.jpg ] < http://www.idug.org >

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

_____________________________________________________________________

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

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

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] z/OS V8 and V9 SQL Optimizer weirdness
(in response to Walter Janißen)
odd is the word!



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]>
07.12.2009 10:33
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
[DB2-L] AW: [DB2-L] z/OS V8 and V9 SQL Optimizer weirdness






Hi Roy

I was surprized, that the query with the RTRIM-function and LIKE returned
a row at all. But, if you use OSC, you see, that DB2 indeed removes the
RTRIM-function.It looks like:

SYSIBM.SYSTABLESPACE.DBNAME LIKE 'DSNDB06'

But, if that woulf be the real predicate, no row should qualify
Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40477 Düsseldorf
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: Montag, 7. Dezember 2009 09:41
An: [login to unmask email]
Betreff: [DB2-L] z/OS V8 and V9 SQL Optimizer weirdness


Hi all!

OK, ok ,I know the SQL below is not 100% politically correct but please
try it at your site and check the EXPLAIN output...

SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;
SELECT *
FROM SYSIBM.SYSTABLESPACE
WHERE RTRIM(DBNAME) = 'DSNDB06'
AND RTRIM(NAME) = 'SYSDDF';
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;
SELECT *
FROM SYSIBM.SYSTABLESPACE
WHERE RTRIM(DBNAME) LIKE 'DSNDB06'
AND RTRIM(NAME) LIKE 'SYSDDF';
SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1;



What I have found is that the rule "= is better than LIKE" does not seem
to work in this case. The first query is always a tablespace scan and the
second wacko sql is using the index correctly albeit just the first
column. If you look in the predicate table what you see is even odder as
it looks to me as if the RTRIM is being *removed* from the SQL LIKE code
before execution and is therefore no longer STAGE2.

Has anyone else hit this problem or can someone say "Silly Roy, Apply
PK65472, rebind the plan and all is well" by the way I ran this with no
COLDIST stats and with all possible COLDIST stats and no change in access
path occurred. You can even change it to be SYSPKAGE and it is the
same.... of course adding a % to the end of the literals changes nothing
either...

any ideas?!?!??!?!



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



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-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

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

Peter Vanroose

Re: z/OS V8 and V9 SQL Optimizer weirdness
(in response to Roy Boxwell)
On Mon, 7 Dec 2009 10:33:32 +0100, Walter Janißen
<[login to unmask email]> wrote:
I was surprized, that the query with the RTRIM-function and LIKE
returned a row at all.

This should not surprise: since version 8, the column DBNAME changed from
CHAR(8) to VARCHAR(24), hence no blanks are *needed* anymore at the end of
the DB name.

Actually, from a v8 point of view, it would rather be surprising when the
following query would return a row at all:
SELECT * FROM SYSIBM.SYSTABLESPACE
WHERE DBNAME LIKE '% ' -- i.e.: dbname ending in space

And indeed, this query only returns tablespaces created in V8CM or before,
as expected.
Notably, it returns DSNDB06.SYSEBCDC and DSNDB06.SYSALTER as the two most
recently created ones (created during v8 CM migration).

Possible new tablespaces in DSNDB06 (none in v8, but e.g. SYSRTSTS in v9)
have no blank at the end of their DBNAME.
The same applies to tables, by the way, so the following will a.o. return
catalog views created since V8:
SELECT CREATOR||'.'||NAME, TYPE
FROM SYSIBM.SYSTABLES
WHERE DBNAME LIKE 'DSNDB06'


-- Peter Vanroose
ABIS Training & Consulting.

_____________________________________________________________________

* 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