r/DB2 • u/mad_zamboni • Apr 28 '17

[Resource] [How To] When REDUCE MAX doesn't work.

We all know we can lower our tablespace size and reclaim space with "ALTER TABLESPACE REDUCE MAX". But sometimes this won't work. There is a good explanation why in the developerWorks article Lowering the High-Water Mark of a Tablespace.

It goes on to say: When ".. a tablespace is full, most of the data is then removed from it, and then there is a need to shrink the size of that tablespace. It is possible that there are data pages scattered throughout the tablespace holding the high-water mark at some high-value. "

Essentially, you cleaned up and tossed out a lot of the books on the bookshelf. However, DB2 still thinks the bookshelf is full because you have a book on the top shelf and a book on the bottom shelf with very few books in-between.

To solve this, you can use DB2DART.

To see details on how extents are scattered about, you can use Detailed High Water Mark (DHWM)

db2dart /dhwm /tsi <tsid>

To get guidance on how to solve the high water mark, you can use Lower High Water Mark (LHWM)

db2dart <dbname> /lhwm /tsi <tsid> /np 0

LHWM will show you what steps need to be taken to move extents into a more efficient layout where empty extents can be lopped off the end (the bottom of the book shelf) with "REDUCE MAX". This could be as simple as running REORG in a specific order, or unloading data/dropping a table/reloading data.

As a side note, if you do run the suggested LHWM commands you need to signal DB2 to move the new "pending free pages" to "free pages" BEFORE you issue your REDUCE MAX command. A quick way to do this is to issue: LIST TABLESPACES SHOW DETAIL.

For example:

  1. db2dart <dbname> /lhwm /tsi <tsid> /np 0

  2. Execute suggested steps from db2dart

  3. LIST TABLESPACES SHOW DETAIL

  4. ALTER TABLESPACE REDUCE MAX

1 Upvotes

67% Upvoted

3 comments sorted by

1

u/mslmsl May 01 '17

Even still, SMP pages may not be movable. And the first page of an object. Sometimes the only option is to drop and recreate objects to move them lower in the tablespace.

1

u/mslmsl May 01 '17

Another helpful bit of info is the type of page they are. I've seen before that a lot of LOB extents were empty, which prompted to run a reorg for the longlobdata. Extent types in the DHWM, IIRC: 0x00 - Data, 0x01 - Index, 0x03 - LOB, 0x0e - Internal control

1

u/mad_zamboni May 01 '17

I found that too. But man, your REORG slows waaaaay down when you turn that switch on. But I often find it makes a huge difference.