Query plans for cursors defined with dynamic SELECT statements can be cached and reused, rather than recompiled every time they are prepared. When a subsequent PREPARE is executed on the identical syntax, the query plan in cache is used and the query is not re-optimized.

This feature provides significant performance improvement for complex queries.

A dynamic query associated with a cursor can be cached by using the keyword REPEAT or REPEATED in the prepared select. For example:

PREPARE statement_name FROM REPEATED SELECT…

The configuration parameter, cache_dynamic (available in CBF or Configuration Manager), enables or disables this feature at the server level at startup. When set to ON, all query plans for cursors defined with dynamic SQL will be cached, removing the need to explicitly code the REPEAT or REPEATED keyword in applications or database procedures. The default setting is OFF.

The server-level default setting can be overridden by using a SET statement of the form:

SET [NO]CACHE_DYNAMIC

The server-level setting can be overridden at the session level by using a SET SESSION statement of the form:

SET SESSION [NO]CACHE_DYNAMIC

The DBMSINFO function returns the current setting for the session, indicating whether caching is on or off. For example:

SELECT DBMSINFO ('CACHE_DYNAMIC')

Prior to this feature, the REPEAT or REPEATED keyword could be used on INSERT, SELECT, DELETE, and UPDATE statements coded directly in an embedded program (that is, without PREPARE or EXECUTE IMMEDIATE). This new feature allows REPEATED SELECT statements to be prepared if they use cursors. The REPEATED keyword results in the caching of the query plan.