Usage
Throughput_system can provide a view of CPU and swap consumption by database.
Source
The data in throughput_system comes from the table function env_get_system_resources and env_get_system_resources.
Columns
As with all repository views, the columns marked Y in the “Delta” column are actually two columns. For example, the metric described here as “db2_cpu_user” is actually a pair of columns “db2_cpu_user” which is a counter of how many rows have been read by activities assigned to this workload since the data server was activated and “db2_cpu_user_delta”, which tells cpu utilization by database in the collection interval. For most purposes, the _delta version of the metrics is the most useful. The raw counter version is provided for completeness.
|
Column Name |
Delta |
Type |
Description |
|
dbconn_int |
n |
integer |
The internal ID for connection name. Included for completeness. |
|
dbconn_id |
n |
varchar(255) |
The string version of the connection name. When the user configures a database to be monitored, this is the name they specified for the connection. |
|
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 |
|
db2_cpu_user |
Y |
bigint |
User cpu utilization by db2 |
|
db2_cpu_system |
Y |
bigint |
System cpu utilization by db2 |
|
cpu_user |
Y |
bigint |
User cpu utilization by OS |
|
cpu_system |
Y |
bigint |
System cpu utilization by OS |
|
cpu_iowait |
Y |
bigint |
CPU io wait time |
|
cpu_idle |
Y |
bigint |
CPU idle time |
|
cpu_load_long |
n |
bigint |
|
|
swap_page_in |
Y |
bigint |
|
|
swap_page_out |
Y |
bigint |
|
|
swap_page_size |
n |
bigint |
|
Example - CPU Utilization
Report on CPU utilization 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, sum(db2_cpu_user_delta) as db2_cpu_user_util
db2_cpu_user_percentage, db2_cpu_system_percentage, db2_cpu_percentage, cpu_user_percentage, cpu_system_percentage, cpu_percentage
from IBM_DSM_VIEWS.THROUGHPUT_SYSTEM
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 year(collected) as year, week(collected) as 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
db2_cpu_user_percentage, db2_cpu_system_percentage, db2_cpu_percentage, cpu_user_percentage, cpu_system_percentage, cpu_percentage
from IBM_DSM_VIEWS.THROUGHPUT_SYSTEM
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, sum(db2_cpu_user_delta) as db2_cpu_user_util
db2_cpu_user_percentage, db2_cpu_system_percentage, db2_cpu_percentage, cpu_user_percentage, cpu_system_percentage, cpu_percentage
from IBM_DSM_VIEWS.THROUGHPUT_SYSTEM
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
db2_cpu_user_percentage, db2_cpu_system_percentage, db2_cpu_percentage, cpu_user_percentage, cpu_system_percentage, cpu_percentage
from IBM_DSM_VIEWS.THROUGHPUT_SYSTEM
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
db2_cpu_user_percentage, db2_cpu_system_percentage, db2_cpu_percentage, cpu_user_percentage, cpu_system_percentage, cpu_percentage
from IBM_DSM_VIEWS.THROUGHPUT_SYSTEM
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 CPU utilization 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.