Oppolzer - Informatik / Blog

I recently had the same problem, when some of my co-workers migrated a database
from DB2 on z/OS to a DB2 on Linux. The DB2 on Linux was configured in a way
that the ordering of the characters didn't respect the lower/upper case, so the
results of the SELECTs were different.

The key is: BETWEEN and ORDER BY does not work on the pure hex values on DB2
UDB, but the char strings are converted to another representation, first. This
conversion can be configured - I don't recall the details.

Because I couldn't change the configuration parms of the subsystem on Linux, I
ended up with converting all the SQLs (which were generated by a single C
program, luckily) so, that the ordering is done on the base of the hex values.

So I got the same results as on z/OS, in the end.

Kind regards

Bernd



Am 22.02.2012 03:26, schrieb P.S.:
>
> Hello List,
>
> I have a DB2 UDB V9.7 database with an SQL that is returning
> unexpected results.
>
> The following demonstrates the behaviour:
>
> select * from sysibm.sysdummy1 where 'A' between 'a' and 'b'
>
> IBMREQD
>
> -------
>
> Y
>
> 1 record(s) selected.
>
> select * from sysibm.sysdummy1 where 'A' between 'a' and 'a'
>
> IBMREQD
>
> -------
>
> 0 record(s) selected.
>
> select * from sysibm.sysdummy1 where 'A' = 'a'
>
> IBMREQD
>
> -------
>
> 0 record(s) selected.
>
> select hex('A') as HEX_A, hex('a') as "HEX_a", hex('b') as "HEX_b"
> from sysibm.sysdummy1 where 'A' between 'a' and 'b'
>
> HEX_A HEX_a HEX_b
>
> ----- ----- -----
>
> 41 61 62
>
> 1 record(s) selected.
>
> Extract from get DB CFG.
>
> Database configuration release level = 0x0d00
>
> Database release level = 0x0d00
>
> Database territory = AU
>
> Database code page = 1252
>
> Database code set = 1252
>
> Database country/region code = 61
>
> Database collating sequence = UNIQUE
>
> Alternate collating sequence (ALT_COLLATE) =
>
> Has anyone run into this and what did you do to fix it.
>
> Regards,
>
> P.S.
>