Reusing dynamic SQL LWPs across multiple connections

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:

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:

NoteReusing dynamic SQL LWPs may have a negative impact on performance because the reused plan is generated with the original set of supplied parameter values.