Usage
Gives a simple view of memory usage for each monitored DPF 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 DPF 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 |
Partition number of the DPF database |
|
memory_pool_used_gb |
n |
double |
Total memory in use by this database, in GB. Memory usage is measured at the end of the collection interval. |
Example - Memory Usage by DPF Database
Report on memory usage by day for specific DPF database:
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,
member as member,
dec(max(memory_pool_used_gb),17,2) as memory_usage
from IBM_DSM_VIEWS.MEM_DB_TOTAL_USED_DPF
where dbconn_id='sampe_dpf'
and 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, member
order by julian_day, dbconn_id, member
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,
member as member,
dec(max(memory_pool_used_gb),17,2) as memory_usage
from IBM_DSM_VIEWS.MEM_DB_TOTAL_USED_DPF
where dbconn_id in ('gsdb_dpf', 'sample_dpf') 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, member
order by dbconn_id, member, julian_week
Report on memory usage by month for all monitored DPF databases:
select monthname(collected) as month, substr(dbconn_id,1,20) as dbconn_id,
date(min(collected)) as begin_date, date(max(collected)) as end_date,
member as member,
dec(max(memory_pool_used_gb),17,2) as memory_usage
from IBM_DSM_VIEWS.MEM_DB_TOTAL_USED_DPF
where prev_collected >= '2017-06-01-00.00.00.000000' and collected < '2017-08-01-00.00.00.000000'
group by monthname(collected), dbconn_id, member
order by month, dbconn_id, member
Report on memory usage by quarter for all monitored DPF 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,
member as member,
dec(max(memory_pool_used_gb),17,2) as memory_usage
from IBM_DSM_VIEWS.MEM_DB_TOTAL_USED_DPF
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, member
order by year, quarter, dbconn_id, member
Report on max/min/average memory usage by month for all monitored DPF databases:
select year(collected) as year, month_name(collected), substr(dbconn_id,1,20) as dbconn_id,
date(min(collected)) as begin_date, date(max(collected)) as end_date,
member as member,
dec(max(memory_pool_used_gb),17,2) as max_memory_usage,
dec(min(memory_pool_used_gb),17,2) as min_memory_usage,
dec(avg(memory_pool_used_gb),17,2) as avg_memory_usage,
from IBM_DSM_VIEWS.MEM_DB_TOTAL_USED_DPF
where prev_collected >= '2017-06-01-00.00.00.000000' and collected < '2017-08-01-00.00.00.000000'
group by year(collected), monthname(collected), dbconn_id, member
order by year, month, dbconn_id, member
Sample Results
Here is a sample result of daily memory usage of monitor DPF database(connection) "sample_dpf"
JULIAN_DAY DBCONN_ID BEGIN_DATE END_DATE MEMBER MEMORY_USAGE
--------------- -------------------- ------------------- ----------------- ------------ -------------------
2457912 sample_dpf 06/07/2017 06/07/2017 0 0.26
2457912 sample_dpf 06/07/2017 06/07/2017 1 0.23
2457913 sample_dpf 06/08/2017 06/08/2017 0 0.27
2457913 sample_dpf 06/08/2017 06/08/2017 1 0.23
2457914 sample_dpf 06/09/2017 06/09/2017 0 0.27
2457914 sample_dpf 06/09/2017 06/09/2017 1 0.24
6 record(s) selected.