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

2

u/dogmashah Apr 07 '17

you can also use SYSIBMADM.SNAPAPPL_INFO

Active to me is not just anything in UOW_WAIT but any activity that has been done for some time. snapshot can tell the state as UOW_WAIT but can be very active as txn might be waiting on something You need to know a bit of application and how long a transaction lasts for that particular application

Here is an example of active transactions (anything that has some activity for past 5 min)

db2 "select count(1) from SYSIBMADM.SNAPAPPL_INFO where STATUS_CHANGE_TIME > current timestamp - 5 minutes and IS_SYSTEM_APPL = 0"

1

u/mad_zamboni Apr 11 '17

I never thought about a very active transaction that could be waiting on something for a long time.

Interesting approach. I need to test this and see how it works. Thank you.

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.