Dynamic SQL Caching

When you use dynamic SQL caching, note your EDM statement cache pool size. Cached statements are not backed by disk and if its pages are stolen and the statement is reused, it will have to be prepared again. Static plans and packages can be flushed from EDM by LRU, but they are backed by disk and can be retrieved when used again.

There are statistics to help monitor cache use and trace fields show effectiveness of the cache and can be seen on the Statistics Long Report. In addition, the dynamic statement cache can be snapped with the EXPLAIN STMTCACHE ALL statement. The results of this statement are placed in the DSN_STATEMENT_CACHE_TABLE.

In addition to the global dynamic statement caching in a subsystem, an application can also cache statements at the thread level with the KEEPDYNAMIC(YES) bind parameter in combination with not re-preparing the statements. In these situations, the statements are cached at the thread level in thread storage and at the global level. If no shortage of virtual storage exists, the local application thread level cache is the most efficient storage for the prepared statements.

Note: You can use the MAXKEEPD subsystem parameter to limit the amount of thread storage that is consumed by applications caching dynamic SQL at the thread level.