Note: The information contained in this document has not been submitted to any formal IBM test and is distributed AS IS. The use of this information or the implementation of any of these techniques is a customer responsibility and depends on the customer's ability to evaluate and integrate them into the customer's operational environment. Although IBM may have reviewed each item for accuracy in a specific situation, there is no guarantee that the same or similar results will be obtained elsewhere. Anyone attempting to adapt these techniques to their own environments does so at their own risk.

Any performance data contained in this document were determined in various controlled laboratory environments and are for reference purposes only. Customers should not adapt these performance numbers to their own environments as system performance standards. The results that may be obtained in other operating environments may vary significantly. Users of this document should verify the applicable data for their specific environment.

IBM recently announced Data Studio pureQuery Runtime 2.1, which now supports client optimization for .NET applications. This capability, previously only available for Java applications, enables transformation of .NET applications to use static SQL, without changing a line of code.

In this article, we'll provide a brief overview of pureQuery use with the IBM Data Server Provider for .NET, and the results of a performance study comparing the benefits of pureQuery client optimization for .NET applications.

In a previous performance article, "IBM Data Studio pureQuery Runtime for z/OS Performance," we described the performance and throughput improvements on DB2 for z/OS with Java applications that use pureQuery. These measurements included the use of both the pureQuery API and the client optimization feature, which enables existing Java applications to execute SQL statically, without changing the Java source code.

Since publishing the article, there has been a lot of interest in IBM's pureQuery capabilities from the DB2 for z/OS community because of its ability to lower costs to deploy Java applications, making it easier to manage workloads, and reduce security risks. However, many of IBM's customers use .NET instead of, or in addition to, Java for data access, and they, too, wanted to enjoy these benefits. As of Data Studio pureQuery Runtime 2.1, now they can.

Static Execution for .NET: A quick primer

The client optimization feature of pureQuery allows one to realize all the benefits of static SQL execution. This capability works with .NET access against the following IBM data servers:

• DB2 for Linux, Unix, or Windows V8 and later
• DB2 for z/OS Version 8 and 9, and later through DB2 Connect
• DB2 for i5/OS Version 5 R3 and later through DB2 Connect.

The focus of this article will be on DB2 for z/OS access, although many of the same principles apply for all supported IBM data servers.

Before you begin: You don't need to install additional software on the DB2 for z/OS system. On your Windows server where the application is running, you must install IBM Data Studio pureQuery Runtime 2.1 (or later) along with at least one product that includes the IBM Data Server Provider for .NET (such as the IBM Data Server Runtime Client, version 9.5.3 or later). Finally, you must activate the pureQuery license. All these prerequisites are described in the DB2 Information Center.

Conceptually, client optimization for .NET applications is similar to the process for Java applications described in this developerWorks tutorial, but some of the underlying technology is a little different. The process consists of the following steps as illustrated in Figure 1:

1. Configure the application runtime to support SQL capture and execute the application so the SQL statements can be captured as they are executing.
2. Bind the captured SQL into DB2 packages using a utility included with the prerequisite clients.
3. Execute the application in static mode, and the successfully captured SQL will run statically.

(A developerWorks tutorial walks through this process in more detail.)

Figure 1. Client optimization process for .NET.

The static SQL packages created by the client optimization tooling are just like any other packages DB2 DBAs manage. Using the unique package names created during the bind process (instead of the generic package ID used for dynamic SQL) can greatly reduce the amount of time and frustration it takes to pinpoint performance problems. Figure 2 shows the difference you'll see when using Tivoli OMEGAMON to monitor your DB2 subsystem: Instead of a system-generated package ID (in this case SYSSH200) for all database access under .NET, the DBA has specified a package name of PAWTST1 for a particular subset of statements in a particular application.

These unique package names can also be used by Workload Manager (WLM) to assign Service Classes to packages, allowing thread transaction level prioritization, similar to CICS and IMS/TM. Furthermore, package-based accounting statistics enable charge-back accounting. From a security point of view, static execution offers a tighter security model by enabling execution of just the SQL statements in the package rather than requiring access to an entire table or view.

Figure 2. With static SQL, even .NET applications can have unique package identifiers.