BLOB in Table - DB2 Database

Stanley Sinclair

(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 data out of BLOB and CLOB
storage, but I see little on getting data into it. I know it's there,
but could someone speed my search? A colleague created the following
table:

CREATE TABLE BLOB_TABLE (
BLOB_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY...,
BLOB_IMAGE BLOB,
BLOB_NAME VARCHAR(100),
BLOB_TYPE VARCHAR(10),
PRIMARY KEY (BLOB_ID)
)

I'm wondering how large the bufferpool and tablespace has to be? Is
this where the BLOB_IMAGE should be placed?

Stan

Nov 12 '05 #1

Subscribe Reply

11514

Erik Hendrix

Hey Stan,

A LOB will not be buffered. This means that you can make the bufferpool as
big or as small as you want, it would not make any difference for your BLOB
(it will offcourse for the other data in your table).
The space requirements will really depend on how much you will store into
this table. However, you can put the LOB into it's own tablespace.
Personally I would prefer this since this can help later on should you want
to move to raw devices or tune filesystems for direct IO (not to use
buffering). You can then leave the LOB tablespace on a buffered filesystem
to help performance (since LOB's do not get logged).

Thanks.

"Stanley Sinclair" <st************ *@bellsouth.net > wrote in message
news:6f******** *************** ***@posting.goo gle.com...

(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 data out of BLOB and CLOB
storage, but I see little on getting data into it. I know it's there,
but could someone speed my search? A colleague created the following
table:

CREATE TABLE BLOB_TABLE (
BLOB_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY...,
BLOB_IMAGE BLOB,
BLOB_NAME VARCHAR(100),
BLOB_TYPE VARCHAR(10),
PRIMARY KEY (BLOB_ID)
)

I'm wondering how large the bufferpool and tablespace has to be? Is
this where the BLOB_IMAGE should be placed?

Stan

Nov 12 '05 #2

Serge Rielau

LOGGED is optional for LOBs up to 1GB (I think it's the default).

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Nov 12 '05 #3

Knut Stolze

Erik Hendrix <he**********@h otmail.com> wrote:

Hey Stan,

A LOB will not be buffered. This means that you can make the bufferpool as
big or as small as you want, it would not make any difference for your
BLOB (it will offcourse for the other data in your table).
The space requirements will really depend on how much you will store into
this table. However, you can put the LOB into it's own tablespace.
Personally I would prefer this since this can help later on should you
want to move to raw devices or tune filesystems for direct IO (not to use
buffering). You can then leave the LOB tablespace on a buffered filesystem
to help performance (since LOB's do not get logged).

A discription how you can calculate the size for a LONG tablespace can be
found here:
http://www-306.ibm.com/cgi-bin/db2ww...n=c0004928.htm

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

Nov 12 '05 #4

Similar topics

6123

Access BLOB

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); $blob=mysql_fetch_assoc($result); Now when I do print_r (array_values($blob));

PHP

2328

Problem nserting BLOB data

by: Mark Fisher | last post by:

I cannot seem to store binary data as a BLOB. I used the following to create a table capable of holding a BLOB: CREATE TABLE blob_table ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, binary_stuff BLOB, PRIMARY KEY (id) );

MySQL Database

4728

Using BLOB for storing data

by: hamvil79 | last post by:

I'm implementig a java web application using MySQL as database. The main function of the application is basically to redistribuite documents. Those documents (PDF, DOC with an average size around 2Mb) are stored in BLOB column. The amount of documents for the first year should not exceed 5/6 Giga, but I cannot make prevision for the next years. Those documents are mainly just accessed (update and delete are not so

MySQL Database

7311

copy a db2/zos table with a blob column

by: Carmine | last post by:

I have to add a date column to a db2/zos table with a blob column. Can I rename the blob table to old, create a new table with the date column, then insert/select the data from the old table? Do I have to select data from the main table and the aux table or is the aux table done automatically? What's the best way to handle this?

DB2 Database

3629

Deadlock or Timeout when delete/update rows with BLOB

by: Ray | last post by:

I am having my first experience using BLOB as a row in a table. I am using it to insert graphics for labels we print. I have no problem inserting into and select from the table. The graphic is being stored correctly. Whenever I attempt to delete a row from my application I get the following error. SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "68". SQLSTATE=40001

DB2 Database

9964

calculate size of table with BLOB

by: Yaro | last post by:

Hi, I am looking for information how calculate size of table with BLOB field. After RUNSTATS command select fpages * tablespace pagesize - gives me only 16kB (table contain about 10M) I am interesting in 2 cases:

DB2 Database

4817

blob to float conversion

by: yoes | last post by:

Dear all, I am very new in MySQL, I am working on image database project for my research and I have problem how to convert blob field into float in MySQL so that I can calculate the blob field with the MATH operation. Does any one how to do it ???? Thank you very much indeed , for your help.

MySQL Database

6535

Extract BLOB from MySQL & save them anywhere in our pc

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 retrieved byte/binary data as i got no idea on how to save them in our pc. For this situation, what i need to do is give the byte/binary data an extension (retrieved from another field in the table) in order to revert back to the original data i had in the...

Java

5211

How to Update Blob with JDBC?

by: Nesa | last post by:

Hi, I am accessing DB2 8.2 (Windows) via JDBC type 4 and am having troubles making Blob.setBinaryStream(long) work to update a BLOB column. Here is the excerpt of code that I am using: Connection conn = ds.getConnection(); conn.setAutoCommit(false); // insert a file FileInputStream fis = new FileInputStream(fileName);

DB2 Database

9688

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

10490

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

9078

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

7570

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

6809

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

5590

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4146

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3762

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2941

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General