Performing static SQL queries against DB2 without PureQuery

Question

I'd like to use JPA over JDBC for a new application. I'm strictly using Named queries and basic CRUD methods of JPA Entity manager, which allows me (with the help of Hibernate, or any other JPA implementation) to extract all SQL native queries that will be performed on the database. With this list of static queries, I understand that I can build a DB2 package that is all execution plans of my requests.

So my question is: Does performing those queries through JDBC against DB2 will take advantage of those execution plans, or not ? I understand that the PureQuery product can capture the list of sql orders. Does it, still through JDBC and not through PureQuery specific API, provide more ? such a specific DB2 static bind feature ? or it is equivalent to JDBC?

Thank you for any piece of answer.

Answer 1

JDBC applications execute dynamic SQL only (i.e. DB2 does not use static packages).

There are only 2 ways to get static SQL (where the queries are stored in a package in the database): Write your application using SQLJ (which eliminates JPA/Hibernate) or use pureQuery (which sits between JDBC and the database).

Keep in mind that even with dynamic SQL, DB2 does cache the execution plans for queries, so if they are executed frequently enough (i.e., they remain in the cache), then you won't see the overhead from query compilation. The cache is only useful if the queries are an exact byte-for-byte match, so select * from t1 where c1 = 1 is not the same as select * from t1 where c1 = 2, nor is select * from t1 where C1 = 1 (which gives the same result, but the query differs). Using parameter markers (select * from t1 where c1 = ?) is key. Your DBA can tune the size of the catalog cache to help maximize the hit ratio on this cache.

Although caching helps avoid repeatedly compiling a query, it does not offer the plan stability that static SQL does, so YMMV.