Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

DB2/OS390 Failing to Cache Prepared Statements

 
Ed Wallen
Ranch Hand
Posts: 34
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
This is an informational post for anyone connecting to a DB2 v. 7 database on OS390 via JDBC.

I have confirmed a bug in the DB2 software that prevents the database engine from successfully determining if a statement is eligible for the prepared statement cache when the SQL string contains leading spaces or lines. This was causing a huge performance problem, as 70%+ of our database CPU time was being spent constantly doing prepares. As far as we know right now, this is not documented by IBM. The DB2 developers have come up with a usermod, but this is not packaged in a formal patch as of yet.

In the interim, we have a very easy workaround that will make the DB2 database engine happy and will successfully allow it to cache prepared statements (I should note that we also go against an Oracle database------it has no problem caching the prepared statement as is). Many OR mapping frameworks use XML as their means of defining and describing how data on the database maps to the Java application objects. In the framework my team is using, we just removed the leading space within the XML persistence configuration documents. An example of the change is below:

Before:




After:



Hope this helps out if you find yourself in this situation.

-Ed
[ August 22, 2005: Message edited by: Ed Wallen ]
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34973
379
Eclipse IDE Java VI Editor
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic