z/OS V8 and V9 SQL Optimizer weirdness

Roy Boxwell

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

_____________________________________________________________________

* 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