Hi,

how much space is used by LOBs. I've the following scenario which I
don't understand:

We've a table with 8 columns with a regular data type and one BLOB-
Column defined as "BLOBDATA" BLOB(104857600) LOGGED NOT COMPACT. The
table is using a SMS-Tablespace.

With the 'list tablespaces show detail' command I've get a usage of
about 320GB in this tablespace. The *.LB-File in the tablespace is
around 310GB (10GB is used by other tables in the tablespace).

But with the query 'select sum(bigint(length(blobdata))) FROM
<tabname>' I get a size of 207GB. Why is there an overhead of about
100GB (ca. 50%)?

I've already reorganized the data with an offline reorg and the
longlobdata-option (REORG TABLE <tabname> USE <tempspace>
LONGLOBDATA). After the reorg the was only decreased by 20Megabytes
(not GB!) Before we've deleted some data an expected to decrease the
tablespace by about 10GB.

Thanks & Regards

Michael

Which version of DB2?

Please post the output of the 'db2 list tablespaces show detail' command for
the tablespaces in question.


--
Helmut K. C. Tessarek
DB2 Performance and Development

/*
Thou shalt not follow the NULL pointer for chaos and madness
await thee at its end.
*/

On 2 Jun., 19:49, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote:
Hi Helmut,

the output of the list tablespace commando:

db2 list tablespaces show detail |more

Tablespaces for Current Database

Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 9491
Useable pages = 9491
Used pages = 9491
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1
Useable pages = 1
Used pages = 1
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1

Tablespace ID = 2
Name = USERSPACE1
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 3490
Useable pages = 3490
Used pages = 3490
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 4096
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Minimum recovery time = 2010-03-02-16.51.04.000000

Tablespace ID = 3
Name = MPOD0001
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 10507119
Useable pages = 10507119
Used pages = 10507119
Free pages = Not applicable
High water mark (pages) = Not applicable
Page size (bytes) = 32768
Extent size (pages) = 32
Prefetch size (pages) = 32
Number of containers = 1
Minimum recovery time = 2010-02-19-13.52.26.000000


Tablespace 4 ist the tablespace with the LOBs.

Regards

Michael

Which version of DB2?
What is the output of 'db2level'?

--
Helmut K. C. Tessarek
DB2 Performance and Development

/*
Thou shalt not follow the NULL pointer for chaos and madness
await thee at its end.
*/

On 3 Jun., 21:13, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote:
We're working with DB2 Version 8.2 on AIX. (I know it's out of
service):


DB21085I Instance "db2pd011" uses "32" bits and DB2 code release
"SQL08020"
with level identifier "03010106".
Informational tokens are "DB2 v8.1.1.72", "s040914", "U498350", and
FixPak "7".
Product is installed at "/usr/opt/db2_08_FP7".

Hi Michael,

Sorry that it took so long, but I had to talk to one of the developers, since
I'm not too familiar with that part of the code.

You probably won't like the answer, but here it is anyway:

LOBs have to be aligned internally. Depending on the index you are using for
the reorg and the variation of LOB sizes, it might be that no space is
reclaimed (on disk) during a reorg.
But if you are deleting e.g. 10 GB of LOBs and the .LB files don't decrease in
size after a reorg, you can insert 10 GB of LOBs again and the .LB files won't
increase in size.

Hope this helps.

--
Helmut K. C. Tessarek
DB2 Performance and Development

/*
Thou shalt not follow the NULL pointer for chaos and madness
await thee at its end.
*/

On 7 Jun., 21:27, Helmut Tessarek <tessa... (AT) evermeet (DOT) cx> wrote:
Thanks Helmut,

is there a different behaviour with DMS instead of SMS tablespaces and/
or in DB2 V9 or V9.5. Would it help to create a temporary index?

Regards

Michael

There is not really a difference between DMS and SMS (regarding the reorg
LOBs), since LOBs have to be aligned in DMS tablespaces as well. With DMS
there is also the HWM barrier. The containers for a DMS tablespace can only be
decreased to the point of the HWM.
Pre 9.7 you had to use db2dart and reorg to lower the HWM, but this did not
work if the high water mark was held by object table data extents.
However in 9.7 there are commands to reduce the HWM and it will even work, if
it is held by object table data extents.

I'm not sure, if creating a temp index would help. You will have to try. But
to be clear, I think that aligning the data to an index, which is dropped
after the reorg, does not really make any sense. It would also be bad for
performance.

What you could do is an export of the table and importing it to a new table.
This should reclaim the space, but I doubt that this is a valid solution for you.

--
Helmut K. C. Tessarek
DB2 Performance and Development

/*
Thou shalt not follow the NULL pointer for chaos and madness
await thee at its end.
*/