Implementing Siebel eBusiness Applications on DB2 UDB for z/OS and OS/390 > Maintenance Considerations for zSeries >

About DB2 Dynamic Statement Caching

Siebel eBusiness Applications take advantage of DB2 global statement caching. This allows dynamic SQL statements that are reexecuted to bypass the PREPARE phase after the first execution. Dynamic statement caching allows dynamic statements to be prepared once, stored on a global statement cache, then reused many times. The Siebel application is also designed to maximize the reuse of statement caching.

You enabled global statement caching when you set the CACHEDYN parameter to YES in macro DSN6SPRM when generating DSNZPARMs. Another method of enabling global statement caching is to set Dynamic Cache SQL to YES when installing DB2.

Because DB2 saves the cached statement in the Environmental Descriptor Manager (EDM) pool, proper sizing of this pool is very important. For guidance on sizing the EDM pool, see your IBM documentation.

NOTE:  The EDM pool should be monitored and adjusted based on your workload.

Other system parameters associated with dynamic statement caching are illustrated in Table 18.

How to Invalidate Cached Statements

After DB2 prepares a SQL statement and places it in the global statement cache, it gets reused. If a customer is performing SQL tuning and it is desirable to force DB2 to prepare and reoptimize a cached statement, the global SQL statement cache must be invalidated.

To invalidate an entry in the global statement cache for a specific SQL statement, one of the tables referenced by the SQL statement or the table catalog statistics must be altered in some way.

Methods of invalidating a dynamically cached statement include the following:

• Use the RUNSTATS utility to automatically invalidate cached statements that refer to objects against which RUNSTATS was executed. The RUNSTATS utility can be run on any table or table space referenced in the SQL statement.
• Use the DB2 command STOP OBJECT or the SQL DROP, ALTER, or REVOKE statements (for example, REVOKE ALL FROM PUBLIC) on any object related to the plan to invalidate both global and local cache. While some ALTER statements might require an object to be stopped, others might not; for example, STOP OBJECT is not required for DROP or REVOKE.
• Use the command ALTER TABLE tablename AUDIT NONE to purge statements that reference that table from the cache.
• Use a CREATE INDEX statement on tables used in the DB2 access path.
• Stop the DB2 subsystem and restart it.

SQL generated by the Siebel application will be sent to the DB2 host on the z/OS platform through ODBC and the DB2 Call Level Interface (CLI) before the SQL command is processed by DB2. To effectively troubleshoot issues it may be necessary to trace the SQL statement through one or all of these layers. This should normally be done under the guidance of Siebel support personnel who will advise on the most appropriate tracing for the situation. Steps to initiate the tracing at each layer are described below.