What is stored in the JNDI cache?

Question

We are using WebSphere 6.1 on Windows connecting to a DB2 database on a different Windows machine. We are using prepared statements in our application. While tuning a database index (adding a column to the end of an index) we do not see the performance boost we saw on a test database with the same query, after changing the index the processor on database server actually was pegged.

Are the prepared statements query plans actually stored in JNDI? If so, how can they be cleared? If not, how can we clear the cache on the DB2 server?

Answer 1

Query plans are 'normally' held in the database by the RDBMS itself, with the exact life cycle being vendor specific I'd guess. They are definitely not held in a JNDI registry.

1. I assume there is a similar volume of data in both databases?

2. If so have you looked at the explain plan for both databases and confirmed they match?

If the answer to both these questions is yes I'm out of ideas and it's time to reboot the database server.

Answer 2

The execution plans for prepared statements are stored in the DB2 package cache. It's possible that after an index is added, the package cache is still holding on to old access plans that are now sub-optimal.

After adding an index, you will want to issue a RUNSTATS statement on at least that index in order to provide the DB2 optimizer with the information it needs to choose a reasonable access plan.

Once the RUNSTATS statistics exist for the new index, issue a FLUSH PACKAGE CACHE statement to release any access plans that involved the affected table. The downside of this is that access plans for other dynamic SQL statements will also be ejected, leading to a temporary uptick in optimizer usage as each distinct SQL statement is optimized and cached.

http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0007117.html