How reduce automatic storage table spaces in a number of ways

ALTER TABLESPACE with the REDUCE clause - ALTER TABLESPACE with the LOWER HIGH WATER MARK clause

Automatic storage table spaces

You can reduce automatic storage table spaces in a number of ways:

Container reduction only

With this option, no extents are moved. The database manager attempts to reduce the size of the containers by first freeing extents for which deletes are pending. (It is possible that some "pending delete" extents cannot be freed for recoverability reasons, so some of these extents may remain.) If the high water mark was among those extents freed, then the high water mark is lowered, otherwise no change to the high water mark takes place. Next, the containers are re-sized such that total amount of space in the table space is equal to or slightly greater than the high water mark. This operation is performed using the ALTER TABLESPACE with the REDUCE clause by itself.

Lower high water mark only

With this option, the maximum number of extents are moved to lower the high water mark, however, no container resizing operations are performed. This operation is performed using the ALTER TABLESPACE with the LOWER HIGH WATER MARK clause by itself.

Lower high water mark and reduce containers by a specific amount

With this option, you can specify an absolute amount in kilo-, mega-, or gigabytes by which to reduce the table space. Or you can specify a relative amount to reduce by entering a percentage. Either way, the database manager first attempts to reduce space by the requested amount without moving extents. That is, it attempts to reduce the table space by reducing the container size only, as described in Container reduction only, by freeing delete pending extents, and attempting to lower the high water mark. If this approach does not yield a sufficient reduction, the database manager then begins moving used extents lower in the table space to lower the high water mark. After extent movement has completed, the containers are resized such that total amount of space in the table space is equal to or slightly greater than the high water mark. If the table space cannot be reduced by the requested amount because there are not enough extents that can be moved, the high water mark is lowered as much as possible. This operation is performed using the ALTER TABLESPACE with a REDUCE clause that includes a specified amount by which to reduce the size the table space.

Lower high water mark and reduce containers the maximum amount possible

In this case, the database manager moves as many extents as possible to reduce the size of the table space and its containers. This operation is performed using the ALTER TABLESPACE with the REDUCE MAX clause.

Once the extent movement process has started, you can stop it using the ALTER TABLESPACE statement with the REDUCE STOP clause. Any extents that have been moved are committed, the high water mark lowered as much as possible, and containers are re-sized to the new, lowered high water mark.

How much log space is requeried during an offline reorg ?

Since offline reorganization uses a shadow copy approach, you need enough additional storage to accommodate another copy of the table. The shadow copy is built either in the table space that the original table resides in or in a user-specified temporary table space.

Additional temporary table space storage may be required for sort processing if a table scan sort is used. The additional space required could be as large as the size of the table being reorganized. If the clustering index is SMS type or unique DMS type, the recreation of this index will not require a sort. Instead, this index is rebuilt by scanning the newly reorganized data. Any other indexes that require recreating will involve a sort, potentially requiring space the temporary table space up to the size of the table being reorganized.

Offline table REORG generates few control log records, and therefore consumes a relatively small amount of log space. If the REORG does not use an index, then the only log records are the table data log records. If an index is specified, or if a clustering index exists on the table, then the RIDs of the rows are logged in the order that they are placed into the new version of the table. Each log record for the RIDs holds a maximum of 8000 RIDs, with each RID consuming 4 bytes. This can be a contributing factor in running out of log space during an offline table reorganization. Note that RIDs are only logged if the database is recoverable (LOGRETAIN=ON).

To check the los space used,apply the command below:

db2pd -d <db_name> -logs

or 

db2 "select int(total_log_used/1024/1024) as Log_Used_Meg, \
int(total_log_available/1024/1024) as Log_Space_Free_Meg, \
int((float(total_log_used) /float(total_log_used+total_log_available))*100) \
as Percent_Used,int(tot_log_used_top/1024/1024) as Max_Log_Used_Meg, \
int(sec_log_used_top/1024/1024) as Max_Secundary_Used_Meg, \
int(sec_logs_allocated) as Secondaries from sysibmadm.snapdb"

source: http://publib.boulder.ibm.com/infocenter/db2luw/v9r5