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