Usage
Bottle_ related repository views are used to get top consuming units of work to show what database transactions are consuming the most resources in the database server.
List of Views
- BOTTLE_CPU
- BOTTLE_DIRECT_READS
- BOTTLE_DIRECT_WRITES
- BOTTLE_ELAPSED_TIME
- BOTTLE_FCMRW
- BOTTLE_FED_ROWS_READ
- BOTTLE_FED_WAITS_TOTAL
- BOTTLE_FED_WAIT_TIME
- BOTTLE_LOCK_WAIT
- BOTTLE_LOG_SPACE
- BOTTLE_MEMORY
- BOTTLE_NUM_LOCKS
- BOTTLE_QUERY_COST
- BOTTLE_ROWS_MOD
- BOTTLE_ROWS_READ
- BOTTLE_ROWS_RETURNED
- BOTTLE_SORTS
- BOTTLE_SORT_OVERFLOWS
- BOTTLE_SORT_TIME
- BOTTLE_WAIT_TIMES
- BOTTLE_WLM_QTIME
Source
The data in bottle_related repository views comes from the table function mon_get_unit_of_work and wlm_get_service_class_workload_occurrences_v97.
Columns
All Bottle_ related repository views have same table structure.
|
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 |
|
application handle |
n |
bigint |
|
|
uow_id |
n |
integer |
|
|
resource_value |
n |
bigint |
|
|
avg_wo_max |
n |
bigint |
|
|
application_name |
n |
varchar(128) |
|
|
session_auth_id |
n |
varchar(128) |
|
|
workload_name |
n |
varchar(128) |
|
Example -
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