DSCA detects, corrects, and avoids the problems that go hand in hand with the execution of growing volumes of dynamic SQL statements. What does that mean in detail? Dynamic statements are usually generated during runtime. The result of this generation is often very uniform, which means it is highly likely that statements that have appeared once will reappear again in the same or similar form. This is where DSCA comes in. It monitors the DSC and collects and categorizes statements, and in doing so builds up a “data warehouse of SQL statements.” An image of the workload is created, highlighting weaknesses as well as pointing out the potential for improvement.
Quality assurance for dynamic SQL is therefore quite feasible, although no longer before execution (as is normally done with static SQL) but rather after the data warehouse of SQL statements has been compiled. This source is then analyzed in order that countermeasures can be taken. Typical problems are easy to spot with the help of this amassed data: access path problems, optimizer errors, DSC trashing (statements without parameter markers), CPU-burning SQL, statements with lock problems.
Since they usually have very limited control over the SQL in licensed products, people often say they can’t change anything anyway and end up neglecting the products they purchase. However, this is not necessarily correct. It is these products especially that demand a clear overview of problematic issues. As soon as you’re aware of them (usually only a few statements or statement groups are involved) you can move quickly to resolution:
• Optimize access paths using new or other indexes
• Adjust the buffer pool design in the case of IO problems
• Change table clustering
• Special runstats for complex queries
Static SQL QS is now standard in most settings, problems arise because dynamic SQL is not as carefully monitored. Comprehensive quality assurance for the entire workload is, however, the order of the day. Standard monitors hardly support this process, if at all. The DSCA component in Exception Master for DB2 (XM4DB2) is specially designed for this task. It compiles the SQL data warehouse, evaluates the statement groups according to your quality criteria, and suggests improvements.
Businesses that are not in a position to acquire a dedicated tool can order the Service Pack DSCA. UBS analyzes the DSC and recommends concrete tuning measures for a fixed price. We tell you the causes of any problems, detect wastage, highlight negative developments, and recommend countermeasures – such as those involving indexes, runstats, and parameter markers. Targeted QS brings together what belongs together: dynamic SQL with performance, transparency, resource conservation, uninterrupted operation, and high availability levels.