ChannelDB2

Client Optimization vs. pureQuery API

You may have heard about several different capabilites available with pureQuery such as Client Optimization, Annotated Method and In-Line. Some questions that I have had about these options would include what is the difference between these methods and where do I use each? I wanted to just quickly write a few paragraphs to answer these two primary questions.

In answer to the first question, both Annotated Method and In-Line are part of the pureQuery API. Annotated Method uses a Java interface to define SQL which is static in nature. It can execute SELECT, INSERT, UPDATE and DELETE, as well as CALL and it provides you with the capability to run the SQL dynamically in a development type environment or to bind the SQL statically and run it statically in a test or production environment.

In-Line is used for SQL that is dynamic in nature. If you need to manipulate the SQL at runtime prior to execution, this is the API methodology you would use. The SQL will always run dynamically, even if you bind annotated methods and run them statically in the same environment.

Client Optimization is functionality that is able to intercept JDBC calls made against a datasource, capture the SQL calls being made, bind those calls against DB2 and then run them statically on subsequent calls.

So where do you use each?

Well, if you have the capability to inject pureQuery into your application coding then Annotated Method and/or In-Line coding would be your best bet. Annotated Method when executed statically against DB2 on z/OS has a savings in CPU of roughly 24%. Against a DB2 LUW instance, the numbers are even more dramatic and in the 40 and 50% CPU savings range. In-Line, since it is executed dynamically, would of course not have such a performance return but still would prodvide a simple and consistent API for dyanamic natured SQL.

Client Optimization (CO) provides you with an option to bind static SQL for an existing JDBC application. If there is no viable or cost effective option to recode the application using the pureQuery API, then capturing the SQL with Client Optimization would be the best answer. CO would generally provide a 9 to 14% improvement in CPU consumption based on a 85% statement cache hit ratio. CO would also be useful if you are using not just native JDBC, but also if you are using a JDBC framework, such as Spring JDBC, a user-written JDBC framework or even an ORM such as IBatis, OpenJPA or Hibernate.

In addition to the performance gains, both the pureQuery API and Client Optimization provide greatly improved debugging, problem determination and tuning capabilites that should definiately be strongly considered.

If you have any question I might be able to answer, please comment or send me an email!

Views: 327

Comment

You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Comment by P Pant on February 18, 2010 at 11:38pm
Dan,Your blog is extremely good.Could you please provide here the Advantages of pureQuery over hibernate and how you can save cost using pureQuery.

Featured Downloads

Try BLU Acceleration on Cloud

© 2019   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service