Dynamic SQL – A new Tuning Challenge

With the increasing rate of ERP and CRM applications accessing DB2 data, Database Administration is challenged by ever-growing Dynamic SQL workloads. Applications developed to work under any DBMS behave different than applications that are specifically designed for DB2. Also the increasing amounts of dynamic requests from QMF, SPUFI, Java JDBC, .NET and the like make quality assurance difficult. Thus for people with DB2 responsibility, there is undoubtedly a new tuning challenge.

DB2 performance tools fail to provide the required insight into Dynamic SQL processing. They do not serve as reliable indicators of system degradation and increased use of CPU time due to inefficient dynamic SQL impacts. XM4DB2™ closes this gap. The tool allows ongoing, simultaneous supervision and the efficient assessment of any number of production DB2 systems.

The strategy of XM4DB2 (“XM”) is based on various quality thresholds. DBAs cannot constantly sit in front of monitors and wait for problems to occur. Monitoring must be automated. For example:

• XM measures the workload at regular intervals and checks the results against predefined thresholds.

• XM identifies the critical SQL statements, the “trouble makers” and “CPU burners.”

• XM's Alerting Component alerts database administration and gives them concise, to the point analysis.

• Problems occurring at night can also be analysed the next day. Measurement data is kept and can be used for comparisons and trend analysis.

• An SQL inventory is build over time, SQL statements, their frequency and behaviour in respect of resource consumption are correlated.

• XM's inventory allows access path analysis and index optimization.

XM not only looks at single SQL statements, it considers statement groups. Example:

SELECT * FROM CUSTOMER WHERE KEY = '404002'

It is easy to determine from Dynamic Statement Cache (DSC) how much CPU, IO, Lock contention this statement caused. Normally these values are small and unobtrusive. However, if this statement is invoked many times over, then closer examination may be warranted. XM consolidates and summarizes the measurement data gathered from DSC. It turns mountains of data into meaningful information. For example, XM will tell you:

• that the measured values were x-times higher than optimizer's estimation. Runstats don't fit?

• that there is a substantial deviation between Examined Rows and Processed Rows. Index missing?

XM's proven technique of Exception Alerting ensures that DBAs are precisely informed about real problems and are saved from senseless message noise. Quality assurance in the area of dynamic SQL becomes more and more important. XM can help.

XM deploys its unique approach of Exception Alerting not only in the field of dynamic SQL but for other key issues, including, impending lack of space for table- and indexspaces, restricted states and utilities, stopped procedures, in-doubt threads, plans and packages, DDF, IO and buffer pool performance.

XM helps Database Administration meet the challenges of growing and diverse DB2 workloads. Savings come from lowering the cost of avoidable CPU charges, reducing labor intensive diagnosis tasks, and improving service levels.