0

On Db2/Linux_x86 version 11.5.8.0 we have several Db2 instances.

With 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?

Thanks

Improve this question
7
  • That kind of metadata (volume of result set) isn't generally recorded by most systems. It's been a long time since I worked with DB2, but could you instead trace a metric like total disk I/O for a SQL to give you an idea which may be generating the most data? Another approach would be to determine which client(s) are receiving all that network traffic, then determine what those clients are doing.
    – pmdba
    Feb 14 at 12:29
  • I have searched for "bytes" in metrics, but can't find one. Also the most traffic is from one single Db2 client that is application server.
    – folow
    Feb 14 at 13:30
  • Doesn't rows_returned from MON_GET_PKG_CACHE_STMT give you what you need?
    – mustaccio
    Feb 14 at 14:11
  • rows_returned metric is for number of rows returned. I don't know if this is very good metric, because we have some tables with few columns and some tables with 300+ columns (this is purchased product). I would really need some bytes metric. Is there any?
    – folow
    Feb 15 at 6:16
  • I haven't examined it very closely, but you can get some information on time spent on network via: 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
    – Lennart - Slava Ukraini
    Feb 17 at 7:06

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.