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.
| 
