Usage
Gives a simple view of usage of each memory pool for each monitored database. Report can be built to find out memory in use by the selected database as time goes by.
Source
This data comes from the table function mon_get_memory_pool(). The field memory_pool_used is totaled for all memory pools used by the monitored database and scaled to GB. Instance memory pools, such as FCM_* pools, are included. These memory pools are effectively shared memory, referenced by all databases in the instance. If you need to distinguish between memory specific to each database and instance level memory you can see a fine grained breakdown of memory usage in the repository view MON_GET_BUFFERPOOL.
Columns
| Column Name | Delta | Type | Description |
| dbconn_int | n | integer | The internal ID for connection name |
| dbconn_id | n | varchar(255) | The string version of the connection name |
| prev_collected | n | timestamp | Timestamp of the start of the collection interval |
| collected | n | timestamp | Timestamp of the end of the collection interval |
| delta_msec | n | bigint | Number of milliseconds in the collection interval |
| memory_pool_type | n | varchar(32) |
|
| memory_pool_used_mb | n | double | Total memory in use by this database, in GB. Memory usage is measured at the end of the collection interval. |
| memory_pool_used_mb_hwm | n | double |
|
| DBCF_NAME | n | varchar(32) |
|
| VALUE_FLAGS | n | varchar(10) |
|
| VALUE_MB | n | double |
|
| USED_PERCENT_TOTAL | n | double |
|
| MEMORY_SET_TYPE | n | varchar(32) |
|
| MEMORY_SET_USED_MB | n | double |
|
| MEMORY_SET_USED_MB_HWM | n | double |
|
| MEMORY_SET_COMMITTED_MB | n | double |
|
| MEMORY_SET_SIZE_MB | n | double |
|
| NUM_POOLS | n | bigint |
|
| ADDITIONAL_COMMITTED | n | double |
|
Example - Memory Usage by Memory Pool
Report on memory pool usage by day for specific monitor database:
select
julian_day(collected) as julian_day, date(min(collected)) as begin_date, date(max(collected)) as end_date,
memory_pool_type as memory_pool_type, memory_set_type as memory_set_type,
dec(avg(memory_pool_used_mb),17,2) as avg_memory_pool_used_mb,
dec(avg(memory_pool_used_mb_hwm),17,2) as avg_memory_pool_used_mb,
dec(avg(used_percent_total),17,2) as avg_used_percent_total,
dbcfg_name as dbcfg_name, value_flags as value_flags, dec(avg(value_mb),17,2) as value_mb
from ibm_dsm_views.MON_GET_MEMORY_POOL
where
dbconn_id='sample' and
prev_collected >= '2017-10-18-00.00.00.000000' and collected < '2017-10-20-00.00.00.000000'
group by julian_day(collected), memory_pool_type, memory_set_type, dbcfg_name, value_flags
order by julian_day, memory_set_type, avg(memory_pool_used_mb) desc
Report on memory usage by week for selected databases:
select julian_day(collected)/7 as julian_week, substr(dbconn_id,1,20) as dbconn_id,
date(min(collected)) as begin_date, date(max(collected)) as end_date, dec(max(memory_pool_used_gb),17,2) as memory_usage_gb
from IBM_DSM_VIEWS.MEM_DB_TOTAL_USED
where dbconn_id in ('sample', 'sample_dpf', 'sample_hadr__0') and
prev_collected >= '2017-06-01-00.00.00.000000' and collected < '2017-08-01-00.00.00.000000'
group by julian_day(collected)/7, dbconn_id
order by dbconn_id, julian_week
Report on memory usage by month for specific database:
select year(collected) as year, monthname(collected) as month, substr(dbconn_id,1,20) as dbconn_id,
date(min(collected)) as begin_date, date(max(collected)) as end_date, dec(max(memory_pool_used_gb),17,2) as memory_usage_gb
from IBM_DSM_VIEWS.MEM_DB_TOTAL_USED
where dbconn_id='sample' and
prev_collected >= '2017-06-01-00.00.00.000000' and collected < '2017-08-01-00.00.00.000000'
group by monthname(collected), dbconn_id
order by month, dbconn_id
Report on memory usage by quarter for all monitor databases:
select year(collected) as year, quarter(collected) || 'Q' as quarter, substr(dbconn_id,1,20) as dbconn_id,
date(min(collected)) as begin_date, date(max(collected)) as end_date, dec(max(memory_pool_used_gb),17,2) as memory_usage_gb
from IBM_DSM_VIEWS.MEM_DB_TOTAL_USED
where prev_collected >= '2017-06-01-00.00.00.000000' and collected < '2017-08-01-00.00.00.000000'
group by year(collected), quarter(collected), dbconn_id
order by year, quarter, dbconn_id
Report on max/min/average memory usage by quarter for all monitor databases:
select year(collected) as year, quarter(collected) || 'Q' as quarter, substr(dbconn_id,1,20) as dbconn_id,
date(min(collected)) as begin_date, date(max(collected)) as end_date, dec(max(memory_pool_used_gb),17,2) as max_memory_usage_gb,
dec(min(memory_pool_used_gb),17,2) as min_memory_usage_gb, dec(avg(memory_pool_used_gb),17,2) as avg_memory_usage_gb
from IBM_DSM_VIEWS.MEM_DB_TOTAL_USED
where prev_collected >= '2017-06-01-00.00.00.000000' and collected < '2017-08-01-00.00.00.000000'
group by year(collected), quarter(collected), dbconn_id
order by year, quarter, dbconn_id
Sample Results
Here is a sample result of daily memory pool usage of monitor database(connection) "sample"
