Running SQL statements statically from non-pureQuery API applications

With pureQuery's client optimization, you can run statically, rather than dynamically, the SQL statements that are embedded in Java applications that connect to databases by using JDBC.

Before you begin

Ensure that your system meets the hardware and software requirements. See System requirements for InfoSphere™ Optim™ pureQuery Runtime.

About this task

Client optimization is unlike other technologies that support running SQL statically:
  • It uses no preprocessor, as the COBOL programming language uses for embedded SQL.
  • It does not use a translator or customizer, as SQLJ does.
  • You do not need to hard-code SQL statements into the source of an application.

Rather than requiring these components or changes to your code, client optimization changes how a JDBC driver interacts with an application.

Procedure

To run SQL statements that are in a non-pureQuery API application statically:

  1. Capture the SQL statements that you want to run statically.
  2. Specify options for configuring the DB2® packages that you will create in the next step from the captured SQL statements.
  3. Create and bind the DB2 packages that contain the SQL statements.
  4. Run the non-pureQuery API application so that captured SQL statements run statically.

Example

This example uses the following simple non-pureQuery API application:

import java.sql.*;
import com.ibm.db2.jcc.*;
public class Sample1
{
	public static void main (String[] args) throws SQLException, ClassNotFoundException
	{
		Connection jdbcCon=null;
		try {
			Class.forName("com.ibm.db2.jcc.DB2Driver");
			jdbcCon=DriverManager.getConnection(
				"jdbc:db2://svl01:500/DB2M", "user01", "myPass");
		}
		catch (SQLException e) {
			System.out.println(e);
		}
		try {
			PreparedStatement pStmt = jdbcCon.prepareStatement(
				"INSERT INTO ADMF001.VIEW1(C1, C3, C2) VALUES(?,?,?)");
			pStmt.setString(1, "1");
			pStmt.setInt(2, 3);
			pStmt.setDouble(3, 2.0e3);
			pStmt.executeUpdate();
			pStmt.close();

			Statement selStmt = jdbcCon.createStatement(
				ResultSet.TYPE_FORWARD_ONLY,
				ResultSet.CONCUR_UPDATABLE);
				ResultSet rs = selStmt.executeQuery(
					"SELECT * FROM ADMF001.VIEW1 WHERE C2 > 200");
			System.out.println("moving to the first row");
			if (rs.next()) {
				System.out.println("deleting it");
				Statement stmt = jdbcCon.createStatement();
				stmt.executeUpdate(
					"DELETE FROM ADMF001.VIEW1 WHERE CURRENT OF "
					+ rs.getCursorName());
				stmt.close();
				}
			selStmt.close();
		}
		catch (SQLException e) {
			System.out.println( "Sample1: " + e );
		}
		jdbcCon.close();
	} //end main
} // end class Sample1
Step 1: Capturing SQL statements that are in the application
Run the application in capture mode and run the SQL statements that you are interested in running statically. SQL statements that run successfully are recorded in an XML file, which is referred to as a pureQueryXML file.
One way to set captureMode to ON and to specify a pureQueryXML file is to use a plain-text pdq.properties file that has this content:
pdq.captureMode=ON
pdq.pureQueryXml=Sample1Cptr.pdqxml
After the application completes, a pureQueryXML file exists in the current directory and has the name Sample1Cptr.pdqxml

All of the statements in a single pureQueryXML file are packaged together by the StaticBinder. Therefore, if your application connects to and runs SQL against more than one database, you must create a pureQueryXML file for each Connection URL or DataSource object.

With a larger application, you can choose to capture SQL statements incrementally. You can capture a number of SQL statements during an initial run and capture the rest of them to the same file or files during subsequent runs. However, if you modify the source of the application, other than to modify the URLs for different Connection objects when you set up multiple pureQueryXML files, you must delete your pureQueryXML files and start the process of capturing again.

You should capture SQL statements while testing your non-pureQuery API application, running them against databases that are similar to the databases that you plan to use when you deploy your application to a production environment.

Step 2: Configuring DB2 packages
When you run the Configure utility on a pureQueryXML file, you supply the root package name of the DB2 package that you create in the next step with the StaticBinder. You can also supply the collection ID and version ID, if you do not want to accept the default values. The Configure utility stores this information in the pureQueryXML file, which you supply later to the StaticBinder.
Here is one way to run the Configure utility:
java com.ibm.pdq.tools.Configure -pureQueryXml Sample1Cptr.pdqxml –rootPkgName SMPL1 -collection COLL01

If you run the Configure utility on a pureQueryXML file and then capture more SQL statements in that file, you must run the Configure utility on the file again.

Step 3: Binding SQL statements into DB2 packages
After you run the Configure utility on a pureQueryXML file, you can run the pureQuery StaticBinder utility to package the SQL statements that are listed in the pureQueryXML file.
Here is a one way to run the StaticBinder utility:
java com.ibm.pdq.tools.StaticBinder –url jdbc:db2://svl01:500/DB2M -username user01 -password myPass -pureQueryXml Sample1Cptr.pdqxml
Step 4: Running the application
When you run your application with the execution mode set to STATIC, the information in the pureQueryXML files allows pureQuery to run captured SQL statements statically.
One way to set the execution mode and the pureQueryXML file is to modify the pdq.properties file that you created in Step 1:
pdq.executionMode=STATIC
pdq.pureQueryXml=Sample1Cptr.pdqxml

By default, your application can dynamically run any SQL statements that you did not capture.


Feedback