< Previous | Next >

Lesson 6: Running the SQL statements statically

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:

  1. In the Package Explorer, locate the Default.genProps file in the pureQueryFolder folder in your pureQuery_test Java™ project: pureQuery_test > pureQueryFolder > Default.genProps. Double-click the file to open it for editing.
  2. 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:
    1. Hold the CTRL key and press the SPACE bar. A small window opens.
      Figure 1. pureQuery listing the interfaces in the Java project
      The Content Assistance window
    2. Double-click the name of the interface to include the name on the blank line.
  3. Type a space, an equals sign, and another space so that the line looks like this:
    com.mycompany.pureQuery.test.InventoryLevelsData = 
  4. 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
  5. Complete the line so that it looks like this:
    com.mycompany.pureQuery.test.InventoryLevelsData = -collection "GOSALES" -rootPkgName "invlevl"
  6. 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.
  7. 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 (The Show tree 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.

  8. Bind the package:
    1. Right-click the name of the package and select Bind. The Select Connection window opens.
    2. 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.
  9. 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.
  10. Run the SQL statements statically. You can then compare the data with the data that you captured when you ran the SQL statements dynamically.
    1. Right-click the project folder and select Run As > Run Configurations.
    2. 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"
    3. Click Run.
  11. In the Project Explorer, right-click the project folder and select pureQuery > Refresh Project in SQL Outline. Then, examine the performance data.
    1. 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 data
      The controls for comparing sets of performance data

      Each 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.

    2. 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 time
      Total Time Max Time Average 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 time
      Total Time Max Time Average 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
< Previous | Next >

Feedback