Hi All,

I'm working on DB2 UDB 8.2 with Fixpack 10 on Windows 2000 Server.

The problem I'm facing is that I have created a table having BLOB type
column with NOT LOGGED option. (see script below)

CREATE TABLE ATTACHMENTS
(
CASEATTACHMENTID INTEGER NOT NULL,
ATTACHMENTDATA BLOB(2G) NOT LOGGED
) LONG IN "TSLRGOBJ"

The TableSpace has also been created without LOG option.(see Script
below)

CREATE LARGE TABLESPACE TSLRGOBJ
PAGESIZE 32 K
MANAGED BY AUTOMATIC STORAGE
EXTENTSIZE 32
OVERHEAD 10.5
PREFETCHSIZE 32
TRANSFERRATE 0.14
BUFFERPOOL FPI_BUFFPOOL

My Questions are :

1. How can I enable logging for LOB type data without dropping the
table?
2. Are any changes to be made to TableSpace also for the same (againn
without dropping it)?
3. I some articles and posting on the same group I came accross the
info that LOB of size greater than 1G are not logged. Are we talking
about the contents of the LOB column or the size of the column? (in my
case the size of the column is 2G but the file size cannot be greater
than 10MB)

Regards,

Ashish

Ashish,

The limit on LOB columns which can be logged in 1 gig. So if you want to
log you will need to reduce the LOB column length, which is only going to
be through a drop and recreate.

I notice you don't have the "COMPACT" keyword on the LOB definition. I
believe this means that you'll be using 2 gig per column regardless of how
many bytes you are actually storing.

Phil

aaggarwal@del.aithent.com wrote:

> Hi All,
>
> I'm working on DB2 UDB 8.2 with Fixpack 10 on Windows 2000 Server.
>
> The problem I'm facing is that I have created a table having BLOB type
> column with NOT LOGGED option. (see script below)
>
> CREATE TABLE ATTACHMENTS
> (
> CASEATTACHMENTID INTEGER NOT NULL,
> ATTACHMENTDATA BLOB(2G) NOT LOGGED
> ) LONG IN "TSLRGOBJ"
>
> The TableSpace has also been created without LOG option.(see Script
> below)
>
> CREATE LARGE TABLESPACE TSLRGOBJ
> PAGESIZE 32 K
> MANAGED BY AUTOMATIC STORAGE
> EXTENTSIZE 32
> OVERHEAD 10.5
> PREFETCHSIZE 32
> TRANSFERRATE 0.14
> BUFFERPOOL FPI_BUFFPOOL
>
> My Questions are :
>
> 1. How can I enable logging for LOB type data without dropping the
> table?
> 2. Are any changes to be made to TableSpace also for the same (againn
> without dropping it)?
> 3. I some articles and posting on the same group I came accross the
> info that LOB of size greater than 1G are not logged. Are we talking
> about the contents of the LOB column or the size of the column? (in my
> case the size of the column is 2G but the file size cannot be greater
> than 10MB)
>
> Regards,
>
> Ashish

> The limit on LOB columns which can be logged in 1 gig.

Just in case my column size is less than or equal to 1G and I have used
NOT LOGGED
option, then can I alter the table or tablspace to activate it? If yes
then how!!!

> I notice you don't have the "COMPACT" keyword on the LOB definition.

Anyways thanks for the COMPACT suggestion.

Thanks and Regards,

Ashish

Philip Nelson wrote:

> I notice you don't have the "COMPACT" keyword on the LOB definition. I
> believe this means that you'll be using 2 gig per column regardless of how
> many bytes you are actually storing.

That's not correct. Not specifying the COMPACT keyword does not imply that
each LOB will occupy the maximum amount of space as is given by the
declared type of the column (2G in this case). If you specify COMPACT,
then the only difference is that DB2 will free leftover pages at the end of
the LOB. For example, if a not-compacted LOB fits on 3 pages, DB2 may have
allocated 4 pages. The last page is freed with COMPACT turned on.

>> 2. Are any changes to be made to TableSpace also for the same (againn
>> without dropping it)?

There is no logging option for tablespaces (in UDB) in the first place.

>> 3. I some articles and posting on the same group I came accross the
>> info that LOB of size greater than 1G are not logged. Are we talking
>> about the contents of the LOB column or the size of the column?

The manual says in the CREATE TABLE statement:

LOBs greater than 1 gigabyte cannot be logged (SQLSTATE 42993).

The LOGGED/NOT LOGGED option depends on the declared type of the column and
not the actual length of the data. If you try to create a table with
BLOB(2G), you will get the SQL0355N error (SQLSTATE 42993).

>> (in my
>> case the size of the column is 2G but the file size cannot be greater
>> than 10MB)

Why are you using BLOB(2G) in that case? After all, larger LOBs need larger
locators in the table. That makes (unnecessarily) rows longer.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

aaggarwal@del.aithent.com wrote:

>> The limit on LOB columns which can be logged in 1 gig.
>
> Just in case my column size is less than or equal to 1G and I have used
> NOT LOGGED
> option, then can I alter the table or tablspace to activate it? If yes
> then how!!!

You must drop the table and recreate it. There is currently no other way.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany