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.
CA IDMS, all supported releases.
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.