Dynamic SQL statements that Db2 can cache

Only certain dynamic SQL statements can be saved in the dynamic statement cache.

The following type of SQL statements can be saved in the dynamic statement cache:
  • SELECT
  • UPDATE
  • INSERT
  • DELETE
  • MERGE

Distributed and local SQL statements are eligible to be saved.

Statements that are sent to an accelerator server are eligible to be saved in the cache.

The following types of SQL statement text with SQL bracketed comments can be saved in the dynamic statement cache:

  • SQL statement text that begins with SQL bracketed comments that are unnested. No single SQL bracketed comment that begins the statement can be greater than 258 bytes. An example of unnested bracketed comments is /* */ /* */.
  • SQL statement text with unnested or nested SQL bracketed comments within the text. An example of nested bracketed comments is /* /* */ */.

Bracketed comments that are in SQL statement source code are saved with the statement text when the SQL statements are placed in the dynamic statement cache, unless other tools remove the bracketed comments before Db2 processes the SQL statement.

SQL statement text that is preceded by SQL simple comments (--) or any other characters besides unnested, bracketed comments is not eligible to be saved in the dynamic statement cache.

Statements in plans or packages that are bound with REOPT(ALWAYS) cannot be saved in the cache. Statements in plans and packages that are bound with REOPT(ONCE) or REOPT(AUTO) can be saved in the cache.

Prepared statements cannot be shared among data sharing members. Because each member has its own EDM pool, a cached statement on one member is not available to an application that runs on another member.