DB2 Clean Up – Close Out the Past
Within every DB2 LUW or DB2 z/OS systems there are things that always need to be cleaned up. Test systems with unused databases, production jobs that need their schedule tweaked, reorgs for tables or indexes that need to be analyzed and scheduled. One thing that I always seem to find at clients is a large amount of history or large number of image copies that have not been cleaned out of their HISTORY or SYSCOPY DB2 catalog tables.
Running the following inquiries will help identify the backups or other items that are really old in your DB2 system. Make sure to adjust the database name and date parameter to match up with your dataset or GDG base and retention recovery practices.
For DB2 LUW you need to first LIST and then PRUNE the old recovery History/Logfile information. Make sure to run this during a system quiet time as deleting the information locks up the table. Also make sure to coordinate the timestamp or date parameter to your database dataset recovery strategy.
DB2 Command: db2 list history since 200912 for dbname
or
SELECT DBPARTITIONNUM, EID, OPERATION, START_TIME, ENTRY_STATUS
FROM SYSIBMADM.DB_HISTORY
WHERE START_TIME < 20091231000000 WITH UR:
Then:
DB2 Command: db2 prune history 200912
For zOS:
SELECT DISTINCT
DBNAME, TSNAME, DSNUM, ICTYPE, ICDATE, ICTIME
FROM SYSIBM.SYSCOPY
WHERE DBNAME LIKE ′database name mask%′
AND DATE(TIMESTAMP) < CURRENT_DATE – 14 DAY
ORDER BY
DBNAME, TSNAME, DSNUM, ICDATE, ICTIME WITH UR;
Using the query results, you should look to clean up these old items a little at a time the oldest first. Running the PRUNE or MODIFY utility locks up your SYSCOPY table so be careful when you schedule the cleanup.
DB2 Performance Maintenance
Using these queries and cleaning up your system will help the performance of your backups and reorg process since they won’t have to deal with such a large system table. This will also help the performance during a recovery operation so it’s best to keep these DB2 system tables as cleaned up as possible.
I will be coming to the in DB2 Users group in Austin, Texas Tuesday January 18, 2011 and will be giving the following presentations.
Database Performance Discoveries and Recommendations
Java DB2 Developer Performance Best Practices
Location:
Teachers Retirement System,
1000 Red River St, Room 110 West
Austin, TX
I will also be coming to the in DB2 Users group in Houston (http://db2now.com/hadug/), Texas Wednesday January 19, 2011 and will be giving the following presentations.
Database Performance Discoveries and Recommendations
Java DB2 Developer Performance Best Practices
Location:
IBM Room 1553
Two Riverway
Houston, TX 77056
Leave a Reply