Overview of using pureQuery with JPA applications and WebSphere® Application Server 7.0 to run SQL statements statically

When you use pureQuery to configure the static execution of SQL statements in JPA applications, you can enjoy the benefits of static SQL and use a number of helpful features in the workbench.

Static SQL offers these 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.

The workbench lets you take advantage of these features:

Automatic configuration of your JPA projects
After you create a JPA project that contains your application, you can enable support for generating pureQueryXML files and for binding the SQL statements that are in those files. When you enable that support, the workbench adds the required JAR files to your project's build path.

The workbench also creates the Default.bindProps file. Use this file to set options for binding SQL statements into the packages. If you generate more than one pureQueryXML file, you can set these options for each.

Do not rename the Default.bindProps file. When you bind captured SQL statements, the workbench expects the bind options to be in a file with this name.

Ease of switching connections for binding against different databases
When you bind the SQL statements that are in a pureQueryXML file, you can bind against the database that is associated with your Java project or against other databases.
pureQueryXML editor
You can use this editor to delete SQL statements from pureQueryXML files. You can also replace individual SQL statements with statements that are better optimized for performance, without changing your application code.
SQL Outline view for seeing the relationships between the objects that you work with
Use this view to see the relationships between your SQL statements, the DB2 packages that the workbench suggests or creates based on your specifications, the Java files that contain the SQL statements, and the database objects that the statements access or refer to.
Java editor that includes assistance for writing and validating SQL statements
If your project contains Java source files, you can write and edit SQL statements by using the Java editor's content assistance and SQL validation features.

Feedback