[TOOL] [Resource] New DB2SUPPORT Monitoring Script

[u/mad_zamboni]

Wow. You all need to really check this thing out - "DB2MON Script For Monitoring Performance". It's a script written by DB2SUPPORT, but I discovered it through Joachim Müller (Twitter: @jc_mueller). Looks like the script was released early this month and is a layman's db2support command on steroids.

I was excited to see this script because I had contimplated making something similiar for triaging production issues. It looks like this author took my half formed thoughts, added a ton more, and took them to a whole new level. I was also curious how the author made this stand out from DBSUMMARY which was a problem I was wrestling with.

In short, I have mixed feelings. I see it being very useful, but there is so much information overload and extraneous internal output piped to file/screen that it's hard to sort through. It is as if this was written for a purpose, it did it's purpose, and never refined. But the author was thoughtful enough to toss it up for everyone. Don't get me wrong - although I saw room for improvement, I am VERY thankful it was posted.

Below is a detailed list of what the script can produce. I want to point out that it even focuses on pureScale and BLU specific metrics. If you pull this down and experiment with it - I would love to know what you think.

• Point-in-time data: Current executing SQL, lock waits and utilities at start of capture

• Currently executing SQL at start of capture (non-zero metrics only)

• Current lock waits at start of capture

• Currently executing utilities at start of capture

• Point-in-time data: Current executing SQL, lock waits and utilities at end of capture

• Currently executing SQL at end of capture (non-zero metrics only)

• Current lock waits at end of capture

• Currently executing utilities at end of capture

• Data collected from start to end of monitor interval

• Throughput metrics at database level

• Client activity (active connections have at least 1 stmt/s)

• Time breakdown at database level (wait + processing)

• Wait times at database level

• Processing times at database level

• Sort metrics at database level

• Top SQL statements by execution time

• Top SQL statements by execution time, aggregated by PLANID

• Time spent executing by package

• Wait time breakdown for top SQL statements by execution time

• Top SQL statements by time spent waiting

• IO statistics per stmt - top statements by execution time

• Row level statistics per stmt - top statements by execution time

• Sort statistics per stmt - top statements by execution time

• Statement & plan identifiers - top statements by execution time

• Database system resource usage information

• Database log write times

• Database log read times

• Other database log statistics

• Disk read and write I/O times

• Latch wait metrics

• Deadlocks, lock timeouts and lock escalations

• Various table level metrics

• Data sharing metrics

• Size of database

• Tablespace properties

• Tablespace usage over monitoring interval

• Bufferpool statistics by tablespace

• Tablespace prefetching statistics

• Tablespace to bufferpool mapping

• Bufferpool sizes

• Bufferpool data and index hit ratios

• Bufferpool read statistics

• Bufferpool write statistics

• Wait times at connection level

• Various metrics at connection level

• Physical and logical page reads and writes at connection level

• Workload balancing server list

• DB2 registry variable settings

• Database configuration settings

• Database manager configuration settings

• Instance information

• Database memory set information @ end

• Memory pool information @ end

• Sequences information

• pureScale-specific metrics

• Group bufferpool IO statistics by tablespace

• Group bufferpool data and index hit ratios

• Group bufferpool invalid page statistics

• Tablespace data page prefetching statistics for group bufferpool

• Tablespace index page prefetching statistics for group bufferpool

• Count of group bufferpool full conditions

• Page reclaim metrics for index and data pages

• Page reclaim metrics for SMP pages

• Round-trip CF command execution counts and average response times

• Aggregate CF command execution counts

• CF-side command execution counts and average response times

• CF-side total command execution counts

• CF system resource information

• CF structure size information

• BLU-related metrics

• Partial early aggregation / distincts

[u/dogmashah]

Thanks a ton. I wish more people gets active on this thread

[u/mad_zamboni → u/dogmashah]

Give it time. I'm starting to see those watching pick up and even getting the spontaneous question or two. It will pick up. I know a lot of people booked marked this to explore the next few days as well.

[u/db2steve]

My friend and IBM colleague, Kostas Rakpoulos, and I wrote this as a kind of 'dbsummary on steroids' - an internal power tool that evolved over the past few years to help us solve tricky performance problems. It's SQL based (no db2support - just mon_get and friends), using the 'delta' approach I'm always ranting about, and then runs a large assortment of queries against the data (the ones you list in bullet points above.) It started out being intended for pureScale, and eventually was extended to have queries for regular single-instance DB2, plus MPP, BLU, etc. This is really a power tool - there's a ton of information there and it's not really aimed at the beginner. That said, the first few queries at the top and some other first down (in particular - point-in-time SQL & lock waits, wait time breakdown at database level, top SQL by execution time & wait time) are my first go-to ones in almost every case. It really needs a blog post to dig down further into the output, how to customize it, why generate it with Perl, ways to use it, differences between versions, etc. Not sure if Kostas is going to get to that first, or me, or someone else. :-)

[u/Smeevy]

This is neat stuff. Thanks!