[DB2 V8 z/OS NFM] complex join using VARCHAR and CHAR

Walter Janißen

[DB2 V8 z/OS NFM] complex join using VARCHAR and CHAR
Hi all

The new year begins as the old ended, with a mystery of the DB2 optimizer.
When I read through the manuals, I learned that in V8 CHAR-column compared
with VARCHAR-column is stage1 and indexable. If I use that comparison in a
join, it is indexable since V7. Now here is a piece of a somehow complex
query:

SELECT ...
CASE
WHEN (YEAR (B.BUCH_DAT) - B.GESCH_JAHR) < 0 THEN "FJ"
WHEN (YEAR (B.BUCH_DAT) - B.GESCH_JAHR) = 0 THEN "GJ"
WHEN (YEAR (B.BUCH_DAT) - B.GESCH_JAHR) = 1 THEN "V1"
WHEN (YEAR (B.BUCH_DAT) - B.GESCH_JAHR) = 2 THEN "V2"
WHEN (YEAR (B.BUCH_DAT) - B.GESCH_JAHR) = 3 THEN "V3"
WHEN (YEAR (B.BUCH_DAT) - B.GESCH_JAHR) >= 4 THEN "V4"
END GJ_SL, ...
) as C, INTB0331 D
WHERE C.GJ_SL = D.GJ_SL

This condition is not indexable, because the number of matching columns
stops here. But if I change the query as follows:

SELECT ...
SUBSTR(CASE
WHEN (YEAR (B.BUCH_DAT) - B.GESCH_JAHR) < 0 THEN "FJ"
WHEN (YEAR (B.BUCH_DAT) - B.GESCH_JAHR) = 0 THEN "GJ"
WHEN (YEAR (B.BUCH_DAT) - B.GESCH_JAHR) = 1 THEN "V1"
WHEN (YEAR (B.BUCH_DAT) - B.GESCH_JAHR) = 2 THEN "V2"
WHEN (YEAR (B.BUCH_DAT) - B.GESCH_JAHR) = 3 THEN "V3"
WHEN (YEAR (B.BUCH_DAT) - B.GESCH_JAHR) >= 4 THEN "V4"
END, 1, 2) GJ_SL, ...
) as C, INTB0331 D
WHERE C.GJ_SL = D.GJ_SL

I get a matching index access. The only thing that changed is the data type
of the column C.GJ_SL. Can anybody explain that?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm