September 28, 2007
The Catalog Cache is like a special memory bufferpool dedicated to catalog objects; it stores information about tables, indexes, views, and other objects to speed up the BIND process for dynamic and static SQL. It CAN be changed online dynamically, but it DOES NOT participate in DB2 9 Autonomic Tuning - SO, you'll have to tune this one yourself. Here's how...
The Catalog Cache Hit Ratio (CATHR)
CATHR = 100 - ( Catalog cache inserts * 100 / Catalog cache lookups )
Use file 'dbsnap2.txt' or a Database Snapshot to file your values for Catalog cache inserts and lookups. Inserts are analogous to Bufferpool Physical Reads, and lookups are analogous to Bufferpool Logical Reads. Why IBM had to call these Inserts and Lookups instead of Physical Reads and Logical Reads is another of the world's greatest mysteries.
Anyway, the objective here is to avoid as many trips to disk (Inserts) as possible so that the requests for data (Lookups) can be satisfied from memory - this will greatly speed up the preparation of your dynamic SQL.
The Catalog Cache Hit Ratio should generally be at least 95%, and most shops are able to achieve this rather easily.
If you find that your CATHR is less than 95%, then you will want to increase DB CFG parameter CATALOGCACHE_SZ in gradual 5% increments, or 16 4K pages, whichever is greater, until such time as you successfully achieve the 95% goal.
The Shameless Marketing Moment
Both Brother-Eagle™ and Brother-Panther™ from DBI compute and monitor the Catalog Cache Hit Ratio. Brother-Panther even provides Performance Trend Charts with correlated change events so that you can see the performance impact of DBM CFG, DB CFG, Bufferpool, Registry, and Index Changes. The Catalog Cache Hit Ratio is just one of about 48 performance metrics that can be graphed over time. The Standard Edition of Brother-Eagle is FREE. Trials of these tools are FREE. Go ahead, we invite you to Take Back Control of Performance, SLAs, and runaway On Demand CPU upgrades in any of your environments. Be a Hero.
Also, be sure to check out DBI's next Webinar which covers DB2 LUW Index Design Tips and Techniques... October 11th... see DBI website for details: www.database-brothers.com
Just for Fun
I found an interesting DB2 related web site the other day. The main URL is www.justdb2.com - no worries if you get forwarded or re-directed to www.db2wisconsin.com ... the web site author apparently owns multiple domains. Do you know of another interesting or helpful DB2 related web site? Please post a comment.
With best regards,
Scott
Scott Hayes
President & CEO, DBI
IBM GOLD Consultant
www.database-performance.info
www.database-auditing.info
Trackback Pings
TrackBack URL for this entry:
http://www.ibmdatabasemag.com/blog/main/archives/2007/09/db2_luw_perform_11.html
« DB2 LUW Performance: Index Design Tips 1 | Main | Heading to LV »
This is a public forum. CMP Media and its affiliates are not responsible for and do not control what is posted herein. CMP Media makes no warranties or guarantees concerning any advice dispensed by its staff members or readers.
Community standards in this comment area do not permit hate language, excessive profanity, or other patently offensive language. Please be aware that all information posted to this comment area becomes the property of CMP Media LLC and may be edited and republished in print or electronic format as outlined in CMP Media's Terms of Service.
Important Note: This comment area is NOT intended for commercial messages or solicitations of business.