File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases 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
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "DB2/OS390 Failing to Cache Prepared Statements" Watch "DB2/OS390 Failing to Cache Prepared Statements" New topic

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:



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

[ August 22, 2005: Message edited by: Ed Wallen ]
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33117

Thanks Ed!

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
I agree. Here's the link:
subject: DB2/OS390 Failing to Cache Prepared Statements
It's not a secret anymore!