By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,683 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

Small & Large Lobs

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.