Request More Information

We are hiring!

Xtivia, Inc.
2012 North America Liferay Partner of the Year

Join our top-notch team of Enterprise Java and Liferay Portal professionals in our Austin, TX office or work remotely! Please check out our current Dice listings.

Recent Bloggers

Bob Dietrich
Posts: 1
Stars: 0
Date: 6/14/13
KL Hamilton
Posts: 3
Stars: 0
Date: 6/6/13
Luke Smith
Posts: 9
Stars: 0
Date: 5/24/13
Brian Fairchild
Posts: 19
Stars: 0
Date: 5/16/13
Mangesh Mharolkar
Posts: 10
Stars: 0
Date: 5/7/13
Barrie Shaw
Posts: 4
Stars: 0
Date: 4/17/13
Andrew Dalby
Posts: 2
Stars: 0
Date: 4/5/13
Tina Xing
Posts: 1
Stars: 0
Date: 3/29/13
Kaushik Nagaraj
Posts: 7
Stars: 0
Date: 3/27/13
Asier Del Pozo
Posts: 3
Stars: 0
Date: 3/27/13
« Back

Drop the Dead Weight in your DB2 database.

One way to make your DB2 database use less storage and speed up Inserts and Updates is to drop the indexes that are never used.  As the application changes and the database workload changes, you may find many indexes are no longer needed.  Dropping these will free up tablespace pages, not to mention speed up database backups and general database maintenance.

Starting with DB2 version 9.7 there is a great table function called MON_GET_INDEX.  You can see all kinds of index monitoring elements including the INDEX_SCANS.  Once you find indexes that are never scanned, you can drop them

Here is a good query for finding indexes with zero scans.

SELECT substr(T.tabschema,1,10) as TABSCHEMA,substr(T.tabname,1, 20) AS TABNAME,substr(S.INDSCHEMA,1,10) as INDSCHEMA,substr(S.INDNAME,1,20) AS IND_NAME,indextype,T.INDEX_SCANS as INDEX_SCANS FROM TABLE(MON_GET_INDEX('','', -1)) as T, SYSCAT.INDEXES AS S WHERE T.TABSCHEMA = S.TABSCHEMA AND T.TABNAME = S.TABNAME AND T.IID = S.IID  and T.INDEX_SCANS = 0;

Once you identify indexes you would like to drop, generate the DDL needed to recreate the index using your favorite method (ie. db2look) and save it to a file.  Then drop the index and execute RUNSTATS on the table and it's remaining indexes.  Now enjoy a slimmer and faster DB2 database!

 

 

 

 

Comments
Trackback URL:

Join the discussion! Speak your mind! Be the first.

Request More Information