0

On Db2 v11.5.8.0 on Linux I would like to get how many seconds have elapsed since application issued last request in current connection.

  1. Check monitor switches

    db2 get monitor switches | grep STATEMENT

SQL Statement Information          (STATEMENT) = OFF
  1. Turn on monitor switches

    db2 update monitor switches using statement on

  2. I tried to get this info from SQL:

    db2 "select distinct appl_idle_time from sysibmadm.snapappl order by 1"

    but I get 0.

  3. Then I tried old fashioned way:

    db2 get snapshot for all applications | grep "idle"

    and it is returned:

Application idle time = 26 minutes 32 seconds

Questions:

  1. How to make SNAPAPPL to produce idle time value?
  2. Is there equivalent metric in any MON_GET function?
2
  • Question_1: db2 "update dbm cfg using DFT_MON_STMT ON" Question_2: Can't find equal info in MON_GET tables...
    – folow
    Commented Feb 17, 2023 at 11:48
  • 1
    Are you looking for info on how much the database is waiting for the application? Commented Feb 17, 2023 at 13:26

2 Answers 2

1

There are UOW_START_TIME, UOW_STOP_TIME, PREV_UOW_STOP_TIME metrics of the MON_GET_CONNECTION table function.
Note, that these timestamps are not for requests, but for unit of works (transactions).

As for old snapshot monitoring.
If you are going to use snapshot functions or views like sysibmadm.snapappl, you should know, that you must turn on the corresponding dbm cfg parameter (DFT_MON_STMT in this case) while attached to the instance to make these functions / views return the corresponding metrics (since you turned on the corresponding parameter).
Monitor switches (which you update with the update monitor switches command using the instance attachment, not the database connection) don't affect these functions / views.

0

You may want to have a look at what your Monitor Collect Settings are, Example:

Monitor Collect Settings
 Request metrics                       (MON_REQ_METRICS) = BASE
 Activity metrics                      (MON_ACT_METRICS) = BASE
 Object metrics                        (MON_OBJ_METRICS) = BASE
 Routine data                             (MON_RTN_DATA) = NONE
   Routine executable list            (MON_RTN_EXECLIST) = OFF
 Unit of work events                      (MON_UOW_DATA) = NONE
   UOW events with package list        (MON_UOW_PKGLIST) = OFF
   UOW events with executable list    (MON_UOW_EXECLIST) = OFF
 Lock timeout events                   (MON_LOCKTIMEOUT) = HISTORY
 Deadlock events                          (MON_DEADLOCK) = HISTORY
 Lock wait events                         (MON_LOCKWAIT) = HISTORY
 Lock wait event threshold               (MON_LW_THRESH) = 4294967295
 Number of package list entries         (MON_PKGLIST_SZ) = 32
 Lock event notification level         (MON_LCK_MSG_LVL) = 1

You will find these in the db cfg: db2 get db cfg

Not sure if this is what you are looking for, but you can get the CLIENT_IDLE_WAIT_TIME metric from:

select u.metric_name, u.parent_metric_name,
       sum(u.total_time_value) sum_time, sum(u.count) cnt
from table(mon_get_service_subclass_details(null,null,-2)) as t
cross join table(mon_format_xml_times_by_row(t.details)) as u
group by metric_name, parent_metric_name order by 3

[...]

TOTAL_RQST_TIME        -     3085732              6708609
CLIENT_IDLE_WAIT_TIME  -  3413906658                    -

These are cumulative numbers so if you want to get the numbers for a period of time you can dump the result into a global temp table and wait for a while and the then do a diff between the a new query and the temp table

You can drill down and find what TOTAL_RQST_TIME consists of

One of the first things I do when I get complaints about the response time from the database is to get the ratio between TOTAL_RQST_TIME and CLIENT_IDLE_WAIT_TIME. Most of the time it ends up in: "It's not a database problem" ;-)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.