How to get IP addresses of applications that have consumed most CPU in last 10 minutes in Db2/Linux?



  • 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:

    #!/bin/sh
    

    OPTS=getopt d:t: "$@"
    eval set -- "$OPTS"

    tm=10
    while true ; do
    case "$1" in
    -d) db="$2"; shift 2;;
    -t) tm="$2"; shift 2;;
    --) shift; break;;
    esac
    done

    db2 connect to $db
    if [ $? -ne 0 ]; then
    echo "Unable to connect to $db"
    exit 1
    fi

    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)
    as
    select t2.executable_id
    , 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
    , t2.stmt_text
    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"

    sleep $tm

    db2 -x +c "select * from stmts_delta order by rows_modified desc fetch first 10 rows only"

    db2 rollback

    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"
    

    APPL_ID SUM_TOTAL_CPU_TIME
    *LOCAL.db2inst1.220316094506 2027
    *LOCAL.db2inst1.220316094705 162
    13x.23x.82.116.58680.220316094933 27
    13x.23x.82.116.58788.220316095429 22
    13x.23x.82.116.58790.220316095430 17367
    13x.23x.82.116.58794.220316095447 429

    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)"
    

    1 SUM_TOTAL_CPU_TIME
    13x.23x.82.116 17845

    •                                           22640
      

    Remember to stop the monitor when you are finished




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2