Calculating the EDM statement cache hit ratio

Start of changeThe EDM storage statistics provide information that can help you determine how successful your applications are at finding statements in the cache and in the catalog.End of change

About this task

Start of changePREPARE REQUESTS ( A ) records the number of requests to search the cache. FULL PREPARES ( B ) records the number of times that a statement went through the optimization process to generate a new runtime structure, which can be interpreted as the number of times a statement was not found in the cache or catalog. LOOK-UP IN CATALOG ( C ) records the number of times that Db2 searched the statement in the catalog. To determine how often the dynamic statement was used from the cache, check the value in CACHE HIT RATIO ( D ). To determine how often a dynamic statement was used from the cache or from the catalog, check the value in CACHE+CATALOG HIT RATIO  E .End of change

Procedure

To calculate the EDM statement cache ratio

Use the following formula:
Start of change
(PREPARE REQUESTS - (FULL PREPARES + LOOK-UP IN CATALOG)) / PREPARE REQUESTS = hit ratio
End of change

Example

The following figure shows the dynamic SQL statements part of the IBM® OMEGAMON® for Db2 Performance Expert on z/OS® statistics report.

Figure 1. EDM storage usage for dynamic SQL statements in the IBM OMEGAMON for Db2 Performance Expert on z/OS statistics report
Start of change
DYNAMIC SQL,STMT,             QUANTITY
---------------------------   --------
PREPARE REQUESTS   A            210225
  FULL PREPARES    B             42681
  SHORT PREPARES                167544

SHORT PREPARES                  167544	
  BASED ON CACHE                154592	
  BASED ON CATALOG               12952 	
                                     
LOOK-UP IN CATALOG    C          42685	

CACHE HIT RATIO       D     (%)  73.54	
CACHE+CATALOG HIT RATIO  E  (%)  79.70	
                                     
TOTAL PREPARES                  210232 	
  EXPLICIT PREPARES             210232 	
  IMPLICIT PREPARES                  0 	

PREPARES AVOIDED                     0 	
CACHE LIMIT EXCEEDED                 0 	
PREP STMT PURGED                     0 	
LOCAL CACHE HIT RATIO (%)          N/C 	
                                      
CSWL - STMTS PARSED                  0 	
CSWL - LITS REPLACED                 0 	
CSWL - MATCHES FOUND                 0 	
CSWL - DUPLS CREATED                 0
End of change

For more information, see the IBM IBM OMEGAMON for Db2 Performance Expert on z/OS Report Reference.