473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

BLOB NOT LOGGED

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
4 7684
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Carolyn Longfoot | last post by:
Help! This is driving me crazy... I'm trying to read a BLOB from a db and display it in a browser, like so: $query="SELECT blob from table where blob_id=9"; $result=mysql_query($query);...
4
by: Ellen K | last post by:
Hi all, It was my understanding (Please correct me if I'm wrong on this!) that BLOB data actually reside on their own separate pages and a BLOB field only holds a pointer to the location of the...
0
by: HABJAN ®iga | last post by:
I'am trying to insert a record with a blob field in it: create table KC113DELO.Test1( ID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ), TTEST...
3
by: Stanley Sinclair | last post by:
(Minor question. Since updating to FP4 I cannot access HTML help. Compiler error. What's wrong?) _______________________ Subject of this message: There is lots of Help regarding getting...
7
by: johnm | last post by:
We have a new CRM application that uses a DB2 7.2 database. Our users noted that the CRM application would not allow them to attach and store any documents over 2 meg in size. When asked, the...
7
by: Kevin Lawrence | last post by:
Hi all I want to do "INSERT INTO Table (Blob) Values('blobdataasstring')". ...rather than using the parameter driven method, is it possible? And if so what encoder do I use to convert the...
2
by: Vinciz | last post by:
hi guys... im new in java and i would love to learn some of these... basically i got a sample code to retrieve the blob from the mysql. however, i dont really know what to do with these...
9
by: matt | last post by:
hello, im doing my first ASP.NET app that inserts & retrieves files from Oracle (no need for a discussion on *that*!). i learned first-hand of the somewhat awkward technique for inserting...
0
by: saurabh29feb | last post by:
Hi, Can i changed a BLOB column in a table which was created as NOT LOGGED to LOGGED ??? Thanks!!!
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.