Usage
Gives a simple view of memory usage for each monitored database. Report can be built to find out total 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 |
|
member |
n |
smallint |
|
|
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 usage by day for all monitor databases:
select julian_day(collected) as julian_day, 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 julian_day(collected), dbconn_id
order by julian_day, dbconn_id
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 usage of monitor database(connection) "sample"
JULIAN_DAY DBCONN_ID BEGIN_DATE END_DATE MEMORY_USAGE_GB
------------- ---------------------- ----------------------- --------------- -------------------
2457934 sample 06/29/2017 06/29/2017 0.39
2457935 sample 06/30/2017 06/30/2017 0.40
2457936 sample 07/01/2017 07/01/2017 0.36
2457937 sample 07/02/2017 07/02/2017 0.36
2457938 sample 07/03/2017 07/03/2017 0.36
2457939 sample 07/04/2017 07/04/2017 0.36
2457940 sample 07/05/2017 07/05/2017 0.36
2457941 sample 07/06/2017 07/06/2017 0.37
2457942 sample 07/07/2017 07/07/2017 0.29
2457943 sample 07/08/2017 07/08/2017 0.29
2457944 sample 07/09/2017 07/09/2017 0.30
2457945 sample 07/10/2017 07/10/2017 0.30
2457946 sample 07/11/2017 07/11/2017 0.30
2457947 sample 07/12/2017 07/12/2017 0.29
2457949 sample 07/14/2017 07/14/2017 2.60
2457950 sample 07/15/2017 07/15/2017 0.30
2457951 sample 07/16/2017 07/16/2017 0.30
2457952 sample 07/17/2017 07/17/2017 0.30
2457953 sample 07/18/2017 07/18/2017 0.31
19 record(s) selected.