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