469,287 Members | 2,731 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,287 developers. It's quick & easy.

Small & Large Lobs

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
4 1724
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
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
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
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

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Janne Ruuttunen | last post: by
9 posts views Thread by Remove the obvious for replies | last post: by
9 posts views Thread by Rom Marshall | last post: by
10 posts views Thread by Alex Greem | last post: by
1 post views Thread by stoat2337 | last post: by
5 posts views Thread by Louis LeBlanc | last post: by
4 posts views Thread by =?Utf-8?B?VzFsZDBuZTc0?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.