In versions earlier than 15.7, Adaptive Server stored dynamic SQL statements (prepared statements) and their corresponding LWP in the dynamic SQL cache. Each LWP for a dynamic SQL statement was identified based on the connection metadata. Because connections had different LWPs associated with the same SQL statement, they could not reuse or share the same LWP. In addition, all LWPS and query plans created by the connection were lost when the Dynamic SQL cache was released.
In versions 15.7 and later, Adaptive Server uses the statement cache to also store dynamic SQL statements converted to LWPs. Because the statement cache is shared among all connections, dynamic SQL statements can be reused across connections. These statements are not cached:
select into statements.
insert-values statements with all literal values and no parameters.
Queries that do not reference any tables.
Individual prepared statements that contain multiple SQL statements. For example:
statement.prepare(‘insert t1 values (1) insert t2 values (3)’);
Statements that cause instead-of triggers to fire.
To enable using the statement cache to store dynamic SQL statements, set the enable functionality group or streamlined dynamic SQL configuration options to 1. See “Setting Configuration Parameters” in the System Administration Guide: Volume 1.
Using the statement cache has several benefits:
LWPs and their associated plans are not purged from the statement cache when the connection that created the entry exits.
LWPs can be shared across connections, further improving performance.
Reusing LWPs also improves performance in execute cursors.
Dynamic SQL statements can be monitored from the monitoring table monCachedStatement.
Reusing dynamic SQL LWPs may have a negative impact on performance because the reused plan is generated with the original set of supplied parameter values.