I had the opportunity recently to talk with Paul Wirth, an IBMer who travels the country in support of DB2 and is a real smart guy. (… and not just because he agrees with me on so many things!). I had asked to talk with Paul after some reading about a new technology called pureQuery, which is part of the relatively new Data Studio suite. The technology is a result of a cross brand initiative from IBM software aimed at the intersection of application programmers and DBA’s for effective data access. This technology’s goal is to reduce the complexity of JDBC programming and queries to relational databases, Java collections and database caches. While there is a lot of detail (see the links below) on how this technology works, there are also a couple of System z implications that Paul shared.
The difference between dynamic and static execution of SQL statements is like the difference between compiled and interpreted execution. If you build static execution packages for DB2, you get a predetermined access path, built for the best performance and predictable execution of the workload. They're also benefits related to security isolation since the application accesses the package and not the database table (see SQL injections, fraud etc.) Those shops who have used the approach of static SQL statements via the whole mechanism of DB2 packages receive long known associated benefits of not only performance, but also cost, security, monitoring, and consistency. While previously available for Java via SQLJ, it was pretty complicated to do and only a limited number of shops did so. pureQuery makes this much easier to do regardless of your Java framework or the API you're using.
One of the strengths of System z is its focus on mixed workloads, performance, and cost effectiveness as a platform. Being able to prioritize and manage workloads through workload manager is essential to accomplishing this. Unfortunately, much of the distributed Java transactions coming off the web to DB2 are only dynamic SQL workload. So, z/OS sees them as undifferentiated pieces of work unless the programmer sets the properties for the connection class-which is often not done. If you have a unique package name you can identify the application program which means it's easier to monitor, do problem determination and especially that it is eligible to manage via the z/OS Workload Manager (WLM). pureQuery gets you get those unique package names as you implement static SQL, and gives WLM the ability to assign them to specific service classes, and allow prioritization of the DB2 threads. (Note: it is not just the Java workload coming off the web, Java workloads via Websphere for z/OS, stored procedures, and CICS Java workload can potentially benefit from pureQuery via the pureQuery Runtime for z/OS).
A third interesting area to look at is stored procedures. First, if you have a simple, one statement procedure that doesn't contain business logic, rules, or network data filtering, and is used just to provide a static plan, then consider using pureQuery and rewrite the SQL statement in the Java application. The pureQuery statement is zIIP eligible, provides the static plan, and avoids the need for the stored procedure. IF the Java program is running on System z it also becomes eligible for zAAP processor use.
Next, we know that DB2 9 gives us the new, native stored procedures, which avoid the use of WLM because they run within the thread, and are zIIP eligible- when used over TCP/IP with DRDA. So, say you rewrite an external procedure (e.g. one that currently uses COBOL) using native SQL/PL. The result? A procedure which is more efficient and is zIIP eligible (DRDA).
As Paul said: “…if you think about it; pureQuery makes Java web applications behave a lot like CICS COBOL applications…” And that, as System z folks know, can be a good thing.
Whew... a lot to think about, but this is a technology to watch that holds the potential for improvements on numerous fronts. Watch this one as it moves in closer on the radar!
...and from DB2 Magaizine: