DB2 table reorg and table truncate

20 May,2011 by Jack Vamvas

I received a db2  Tivoli monitoring message , warning  of  disk space issues.

 TRV_DB2_UsedSpace_Minor db2inst1:myserver:UD Fri May 13 16:57:21 2011

Description                 

 I executed a Linux find command 

find /db2inst1 -type f -ls | sort -k 7 -r -n | head -10

 The 10 biggest files in the directory /db2inst1

 This pointed to the Datawarehouse

 

21141913600

 /dbinst1/db2inst1/NODE0000/SQL00002/itmreg8k/SQL00132.DAT

18296864768

 /dbinst1/db2inst1/NODE0000/SQL00002/itmreg8k/SQL00293.DAT

18238668800

 /dbinst1/db2inst1/NODE0000/SQL00002/itmreg8k/SQL00231.DAT

8841330688

 /dbinst1/db2inst1/NODE0000/SQL00002/itmreg8k/SQL00293.INX

5305532416

 /dbinst1/db2inst1/NODE0000/SQL00002/itmreg8k/SQL00299.DAT

4767088640

 /dbinst1/db2inst1/NODE0000/SQL00002/itmreg8k/SQL00301.DAT

4516478976

 /dbinst1/db2inst1/NODE0000/SQL00002/itmreg8k/SQL00301.INX

4393009152

 /dbinst1/db2inst1/NODE0000/SQL00002/itmreg8k/SQL00299.INX

3483893760

 /dbinst1/db2inst1/NODE0000/SQL00002/itmreg8k/SQL00287.DAT

3235643392

 /dbinst1/db2inst1/NODE0000/SQL00002/itmreg8k/SQL00012.DAT

 To be on the safe side I checked out the growth stats of the other databases – growth rates . They looked OK

 db2 "CALL GET_DBSIZE_INFO (?, ?, ?, -1)"

 The database size  is 180 GB – and growing .

 I issued a full reorg – on the Datawarehouse database , this had no impact. This is part of a regular maintenance anyway –

 I sent a list of tables , with all sizes in kilobytes  , using the DB2 table size shell script .

 A request to the owner for a list of tables . allowed me to truncate DB2 tables, with the following script 

 ALTER TABLE schema.table_name activate not logged initially with empty table;

 I reran the reorg maintenance job – after the truncate . The space is reclaimed

Read More

How to monitor a DB2 REORG

Reorg table DB2

DB2 Used Space and REORG

Author: Jack Vamvas(http://www.dba-db2.com)

Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been saved. Comments are moderated and will not appear until approved by the author. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on DB2 table reorg and table truncate

Comments are moderated, and will not appear until the author has approved them.


dba-db2.com | DB2 Performance Tuning | DBA DB2:Everything | FAQ | Contact | Copyright