Package cache

What is the package cache?

            DB2 LUW uses many different memory areas to cache critical information. These memory areas are very powerful tools in controlling database performance. The package cache is one such area. This is where DB2 stores static and dynamic queries, access plans, and information about the execution of those queries.

 What information is in the package cache?

            Just like a gold mine, your package cache may seem like a black hole with an entire mountain of information behind it that you must sort through to find the gold nuggets that will make you rich. 

Few of the more interesting pieces of information available are:

Number of executions — the number of times a particular query has been executed

Execution time — the total time spent on all executions of a particular query

Lock wait time — the total time a particular query spent waiting on locks

Sort time — the total time a particular query spent organizing and ordering data

Rows read — the total number of rows a particular query has examined

Rows returned — the total number of rows a query has returned in result sets

CPU time — the total CPU time consumed by a particular query

Execution time — the total time a particular query spent executing

All information in the package cache is cumulative since the last time a particular query was placed in the package cache.

What information is NOT available in the package cache?

While the package cache contains a lot of useful information, it does not replace a SQL statement event monitor. 

The package cache cannot tell you:

             -When a particular query was executed, Some details on static SQL Specific values used in execution of a statement, when that statement uses parameter markers, Literal values used during execution .

How long does information stay in the package cache?

     DB2 moves individual statements in and out of the package cache to keep the most frequently executed statements in memory. Consequently, a statement might be in the package cache for a while before it is overwritten.

You can purge the contents of the package cache :

            FLUSH PACKAGE CACHE DYNAMIC

It is important to note that the execution of this statement can affect performance on a running system, since it will cause the access plans for all dynamic SQL to be re-generated.

Deactivating a database will also cause the package cache to be emptied.