MEMORY_POOL table function
The MEMORY_POOL table function returns one row for every pool.
The information returned is similar to the detail seen from the Work System Status (WRKSYSSTS) command.
Authorization: None required.
The schema is QSYS2.
- reset_statistics
- A character or graphic string expression that contains a value
of YES or NO.
If this parameter has a value of YES, statistics are reset such that the time of this query execution is used as the new baseline. The columns that contain this statistical data have names that are prefixed with ELAPSED_. Future invocations of MEMORY_POOL within this connection will return statistical detail relative to the new baseline. If this parameter has a value of NO, statistics are not reset for the invocation. If this parameter is not specified, the default is NO.
The result of the function is a table containing multiple rows with the format shown in the following table. All the columns are nullable.
| Column Name | Data Type | Description |
|---|---|---|
| SYSTEM_POOL_ID | INTEGER | The system-related pool identifier for each of the system storage pools that currently has main storage allocated to it. |
| POOL_NAME | VARCHAR(10) | The name of this storage pool.
The name may be a number, in which case it is a private pool associated
with a subsystem, or one of the following special values.
|
| CURRENT_SIZE | DECIMAL(20,2) | The amount of main storage, in megabytes, in the pool. |
| RESERVED_SIZE | DECIMAL(10,2) | The amount of storage, in megabytes, in the pool reserved for system use (for example, for save/restore operations). |
| DEFINED_SIZE | DECIMAL(20,2) | The size of the pool, in megabytes, as defined in the shared pool, subsystem description, or system value QMCHPOOL. Contains the null value for a pool without a defined size. |
| MAXIMUM_ACTIVE_THREADS | INTEGER | The maximum number of threads that can be active in the pool at any one time. |
| CURRENT_THREADS | INTEGER | The number of threads currently using the pool. |
| CURRENT_INELIGIBLE_THREADS | INTEGER | The number of ineligible threads in the pool. |
| STATUS | VARCHAR(8) | The status of the pool.
|
| SUBSYSTEM_LIBRARY_NAME | VARCHAR(10) | The library containing the subsystem name. Contains the null value for shared pools. |
| SUBSYSTEM_NAME | VARCHAR(10) | The subsystem with which this storage pool is associated. Contains the null value for shared pools. |
| DESCRIPTION | VARCHAR(50) | The description of the shared pool. Contains the null value for private pools or if a description does not exist for a shared pool. |
| PAGING_OPTION | VARCHAR(10) | Whether the system will dynamically
adjust the paging characteristics of the storage pool for optimum
performance.
|
| ELAPSED_TIME | INTEGER | The time, in seconds, since the measurement start time. |
| ELAPSED_DATABASE_FAULTS | DECIMAL(10,1) | The rate, in page faults per second, of database page faults against pages containing either database access paths or data. |
| ELAPSED_NON_DATABASE_FAULTS | DECIMAL(10,1) | The rate, in page faults per second, of nondatabase page faults against pages other than those designated as database pages. |
| ELAPSED_TOTAL_FAULTS | DECIMAL(10,1) | The rate, in page faults per second, of database faults and non-database faults. |
| ELAPSED_DATABASE_PAGES | DECIMAL(10,1) | The rate, in pages per second, at which database pages are brought into the storage pool. |
| ELAPSED_NON_DATABASE_PAGES | DECIMAL(10,1) | The rate in pages per second at which nondatabase pages are brought into the storage pool. |
| ELAPSED_ACTIVE_TO_WAIT | DECIMAL(10,1) | The rate, in transitions per minute, of transitions of threads from an active condition to a waiting condition. |
| ELAPSED_WAIT_TO_INELIGIBLE | DECIMAL(10,1) | The rate, in transitions per minute, of transitions of threads from a waiting condition to an ineligible condition. |
| ELAPSED_ACTIVE_TO_INELIGIBLE | DECIMAL(10,1) | The rate, in transitions per minute, of transitions of threads from an active condition to an ineligible condition. |
| TUNING_PRIORITY | INTEGER | The priority of the shared storage pool used by the system when making automatic performance adjustments. Contains the null value for private pools defined in subsystem descriptions. |
| TUNING_MINIMUM_SIZE | DECIMAL(10,2) | The minimum amount of storage to allocate to the shared storage pool (as a percentage of total main storage). Contains the null value for private pools defined in subsystem descriptions. |
| TUNING_MAXIMUM_SIZE | DECIMAL(10,2) | The maximum amount of storage to allocate to the shared storage pool (as a percentage of total main storage). Contains the null value for private pools defined in subsystem descriptions. |
| TUNING_MINIMUM_FAULTS | DECIMAL(10,2) | The maximum page faults per second to use as a guideline for the shared storage pool. Contains the null value for private pools defined in subsystem descriptions. |
| TUNING_MAXIMUM_FAULTS | DECIMAL(10,2) | The minimum page faults per second to use as a guideline for the shared storage pool. Contains the null value for private pools defined in subsystem descriptions. |
| TUNING_THREAD_FAULTS | DECIMAL(10,2) | The page faults per second for each active thread to use as a guideline for the shared storage pool. Contains the null value for private pools defined in subsystem descriptions. |
| TUNING_MINIMUM_ACTIVITY | DECIMAL(10,2) | The minimum value that the shared pool's activity level can be set to by the performance adjuster when the QPFRADJ system value is set to 2 or 3. Contains the null value for private pools defined in subsystem descriptions. |
| TUNING_MAXIMUM_ACTIVITY | DECIMAL(10,2) | The maximum value that the shared pool's activity level can be set to by the performance adjuster when the QPFRADJ system value is set to 2 or 3. Contains the null value for private pools defined in subsystem descriptions. |
Example
Return all available pool information, both private and
shared, active and inactive. Specify to reset all the elapsed values
to 0.
SELECT * FROM TABLE(QSYS2.MEMORY_POOL(RESET_STATISTICS=>'YES')) X;