Monitoring, explanations and alerts for all dynamic SQL executed in Db2
Whether it comes from an in-house development team or a software product, dynamic SQL has become increasingly more important, and the common usage of dynamic SQL impacts Db2 performance. Unlike static SQL, “explained” and optimized at the BIND, dynamic SQL is bound at run-time, i.e. when a business application executes a query.
For a DBA, it is not possible to know in advance the quality of those queries, nor to control the access path that Db2 will use. They need to monitor the execution on the targeted environments, detect problems and fix the problem by tuning indexes, and/or applications.
Numerous tools can activate and process Db2 traces in order to monitor the execution of the SQL queries. Nevertheless, the overhead generated by the traces drastically limits their usage. A better solution is to monitor the execution of dynamic SQL queries from the Db2 cache, without starting any trace.