Overview

After you install InfoSphere™ Optim™ pureQuery Runtime, you can deploy your pureQuery or pureQuery-enabled Java applications to production environments. You can also develop DB2® CLI and Microsoft .NET applications.

Features for Java applications

For applications that use the pureQuery API, InfoSphere Optim pureQuery offers these features:

Provides a single API that you can use to run SQL statements against databases, in-memory collections, and Iterator objects

The Java platform provides a multitude of data structures and APIs for processing in-memory collections and external data from various sources. Each of these features is perfectly suited for solving a specific type of problem. pureQuery also has a unique set of interfaces for retrieving and modifying the data within the collections. For example, pureQuery provides an API for Java Arrays, Lists, Maps, and Iterators. Each API queries and in some cases updates the data in the collection.

Similarly, the JDBC API provides a means to use SQL to access relational and other external JDBC DataSource objects. However, pureQuery allows an application to access all of these sources of data, individually, or in combination, with a single, well-known query language.

Provides a simple programming model for data access
pureQuery runs as a thin layer on top of JDBC. pureQuery simplifies queries, updates, calls to stored procedures, and other tasks that are associated with accessing databases. pureQuery runs on the Java 2 Platform, Standard Edition 5.0 (J2SE 5.0) and the Java Platform, Standard Edition 6 (Java SE 6).
Automatically maps SQL data to return types with little developer intervention, but you can supply customized code for complex mappings
pureQuery provides automatic mapping between SQL query results and returned objects for many Java object patterns. For complex mappings between query results and Java objects, a pluggable exit interface allows for custom mappings.

You can create and annotate beans that return the results of SQL queries that contain table joins. The query results are returned as a set of beans that contain instances of other beans. The hierarchy of the beans reflects the one-to-one and one-to-many relationships of the query results. For example, a query contains a join between an employee table and a department table. The query returns the employees who are assigned to different departments. The results are returned as a set of department beans, where each instance of the department bean represents a department. Each department bean contains a list of employee beans. Each instance of an employee bean contains employee information.

Supports developing SQL applications using annotated methods and inline methods
Annotated methods are supported for defining your own methods in custom interfaces. You create methods with definitions that are annotated with SQL statements. You can develop applications that follow the Data Access Object (DAO) pattern by creating data access objects with annotated methods.

Inline methods are supported so that you can take advantage of the methods that are defined in the IBM-supplied Data interface. Inline methods are useful for applications that require the construction of SQL statements during application run time. With inline methods, SQL statements are passed as parameters in method invocations, which is similar to the approach that is taken in JDBC.

Support is provided for management of SQL statements that are run statically. You can annotate your code to group the SQL statements that are used in an interface. You can control the bind process for the groups. You can control the bind options that are used by the pureQuery StaticBinder utility. You can also control the names that are generated for the packages that contain the SQL statements.

Supports both dynamic and static SQL at run time
pureQuery code can use dynamic SQL with a standard JDBC driver for both embedded SQL and ad hoc SQL that is not known until run time.
pureQuery code can also use static SQL when the code uses the annotated-method programming style and DB2 databases. Static SQL has these advantages:
Avoidance of the dynamic statement cache
Using static SQL reduces contention for the DB2 dynamic statement cache, which improves 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, less network traffic exists 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 a DB2 package, rather than granting user privileges on database objects.
Ease of revising packages
Creating versions of DB2 packages allows you to rebind packages without the risk of losing prior, better, access paths.
Supports JDBC Type 2 connections when you run pureQuery applications on z/OS®
Because the JDBC Type 2 driver is a combination of Java and native code, Type 2 connections are generally better performing than Type 4 connections.
Your applications that do not use the pureQuery API can take advantage of client optimization, which is unlike other technologies that support running SQL statically:
  • pureQuery client optimization does not use a preprocessor, unlike the COBOL programming language which uses a preprocessor for embedded SQL.
  • pureQuery client optimization does not use a translator or customizer, unlike SQLJ.
  • 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.

Secure storage of pureQuery data
pureQuery configuration information, pureQueryXML data, and captured SQL data can be stored in a secure location and accessed by pureQuery Runtime as needed. pureQuery Runtime can be configured to retrieve the pureQuery data from secure locations. pureQuery Runtime can store in a secure location the SQL data captured from pureQuery-enabled applications that use pureQuery client optimization.
DB2 special register support
When the SQL statements are captured from an application, pureQuery client optimization tracks special register information. pureQuery client optimization records the special register values of the special registers that are commonly used and that might affect the behavior of the SQL statement. In some cases, when the identical SQL statement is issued twice, the behavior of the statement can differ if the value of a special register is changed between the first and the second time it is run.

The pureQuery Configure utility can generate bind options based on the special register information that is recorded with the captured SQL statements. Use the Configure utility option -optionsFileForBind to create a StaticBinder options file that contains the bind options that are based on the special register settings that are recorded when the statements are captured. The options file also contains information and warnings about the statement sets, SQL statements, and special register information in the pureQueryXML file.

The information in the file helps you specify a set of bind options so that when an SQL statement is run statically, the behavior of the statement is similar to when it is issued from the application and runs dynamically. You specify bind options with the pureQuery StaticBinder utility when you bind DB2 packages that contain the SQL statements.

pureQuery Runtime Version 2.2.0.3 and later versions record special register information in a pureQueryXML file. If your pureQueryXML file was created with a version of pureQuery Runtime that is earlier than Version 2.2.0.3, you might need to capture some SQL statements again to record the special register information.

Easily manage pureQueryXML files
The following features help you manage SQL statements and statement sets in a pureQueryXML file:
  • The Configure utility can group SQL statements by the text in the SQL statement, such as table or column names, or by special register use.
  • The Configure utility can delete SQL statements from a pureQueryXML file if the statement was not issued by the application for a specified number of days. pureQuery Runtime updates the time an SQL statement was issued from an application while the pureQuery property captureMode is set to ON.
  • The Configure utility can set the status of statement sets before processing the pureQueryXML files or after processing. The status of statement sets controls whether the Configure utility attempts to modify the statement. If you use the StaticBinder on the pureQueryXML file to bind SQL statements on a DB2 database, you need to rebind the SQL statements in the statement set after the set is modified.
  • The Configure utility can change database location names and schema names for SQL statements in pureQueryXML files.
  • The StaticBinder utility can handle SQL statements in a pureQueryXML file when the bind process returns an error for the statement. The utility can mark the statement as invalid or remove it from the file. If a statement is marked invalid, the statement remains in the file but it is treated as if its isBindable attribute is set to false. You can use the Configure utility to mark the statement as valid or remove statements that are marked invalid.
  • The Configure, Merge, and StaticBinder utilities can perform XML schema validation on input pureQueryXML files.
  • The ManageRepository utility can generate a report that lists the differences between two pureQueryXML files. You can easily see the changes that were made to an updated pureQueryXML file by comparing it to the original file.

Features for C and C++ call level interface (CLI) applications

The call level interface (CLI) is a C and C++ application programming interface for relational database access. CLI uses function calls to pass dynamic SQL statements as function arguments.

After you enable pureQuery client optimization for a CLI application that connects to a DB2 database or Informix® database, you can control the SQL statements that are issued by the application. For example, if the application connects to a DB2 database, you can configure the application to run SQL statements statically against the database. You can control the SQL statements that are allowed to run against the database.

Benefits of pureQuery client optimization include:
  • Running SQL statements statically against DB2 databases.
  • Diagnosing problems with SQL statements with facilities to trace back to the application source code.
  • Replacing the poor-performing SQL statements with optimized statements.
  • Reducing risk of SQL injection attacks by running a restricted set of SQL statements.

You can run SQL statements and validate SQL statements with the DB2 command db2cli. You can run the db2cli command with the exec option to run or validate SQL statements that are in a file. You can specify that the SQL statements in the file are prepared but not run to validate the SQL statements. You can capture the SQL statements that are run or validated by the db2cli command in a pureQueryXML file.

Features for Microsoft .NET applications

InfoSphere Optim pureQuery Runtime for Linux, UNIX, and Windows provides support to improve the stability, security, and manageability of .NET applications, including those applications that are written in any .NET based language, such as C# and VB.NET.

Benefits of pureQuery Runtime include:
  • Improve the quality of service for existing applications
    • Improve performance
    • Lock in access plans
    • Increase accuracy of capacity planning
  • Get more value from existing investments
    • Increase system throughput
    • Speed up problem resolution
  • Enhance security
    • Limit user access
    • Minimize attacks
    • Improve audit readiness

For information about using pureQuery Runtime for Linux, UNIX, and Windows with .NET, see http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=/com.ibm.swg.im.dbclient.adonet.doc/doc/t0054569.html


Feedback