DB server-side checks

See Basic health checks to ensure optimal performance for more information. In addition, check the following:

• database configuration matches general guidelines
• all required indexes exist
• table and index statistics are up-to-date
• old versions are cleaned from the system regularly
• DB server physical resource utilization: CPU, memory
• buffer pool settings and buffer pool hit ratio
• need for table and index reorganization

If all of the above checks do not reveal an issue, select some of the identified slow running SQLs and measure their runtime while triggering them from within some native database client, for example DB2® or SQL plus.

• directly on Db2 server side
• on Product Master side

Repeat the tests a couple of times.

If the measured runtimes differ largely between both locations, then a slow network in between both servers are the culprit and needs to be checked.

If the runtimes measured on both servers by using the native database clients are much faster than what is logged in by Product Master services in the db.log files, then this would be an indication that the respective Product Master services JVM is resource that is constrained. Meaning, the JVM threads cannot pick up the returned results fast enough. In this case, JVM profiling should help to reveal the problems.

If measured runtimes are slow on its first execution, but much faster on subsequent executions, it might be an indication that database buffer pool are too small to hold most of the relevant data in memory or data intensive queries are running often, which dispose data from the buffer pool so the data needs to be read from the disk continuously, which is expensive.