IBM pureQuery

Q. From a Java perspective, pureQuery sounds a little bit like a data access framework. How is it different than other leading data access frameworks?
Because coding in JDBC is so tedious, it only makes sense that Java developers would turn to frameworks such as Spring, iBatis, and JPA to help with their object-relational mapping, among other things. pureQuery does provide productivity boosts that many are looking for in a Java framework, but it does not provide the full persistence or container management associated with Hibernate or JPA. As a framework alternative, pureQuery attempts to provide a balance point between enhanced productivity and full SQL control. However, pureQuery is different from other frameworks in that it can complement any framework. Developers can continue to use their framework of choice and add pureQuery to get the added benefits of performance enhancement, improved security, and improved manageability.

Of course, we think pureQuery is really the best choice in terms of performance and productivity when your data is stored in DB2, Informix, or Oracle.

Q. If I use the pureQuery API, am I locked into IBM proprietary technology?
The pureQuery annotated method style of programming follows the Data Access Object (DAO) pattern and therefore separates the interface definition from its implementation, allowing the developer to either leverage the automatically generated pureQuery code or implement the interface using a technology of their choice.

In addition, if you want to use another API such as OpenJPA or Hibernate, you can still take advantage of pureQuery performance and security benefits “under the covers”. See the next question.

Q. You mention performance benefits of using pureQuery. Is there a way to get those benefits without rewriting my existing applications?
For any Java or .NET data access application, you can use pureQuery to capture SQL(while the application is running), which can then be packaged into static SQL for improved security and performance. This capability is known as “client optimization.” For more information, see this tutorial.

For WebSphere applications that use OpenJPA, there is a way to accelerate this process using a utility that generates the SQL from the application rather than capturing it while it runs. For more information, see this article.

Q. Does the pureQuery platform support any non-IBM databases?
Yes, as of Version 2.2, Optim pureQuery Runtime and Optim Development Studio support Oracle. Although Oracle does not support static, precompiled SQL, there are still many benefits to using pureQuery with Oracle. You can develop new Java applications using the pureQuery API, or you can use the client optimization capability to capture SQL and metadata to take advantage of the SQL traceability, impact analysis, and SQL replacement capabilities in Optim Development Studio.

The vision for IBM Integrated Data Management is to make it easier to manage data across lifecycle tasks spanning design, development, deployment, management, optimization, and governance across a heterogeneous database environment. It is likely that additional databases will be supported over time.

Q. How does pureQuery support Web 2.0 applications?
Interestingly enough, the pureQuery APIs and runtime were designed specifically with Web 2.0 applications in mind. Its simple API lets you return results in Java objects, JSON, or XML. As a matter of fact, pureQuery is the data access technology used by WebSphere sMash (aka Project Zero).

Q. We're a DB2 for z/OS shop using DB2 Connect. Does pureQuery provide value for us?
DB2 for z/OS shops are well-positioned to take advantage of a number of pureQuery capabilities, with more coming in the future. Many organizations can use the pureQuery runtime to reduce software license costs on the mainframe, even when connecting via DB2 Connect.

The integrated development environment provided by Optim Development Studio supports not just all the pureQuery capabilities described above for z/OS, but also includes SQL procedure development and debugging, basic object management, Visual Explain, and many other capabilities. In addition, you can install DB2 Optimization Expert for z/OS into the same environment as Optim Development Studio so developers can take advantage of the advanced advisory capabilities in that product without leaving their development environment.

Q. We are an Informix or Oracle shop, so we can’t realize the benefits of precompiled, static SQL. Does pureQuery help us?
Yes, it does. With Oracle and Informix Dynamic Server, you can use the pureQuery runtime, tools, and APIs for productivity benefits in developing or modifying Java database applications:

• You can use the developer tools to be more productive creating Java database applications.
• You can quickly trace a poorly performing query back to the source code or see which code might be impacted by a proposed database schema change.
• You can visualize which queries are issued most often or take the most time to focus optimization efforts
• You can reduce or eliminate the risk of SQL injection by restricting SQL execution to only SQL that was previously captured and approved.
• You can make emergency modifications to application SQL, validating its equivalency, without modifying the application.
• For Informix, you can use special pureQuery APIs to reduce network traffic between the client and the server with heterogeneous batching functions (ie batching of updates across different tables). (Homogeneous batching is available for both Oracle and Informix.)

Monitoring capabilities are planned to expand over time to include non-IBM databases.