On Db2/Linux_x86 version 22.214.171.124 we have several Db2 instances.
iptraf-ng command sysadmin has noticed that one of this Db2 instance has 20-times more output network traffic then others which is unexpected.
There are no user complains of slow working applications and application developers testing production applications of "large network consuming database" and they can not determine any performance penalty for applications.
I have searched the most executed SQL-s from MON_GET_PKG_CACHE_STMT catalog table and few of them have been executed for few millions of times. The most executed SQL is executed 113 million times and this particular Db2 instance is up for 22 days. In average this is same 60 SQLs per second. This SQL and some of the top executed from the list have low volume of data returned, because where conditions produce no data or small amount of data.
We have to investigate why are those SQLs executed so many times, but why network consumption.
How to get list of top most SQLs that produces the most of network traffic? Or probably similar, how to get the list of SQLs that return the most data in bytes?
MON_GET_PKG_CACHE_STMTgive you what you need?
table(mon_get_service_subclass_details(null,null,-2)) as t cross join table(mon_format_xml_times_by_row(t.details))Probably not possible, but you can check if it is possible to relate that somehow to MON_GET_PKG_CACHE_STMT