INSERT statements are not cached

book

Article ID: 127532

calendar_today

Updated On:

Products

CA IDMS CA IDMS - Database CA IDMS - ADS

Issue/Introduction

The SQL caching feature of CA-IDMS does not cache INSERT statements. This article describes this situation and a circumvention.

In CA-IDMS, the result of the compilation of dynamic SQL statements can be cached in memory.
This saves the overhead in compiling identical SQL statements every time they are invoked.
For ODBC and JDBC based applications which make heavy use of dynamic SQL, this can result in a significant performance benefit.
This feature does not support INSERT statements.
This is documented in the "Note:" at the top of the documentation page for Dynamic SQL Caching.
In the case of SQL against a network-defined database, if the schema is large, this can have a noticeable impact on performance.

Environment

CA IDMS, all supported releases.

Resolution

An IDEA has been raised on the CA IDMS Communites page to request that INSERT statements be made cacheable.

In the mean time, as a circumvention, it is possible to define an SQL PROCEDURE to perform the INSERT.
This will result in an access module being compiled and the INSERT becomes static, and does not need to be compiled on every invokation.
The SQL PROCEDURE can be invoked with a CALL statement which can be cached.

Sample code to define and invoke the procedure are in the text file attached to this document.

Additional Information

CREATE PROCEDURE
Invoking Procedures
 

Attachments

1558537093517kb000127532.txt get_app