DB2 Version 9.7 for Linux, UNIX, and Windows

cat_cache_lookups - Catalog cache lookups monitor element

The number of times that the catalog cache was referenced to obtain table descriptor information or authorization information.

Table 1. Table Function Monitoring Information
Table Function	Monitor Element Collection Level
MON_GET_CONNECTION table function - Get connection metrics	REQUEST METRICS BASE
MON_GET_CONNECTION_DETAILS table function - Get detailed connection metrics (reported in DETAILS XML document)	REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics	REQUEST METRICS BASE
MON_GET_SERVICE_SUBCLASS_DETAILS table function - Get detailed service subclass metrics (reported in DETAILS XML document)	REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK table function - Get unit of work metrics	REQUEST METRICS BASE
MON_GET_UNIT_OF_WORK_DETAILS table function - Get detailed unit of work metrics (reported in DETAILS XML document)	REQUEST METRICS BASE
MON_GET_WORKLOAD table function - Get workload metrics	REQUEST METRICS BASE
MON_GET_WORKLOAD_DETAILS table function - Get detailed workload metrics (reported in DETAILS XML document)	REQUEST METRICS BASE

Table 2. Snapshot Monitoring Information
Snapshot Level	Logical Data Grouping	Monitor Switch
Database	dbase	Basic
Application	appl	Basic

For snapshot monitoring, this counter can be reset.

Table 3. Event Monitoring Information
Event Type	Logical Data Grouping	Monitor Switch
Database	event_db	-
Connection	event_conn	-
Statistics	event_scstats (reported in the metrics document)	REQUEST METRICS BASE
Statistics	event_wlstats (reported in the metrics document)	REQUEST METRICS BASE
Unit of work	Reported in the in the system_metrics document.	REQUEST METRICS BASE
Statistics	event_scmetrics*	REQUEST METRICS BASE
Statistics	event_wlmetrics*	REQUEST METRICS BASE
* When returned as part of this logical data group, this element reflects the change in value of this metric since the last statistics collection or database activation, whichever was more recent.

Usage

This element includes both successful and unsuccessful accesses to the catalog cache. The catalog cache is referenced whenever:

a table, view, or alias name is processed during the compilation of an SQL statement
database authorization information is accessed
a routine is processed during the compilation of an SQL statement

To calculate the catalog cache hit ratio use the following formula:

 
  (1 - (cat_cache_inserts / cat_cache_lookups))

indicates how well the catalog cache is avoiding catalog accesses. If the ratio is high (more than 0.8), then the cache is performing well. A smaller ratio might suggest that the catalogcache_sz configuration parameter should be increased. You should expect a large ratio immediately following the first connection to the database.

The execution of Data Definition Language (DDL) SQL statements involving a table, view, or alias will evict the table descriptor information for that object from the catalog cache causing it to be re-inserted on the next reference. In addition, GRANT and REVOKE statements for database authorization and execute privilege of routines will evict the subject authorization information from the catalog cache. Therefore, the heavy use of DDL statements and GRANT/REVOKE statements may also increase the ratio.