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

BLOB NOT LOGGED

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

Sep 25 '06 #1
Share this Question
Share on Google+
4 Replies


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

aa*******@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
Sep 25 '06 #2

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

Sep 26 '06 #3

P: n/a
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
Sep 26 '06 #4

P: n/a
aa*******@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
Sep 27 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.