Minimizing the use of real and virtual storage

You can use several techniques to minimize the use of storage by Db2.

About this task

The amount of real storage often needs to be close to the amount of virtual storage.

Real storage refers to the processor storage where program instructions reside while they are executing. Real storage also refers to where data is held, for example, data in Db2 buffer pools that has not been paged out to auxiliary storage, the EDM pools, and the sort pool. To be used, data must either reside or be brought into processor storage or processor special registers. The maximum amount of real storage that one Db2 subsystem can use is the real storage of the processor, although other limitations might be encountered first.

The large capacity for buffers in real storage and the write avoidance and sequential access techniques allow applications to avoid a substantial amount of read and write I/O, combining single accesses into sequential access, so that the disk devices are used more effectively.

Virtual storage is auxiliary storage space that can be regarded as addressable storage because virtual addresses are mapped to real addresses.

Proper tuning of your buffer pools, EDM pools, RID pools, and sort pools can improve the response time and throughput for your applications and provide optimum resource utilization. Using data compression can also improve buffer-pool hit ratios and reduce table space I/O rates.

Procedure

To minimize the amount of storage that Db2 uses:

  • Use less buffer pool storage.
    Using fewer and smaller buffer pools reduces the amount of real storage space Db2 requires. Buffer pool size can also affect the number of I/O operations performed; the smaller the buffer pool, the more I/O operations needed. Also, some SQL operations, such as joins, can create a result row that does not fit on a 4-KB page.
  • Commit frequently to minimize the storage needed for locks.
  • Improve the performance for sorting.
    The highest performance sort is the sort that is avoided. However, because some sorting cannot be avoided, make sorting as efficient as possible.
    For example, assign the buffer pool for your work file table spaces in database DSNDB07, which are used in sorting, to a buffer pool other than BP0, such as to BP07.
  • Provide for pooled threads.
    Distributed threads that are allowed to be pooled use less storage than inactive database access threads. On a per connection basis, pooled threads use even less storage than inactive database access threads.
  • Ensure ECSA size is adequate.
    The extended common service area (ECSA) is a system area that Db2 shares with other programs. Shortage of ECSA at the system level leads to use of the common service area.

    Db2 places some load modules and data into the common service area. These modules require primary addressability to any address space, including the address space of the application. Some control blocks are obtained from common storage and require global addressability.

  • Ensure EDM pool space is being used efficiently.
    Monitor your use of EDM pool storage using Db2 statistics.
  • Use the long-term page fix option for I/O intensive bufferpools.
    Use PGFIX(YES) for buffer pools with a high I/O rate, that is, a high number of pages read or written.
  • Specify an appropriate value for the MAXKEEPD subsystem parameter.
    A larger value might improve the performance of applications that are bound with the KEEPDYNAMIC(YES) option but also keep SQL statement storage allocated when it is not in use. For systems with real-storage constraints, minimizing the value of the MAXKEEPD might reduce storage use.