Configuring the static execution of SQL statements in non-pureQuery API applications by using the workbench

The workbench provides features for capturing and binding SQL statements in a non-pureQuery API application, and for executing those statements statically when you run that application.

Before you begin

Ensure that your system meets the hardware and software requirements. See System requirements for Optim™ pureQuery Runtime.

The application must be located in a Java™ project in the workbench.

Before performing a bind operation, ensure that the following prerequisites are met.
  • The privilege set of the user that invokes the pureQuery StaticBinder utility must include one of the following authorities:
    • SYSADM authority
    • DBADM authority
    • If the package does not exist, the BINDADD privilege, and one of the following privileges:
      • CREATEIN privilege
      • DB2® for z/OS®: PACKADM authority on the collection or on all collections
      • DB2 Database for Linux®, UNIX®, and Windows®: IMPLICIT_SCHEMA authority on the database if the schema name of the package does not exist
    • DB2 for z/OS: If the package exists, the BIND privilege on the package
    • DB2 Database for Linux, UNIX, and Windows: If the package exists:
      • ALTERIN privilege on the schema
      • BIND privilege on the package
  • DB2 for Linux, UNIX, and Windows: The user also needs all privileges that are required to compile any static SQL statements in the application. Privileges that are granted to groups are not used for authorization checking of static statements. If the user has SYSADM authority, but no explicit privileges to complete the bind, the DB2 database manager grants explicit DBADM authority automatically.
You can use a SQL Management repository created in a database for the following activities when using pureQuery Runtime for the static execution in non-pureQuery API applications:
  • Store SQL statements and related information captured by pureQuery client optimization.
  • Store and manage pureQuery runtime configuration information and pureQueryXML file information.
  • Retrieve the pureQuery information for use by an application enabled for pureQuery client optimization.

Procedure

To configure the static execution of SQL statements in non-pureQuery API applications, these are the basic steps:

  1. Enable pureQuery to capture and bind the SQL statements that are in your application.
  2. Optional: Create JUnit tests for your non-pureQuery API application that run all of the SQL statements that you want to capture. If you do not create unit tests, you must run the application so that it executes all of the SQL statements that you eventually want to run statically.

    For information about JUnits in the workbench, see Writing and running JUnit tests.

  3. Capture the SQL statements that you want to run statically.
    1. Set values for properties that determine how pureQuery captures SQL statements.
    2. Run the application or the JUnit tests.
    After you are finished, the workbench creates in your Java project the pureQueryXML files that you specified. You can repeat this step if you did not capture all of the SQL statements in your application and want to capture the rest of them.
  4. Configure the DB2 packages that you want to create from the SQL statements.
    1. Edit the Default.genProps file by specifying the collection, root names, and versions of the DB2 packages that you want to create.
      After you save the file, refresh the SQL Outline view, which can show you what your DB2 packages would look like, given the values that you specified. To learn about this view, see SQL Outline view.
    2. Optional: Edit or merge your pureQueryXML files.
      These files contain sets of SQL statements. The DB2 packages that you create from the statements are based on these sets. You can change the names of statement sets, move statements into different sets, add alternative statements, and delete statements. You can also merge together pureQueryXML files.
  5. Create the DB2 packages.
    1. Edit the Default.bindProps file by specifying options that determine how the StaticBinder utility binds captured SQL statements into DB2 packages.
    2. Bind the captured SQL statements into packages by following either of these steps:
      • Right-click individual pureQueryXML files in the folder pureQueryFolder and select Bind.
      • Right-click the project folder and select pureQuery > Bind Application. With this method, you can bind the SQL in all of your pureQueryXML files at once.
      The Select Connection window opens so that you can choose the DB2 database that you want to use. You can choose a database that is not associated with your Java project.
      Attention: You cannot perform a bind operation if you are working offline. You must be connected to the DB2 database that you want to bind against.

      If you capture more SQL statements in the pureQueryXML files after this step, you must rerun the bind operation on those files.

    3. View the DB2 packages and the SQL statements that they contain.
      Select the project folder and look on the SQL page of the SQL Outline view to ensure that you created the DB2 packages that you wanted.
      To change the properties of the packages:
      1. Edit the Default.genProps file. If you are creating new versions of the existing packages, use the -pkgVersion option.
      2. Edit the Default.bindProps file.
      3. Repeat the bind operation. The existing DB2 packages are overwritten, unless you used the -pkgVersion option.

      You can still edit either Java source or SQL statements in your application. After you do so, delete your pureQueryXML files and recapture the SQL statements. Then, configure the DB2 packages that you want to create and perform the bind operation again.

      Attention: You do not have to delete your pureQueryXML files if you are only modifying properties for client optimization in a URL for a Connection object or in a Properties object. However, you must be careful not to change the number of lines in the application when you modify these properties.
  6. Run your application.

What to do next

You can use the SQL Outline view to help you troubleshoot any problems that you might have with your SQL statements. Remember that if you modify any of your SQL statements or any other aspect of your application, you must recapture the SQL and re-create your DB2 packages.

If you plan to deploy your application in a JAR file: Before you deploy your application in a JAR file, you must ensure that the Default.bindProps file contains an entry for every pureQueryXML file that the contains SQL statements that you want to bind. The pureQuery StaticBinder utility, which you must run from a command line on the database where you deploy your application, acts upon only the pureQueryXML files that are listed in the Default.bindProps file.

To add the files:
  1. In the folder pureQueryFolder in your project, Right-click the Default.bindProps file and select pureQuery > Add or Remove Entries.
  2. In the Add or Remove Entries window, add all of the required pureQueryXML files.

If you think that you might need to revise the root package names, collection IDs, and versions for your DB2 packages after you deploy the application, you must also ensure that your Default.genProps file contains an entry for every pureQueryXML file that the defaultOptions line applies to. You can also add entries to this file by right-clicking it and using the Add or Remove Entries window.


Feedback