The applications that your company is developing will need
to run SQL statically against the GSDB database, which is running
on DB2®. In this lesson, you
will bind and run statically the SQL statements that are in the InventoryLevelsData
interface.
Static SQL offers the following advantages:
- Avoidance of of the dynamic statement cache
- Using static SQL reduces contention for DB2's dynamic statement
cache, improving performance for applications that use dynamic SQL.
- Consistency of access paths
- Static SQL makes response times predictable and stable by locking
in access paths before an application runs. By contrast, access paths
for dynamic SQL are computed at run time.
- Potential for improved performance of your applications
- Static SQL can improve the performance of your applications.
- Because access plans are determined before run time, the need
for preparing SQL statements at run time is eliminated.
- Because each statement does not need to be prepared and described,
there is less network traffic between client applications and database
servers.
- Static SQL strictly enforces data types for host variables or
parameters that are used in predicates. This strict enforcement ensures
that input data matches target types in the database.
- Tighter security
- You can grant users the EXECUTE privilege on DB2 packages, rather than granting
them privileges on database objects.
- Ease of revising packages
- Versioning DB2 packages
allows you to rebind packages without the risk of losing prior, better,
access paths.
To bind and run statically the SQL statements that are
in the InventoryLevelsData interface:
- In the Package Explorer, locate the
Default.genProps file in the pureQueryFolder folder in your pureQuery_test Java project: . Double-click the file to open it for editing.
- On a blank line in the Default.genProps file, specify the
package and name of the interface. You can type the package
and name manually, or pureQuery can assist you. To have pureQuery
assist you:
- Hold the CTRL key and press the SPACE bar. A small window opens.
Figure 1. pureQuery listing the
interfaces in the Java project
- Double-click the name of the interface to include the
name on the blank line.
- Type a space, an equals sign, and another space so that
the line looks like this:
com.mycompany.pureQuery.test.InventoryLevelsData =
- Determine which options you can use to set the name of
the collection and the root package name. Hold the CTRL key and press
the SPACE bar. Another small window opens.
Tip: When you click on an option, a description of it appears
next to the list. You can click on the description and use the scroll
bar at the bottom of the yellow window to see the full text.
Figure 2. The options that you can specify for the interface in the Default.genProps
file
- Complete the line so that it looks like this:
com.mycompany.pureQuery.test.InventoryLevelsData = -collection "GOSALES" -rootPkgName "invlevl"
- Save the file and click Yes for
the message that appears. pureQuery needs to store in
the implementation class for the interface the values that you entered
in the Default.genProps file. When you bind the SQL that is in the
interface, pureQuery's StaticBinder utility looks in the implementation
class for these values so that it knows how to create the database
packages.
- Review the structure of the packages that you plan to create. In the SQL Outline view, click the Database
packages tab.
Tip: If the
SQL Outline view
is still showing performance data arranged in a table, click the
Show
tree view or table view button (

).
Although only
one package appears in the view, you are going to create four packages,
one for each isolation level. However, each of the packages will have
the same root name of invlevl and the same SQL statements. If you
wanted to create a package for a single isolation level only, you
could edit the Default.bindProps file. This file lets you specify
options for the StaticBinder utility, which creates the packages.
However, for this tutorial, you can leave the options for the StaticBinder
utility at their default values.
- Bind the package:
- Right-click the name of the package and select Bind. The Select Connection window opens.
- Select the GSDB connection and click Finish.
pureQuery runs the StaticBinder utility
and prints the output from that utility in the Console view:
================================================================================
The StaticBinder utility successfully bound the package 'invlevl1' for the isolation level UR.
The StaticBinder utility successfully bound the package 'invlevl2' for the isolation level CS.
The StaticBinder utility successfully bound the package 'invlevl3' for the isolation level RS.
The StaticBinder utility successfully bound the package 'invlevl4' for the isolation level RR.
The StaticBinder utility successfully bound 'com.mycompany.pureQuery.test.InventoryLevelsDataImpl'.
================================================================================
Results of the StaticBinder utility's activity:
Number of implementation classes and pureQueryXml files for which the bind operation SUCCEEDED: 1
Bind for package invlevl using connection GSDB succeeded.
You can inspect the packages in the Data Source Explorer.
The SQL Outline view makes it easy to find them.
- In the SQL Outline view, right-click
the name of the package and select Find in Data Source
Explorer. The Data Source Explorer opens
and expands the folders of the GSDB connection until it finds and
highlights the packages.
- Run the SQL statements statically. You can
then compare the data with the data that you captured when you ran
the SQL statements dynamically.
- Right-click the project folder and select .
- In the Run Configurations window,
open the configuration that you used to capture the first set of performance
data. In the VM arguments field
on the Arguments page, type this line and then click Apply:
-Dpdq.executionMode="STATIC"
- Click Run.
- In the Project Explorer, right-click the project folder
and select . Then, examine the
performance data.
- In the SQL Outline view, select the Compare check
box. The view is refreshed. If your project contained
more than one saved set of performance data, you could select one
of them in the field next to the Compare check
box.
Figure 3. The controls for comparing sets of performance dataEach column now contains a gray column and a white column. The
gray columns show the current performance data from the static run
of the SQL statements. The white columns show the performance data
from the previous dynamic run of the same SQL statements.
By default, the view compares the time in milliseconds for the
execution time of the statements.
- Use the Show field at the top
of the view to compare the performance data in two other ways:
- Difference
- The gray columns show the time in milliseconds for the current
data set. The white columns show the difference between the times.
For example, here are the statistics for the first statement in the
view.
Table 1. The current time in milliseconds for the statement
compared to the difference between the current time and the previous
timeTotal
Client Time |
Max
Time |
Average
Client Time |
Min
Time |
18.32 |
11.11 |
15.92 |
-0.25 |
6.11 |
3.70 |
1.11 |
1.08 |
The figures show that in the previous data set:- The total run time is 11.11 milliseconds more than in the current
data set
- The maximum run time is 0.25 milliseconds less than in the current
data set
- The average run time is 3.70 milliseconds more than in the current
data set
- The minimum run time is 1.08 milliseconds more than in the current
data set
- Percentage
- The gray columns show the time in milliseconds for the current
data set. The white columns show the difference between the times
as a percentage. Again, here are the statistics for the first statement
in the view.
Table 2. The current time in milliseconds for the
statement compared to the difference as a percentage between the current
time and the previous timeTotal
Client Time |
Max
Time |
Average
Client Time |
Min
Time |
18.32 |
60.66% |
15.92 |
-1.56% |
6.11 |
60.66% |
1.11 |
97.29% |
The figures show that in the previous data set:- The total run time is 60.66% higher than the run time in the current
data set
- The maximum run time is 1.56% lower than the run time in the current
data set
- The average run time is 60.66% higher than the run time in the
current data set
- The minumum run time is 97.29% higher than the run time in the
current data set