[DB2-L] Page forward query - non-matching IX scan

Walter Janißen

[DB2-L] Page forward query - non-matching IX scan
You can use DIGITS instead

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


-----Ursprüngliche Nachricht-----
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Joe Geller
Gesendet: Mittwoch, 10. Februar 2010 20:21
An: [login to unmask email]
Betreff: Re: [DB2-L] Page forward query - non-matching IX scan

This might work for char, decimal and date columns, but will definitely not work for integer. The CHAR function will left justify an integer with trailing spaces. The number 2 is smaller than the number 13, but '2' collates after '13', so you won't get the rows in the order you are expecting. Decimal columns will have leading 0's.

Joe







Not at v9 yet, otherwise I would have already tried this. Would an index on
expression: concatenated columns CHAR(BEN_CD)||CHAR(NTWRKID)
||CHAR(BEN_NTWRK_EFF_DT,ISO) and use this same expression in the
WHERE predicate be reasonable? WHERE CHAR(BEN_CD)||CHAR
(NTWRKID)||CHAR(BEN_NTWRK_EFF_DT,ISO) > :hv?


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** NEW - IBM hands-on labs -> no additional charge
_____________________________________________________________________

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