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.