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
The IBM Data Server Provider for .Net evaluates runtime configuration
settings to determine whether special pureQuery processing is
required. If set to
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
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
pureQueryXml properties to this connection
string, and assign the appropriate values to them:
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 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
captureMode is set to
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):
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.
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.
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
traceFileproperty 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
pureQueryXmlproperty 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
db2caputility 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
bindutility 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
bindutility 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.