aspose file tools*
The moose likes JDBC and the fly likes DB2/OS390 Failing to Cache Prepared Statements Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "DB2/OS390 Failing to Cache Prepared Statements" Watch "DB2/OS390 Failing to Cache Prepared Statements" New topic
Author

DB2/OS390 Failing to Cache Prepared Statements

Ed Wallen
Ranch Hand

Joined: Feb 11, 2002
Posts: 34
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

Joined: May 26, 2003
Posts: 30762
    
156

Thanks Ed!


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: DB2/OS390 Failing to Cache Prepared Statements