By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,702 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.

SLOB in Table

P: n/a
[Win2003 server with XP clients. DB2 8.2 with updates added
continuously. VB6, latest update. OLE. IBM's ODBC.]

I have a table which saves doctors' notes about patients. The format
of the material is rich text format +/- embedded graphic(s) (.bmp,
..gif, .tif, .jpg, etc).

The size of a note is unpredictable: maybe 4k, maybe 100k. Majority
under 32k. Figure five under 32k for one over.

At this moment all rows saved as BLOBs.

Would I be better off to redo this as two tables: one for VARCHAR(32K)
FOR BIT DATA, and one for BLOB? Then do a join? Guess 500,000 rows.

Would the VARCHAR(32K) FOR BIT DATA work for .rtf with embedded
graphics files?

Any special temporary tablespaces required to do this efficiently, or
does db2 handle that?

Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
I don't like splitting it up. It adds a layer of complexity to the
application code to determine, at insert time, which way to handle the
data. Retrievals will require a join that fails 80% of the time or an
additional call to retrieve the blob when there is no varchar data.

Blobs have options that minimize space waste. You should make sure that
you've specified these when defining the blob. you'll also need to
decide what to do about logging blob data.

Phil Sherman
28******@gmail.com wrote:
[Win2003 server with XP clients. DB2 8.2 with updates added
continuously. VB6, latest update. OLE. IBM's ODBC.]

I have a table which saves doctors' notes about patients. The format
of the material is rich text format +/- embedded graphic(s) (.bmp,
.gif, .tif, .jpg, etc).

The size of a note is unpredictable: maybe 4k, maybe 100k. Majority
under 32k. Figure five under 32k for one over.

At this moment all rows saved as BLOBs.

Would I be better off to redo this as two tables: one for VARCHAR(32K)
FOR BIT DATA, and one for BLOB? Then do a join? Guess 500,000 rows.

Would the VARCHAR(32K) FOR BIT DATA work for .rtf with embedded
graphics files?

Any special temporary tablespaces required to do this efficiently, or
does db2 handle that?

Nov 12 '05 #2

P: n/a
Phil Sherman wrote:
I don't like splitting it up. It adds a layer of complexity to the
application code to determine, at insert time, which way to handle the
data. Retrievals will require a join that fails 80% of the time or an
additional call to retrieve the blob when there is no varchar data.

Blobs have options that minimize space waste. You should make sure that
you've specified these when defining the blob. you'll also need to
decide what to do about logging blob data.

Phil Sherman
28******@gmail.com wrote:
[Win2003 server with XP clients. DB2 8.2 with updates added
continuously. VB6, latest update. OLE. IBM's ODBC.]

I have a table which saves doctors' notes about patients. The format
of the material is rich text format +/- embedded graphic(s) (.bmp,
.gif, .tif, .jpg, etc).

The size of a note is unpredictable: maybe 4k, maybe 100k. Majority
under 32k. Figure five under 32k for one over.

At this moment all rows saved as BLOBs.

Would I be better off to redo this as two tables: one for VARCHAR(32K)
FOR BIT DATA, and one for BLOB? Then do a join? Guess 500,000 rows.

Would the VARCHAR(32K) FOR BIT DATA work for .rtf with embedded
graphics files?

Any special temporary tablespaces required to do this efficiently, or
does db2 handle that?

Another, unortodox, proposal is to wrap the BLOB into a structured
datatype. Structured datatypes can use the INLINE LENGTH column
property, so the instance will only be stored out of row when it exceeds
teh inline length.
Note that using structured types puts restrictions upon you w.r.t. LOAD
and IMPORT.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3

P: n/a
Serge,

This is certainly worth a try. When the column exceeds the INLINE
LENGTH attribute, where is it stored? I assume it would not be
buffered then, so how to use tablespace and bufferpools? Also, in a
RAID array with stripe 16k, can I use a tablespace of 32k?

Stan

Nov 12 '05 #4

P: n/a
Stanley Sinclair wrote:
Serge,

This is certainly worth a try. When the column exceeds the INLINE
LENGTH attribute, where is it stored? I assume it would not be
buffered then, so how to use tablespace and bufferpools?
Not-inlined values will be stored like LOBs. The DB2-internal levels don't
even know that they are not dealing with a "real" LOB. So all
considerations that apply to LOBs also apply to such "lobified" values of
structured types: no buffering in DB2's bufferpools (except on the file
system cache level handled by the operating system).
Also, in a
RAID array with stripe 16k, can I use a tablespace of 32k?


Of course you can do that. The question is only if 32K stripes might give
some better performance.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 12 '05 #5

P: n/a
Thanks, all.

Stanley Sinclair

Nov 12 '05 #6

P: n/a
<<When the column exceeds the INLINE LENGTH attribute, where is it
stored?>>

What I meant was, is there an advantage to CREATE TABLE NOTE( . . . . ,
MANAGED BY DATABASE IN REGULARTABLE LONG IN LONGTABLE) where LONGTABLE
is managed by database. If not, should the original table be managed
by system or database?

I have the situation where one such table (like NOTE) changes rapidly
and is small, and another is very large and changes rarely but is read
often.

Nov 12 '05 #7

P: n/a
Stanley Sinclair wrote:
<<When the column exceeds the INLINE LENGTH attribute, where is it
stored?>>

What I meant was, is there an advantage to CREATE TABLE NOTE( . . . . ,
MANAGED BY DATABASE IN REGULARTABLE LONG IN LONGTABLE) where LONGTABLE
is managed by database. If not, should the original table be managed
by system or database?

I have the situation where one such table (like NOTE) changes rapidly
and is small, and another is very large and changes rarely but is read
often.


You can only place the LONG (LOB) data in a separate table space if you use
DMS table spaces (managed by database). For SMS you don't have that choice
in the first place. So it comes down to choose the proper tablespace type.
Additionally, you might want to choose between raw devices and files. I'd
probably place the LOB data in a file-based LONG tablespace to take
advantage of file system caching, and the indexes and other data can go to
raw devices. So you'd automatically have to use DMS table spaces.

For the small, rapidly changing table a DMS table space is probably better
in the first place because you don't loose performance on page allocation
and deallocation operations - the pages are already preallocated. For the
large table which is mostly read, you might want to consider a separate
buffer pool. I'm not sure if SMS or DMS would make a huge difference
there.

But all this depends on a lot of things like how many disks you have and how
fast they are. How much memory do you have so you can decide between
buffer pool and file system cache (and whatever else needs memory). It's a
bit difficult to give good recommendations without knowing the exact
scenario...

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.