Running SQL statements statically from Java Persistence API (JPA) applications run under WebSphere Application Server 7.0

The workbench provides features for running the wsdb2gen command to create pureQueryXML files, binding SQL statements, and executing those statements statically when you run your JPA application.

Before you begin

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

The application must exist in a JPA project in the workbench. You can either import the application into a JPA project or develop one in the workbench. If you want to develop one, see the user guide for the Eclipse Dali JPA Tools at http://www.eclipse.org/webtools/dali/. For the Apache OpenJPA User Guide, see the "OpenJPA Documentation" page at http://openjpa.apache.org/documentation.html.

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.

About this task

The steps in this task elaborate on step 4 in these topics in the documentation for WebSphere® Application Server 7.0:

Procedure

To configure the static execution of SQL statements in JPA applications that use Websphere Application Server 7.0:

  1. Enable pureQuery support in your JPA project. You must configure JPA projects to support pureQuery.
    1. Right-click your JPA project and click Data Access Development. Then click either Add Data Access Development Support or Add pureQuery Support. Follow the steps in the Add Data Access Development Support wizard. For information about the controls on the wizard, click Help in the lower-left corner of the wizard. If the help does not appear, select a control on the Add pureQuery Support page and then click Help again.

    After you click Finish, the workbench creates the folder dataAccessFolder in your JPA project. This folder contains the Default.bindProps file.

    If you want to disable the capturing and binding of SQL statements that are in non-pureQuery API applications and remove the support for pureQuery from the JPA project, right-click the project and select Data Access Development > Remove pureQuery Support, and then select Data Access Development > Remove Data Access Development Support.

  2. Import the com.ibm.ws.jpa.jar file into your project and add it to your buildpath. The file comes with WebSphere Application Server 7.0.
  3. Create the pureQueryXML files for your JPA application by right-clicking the file persistence.xml in your JPA project and selecting Data Access Development > Generate pureQuery XML Files. This step takes the place of running the wsdb2gen command manually.
  4. Optional: View the SQL statements that are in your pureQueryXML files and information about them.
    When you select your project folder in the Package Explorer or any of the files in your project, the SQL Outline view is populated. If the view is not open, right-click the project and select Data Access Development > Show SQL Outline.
    • On the Database page, you can see the database objects that the SQL statements refer to.
    • On the Java page, you can see the Java files that contain the captured SQL statements. If your project contains Java source files, you can double-click an SQL statement to open the corresponding source file in the Java editor.
    • On the SQL page, you can see the DB2 packages that the workbench suggests for the captured SQL statements. At this point, because you did not yet perform the bind operation, the view shows only the packages that the workbench will create when you do perform the bind.
  5. Optional: Edit the pureQueryXML files for your application.
  6. Edit the Default.bindProps file by specifying options that determine how the StaticBinder utility binds captured SQL statements into DB2 packages.
  7. Bind the captured SQL statements into packages by following either of these steps:
    • Right-click individual pureQueryXML files in the folder dataAccessFolder and select Bind.
    • Right-click the project folder and select Data Access Development > 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.

    1. Bind the captured SQL statements into packages by following either of these steps:
      • Right-click individual pureQueryXML files in the folder dataAccessFolder and select Bind.
      • Right-click the project folder and select Data Access Development > 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.

  8. Optional: 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.bindProps file.
    2. Repeat the bind operation.

    You can still edit either Java source or SQL statements in your application. After you do so, delete your pureQueryXML files and regenerate them. Then, perform the bind operation again.

  9. Test your application by running it. If the SQL statements for your application are bound in the collection in which the packages for the driver are located, you do not need to perform this step.

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 regenerate the pureQueryXML files and recreate 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 dataAccessFolder in your project, Right-click the Default.bindProps file and select Data Access Development > Add or Remove Entries.
  2. In the Add or Remove Entries window, add all of the required pureQueryXML files.

Feedback