DB2 9.7 BLOB Storage

Posted on 2011-10-20
Medium Priority
Last Modified: 2012-05-12
I have a have table in db2 9.7 like
              SEQUENCE_NO INT (10, 0) NOT NULL,
              IMG BLOB (524288) LOGGED NOT COMPACT,
              IMG_TYPE VARCHAR (3),
              SCAN_BY VARCHAR (10),
              SCAN_TIME TIME  
             COMPRESS YES  
             IN LOBSTBS;
My image type is tiff, size 12K. But if I insert this row into the database, “.LB file” grows 250MB. I can’t understand why this happens, b/c if I extract the image from database the size is “12K”.
Question by:sgogan
LVL 46

Accepted Solution

Kent Olsen earned 1000 total points
ID: 37008451
Hi sgogan,

The file won't grow 250MB for each file that you add.  :)

Like most DBMS, DB2 blocks/buffers data.   It suggests that DB2 has created 1/4 Gig of storage space for the BLOB objects.  Since they can eat up a lot of space in a hurry, DB2 has allocated 1/4 GB the first time that it had to insert an BLOB.  You shouldn't see that change for about a 1,000 inserts now.  :)

LVL 36

Assisted Solution

by:Gary Patterson
Gary Patterson earned 1000 total points
ID: 37008708
If you expect most of your rows to contain a LOB, and the LOBs are generally small, you do have the options to store small LOB's inline in V9.7:


- Gary Patterson
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37218633
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question