Found this in the manuals....
After deleting 10 - 13GB of data from the table and running a REORG, no
space is reclaimed; the size of the table space did not decrease.
Cause
When an SMS tablespace is used a REORG is not going to reclaim space.
An SMS table space is defined as a directory. The directory uses the
space available on the underlying file system. When a row is inserted
and subsequently deleted in an SMS table space, the space is reused.
The data for an SMS table space is always contiguous and there are
never any empty pages in the table space. The files in the SMS
directory will not change in size.
Solution
The only way to make the LOB file decrease in size in an SMS table
space is to do an EXPORT/LOAD of the data in the table. When you LOAD
data into an empty table the data is placed in a contiguous fashion and
there should be no free space. Here are your steps for doing the
EXPORT/LOAD:
1. EXPORT the data
2. drop the table
3. re-create the DDL for the table
4. LOAD data
Cheers....
Shashi Mannepalli
Patrick wrote:
Josh Tiefenbach wrote: I have a tablespace that contains the LOB data for 3 tables which exist
in other tablespaces. Even after reorging the LOB tablespace with the
LONG option, the high water mark is still too high. I want to be able
to shrink the tablespace below the current high water mark and return
the excess space back to the filesystem. Is there another way to lower
the HWM without exporting the data to another tablespace, dropping and
recreating the tables, and importing the data back in? The OS is AIX
5.2 while the database itself is UDB 8.1 fixpack 6. In the create
table statement the LOB columns are declared as compact.
Try db2dart /LHWM
Its likely that the HWM is being held by an SMP extent. If the extent
is truly empty, then db2dart can mark it unused (/RHWM option), which
will allow you to release space back to the fs.
josh
Thanks for the suggestion but this just offers suggrestions for
reducing the HWM, but doesn't actually lower it.
Patrick
