Spaceusage of LOBs
in [DB2]
|
From: Michael on 2 Jun 2010 12:00 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
From: Helmut Tessarek on 2 Jun 2010 13:49 Which version of DB2? > 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). 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. */
From: Michael on 3 Jun 2010 14:22 On 2 Jun., 19:49, Helmut Tessarek <tessa...(a)evermeet.cx> wrote: > Which version of DB2? > > > 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). > > 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. > */ 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
From: Helmut Tessarek on 3 Jun 2010 15:13 Which version of DB2? What is the output of 'db2level'? > 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 -- Helmut K. C. Tessarek DB2 Performance and Development /* Thou shalt not follow the NULL pointer for chaos and madness await thee at its end. */
From: Michael on 4 Jun 2010 05:47
On 3 Jun., 21:13, Helmut Tessarek <tessa...(a)evermeet.cx> wrote: > Which version of DB2? > What is the output of 'db2level'? > 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". |