pureQuery Runtime feature summary

pureQuery™ Runtime can be used with Java™ applications. pureQuery Runtime can also be used with DB2® ODBC/CLI and Microsoft .NET applications.

Features for Java pureQuery API applications

pureQuery Runtime provides API features in addition to client optimization features.

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

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.

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).
Automatic mapping of SQL data to return types with little developer intervention
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.

Support for developing SQL applications that use 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 and control the names that are generated for the packages that contain the SQL statements.

Support for 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.
  • Access plans are determined before run time, and so the need for preparing SQL statements at run time is eliminated.
  • Each statement does not need to be prepared and described, and therefore 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
Because you can create multiple versions of each DB2 package, you can rebind packages without the risk of losing prior, better, access paths.
Support for 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 generally perform better than Type 4 connections.
Support for platforms and frameworks for data access application development
pureQuery Runtime supports the following platforms and frameworks:
  • Java
  • Java Persistence API (JPA)
  • Spring
  • Hibernate
  • iBatis

Features for Java applications enabled with pureQuery client optimization

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 on a DB2 database:
  • pureQuery client optimization does not use a preprocessor, in contrast to the COBOL programming language, which uses a preprocessor for embedded SQL.
  • pureQuery client optimization does not use a translator or customizer, in contrast to 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. pureQuery client optimization offers these features:

Secure storage of pureQuery data
pureQuery configuration information, pureQueryXML data that includes SQL data used by pureQuery Runtime, 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 that is 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 DB2 special register information that is used by SQL statements. 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 option -optionsFileForBind can generate a StaticBinder options file containing bind options based on the special register information that is recorded with the captured SQL statements. 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 its behavior 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.

Easy management of 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 can track when an SQL statement was issued from an application and update that information in the pureQueryXML file.
  • 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.
  • 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 not processed as a bindable statement. You can use the Configure utility to change the statement to a valid, bindable statement or remove invalid statement.
  • 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 ODBC/CLI applications and .NET 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. .NET applications include applications that are written in any .NET based language, such as C# and VB.NET.

After you enable pureQuery client optimization for a CLI or .NET 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.

For CLI applications, you can run SQL statements and validate SQL statements with the DB2 command db2cli. You specify that SQL statements are prepared but not run to validate the SQL statements. You can capture the SQL statements that are run or validated in a pureQueryXML file.

For information about developing DB2 applications with .NET, see ADO.NET application development.

For information about developing DB2 applications with CLI, see Introduction to DB2 Call Level Interface and ODBC.


Feedback