By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,734 Members | 1,416 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,734 IT Pros & Developers. It's quick & easy.

Reducing the high water mark for LOB tablespace.

P: n/a
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.

Please let me know if you need any further information.

Thanks.

Jun 9 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
> 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.

jsoh

Jun 9 '06 #2

P: n/a

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

Jun 11 '06 #3

P: n/a
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


Jun 12 '06 #4

P: n/a
> Thanks for the suggestion but this just offers suggrestions for
reducing the HWM, but doesn't actually lower it.


Well, yes. the /LHWM option is designed to report to you the options
that you have. The /RHWM option is only useful if the HWM is being
'artificially' being held up by an object table or SMP extent.

Dart will not export/reload data for you, should that be the only
option available to you.

jsoh

Jun 12 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.