Re: What is "*DATABASE Segment Cache"?

Hi Rob,

What is this "*DATABASE Segment Cache"?

The "*DATABASE Segment Cache" refers to storage that is allocated during
the running of SQL queries. Even if you have no queries currently
executing, the storage may still be allocated for use. The two largest
allocations are in the plan cache and in the segment cache proper.

The plan cache is described in more detail at http://ibm.co/1CTqU3h. In
order to provide sorting, cursor stability, SMP capability, and other
functions, the query engine may use temporary data objects like lists,
buffers, or hash tables to implement the query. (These are collectively
referred to as "query runtime objects" in the link above.) When a query is
run through the SQE optimizer, the optimized plan is stored in the plan
cache and, where possible, the query runtime objects are also stored with
the plan cache. This allows a subsequent run of the query to not only skip
re-optimization but also to skip some of the work of re-populating these
temporary objects.

Underneath all of these temporary objects is the segment cache itself.
This cache is used to allow the query engine to avoid some of the overhead
of allocating and de-allocating storage. When a temporary runtime object
is no longer needed, its storage allocation(segments) may be placed back
in this cache, ready for use by the next query.

Currently, you can expect the segment cache proper to use up to 1% of
system storage for "unused" segments. The amount of segment storage used
for temporary objects in the plan cache is limited by the plan cache size.
As described in the link above, this size can be explicitly set, but it is
usually managed by the system.

Is there a way to reduce this which doesn't affect performance

negatively?

Not in most cases. Unless you're really concerned about storage levels in
your system ASP, I would recommend that you let DB2 manage the plan cache
size. The storage used isn't hurting anything, and it is providing a
significant performance boost to any queries that are run multiple times.

Tim Clark
IBM DB2 for i