How much log space is requeried during an online reorg ?
The log space required for an online table REORG is typically larger than what is required for an offline table REORG. The amount of space required is determined by the number of rows being reorganized, the number of indexes, the size of the index keys, and how poorly organized the table is at the outset. It is a good idea to establish a typical benchmark for log space utilization for your tables.
For every row in the table, it will likely be moved twice
during an online table reorganization. Given one index, each row has to update the index
key to reflect the new location, and once all accesses to the old location
are complete, the key is updated again to remove the reference to the old
RID. When the row is moved back, these updates to the index key are performed
again. All of this activity is logged to make online table reorganization fully recoverable,
therefore there is a minimum of two data log records (each instance including
the row data) and four index log records (each instance including the key
data). The clustering index in particular is prone to filling up the index
pages, causing index splits and merges which also must be logged.
Since
online table reorganization frequently issues internal commits, it usually does not
hold significant logs as active. If there is a time when an online REORG does
hold a large number of active logs, it is during the truncate phase, since
it acquires an S table lock at the truncate phase. If the table reorganization cannot acquire
the lock, it waits and holds the log, and other transactions might fill up
the logs quickly.
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