pureQuery® Runtime
can be used with Java™ applications. pureQuery Runtime
can also be used with DB2® ODBC/CLI
and Microsoft .NET applications.
The features depend on the type of 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.