Storage pool paging option effect on database performance

The paging option of shared pools can have a significant impact on the performance of reading and changing database files.

  • A paging option of *FIXED causes the program to minimize the amount of memory it uses by:
    • Transferring data from auxiliary storage to main memory in smaller blocks
    • Writing file changes (updates to existing records or newly added records) to auxiliary storage frequently
    This option allows the system to perform much like it did before the paging option was added.
  • A paging option of *CALC might improve how the program performs when it reads and updates database files. In cases where there is sufficient memory available within a shared pool, the program might:
    • Transfer larger blocks of data to memory from auxiliary storage.
    • Write changed data to auxiliary storage less frequently.
    The paging operations done on database files vary dynamically based on file use and memory availability. Frequently referenced files are more likely to remain resident than those less often accessed. The memory is used somewhat like a cache for popular data. The overall number of I/O operations might be reduced using the *CALC paging option.