Small & Large Lobs

Stanley Sinclair

Given I have binary input which is to be stored in one table, and it
may consist of any size -- ie, VARCHAR(n) FOR BIT DATA, WHERE THE n is
less than 32K, or the n is bigger such that it is a BLOB ...

Given that I have a LOB table too, named LOBTABLE,

Is there any problem with,

CREATE TABLE(
ID INTEGER NOT NULL,
DATA VARCHAR(31k) FOR BIT DATA,
LOB ADDRESS,
.....
LOB IN LOBTABLE
.....

Would there be a problem in storing either or both data types,
retrieving them, sorting them, etc.

Nov 12 '05 #1

Serge Rielau

Stanley Sinclair wrote:

Given I have binary input which is to be stored in one table, and it
may consist of any size -- ie, VARCHAR(n) FOR BIT DATA, WHERE THE n is
less than 32K, or the n is bigger such that it is a BLOB ...

Given that I have a LOB table too, named LOBTABLE,

Is there any problem with,

CREATE TABLE(
ID INTEGER NOT NULL,
DATA VARCHAR(31k) FOR BIT DATA,
LOB ADDRESS,
....
LOB IN LOBTABLE
....

Would there be a problem in storing either or both data types,
retrieving them, sorting them, etc.

This is a popular choice. You can also use COALESCE(DATA, LOB) (maybe in
a view) to make it transparent.

Note: No sorting LOBs, no comparison functions for these puppies
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #2

2803stan

Thanks, this looks good, but . . .

I'm worried about that COALESCE. To remind: this is a case where a
row will contain either a VARCHAR or a BLOB. Using a view with
COALESCE(VARCHAR, BLOB) . . . will a BLOB be searched for even if there
is a null BLOB address in the base table?

SS

Nov 12 '05 #3

Knut Stolze

28******@gmail.com wrote:

Thanks, this looks good, but . . .

I'm worried about that COALESCE. To remind: this is a case where a
row will contain either a VARCHAR or a BLOB. Using a view with
COALESCE(VARCHAR, BLOB) . . . will a BLOB be searched for even if there
is a null BLOB address in the base table?

I'm not 100% sure, but I think it will be read from disk, just consider this
example:

$ db2 "values coalesce(1, 1 / 0)"
SQL0801N Division by zero was attempted. SQLSTATE=22012

So you could change the coalesce to:

SELECT <varchar>
FROM <table>
WHERE <varchar> IS NOT NULL
UNION ALL
SELECT <blob>
FROM <table>
WHERE <varchar> IS NULL

The question is, however, how to align the data types. But you also have
the same issue with the VARCHAR and the BLOB in the COALESCE.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Nov 12 '05 #4

Serge Rielau

Knut Stolze wrote:

28******@gmail.com wrote:

Thanks, this looks good, but . . .

I'm worried about that COALESCE. To remind: this is a case where a
row will contain either a VARCHAR or a BLOB. Using a view with
COALESCE(VARCHAR, BLOB) . . . will a BLOB be searched for even if there
is a null BLOB address in the base table?

I'm not 100% sure, but I think it will be read from disk, just consider this
example:

$ db2 "values coalesce(1, 1 / 0)"
SQL0801N Division by zero was attempted. SQLSTATE=22012

So you could change the coalesce to:

SELECT <varchar>
FROM <table>
WHERE <varchar> IS NOT NULL
UNION ALL
SELECT <blob>
FROM <table>
WHERE <varchar> IS NULL

The question is, however, how to align the data types. But you also have
the same issue with the VARCHAR and the BLOB in the COALESCE.

Also keep in mind that NULL is stored in the row.
The LOB content is stored doutside of the row.
A NULL LOB doesn't even have a pointer to the out-of row space, so it
simply CANNOT be more expensive than going to teh row.
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #5