Task overview: Data Studio pureQuery
Data Studio pureQuery provides Java Persistence API users an alternative way to access a DB2 database. PureQuery supports static SQL.
JPA in the Java EE and Java SE environments provides optional support for the DB2 pureQuery runtime environment.
PureQuery is a high performance Java data access platform that helps manage applications that access data. PureQuery provides an alternate set of APIs that can be used instead of JDBC to access the DB2 database. newfeat
To use this feature on the appserver, Data Studio pureQuery runtime version 1.2 must be installed. If we plan on performing the DB2 bind command from the admin console or with wsadmin, have pureQuery v1.2 or later. Refer to the Data Studio information center topic on installing pureQuery Runtime for more information.
The appserver offers support for static SQL for EJB 2.x entity beans with the ejbdeploy SQLj option. With JPA, this feature is offered through pureQuery.
DB2 pureQuery offers a number of benefits over JDBC and SQLJ....
- Static SQL offers greater security and control over access to data because applications are only granted authority to execute known SQL.
- Static SQL offers better resource utilization on the DB2 server because it avoids runtime parsing and optimizing of the SQL statements.
PureQuery makes use of DB2 packages, which consist of information for one or more SQL statements and are stored in the DB2 catalog.
To create the packages, run wsdb2gen on a JPA application, which creates a file...
persistence_unit_name.pdqxml...containing pre-generated SQL statements for...
- Create
- Update
- Delete
- Retrieve
- NamedQueries
- NamedNativeQueries
The persistence_unit_name.pdqxml file must be bound against a database.
Associated DB2 packages are generated and the SQL statement is executed statically at runtime.
This persistence_unit_name.pdqxml file must be included into the JAR file. When doing the bind process and when you define the JDBC provider, the following four JAR files must be in the class path:
- db2jcc_license_cisuz.jar
- db2jcc_license_cu.jar
- pdq.jar
- pdqmgmt.jar
There is no support for the QueryTimeout property specified either through the FetchPlan API or through the property plug-in string for wsjpa.ConnectionFactoryProperties. The QueryTimeout value is ignored if specified.
OpenJPA large result processing uses JDBC APIs for scrollable cursors.
JPA sets the pureQuery property, pdq.executionMode, to the value STATIC.
In addition to the JDBC driver JAR file, the JDBC provider configuration must include the JAR file for the pureQuery runtime environment.
OpenJPA provides support to allow application programs to programmatically access and alter the FetchPlan at run time. Altering the fetch plan might result in SQL that has not been generated by the wsdb2gen command at application build time. If this occurs, the SQL is executed dynamically rather than using static SQL from the DB2 package.
If the user makes changes to the application queries, entity mapping or persistence properties, then run the wsdb2gen command and bind again. This process generates and binds the updated DB2 packages.
Input parameter values in JPA queries (with both EJB SQL queries and native SQL queries) cannot be NULL values except in the case of update statements SET expression values. To search for NULL values in a WHERE clause of SELECT, UPDATE or DELETE, then enter the is null predicate instead.
Related tasks
Set to use pureQuery in a Java EE environment
Set to use pureQuery in Java SE environment
Set Persistence Provider support in the appserver
wsdb2gen
wsenhancer
Develop JPA applications for a Java EE environment
Develop and packaging JPA applications for a Java SE environment