How to get IP addresses of applications that have consumed most CPU in last 10 minutes in Db2/Linux?
Laycee last edited by
Db2 v11.1 on Linux I would like to get top 5 SQL statements that consumed the most CPU in last 10 minutes and there corresponding IP addresses.
I queried "package cache" table function and I get SQL statements. This is fine.
SELECT A.STMT_TEXT FROM TABLE(MON_GET_PKG_CACHE_STMT (NULL, NULL, NULL, -2)) AS A WHERE A.LAST_METRICS_UPDATE > (CURRENT_TIMESTAMP) - 10 MINUTES ORDER BY TOTAL_CPU_TIME DESC LIMIT 5
Now I would like to get IP addresses for Db2 clients that executed those SQLs.
I tried to join above "package cache" data with "connections" where IP address is stored:
SELECT B.APPLICATION_ID, A.STMT_TEXT FROM TABLE(MON_GET_PKG_CACHE_STMT (NULL, NULL, NULL, -2)) AS A LEFT OUTER JOIN TABLE(MON_GET_CONNECTION(CAST(NULL AS BIGINT), -2)) AS B ON A.EXECUTABLE_ID = B.LAST_EXECUTABLE_ID WHERE A.LAST_METRICS_UPDATE > (CURRENT_TIMESTAMP) - 10 MINUTES ORDER BY TOTAL_CPU_TIME DESC LIMIT 5
In majority of cases null is returned for APPLICATION_ID field. This makes sense, because "connection" table function holds info about currently connected applications, but when I executed above select statement every 10 minutes, majority of applications have already disconnected from database and so connection info is lost.
Question: How to get IP address for SQLs that have consumed the most CPU in last 10 minutes?
Like mustaccio says you probably should have a look at event monitors. That said, an alternative is to take a snapshot into a temp table, wait a while and then compare a new snapshot with the temp table. Below is an example for a sh script that you can adapt to your needs:
getopt d:t: "$@"
eval set -- "$OPTS"
while true ; do
case "$1" in
-d) db="$2"; shift 2;;
-t) tm="$2"; shift 2;;
--) shift; break;;
db2 connect to $db
if [ $? -ne 0 ]; then
echo "Unable to connect to $db"
db2 +c "create view stmt_metrics
(executable_id, num_executions, rows_read, rows_modified, stmt_text)
as select executable_id, num_executions, rows_read, rows_modified, stmt_text
from TABLE (MON_GET_PKG_CACHE_STMT('d', null, null, -1))"
db2 +c "create global temporary table stmt_samples as (
select * from stmt_metrics)
definition only on commit delete rows"
db2 +c "create view stmts_delta (executable_id, num_executions, rows_read, rows_modified, stmt_text)
, t2.num_executions - t1.num_executions as num_executions
, t2.rows_read - t1.rows_read as rows_read
, t2.rows_modified - t1.rows_modified as rows_modified
from stmt_metrics as t2
, stmt_samples as t1
where t2.executable_id = t1.executable_id"
db2 +c "insert into stmt_samples select * from stmt_metrics"
db2 -x +c "select * from stmts_delta order by rows_modified desc fetch first 10 rows only"
Here is an example of an activity event monitor:
db2 connect to db2 "alter service class sysdefaultsubclass under sysdefaultuserclass collect activity data on all database partitions with details and values"
db2 "create event monitor act_stmt for activities write to table manualstart"
db2 "set event monitor act_stmt state = 1"
You will get a number of tables created, I believe you will be mostly interested in ACTIVITYMETRICS_ACT_STMT.
You can run your workload and then query that table (an example, there are a lot of other metrics that you might be interested in):
db2 "select APPL_ID, sum(TOTAL_CPU_TIME) as SUM_TOTAL_CPU_TIME from ACTIVITYMETRICS_ACT_STMT where EVENT_TIMESTAMP > current_timestamp - 10 minutes group by APPL_ID"
6 record(s) selected.
I replaced two digits with x. If you want to isolate the IP:
db2 "select REGEXP_SUBSTR(APPL_ID, '^\d+\.\d+\.\d+\.\d+', 1, 1), sum(TOTAL_CPU_TIME) as SUM_TOTAL_CPU_TIME from ACTIVITYMETRICS_ACT_STMT where EVENT_TIMESTAMP > current_timestamp - 10 minutes group by REGEXP_SUBSTR(APPL_ID, '^\d+\.\d+\.\d+\.\d+', 1, 1)"
Remember to stop the monitor when you are finished