The PER_STMT_PREP memory duration

A PER_STMT_PREP memory pool is associated with each SQL prepared statement. A prepared statement is an SQL statement that is parsed and ready for execution.

The following table summarizes ways to create and drop a prepared statement.
Method To create a prepared statement To drop a prepared statement
Client application (SQL) PREPARE FREE
C UDR (DataBlade API) mi_prepare() mi_drop_prepared_statement()

When a C UDR allocates PER_STMT_PREP memory, this memory is available to all routine instances that execute before the current prepared statement is dropped. Unlike PER_STMT_EXEC memory, PER_STMT_PREP memory does not get freed upon re-execution of the prepared statement; that is, it remains allocated if the cursor is closed and reopened. For example, in the second code fragment in Examples of using PER_STMT_EXEC memory duration, any PER_STMT_PREP memory that a_func() allocated is not released when the cursor is reopened. Therefore, any code that needs to access this memory once the cursor is reopened can do so. The PER_STMT_PREP memory that a_func() allocates remains allocated until the mi_drop_prepared_statement() drops the stmt prepared statement.

When the prepared SQL statement is dropped, the database server reclaims any PER_STMT_PREP memory in the memory context. It does not reclaim any memory in the memory context with a duration higher than PER_STMT_PREP.

No DataBlade API constructor function allocates its data type structure with a memory duration of PER_STMT_PREP.

Copyright© 2018 HCL Technologies Limited