Quality assurance measures for static SQL are commonplace in most installations. This is not the case however for dynamic SQL. Dynamic SQL is all too often shabbily treated although it makes an ever growing part of the load and bears more challenges than EXPLAINed and proven static workload. The peculiar difficulty with dynamic SQL lies in the fact that normally the SQL statements are constructed only milliseconds before being executed. DB2 system changes can have an instant effect on dynamic access paths. Static SQL, in contrast, is only affected after a REBIND. Modified system parameters (zParm) as well as the system environment itself (bufferpool changes) can cause immediate impact on access path selection. This being said, you understand, why production is repeatedly off-guard and why DBAs are surprised by unexpected load patterns. Hence, it performs way below its best or even encounters significant problems. At the bottom line, with dynamic SQL one has fewer control capabilities prior to execution. Problems occur with increasing frequency and there is very little reaction time at the DBA’s disposal.
SQLQAM provides Quality Assurance
SQLQAM is designed to detect the few statements (normally less than 1%) within the thousands that create the performance problems. The tool digs deep and analyses the root causes of the problems and it shows ways to resolve them. Typical issues are inappropriate runstats data, missing / inappropriate indexes, or missing parameter markers. The unique characteristics of SQLQAM allow for a permanent comparison of performance measurement data with optimizer data. Deviations are immediately detected, hence ‘misperceptions’ of the optimizer are pinpointed and countermeasures can be promptly commenced.
Most DBAs hear regularly from the business or application development folk something that is destined to ruin their day: “What was changed? Last week the programs had no performance issues but today it’s a catastrophe.” Measurement data over long comparison periods are retained within the SQLQAM system. With SQLQAM the DBA now has a tool with the capability to follow back through the exact times of any changes in the access path. This gives the ability to trace and understand why exactly the abrupt change in performance took place, e.g. follow which access path was used to which date and time, when it changed and why. more»
One of the highlights of SQLQAM is the Index Advisor component. Thresholds on CPU usage or frequency of usage are used to trigger SQL analysis. The result of the analysis includes reports that list which SQL should be improved as well as absent indices, insufficient column combinations, etc. SQLQAM is able to determine whether a field in an index, say placed in column 5 would be better if placed in column 3. The recommendations are collected and preserved in DB2 tables.
Since SQLQAM is delivered as a web application, it means that everybody in application development has access to its capabilities without any lengthy
installation or training period.
The programmer/analyst can target the relevant programs and statements and customize the analysis as required.
Through the automated access-path analysis, SQLQAM continuously presents programs and SQL statements in need of improvement.
SQLQAM is not just a tool for DBAs who are monitoring the running production and problems encountered in applications. Even application programmers benefit from SQLQAM because they are led directly to the weak points of their apps.
Powered by Hackadelic Sliding Notes 1.6.4