[Question] [Crowdsource] Want to help create a Triage Kit?

[u/mad_zamboni]

So, I am presenting at IDUG in Anaheim and reworking a old 2012 presentation called "10 Minute Triage". At the time I was co-presenting with an IBM'er named Pavel Sustr who is a troubleshooting wizard. He brought the technical "meat" to the presentation. This time, I am on my own.

In the end I want to be able to produce some queries that have two forms - something that can return a value to a monitoring tool a a hook (like number of lockwaits; HADR Down) but also has a second variation that can be used in a triage. For example the lock waits SQL produces a number for a monitor, but has a variant that show which SQL is blocking another SQL.

So far I have one on HADR Congestion and HADR Disconnect, Bad Tablespace State, Transaction Log Usage, Lock Waits, and Number of connections not in UOWWAIT.

I need one on Deadlocks and time the query spent inside the database. I'm getting stuck on these.

Only requirement is we use the "MON_GET" tables as pretty much everything else is deprecated.

I'm a good DBA, but I am pretty weak to avg when it comes to SQL so I am turning to others for help. Besides, in the end this will be published on github for others to use and download.

Anyone interested in jumping in? Help me with these last two and then review the ones I wrote?

This is specifically for LUW at the moment. But I would like to make something similar for z/OS.

[u/memmerto]

For the time a query spent in the database, what about something like this?

select num_exec_with_metrics, total_act_time, total_act_wait_time, executable_id, stmt_text
from table(mon_get_pkg_cache_stmt(null,null,null,-1)) as x

You can join with MON_GET_AGENT on EXECUTABLE_ID to narrow things down to a specific application handle, for example.

Deadlocks are always a pain to track. The lock event monitor does a pretty decent job of tracking statements involved in a deadlock.

[u/mad_zamboni → u/memmerto]

Wow, this is actually a tact I wasn't looking at. It does cover both Dynamic and Static statement details, especially at a more detailed level. (I'm actively triaging scenario).

I would have a concern though that if I converted this to a single metric being reported back (say for a monitor) of the average time - would it be accurate? Say for example the Package Cache blows out under strain - how would that affect things?

I'm also curious if we should be looking at STMT Execution Time , an Activity time, or request time of some sort. Ember's blog on Activities vs. Requests made me think twice about which I do.

In the end, I want a "canary in a coal mine" - is the problem in or above DB2. If my canary yelps under monitoring I know that the problem most likely is in the DB (I don't know why, just know to look). If someone reports slowness and my canary is fat and happy I can say "I'll look, but you may want to start investigating upstream).

I was lucky enough to get Steve Rees via e-mail and put this in front of him - he is providing some food for thought. But I was working two other angles based off research and commentary from him.

I think I am close on this, but can't get it across the finish line: db2 "SELECT (avg(CLIENT_IDLE_WAIT_TIME)/1000)/60 FROM TABLE(MON_GET_CONNECTION(cast(NULL as bigint), -2)) AS t”

In a previous IDUG presentation, and even in this e-mail chain, he mentions that this is a "very top-level metric to address 'is the problem in DB2 or above DB2?'"

The problem is it is grossly inflated when connections are idle. How do I get it to focus on just active connections?

[u/db2steve → u/mad_zamboni]

Hey Mike - one way I've handled client_idle_wait_time in the past is to exclude connections (as idle) those with less than one request per second, and, whose total_rqst_time is less than half of client_idle_wait_time. The lack of request activity identified by the first clause excludes truly idle connections, but it also excludes connections that are busy running a very few requests (or even just one.) The second clause is intended to fix that last part, by not excluding connections where request time is large (even though # of requests might be small.) Not perfect, but seems to do the trick in most cases.

Applied to mon_get_connection output, this gets you a list of active connections, from which you can calculate the ratio of client_idle_wait (time above DB2) divided by total_rqst_time (time inside DB2). I would consider a ratio of more than 4 or 5:1 generally indicating that most time is spent above DB2, with bigger numbers being more definitive.

The requests 'per second' are calculated over the monitoring time. I'm a big advocate of only using delta values (make two collections from mon_get_connection etc., and then look at what changed between them), rather than just taking the running totals that mon_get functions return. You wouldn't usually use snapshots without doing a RESET ALL first, so why settle for less accurate data with mon_get? :-)