Problem determination, performance and security - why pureQuery could matter to you, the DBA
As a database administrator you are sometimes at the mercy of the developers. You've probably seen your fair share of unpredictable SQL performance and had trouble identifying which .NET application has submitted a particular SQL that is causing issues. This is because .NET applications typically run under a single, generic package identifier.
Let's illustrate with an example. If you are a DBA for a DB2 for z/OS® system, you may be quite familiar with the following Tivoli® OMEGAMON® XE Performance Expert for DB2 monitoring screen (Figure 1):
Figure 1. Tivoli OMEGAMON. Which application is using package SYSxxx00???
With almost all of the dynamic SQL applications using packages like "SYSxxx00", identifying specific programs is difficult. How would you even know which application to look to first for identifying a problem query? Wouldn't it be nice to be able to correlate SQL with its originating application even if the .NET developer didn't think about this beforehand and coded according to best practices? With client optimization, you, the DBA, can determine specific package names to correlate with specific applications, significantly improving your ability to chase down problems in this environment.
A key reason that many people like static SQL, especially when they are paying for CPU usage, is the potential to reduce CPU consumption. With dynamic SQL, there can be significant overhead for determining an access plan, sending prepare messages back and forth, and so on. While the pureQuery client optimization feature won't turn poorly written SQL into a road runner, it eliminates this overhead and can therefore reduce DB2 CPU usage, sometimes significantly.
To illustrate, IBM has run some lab tests using the IRWW benchmark to measure the benefit of static SQL when compared with dynamic SQL for .NET applications. The chart below (Figure 2) shows that in this particular environment, the lab was able to significantly reduce CPU utilization by using static SQL. They even saw good results when DB2 was finely tuned and was able to attain 100% statement cache hit ratios. For more information about this performance study, see the article "More pureQuery Performance: Now with .NET applications accessing DB2 for z/OS" (see Resources).
Figure 2. .NET-based IRWW benchmark average CPU consumption per transaction (lower is better)
In addition, because the access plan is determined ahead of time (at bind time), your applications are much more likely to yield consistent, stable performance.
Finally, from a security point of view, static execution of SQL provides greater control over the SQL that is executed and can help reduce the risk of SQL injection, which is a known security risk for dynamic SQL. This is because with static packages, you only need to authorize users to execute the package, which means they are limited to only executing SQL that's in the package. With dynamic SQL, you need to provide access to the underlying data object.
If you're interested in learning more about how pureQuery client optimization can help you transform your existing .NET applications to use static SQL (without modifying any source code!), then read on.