Use pureQuery in dynamic versus static mode for DB2 and Informix

Use 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).

Important: BatchLimit is a configurable property. The default value for DB2 is 100 and for Informix the default value is 0. If we set batchLimit to 0, batching does not occur. For an application to get heterogenous batching for the Informix database backend, batchLimit is configured in the persistence.xml file. An example of how to set batchLimit to 100 is as follows:

<property name="openjpa.jdbc.DBDictionary" value="batchLimit=100"/> 

The Feature Pack for OSGi Applications and Java Persistence API (JPA) 2.0 introduced support for IBM Optim PureQuery Runtime 2.2.0.2 and later. The new feature added for IBM Optim PureQuery Runtime 2.2.0.3, supports Informix, and DB2 applications to use pureQuery in DYNAMIC mode.

This is achieved by setting up pdqProperties on the data source in the Java Enterprise Edition (Java EE) environment. Or, setting pdqProperties on the connection URL in the Java Standard Edition (Java SE) environment.

If pdqProperties is not defined, pureQuery runs in compatible mode, which means that the pdqxml file is packaged in the application JAR files.

There are several topics that reference pdqProperties in the IBM Integrated Data Management information center.

Tasks

1. When defining a data source In a Java EE environment, add a custom property pdqProperties and set a string value to valid IBM Optim PureQuery Runtime properties. In the following table, an example of executionMode(DYNAMIC) is shown to use pureQuery dynamic mode. Any valid IBM Optim PureQuery Runtime property can be specified to get dynamic execution. When the pdqProperties contains the pureQuery Xml(pdqxml-file-location) property, the IBM Optim PureQuery Runtime uses the SQL in STATIC mode found in the pdqxml file. This pureQueryXML property contains the pdqxml file location.

   It is assumed that the pdqxml file is previously bound against the database.

   pdqProperties=	DB2 backend	Informix
   executionMode(DYNAMIC)	dynamic execution	dynamic execution
   executionMode(STATIC), pureQueryXML(c:/temp/ItemEJB.pdqxml) executionMODE(DYNAMIC) and executionMode(STATIC) are required for dynamic and static executions. For static execution mode, the pdqxml file specification follows the IBM Optim PureQuery Runtime documentation. pureQueryXML(pdqxml-file-location) is one of many ways to specify the pdqxml-file-location.The location of the pdqxml file is the path of the pdqxml file. For example, c:/temp/ItemEJB.pdqxml.	static execution	NA (ignored)

2. In a Java SE environment, pdqProperties can be set on the connectionURL. There are two ways to specify the pdqProperties on the connectionURL:

   1. Use openjpa.connectionProperties For example:

      <property name="openjpa.ConnectionProperties"
                value="DriverClassName=com.ibm.db2.jcc.DB2Driver, 
                                       Url='jdbc:db2://localhost:50000/demodb:pdqProperties=
                                           pureQueryXml(C:/wsjpa1/fvt/resources/demo.pdqxml);', 
                Username=myid, Password=secret" />
      

   2. Use openjpa.ConnectionURL. For example:

      <property name="javax.persistence.jdbc.driver" value="com.ibm.db2.jcc.DB2Driver"/>
      <property name="javax.persistence.jdbc.url" 
                value="jdbc:db2:fvt2:pdqProperties=pureQueryXml(C:/wsjpa1/fvt/resources/demo.pdqxml);"/>
      <property name="javax.persistence.jdbc.user" value="myid"/>
      <property name="javax.persistence.jdbc.password" value="secret"/>
      

   The pureQueryXml file location must grant read-write permissions for the IBM Optim PureQuery Runtime to update the file.

   The data source that is defined to run IBM Optim PureQuery Runtime should not be shared with applications that run SQL in JDBC. When pdqProperties is set on the data source of a connection URL, even if the pdqxml file is packaged within the application JAR file, the pdqxml file is ignored. However, if the pdqProperties is not set, the pdqxml file that is in the application JAR file is searched by the runtime (this is the compatible mode). If the pdqxml file is found, then the setting is STATIC execution mode. Otherwise, all SQLs are run in JDBC.

IBM Optim pureQuery Runtime

IBM Integrated Data Management Information Center

Merge utility

System requirements for IBM Optim pureQuery Runtime for Linux, UNIX, and Windows