Since DB2 can't index BLOB/CLOB, can it index CHAR(N) FOR BIT DATA,
especially for storing GUID primary keys.

(Sorry, I don't use DB2 actually).

Absolutely. Is it truly teh whole LOB you need to index?
One thing you may look into is a generated column.
.... lobheader VARCHAR(100) GENERATED ALWAYS AS (CAST(SUBSTR(clobcol, 1,
100))
then index the header and search by it.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Serge Rielau <srielau@ca.eye-be-em.com> wrote in message news:<c02sq4$hs6$1@hanover.torolab.ibm.com>...[color=blue]
> Absolutely. Is it truly teh whole LOB you need to index?
> One thing you may look into is a generated column.
> ... lobheader VARCHAR(100) GENERATED ALWAYS AS (CAST(SUBSTR(clobcol, 1,
> 100))
> then index the header and search by it.[/color]

Thanks, Serge. I never actually have the need to index (B-tree) a very
long string (hundreds to thousands of bytes). But in this case I want
to index CHAR(16) FOR BIT DATA to store 128-bit GUID. I don't want to
use CHAR and store GUID in base64/hex encoding because that would
waste some space (22-32 bytes instead of just 16).

In Postgres I can already index BYTEA and in SQL Server I can index on
BINARY/VARBINARY.

--
Pascal

No problem then. (VAR)CHAR FOR BIT DATA will do what you need.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab