Whether it comes from an in-house development or a software product, the part dynamic SQL plays has become increasingly more important. 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 DBA, it is not possible to know in advance the quality of those queries, neither to control the access path that DB2 will use. They need to monitor the execution on the targeted environments, detect problems and fix them 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.