Capture the application's SQL
Traditionally, if you've developed an application that leverages
static SQL, you had to embed special programming language constructs
into the source code that define the SQL operations. These language
constructs had to be processed by a precompiler that generated
additional source code and SQL metadata information. This SQL metadata
was used to generate a database package (the process is called bind),
which contains the SQL access plan that is used to execute the SQL
when the application is running. The generated source code contains
the logic to execute the SQL that was embedded in the database
package.
While pureQuery client optimization's ability to statically process a
.NET application's SQL is based on the same fundamental concept, it is
taking a slightly different approach that does not require any source
code analysis and does not generate any additional source code. The
IBM Data Server Provider for .Net can be configured to execute in a
special mode that captures the SQL that an application is submitting.
This captured SQL and its associated metadata serve two purposes:
- (By the bind utility) to create one or more database packages that
contain the SQL access plans
- (By the IBM Data Server Provider at runtime) to determine whether
a compiled version of an SQL statement is present in a database
package, which enables it to run this SQL statically
Configure the IBM Data Server Provider for .Net to capture SQL
The IBM Data Server Provider for .Net evaluates runtime configuration
settings to determine whether special pureQuery processing is
required. If set to ON, the
captureMode property instructs the data
provider to capture information about each SQL statement that is
successfully executed in a metadata file, which is identified by the
value of the pureQueryXml property.
 |
Configuring pureQuery configuration
properties
Define pureQuery configuration properties in the database
connection string (which is typically done in the application
configuration file) to limit their scope to a single application.
Define these properties in the IBM Data Server driver
configuration file db2dsdriver.cfg to apply them to a data source
that can be shared by multiple applications.
|
|
Let's take a look at an example and assume that your application
connects to the GSDB database. Your regular connection string would
look like the following if the application is accessing a local
database and doesn't provide alternate user id and password:
To enable the Data Server Provider's SQL capture mode and store the
captured SQL in a file, you'd append the
captureMode and
pureQueryXml properties to this connection
string, and assign the appropriate values to them:
database=GSDB;captureMode=ON;pureQueryXml=c:\myapp.xml |
To associate the captured metadata with a unique package identifier,
you have to define two additional connection string properties while
capturing an application's SQL: collection
and rootPkgName. The
collection property identifies the
collection in which the package resides, whereas the
rootPkgName property defines the base
database package name that the bind utility will use to generate one
or, if necessary more, package names. Both properties are considered
mandatory if captureMode is set to
ON.
database=GSDB;captureMode=ON;pureQueryXml=c:\myapp.xml;collection=NULLID;rootPkgName=TOOT |
While running in SQL capture mode, the provider can optionally
generate a trace file that provides basic information about the
capture activity. The trace information identifies, among other
things, how many new (or, in other words, previously not captured in
this pureQueryXml file) SQL statements have been recorded. To activate
tracing, specify the traceFile property and
assign a file name as a value.
A typical database connection string for the pureQuery capture activity
looks like this (a line feed was added to improve readability):
database=GSDB;captureMode=ON;pureQueryXml=c:\myapp.xml;
collection=NULLID;rootPkgName=TOOT;traceFile=c:\pdqcapturetrace.txt |
Now that you've explored how to configure the capture mode for the
Data Server Provider, you are ready to run the application and capture
its SQL. If you are using the testconn20.exe application while you are
working on this tutorial, you will specify the database connection
properties when you run the application. If you are using your own
application, please alter its current database connection string and
append the properties discussed in this tutorial.
Run the example application to capture its SQL
The pureQuery capture operation is completely transparent to the
application. The Data Server Provider is processing the application's
SQL as it would do if it is not configured to exploit the pureQuery
technology. The only indication that you have that a capture activity
is in progress is the growing capture file size and the trace file
information.
If you are using the testconn20.exe application, follow the
instructions below to run it. If you are using your own application,
run it as you would normally do.
- Open a command prompt window and change to the C:\Program
Files\IBM\SQLLIB\BIN directory.
- Enter the following command to run the application (a line feed
was added to improve readability):
testconn20.exe "database=GSDB;captureMode=ON;pureQueryXml=c:\myapp.xml;
collection=NULLID;rootPkgName=TOOT;traceFile=c:\pdqcapturetrace.txt" |
Figure 5. Example
program output while capturing SQL
If the application returns database connection error SQL8029N, the
Data Server Provider was unable to verify the pureQuery license. If
another connection error occurs, verify that the pureQuery property
names and values are correct.
Browse (and configure) the captured SQL
Let's take a look at the trace file information before browsing the
SQL capture file.
- Open the trace file that you've specified as the
traceFile property value using an
editor of your choice. Note that it displays information about how
many SQL statements were executed while the Data Server Provider
was running in capture mode.
Figure 6.
pureQuery capture trace output
- Close the trace file.
- Open the file that you've specified as the
pureQueryXml property value to browse
the captured SQL. Note that this file is a plain text file even
though the property name implies that this file uses XML markup.
You will see in a minute, when this tutorial discusses how to bind
this file using the DB2 tooling, why the capture file currently
does not use XML.
Figure 7.
pureQuery capture file
The file consists of multiple sections, each one containing
either database package configuration information, bind
options, or SQL statement information.
The [COMMON] section associates this capture file with one or
more consistency tokens, which are used internally to
determine whether a certain database package that is present
in a database matches the current SQL capture file. Since you
have not bound this SQL file to a database package yet, no
consistency token has been assigned. The
db2cap utility will update this
section as necessary when it creates the database package.
The [BINDOPTIONS] section defines, as the name implies, the
database package characteristics. The
bind utility uses this information
to create the database package that the Data Server Provider
will use to statically execute SQL. You can customize these
bind options as necessary prior to running the bind operation,
which this tutorial discusses in the next section.
The [STATEMENTx] sections describe the SQL that was captured.
One such section is created for each captured SQL. Each
section contains information about the SQL text, the cursor
characteristics, as well as input and output parameter
information. The bind utility uses
this information when it creates a database package, and the
Data Server Provider uses it to determine whether it can
statically execute an SQL statement. You should not modify
these sections.
- Close the pureQuery capture file.
 |
| 
