For many years the amount of dynamic SQL being processed by DB2 has exploded. For each dynamic SQL statement DB2 has to evaluate the access path. As you probably know this is done once at BIND time when using static SQL. To reduce the cost of executing dynamic SQL in DB2, IBM introduced the so-called dynamic statement cache many years ago. In this cache the statement text of each dynamic statement is stored alongside its access path. The statement text of all incoming SQL statements are compared to the statement texts stored in the cache. If the text matches the text of a statement in the cache, the access path of the stored statement is used and the access path evaluation is skipped.
Before DB2 version 8 the statement cache did not give you any benefits as a developer. It was just a tool used by the DBA and the system programmer for optimizing DB2 performance. But in DB2 version 8 this changed dramatically. The EXPLAIN statement was extented to make it possible to see which dynamic statements are stored in the statement cache. You are also able to see which access path a stored statement has been using. Even better is that you are able to see, how many resources has been used to execute each individual statement in the statement cache. If you are interested in SQL performance this information is like a goldmine.
In order to extract information from the statement cache you can use these SQL statements:
The essential SQL statement is EXPLAIN STMTCACHE ALL which writes the contents of the statement cache into the DB2 table DSN_STATEMENT_CACHE_TABLE. The SQLID executing the above must have created the DB2 table DSN_STATEMENT_CACHE_TABLE before executing the above SQL statements. When EXPLAIN STMTCACHE ALL is executed DB2 evaluates which authorisations the current SQLID is possesing. If one of them is the SYSADM authorisation the complete contents of the statement cache is written. If not, only the statements executed by the SQLID are written. In other words only the statements you have carried out yourself are written to DSN_STATEMENT_CACHE_TABLE unless you have SYSADM authorisation.
I execute a DELETE FROM DSN_STATEMENT_CACHE_TABLE before EXPLAIN STMTCACHE ALL, because EXPLAIN STMTCACHE ALL only inserts tables into DSN_STATEMENT_CACHE_TABLE. If you do not delete the rows you will create historic data of the statements in the statement cache. Try it and you will learn a lot more about how the statement cache works.
In order to make the above work it is of course quite essential to create the DSN_STATEMENT_CACHE_TABLE. Du can use the CREATE TABLE statement located in SQL Reference. Drop the last two columns if you are running version 8. Here you can also read what the columns contain. In DB2 version 9 DB2 will automatically create the corresponding LOB objects. This is not happening in DB2 version 8. If you are running DB2 version 8, you also need to execute the following SQL statements after the CREATE TABLE statement:
Remember to replace userid with your own userid. By the way some installation removes developers authorisation to create tables in DSNDB04. If you run into such trouble you need to consult a DBA in order to proceed.
When you finally make EXPLAIN STMTCACHE ALL work for you, you might experience that the columns containing the exciting information (all the STAT_ columns) all contain zeroes. The reason for this is that the DB2 system programmer or the DBA has not started the DB2 TRACE that collects this information. Please ask them to execute a -START TRACE(MON) IFCID(318) which will make it work. I will be returning with more tips about the statement cache as this tip only scratches the surface.