Enable an existing application for pureQuery using client optimization
A key benefit of pureQuery is the ability for database applications to take advantage of static SQL when they may not have been able to before. As mentioned before, static SQL (as defined by DB2) is the ability to precompile an application's SQL before the application is executed for the first time. Having this information can provide insights that may improve performance, problem determination, and security.
Support for static SQL in new application development has been around for a long time for traditional programming languages (for example, COBOL or C) and more recently for Java applications (for example, through Java's SQLJ language extension and the Java pureQuery API). Existing ODBC- and CLI-based applications that were implemented without the programming languages static SQL support can leverage static SQL through DB2's CLI/ODBC Static Profiling feature. With the introduction of the pureQuery client optimization feature, existing .NET applications can now also reap the benefits of static SQL.
The pureQuery client optimization feature was designed with those existing applications in mind, for which a re-implementation of the data access layer (to take advantage of static SQL) is not a viable option. The process of enabling an existing application for pureQuery (or in other words, static SQL) is divided into four phases, which are depicted in Figure 3.
Figure 3. pureQuery client optimization process overview
- Capture: You configure the IBM Data Server Provider for .Net, which is used by .NET applications to access DB2 data servers, to capture the application's successfully executed SQL while that application is running. The captured information is stored in a text file, which is processed in a subsequent phase by a DB2 utility and consumed by the IBM Data Server Provider for .Net.
- Configure: The captured SQL and its associated metadata is enriched with information that enables a DB2 utility to create one or more database packages, which enable the IBM Data Server Provider for .Net to execute SQL statically.
Bind: Use the
db2caputility to process the configured metadata file and create database packages that correlate to the captured SQL.
- Execute: Configure the IBM Data Server Provider for .Net to statically process SQL you had previously captured, configured, and bound.
This process is an iterative process that is typically performed in a test environment. Once the target application has been validated, you can move it and its associated pureQuery-related artifacts (the configured capture file and the database packages) to the production environment.