Hello again all..
We have a giant application from a giant software vendor that has very
poor SQL.
It's a PLM CAD application, that makes a call to the db for every cad
node in the assembly.
So for an assembly with many parts, from remote locations, the
performance goes out the window.
Locally, it all works fine because network latency is <1ms. But some
remote sites we have (that are growing) latency can be as high as
80-100ms.
We've opened many PMR's with the software vendor, they are somewhat
willing to not help, saying our expectations are too high. Nice..
Anyhow, what we notice when we put traces/network sniffers on to try
and figure out where the slowdown is coming from, are many many calls
to the DB with small simple SQL. Each call is initiated in it's own
bubble and is thus subjected to the 80ms wait. The speed is ok, it's
the latency that is killing it.
We've done many things to try and improve the load times, but with
little success.

Local assembly load time: 12s
Current Remote Assembly load time: 59s

The customer would like to see times in the 20s to be happy.

What I'm wondering is, are there any tools or tricks out there that
could help us? Something to collect/intercept sql and send a bunch of
SQL at once, like at a few second interval, instead of bombarding the
server with hundreds of individual calls? Tuning? Anything we can do
db2 wise to help it?

Any help is much appreciated.
Thanks

Ken