pureQuery Training: A Success Story from the Trenches

Recently I trained another group of developers and DBAs on pureQuery, IBM’s new data access platform for Java.  Here I want to share my experiences from teaching the course Developing Database Applications with Optim Development Studio and pureQuery and reflect on reception of pureQuery among experienced developers whose daily job is developing mission critical applications. In their environment, data amounts to petabytes, and performance and throughput are essential. The database is DB2 and the computer is IBM System z. Sometimes, a big machine is what you really need.  An army of scooters may not always be replacement for a forty-ton truck if you need to haul a lot. 

JDBC and ORM Experiences

Everyone in the student group had their share of Java enterprise applications under the belt and they had ups and downs with Java persistence technologies.  They have experienced the tediousness of writing JDBC code.  They have seen the initial productivity gain provided by object relational mapping (ORM) frameworks, just to be disillusioned by poor performance, difficulties in debugging and SQL tuning, and poor traceability. While they provide productive way of navigating persistent object associations, to dismay of DBAs, ORM solutions typically leave sophisticated data access and optimization features of modern databases untouched. Buried under to many layers of abstraction, the database languishes, its capabilities underutilized. At the end, performance and control suffer.

Enter pureQuery: The new Data Access Platform

pureQuery data access platform is different from ORM solutions.  It does not try to hide the database. Instead, pureQuery gives developers full access to the SQL, end achieves high productivity not only through developer friendly API design, but also through tooling provided by the Optim Development Studio, an Eclipse based IDE for development of data centric applications.

It was interesting to observe the students as they were learning about the different capabilities of pureQuery and its tooling. We prepared a set of labs and many hands on exercises where students could learn about pureQuery through a set of realistic scenarios. While there are many interesting and productive features of pureQuery, here are some that participants in my training classes liked the most.

The Code and the API

The real winner is the pureQuery API which enables application code that is significantly shorter and simpler than JDBC. A big advantage is that the pureQuery will map the Java bean properties to database columns, while the developers can write arbitrarily complex SQL. Annotated interfaces give another, more disciplined option for defining the queries for the data interfaces. Just check out this code fragment: this is all you need to do to get all employees of a company:


The Editor and the Tooling

Tedious database development tasks are greatly simplified.  The way how it is done resonates well with participants.  While the tools provide arbitration and guidance, what is generated is simple, easy to understand, and easy to modify. The editors provide auto-completion on SQL statements in Java strings.

A particularly interesting feature for many participants is that you can start with an arbitrary SQL query, and the tool will create everything needed to call that query, and transport the values in and out of the query or stored procedure. In many organizations where data is a critical asset, this fits the normal workflow where the data developers create the SQL.  In addition, you can even create a web service out of a query – a welcome feature for data centric services in SOA.

SQL Outline

This is a view in the IDE that provides insight into relationship between Java and SQL.  While developers liked this feature, it is the DBAs who are thrilled by it – you can trace the SQL to Java code, and vice versa.  Tracing from Java to the database:

And here is the tracing from database to Java:

This feature works when SQL is visible in the application, as a string or in the annotation. But it also works when optimizing JPA or Hibernate.  Even though the code is generated on the fly, at runtime, pureQuery runtime can capture the SQL sent to the database, and trace it to the line of code that emitted it in the user’s program. 

This traceability makes the work of developers and DBAs much easier.  It eliminates guesswork.  The developers and DBAs can now sit together and troubleshoot or optimize the code or tune the queries. 

When you run the application using pureQuery, one option is to collect performance metrics.  This immediately enables the developers to spot inefficient queries. Here are the metrics from the application run:

Static SQL

If DB2 DBAs have been missing one thing from Java persistence applications, this is it.  With static SQL, you send your SQL to the DB2 during development.  It gets prepared at a high optimization level, and runs faster than the SQL that is sent to the DB at development time.  Security is also enhanced, as the security is set up for a package – a group of SQL statements, and not just at the table level. Here is the representation of DB2 packages created from our Java application:







These are just some highlights - the features that immediately caught attention of my students since they directly translate to savings in time and effort.

Who is the pureQuery for?  One thing during training that was always well received is that pureQuery does not hide the database, yet it enables high productivity.  Is pureQuery an ideal solution for everyone?  Certainly not.  We know that there are no silver bullets.  If you do not care about your database, if you have a handful of users or performance is not an issue, then ORM frameworks and JPA may work out just fine. On the other hand, if your database is an essential part of your business, and you have strict and demanding performance requirements, you need to look into pureQuery.  And if you are running on System z, the performance gain translates directly into money savings due to lower utilization of the CPU. You can find some telling benchmarks in a blog post by Simon Harris on performance results.

While developers and DBAs obviously liked the technical advantages, it was the managers who smiled when they heard about the savings.

If you want to learn more about how to productively apply pureQuery in your team, check out the Developing Database Applications with Optim Development Studio and pureQuery  course description. For many clients, we customize the course to include the client’s coding standards, frameworks, and other specifics.

Happy persistence!


  1. Hi Dr. B!

    pureQuery sounds great! A concern might be the licensing costs?


  2. pureQuery Runtime controls the execution of SQL statements that are issued by applications at the database driver level. When you deploy pureQuery Runtime, you set pureQuery Runtime properties to control the execution of SQL statements. After you install InfoSphere Optim pureQuery Runtime in your production environment, you can deploy your pureQuery Java applications. You can also deploy DB2 ODBC/CLI and Microsoft .NET applications. Thanks for shareing~ Charlotte W. from IoT software solutions