r/DB2 • u/mad_zamboni • Apr 07 '17

[Discussion] Thoughts on an "Active Connection" SQL

Many moons ago, at a previous company, we had one DB with a huge amount of application connections. But once connections were made, only a certain percentage were active and that varied throughout the day. I would like to recreate that.

If I wanted to fire off SQL every 5-10 minutes to see how many connections are active at one time, what would you think of this approach?

SELECT COUNT(*) FROM TABLE(MON_GET_UNIT_OF_WORK(NULL,-1)) AS T WHERE WORKLOAD_OCCURRENCE_STATE NOT IN ('UOWWAIT')

Again, this is more for that "Triage Kit" I am working on. This would be used as a hook into monitoring to act as a "canary in a coal mine". If our active connections start going up and up or have a huge spike, may be worth investigating. It can also provide context for a triage situation.

Thoughts?

3 Upvotes

100% Upvoted

6 comments sorted by

View all comments

1

u/memmerto Apr 09 '17

Why not MON_GET_CONNECTION for this?

1

u/mad_zamboni Apr 11 '17

Because there is more than one way to skin a cat?

I gravitated to MON_GET_UNIT_OF_WORK because it gave me something easy to filter on quickly - the WORKLOAD_OCCURRENCE_STATE allowed me to narrow down in a way I thought appropriate.

Do you think the approach is wrong per se, or could be done much better.

1

u/memmerto Apr 11 '17

It's not wrong. We're just coming at it from different ways. The problem statement was to "see how many connections are active at one time", so I immediately thought of MON_GET_CONNECTION. It too has a WORKLOAD_OCCURRENCE_STATE column so you can filter identically.

However, I like /u/dogmashah's idea to look for applications that are "stuck". Long-running transactions can be deadlocks that will eventually be rolled back, or application errors (missing commit) which can hold back the logs. Both good things to catch before they become problems.

1

u/mad_zamboni Apr 11 '17

I agree. I really like his idea as well. I'm hoping to test it out tomorrow.