Monitors dynamic SQL executed in your z/OS DB2 systems2017-01-19T11:37:28+00:00

Monitors dynamic SQL executed in your z/OS DB2 systems

How do you monitor performance of dynamic SQL queries?
Are you interested in controlling the execution of dynamic SQL without CPU overhead?

Monitoring, explanations and alerts for all dynamic SQL executed in DB2

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.


  • Monitors all dynamic SQL regardless of origin

  • Applys the same quality requirements to all SQL queries, whether static or dynamic

  • Reports “SQL problems” in real-time

  • Continuously tunes performance for all dynamic SQL

  • Locates “hot” consumers and tracks them to measure quality development

  • No expensive DB2 traces required


  • Unique DB2 cache monitoring
  • Alert System specifying thresholds and parameters
  • Cache “snap shots” can be taken at given periods and consolidated to reduce volume of data being evaluated
  • Average CPU Time and Elapse Time for each SQL; easy identification of poor performing SQL
  • Powerful ISPF dialogue enables easy navigation – locates highest no. of rows returned, gets pages or heaviest CPU consumers

  • Detailed explanatory analysis

  • Reports include Period Analysis, Trends, CPU consumers
  • Neutralizes and identifies duplicate SQLs

  • Tags SQL statements and monitors over a long period of time

  • Contributes to collecting execution frequencies

What you can do