Personal tools
You are here: Home DB2 How To's How much log space is requeried during an online reorg ?
Navigation
Log in


Forgot your password?
 
Document Actions

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

Security Awareness
Would you like your company to implement gamification into your security awareness program?





Polls