Call Level Interface Guide and Reference

Caching Statement Handles on the Client

Previous versions of DB2 did not have the global statement cache. They did, however, provide dynamic statement caching at the server. In DB2 CLI terms this means that for a given statement handle, once a statement has been prepared, it does not need to be prepared again (even after commits or rollbacks), so long as the statement handle is not freed. Applications that repeatedly execute the same SQL statement across multiple transactions, can save a significant amount of processing time and network traffic by:

1. Associating each such statement with its own statement handle, and

2. Preparing these statements once at the beginning of the application, then

3. Executing the statements as many times as is needed throughout the application.

This is not needed in DB2 Universal Database Version 5 and later because of the global dynamic statement cache. Preparing the first statement would create the package in the global cache. Each subsequent prepare request would find the first access plan in the cache and use it right away.

If an application is connected to a server that does not support dynamic statement caching across transaction boundaries, DB2 CLI will prepare each statement internally as needed. This means the method described above can be used for all applications, regardless of the RDBMS.

[ DB2 List of Books | Search the DB2 Books ]