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


Forgot your password?
 
Document Actions

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

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





Polls