Oppolzer - Informatik / Blog


Blog-Hauptseite      Neuester Artikel      Älterer Artikel      Neuerer Artikel      Älterer gleiche Kategorie      Neuerer gleiche Kategorie

DB2-L - Probleme mit WHERE-Bedingung auf VARCHAR-Spalte

Subject:

Re: Where Clause on a VARCHAR Column

From:

Bernd Oppolzer <bernd.oppolzer@T-ONLINE.DE>

Reply-To:

DB2 List <db2-l@lists.idug.org>

Date:

2010.03.03 18:42:00


V.,

I would like to be informed - maybe off list -
how this problem gets solved in the end,
and what was the source of the problem.
Thank you.

Kind regards

Bernd



T.P. schrieb:
> V.,
>
> I suggest opening a PMR with IBM. DB2 development ran a quick test on
> current V8 maintenance level and cannot recreate your problem. Opening the
> PMR and sending the appropriate data will allow support to test your same
> maintenance level, your table definition, data etc.
>
> Regards
> T.
>
> On Tue, 2 Mar 2010 15:15:47 +0000, V.A. wrote:
>
>
>> Hi,
>>
>> We tried same query in one of the other subsystems we have and there it
>> returns the data irrespective of the access path chosen. This is the test
>> subsystem. May be we have more recent maintenance on test subsystems.
>>
>> Regards
>> V.
>>
>>
>>
>> On Tue, 2 Mar 2010 12:08:17 +0100, Bernd Oppolzer wrote:
>>
>>
>>> IMHO this could indeed be a problem in DB2, related to the access path,
>>> because AFAIK the VARCHAR columns are stored in the indexes in their
>>> maximum length, padded with blanks, and the length field is not accessible
>>> by simply accessing the index. So the index scan could involve other
>>> semantics on WHERE than the table space scan does (although I also
>>> would have assumed all rows to be returned in both cases). I would ask
>>> IBM on this.
>>>
>>> Kind regards
>>>
>>> Bernd
>>>
>>>
>>>



V.A.
Re: Where Clause on a VARCHAR Column
March 1, 2010 11:32 PM (in response to Suresh Sane)
Suresh,

The zparms are set to as follows:

RETVLCFK=NO
PADIX=YES

But what surprises me is , if I code my WHERE clause as WHERE COLUMN > ' ', then
it works great (a blank in between single quotes). The problems seems to be only
when it is null string - no blank in between single quotes ('') and the access
type is Table Space scan.

Regards
V.



V.A.
Where Clause on a VARCHAR Column
March 1, 2010 06:34 PM
Hello,

We have in one of the queries a where clause like WHERE COLUMN > '' (without a
blank in between the single quotes). This query when it uses index, it is
returning the data and when it uses Tablespace Scan it is not returning any data
(infact every row in the table should be satisfied).

did any of you experience this behaviour? we are on DB2 V8 under z/OS 1.10

Regards
V.

Blog-Hauptseite      Neuester Artikel      Älterer Artikel      Neuerer Artikel      Älterer gleiche Kategorie      Neuerer gleiche Kategorie