Optimize your existing .NET applications using IBM Data Studio's pureQuery

Improve performance and security for existing .NET applications that access DB2

Patrick Titzler (ptitzler@us.ibm.com), Enablement Architect, IBM

Patrick Titzler is an engineer in the Data Studio Enablement team at IBM's Silicon Valley Lab in San Jose, CA, and focuses on the application development components of the Data Studio portfolio. Prior to joining this team, Patrick was a member of Data Warehousing and OLAP application development teams. Patrick holds a Master's degree in Computer Science from University of Rostock, Germany.

Summary: IBM® Data Studio pureQuery Runtime 2.1 includes a new feature, called client optimization, that enables database administrators and developers to take advantage of the performance and security benefits of static SQL execution against IBM DB2® databases without having to modify their existing custom-developed or packaged .NET applications. In this tutorial, learn how to enable this capability for an existing .NET application.

Tag this!

Date: 26 Mar 2009
Level: Introductory
PDF: A4 and Letter (914 KB | 21 pages)Get Adobe® Reader®

Activity: 628 views
Comments:

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???
Tivoli OMEGAMON: DB2 thread activity listing for dynamic SQL

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.

Comments

Trademarks | My developerWorks terms and conditions

Close [x]

Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Close [x]

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

static.content.url=http://www.ibm.com/developerworks/js/artrating/

SITE_ID=1

Zone=Information Management

ArticleID=378707

TutorialTitle=Optimize your existing .NET applications using IBM Data Studio's pureQuery

publish-date=03262009

author1-email=ptitzler@us.ibm.com

author1-email-cc=