Using the workbench to configure support for static SQL in pureQuery code

After you create interfaces that declare annotated methods, you can start configuring and creating DB2® packages that contain the SQL statements that you want to run statically.

Before you begin

You cannot run SQL statically if you are using an Oracle database.

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.

Procedure

To use the workbench to configure support for static SQL in pureQuery code:

  1. If you want to group SQL statements from more than one interface into a separate DB2 package, extend one of those interfaces with another interface.
  2. Edit the Default.genProps file by specifying the collection, root names, and versions of the DB2 packages that you want to create.
  3. View the SQL statements that are in your interfaces and information about them.
    When you select your project folder in the Package Explorer or any of the files in your Java project, the SQL Outline view is populated.

    If the view is not open, select Window > Show View > Other. In the Show View window, select Other > SQL Outline.

    The view shows only unbound packages because you have not yet performed the bind operation.

  4. Edit the Default.bindProps file by specifying options for determining how SQL statements are bound into DB2 packages.
  5. Bind the SQL statements that are in interfaces in your Java project by following either of these steps:
    • To bind all of the interfaces in a Java project, right-click the project and select pureQuery > Bind pureQuery Application.
    • To bind one or more interfaces in a Java project, right-click a selection of interfaces and select pureQuery > Bind.
    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.
  6. 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.

    If you want to verify that you created the package in the database, right-click the package in the SQL Outline view and select Show in Data Source Explorer.

    You can recreate DB2 packages with different options or create new versions of them before you run your pureQuery code.

  7. Run your application so that it executes bound SQL statements statically.

Results

You can use the SQL Outline view to help you troubleshoot any problems that you might have with your SQL statements.

If you modify one or more SQL statements in an interface, or add statements to an interface: You can change or create new versions of the DB2 packages that contain those statements.

If you plan to deploy your application in a JAR file: In the workbench, the defaultOptions line in the Default.bindProps file applies to all of the interfaces in your project, except for the interfaces that you specify options for individually in that file. Before you deploy your application in a JAR file, you must ensure that the Default.bindProps file contains an entry for every interface that contains SQL statements that you plan 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 interfaces that are listed in the Default.bindProps file.

To add the interfaces:
  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 interfaces.

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 interface 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