db2 v7 - blob allocation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alberto Salvati - 09 Apr 2008 09:42 GMT

Hi, all.
I'm working with a very old db2 version.
Now, I need to add to existing table a blob column.
I've not experience on db2... :-(

Records on this table are writed from an application and used ( ie,
read..) by a service application that update only one field as a flag
column, but blob content is never modified.
I've seen db2v7 documentation (ibm web site).
I think that in this table/blob field I can use "compact" and "no
logged" option.

But, in documentation I've not found some information about block size
for blob fields...

My questions...

1) my idea to use "compact" and "no logged" it's correct for my
context?
2) there is some db2 parameter or options to specify block size for
blob (and/or clob...) fields?
3) blob data are deflated by db2 server or not?

TIA

A.Salvati

Reply to this Message

Mark A - 10 Apr 2008 03:01 GMT

> Hi, all.
> I'm working with a very old db2 version.
[quoted text clipped - 15 lines]
> 1) my idea to use "compact" and "no logged" it's correct for my
> context?

Compact would be correct if you don't ever update them. Whether or not you
want the blob logged depends on whether you need roll-forward recovery of
the blob data after a restore of a previous backup (in case of a serious
problem).

> 2) there is some db2 parameter or options to specify block size for
> blob (and/or clob...) fields?

Blobs will be stored in the same tablespace as the table, unless you specify
a DMS tablespace for the table and specify where the large objects will be
stored in the CREATE TABLE statement. The blob itself does not care about
block size, since it does not use the bufferpools (all blob i/o is directly
to/from disk).

> 3) blob data are deflated by db2 server or not?

Compressed? No, you would have to do that yourself.

Reply to this Message

Alberto Salvati - 10 Apr 2008 09:05 GMT

Hi, Mark. Firsrt, tnx for your answers..

> Compact would be correct if you don't ever update them. Whether or not you
> want the blob logged depends on whether you need roll-forward recovery of
> the blob data after a restore of a previous backup (in case of a serious
> problem).

Perfect.

> > 2) there is some db2 parameter or options to specify block size for
> > blob (and/or clob...) fields?
[quoted text clipped - 4 lines]
> block size, since it does not use the bufferpools (all blob i/o is directly
> to/from disk).

ok. Im' sorry, ma at ibm2 db2 documentationI see that I can set a
specific tablespace for table storage, ma anything about blob storage
excluding options descripted in my 1st post..
Are you speaking about DATALINK columns insthead blob columns?
Have you some additional info?

> Compressed? No, you would have to do that yourself.

ok. best.

A.

Reply to this Message

Mark A - 10 Apr 2008 09:46 GMT

> ok. Im' sorry, ma at ibm2 db2 documentationI see that I can set a
> specific tablespace for table storage, ma anything about blob storage
> excluding options descripted in my 1st post..
> Are you speaking about DATALINK columns insthead blob columns?
> Have you some additional info?

In the CREATE TABLE you specify:
LONG IN <tablespace-name>
to specify where the LOB columns will be stored. Otherwise they will be in
the same tablespace as the table. In order to split them out like this
(INDEX IN also) the tablespaces must be DMS.

For example:

CREATE TABLE FOO
(
COL1 INT,
COL2 INT
)
IN <tsname for table> INDEX IN <tsname for indexes> LONG IN <tsname for
LOBs>;

Regardless of which tablespace the LOBs end up in, they will not utilize the
bufferpool.

Reply to this Message

Alberto Salvati - 10 Apr 2008 15:28 GMT

Thanks.

A.

Reply to this Message