Task overview: IBM Optim pureQuery Runtime

IBM Optim™ pureQuery Runtime provides Java Persistence API (JPA) with an alternative way to access a database. PureQuery supports static Structured Query Language (SQL).

JPA in the Java EE and Java SE environments provides optional support for the 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 and Informix database.

To use this feature on the application server, install Data Studio pureQuery runtime version 1.2 or later. If we plan to perform the DB2 bind command from the console, or with wsadmin.sh, you must have pureQuery v1.2 or later. Refer to the IBM Optim pureQuery Runtime information center topic on installing pureQuery Runtime for more information.

We can use pureQuery dynamically. The pdqxml file location is specified by the pdqProperties property on the data source or connection URL. For more information, see the topic, Using pureQuery in dynamic mode.

PureQuery uses DB2 packages. These packages consist of information for one or more SQL statements and are stored in the DB2 catalog. To create the packages, the user must first run the wsdbgen command on a JPA application. The wsdbgen command creates a persistence_unit_name.pdqxml file. This file contains pre-generated SQL statements for Create, Update, Delete, and Retrieve, NamedQueries, and NamedNativeQueries of JPA entities. The persistence_unit_name.pdqxml file must be bound against database. Associated DB2 packages are generated and the SQL statement is started statically at run time. This persistence_unit_name.pdqxml file must be included into the application JAR file.

The application server offers support for static SQL for EJB 2.x and later entity beans with the ejbdeploy SQLj option. With JPA, this feature is offered through pureQuery.

There are several benefits to using pureQuery instead of 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.

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

Read more about the DB2 JAR level compliance for pureQuery at the IBM Support website: System requirements for IBM Optim pureQuery Runtime for Linux, UNIX, and Windows.

Restriction:

• 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.

• There is no support for the QueryTimeout property specified through the FetchPlan API. The QueryTimeout value is ignored if specified.

• OpenJPA large result processing uses JDBC APIs for scrollable cursors.

Important:

• 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 for application programs to programmatically access and alter the FetchPlan at run time. Altering the fetch plan might result in an SQL that has not been generated by the wsdbgen command at application build time. If this occurs, the SQL is executed dynamically rather than using static SQL from the database package.

• If the user changes the application queries, entity mapping or persistence properties, run the wsdbgen command and bind again. This process generates and binds the updated database 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.

• Learn how to Configure JDBC providers to use pureQuery to access DB2.

• Learn how to Configure JDBC providers to use pureQuery to access Informix.

• Learn how to Configure data source JDBC providers to use pureQuery in a Java SE environment.

• Learn how to Use pureQuery in dynamic versus static mode for DB2 and Informix.

Subtopics

• Configure JDBC providers to use pureQuery to access DB2
  Use this task to configure the application data source JDBC provider to use pureQuery to access DB2 in a Java EE environment.

• Configure JDBC providers to use pureQuery to access Informix
  Use this task to configure the application data source JDBC provider to use pureQuery to access Informix in a Java EE environment.

• Use pureQuery in dynamic versus static mode for DB2 and Informix
  Using IBM Optim PureQuery Runtime is another way for Java Persistence API (JPA) to access a DB2 and Informix databases. IBM Optim PureQuery Runtime supports static Structured Query Language (SQL).

• Configure pureQuery to use multiple DB2 package collections
  Set up a pureQuery Java Persistence API (JPA) application to use multiple DB2 package collections.

Related tasks

Configure persistence provider support in the application server

Configure data source JDBC providers to use pureQuery in a Java SE environment

wsenhancer command

Develop JPA 2.x applications for a Java EE environment

Develop JPA 2.x applications for a Java SE environment

wsdbgen command

Related information:

IBM Integrated Data Management Information Center
System requirements for IBM Optim pureQuery Runtime for Linux, UNIX, and Windows