Calculating the EDM statement cache hit ratio

If you activate the caching of dynamic SQL statements in the dynamic statement cache, the 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.

About this task

PREPARE REQUESTS ( A ) records the number of requests to search the cache. FULL PREPARES ( B ) records the number of times that a statement was inserted into the cache, which can be interpreted as the number of times a statement was not found in the cache. To determine how often the dynamic statement was used from the cache, check the value in GLOBAL CACHE HIT RATIO ( C ).

Procedure

To calculate the EDM statement cache ratio

Use the following formula:

(PREPARE REQUESTS - FULL PREPARES) / PREPARE REQUESTS = hit ratio

Example

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

Figure 1. EDM storage usage for dynamic SQL statements in the Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS statistics report

DYNAMIC SQL,STMT,            QUANTITY
---------------------------  --------
PREPARE REQUESTS   A          8305.3K
  FULL PREPARES    B             0.00
  SHORT PREPARES              8544.5K
GLOBAL CACHE HIT RATIO (%)  C  100.00

IMPLICIT PREPARES                0.00
PREPARES AVOIDED                 0.00
CACHE LIMIT EXCEEDED             0.00
PREP STMT PURGED                 0.00
LOCAL CACHE HIT RATIO (%)         N/C

CSWL - STMTS PARSED              0.00
CSWL - LITS REPLACED             0.00
CSWL - MATCHES FOUND             0.00
CSWL - DUPLS CREATED             0.00

For more information, see the IBM® Tivoli OMEGAMON XE for DB2 Performance Expert on z/OS for DB2 Performance Expert on z/OS Report Reference.