 | Statically run the .NET application's SQL
Configure the IBM Data Server Provider for .Net to statically execute SQL
Once the captured metadata has been configured and bound to a database
package, you can configure the IBM Data Server Provider to statically
execute SQL by setting the executionMode
database connection property to STATIC
(DYNAMIC is the default if this property is
not defined). To determine how an SQL statement can be processed
statically, the provider retrieves, upon startup, the content of the
metadata file (which is identified by the
pureQueryXml property), and matches
incoming SQL requests with the SQL that was previously captured and
mapped to a database package. If the lookup and mapping is successful,
the provider statically executes the SQL. If the lookup fails, the SQL
will be executed dynamically.
database=GSDB;executionMode=STATIC;pureQueryXml=c:\myapp.xml |
If you'd like to determine whether a particular SQL is executed
dynamically or statically, define the connection property
traceFile and assign a log file name to it.
If logging is enabled, the provider will log an entry for each SQL
that it was unable to map to the database package. The following code
shows a typical connection string definition that you would use during
testing to validate that the application behaves as expected when
static SQL execution is enabled:
database=GSDB;executionMode=STATIC;pureQueryXml=c:\myapp.xml;traceFile=c:\pdqtrace.txt |
Run the example application's SQL statically
- Open a command prompt window, and change to the C:\Program
Files\IBM\SQLLIB\BIN directory.
- Enter the following to run the application (a line feed was added
to improve readability):
testconn20.exe "database=GSDB;executionMode=STATIC;pureQueryXml=c:\myapp.xml;
traceFile=c:\pdqtrace.txt" |
The example application (or the program you are using) should
generate the same output as before. Let's verify that all SQL
was executed statically.
- Open the log file that you've specified as the
traceFile property value. If you've
executed the application the same way you did when you captured
its SQL, you should see an output similar to the one shown in
Figure 10:
Figure 10.
pureQuery static SQL execution log file
- Let's try a scenario where you haven't captured all of the
application's SQL. To simulate this scenario, remove one of the
captured SQL statements from the capture file and re-run the
application. If you are using your own application, skip the steps
below and simply run a part of the application for which you
haven't yet captured any SQL.
- Open the file that you've specified as the
pureQueryXml property value to
edit the captured SQL.
- Locate one of the [STATEMENTx] sections, as shown in
Figure 11:
Figure 11. Remove SQL statement from pureQuery
capture file
- Delete all associated SQL metadata entries for this
section. Since you are not invoking the
db2cap bind utility, the
capture file and the database package are now out of sync.
Even though the SQL is present in the database package,
the Data Provider is unable to map the incoming statement
because it is not present in the metadata file.
Figure 12. pureQuery capture file after SQL
metadata has been removed
- Save the modified capture file.
- Run the application again, like you did in step 2 above, and open
the log file. A warning is displayed, indicating that one or more
SQL statements that you've removed (or didn't originally capture)
could not be executed statically.
Figure 13.
Unmatched SQL is displayed in the pureQuery log
file
In a typical scenario where the pureQuery technology is used
to statically run SQL, you'd now re-run the capture process to
iteratively capture SQL that was previously not recorded. This
approach is common during development cycles when new code is
added to the application.
 |
| |
