Hi,
I'm new to DB2. We have a database (db2 7.2, solaris 8) which was not maintained for several months, execpt for deleteing of obsolete records. Now we find that even though older recs are deleted the file sizes are only increasing (> 2Gb) . I understand that we need to do a REORG to free up unused space. There are some 3 or more files occupying large amount of diskspace. We know that one particular table is causing all this. It has a CLOB column in it. Question is: 1. How do we reorg the table without consuming a similar additional amount of disk space.
2. Will dropping the table and then recreating it without any data reduce the filesizes (i,e. truncate ) ?


thanks in advance

Johar

Tables exist in tablespaces. You need to give us more information about the tablespaces such as SMS/DMS, size, high water mark, etc.

DB2 list tablespaces show detail

Looks like you are using SMS tablespaces ... If it is possible, dropping and recreating is the best way to go about ... Otherwise, you should do a REORG .. Given your scenario, REORG may not want as much space as it occupies now ... It may want the amount of actual data currently in the table + obviously some overhead ... Just my guess ...

HTH

Sathyaram

Hi,

Here's the output :

db2 => list tablespaces show detail

Tablespaces for Current Database

Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1850
Useable pages = 1850
Used pages = 1850
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 = 1234521
Useable pages = 1234521
Used pages = 1234521
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



The problem here is the table was never reorg'd. The table needs to hold only a few weeks data which may come to less than a 100 MB. In that case will REORG require all that additional GB space?


Tks & regds

Do the reorg and it will reclaim the space.