Memory preference controls

Memory preference controls can be used as a technique to maximize performance and utilization of resources.

Memory preference controls

Memory preference controls can be used against performance critical database tables, indexes, physical files, and logical files as a technique to maximize performance and utilization of resources. Several approaches are available for controlling the memory preference:
  1. Set Object Access (SETOBJACC) command

    One benefit of SETOBJACC is that you can carve out a separate memory pool that is not used by from any running applications or MEMORY_POOL_PREFERENCE and those objects will then not get paged out because neither applications nor SQE will be using that pool. If the target objects are primarily accessed using Native database I/O,SETOBJACC is the preferred approach.SETOBJACC uses a single thread to bring the object into memory.

  2. Change Physical File (CHGPF) and Change Logical File (CHGLF) commands - Keep in memory (KEEPINMEM) parameter

    When an object is changed to have Keep in memory set to *YES, the database will bring the object into memory and attempt to keep it in memory when it is accessed using SQL via SQE. Native database I/O (for example RPG CHAIN, READ, etc.) does not do this.KEEPINMEM has the ability to use parallel I/O to bring the object into memory.

    • CHGPF KEEPINMEM(*YES|*NO)
    • CHGLF KEEPINMEM(*YES|*NO)
  3. The SQL memory-preference can be used as an alternative to the KEEPINMEM command parameter.

    The behavior of SQL configured in memory objects matches the behavior described in theKEEPINMEM section.

    KEEP IN MEMORY <NO/YES> is available on the following SQL statements:
    • ALTER TABLE
    • CREATE INDEX
    • CREATE TABLE
    • DECLARE GLOBAL TEMPORARY TABLE
Note: The QSYS2/SYSPARTITIONSTAT and SYSPARTITIONINDEXSTAT catalogs can be queried to determine the memory-preference for specific objects. When a memory-preference is specified for an object, the MEMORY_POOL_PREFERENCE QAQQINI option can be used to influence where we attempt to page objects. There is no guarantee that objects will remain in memory.