The PER_COMMAND memory duration

A PER_COMMAND memory pool is associated with each SQL command. An SQL command is a subquery, which is a separate SQL statement initiated as part of the current SQL statement. The most common kind of subquery is a SELECT statement in the WHERE clause of a SELECT.

When a C UDR allocates PER_COMMAND memory, this memory is available to all routine instances that execute in the same SQL command. For example, the following SELECT statement contains two SQL commands:

SELECT a_func(x) FROM table1
   WHERE i <= 
      (SELECT y FROM table2 WHERE a_func(x) <= 17);

The SELECT operation on table1 is the main query and is one SQL command. The SELECT operation on table2 is a subquery of the main query and is therefore a separate SQL command. All invocations of the a_func() function in the main query can share any PER_COMMAND memory that this instance of a_func() allocates; however, the invocations of a_func() in the subquery have their own PER_COMMAND memory pool. These invocations would not share their memory pool with the invocations of a_func() in the main query.

Other examples of subqueries follow:

A SELECT statement after an IN, EXISTS, ALL, ANY, or SOME keyword in a WHERE clause:

SELECT stock_num, manu_code FROM stock
   WHERE NOT EXISTS 
      (SELECT stock_num, manu_code FROM items
         WHERE stock.stock_num = items.stock_num 
            AND stock.manu_code = items.manu_code);

A SELECT statement after the table name in an INSERT statement:

INSERT INTO table1 (int_col)
   SELECT another_int_col FROM table2 
      WHERE a_func(x) <= 17);

A separate SQL command is not created for simple WHERE clauses. For example, the following query contains only one SQL command:

SELECT a_func(x) FROM table1 WHERE a_func(y) > 6;

Both instances of a_func() use the same PER_COMMAND memory pool for their PER_COMMAND allocations. Therefore, any PER_COMMAND memory that the a_func() function allocates can be shared by all invocations of the a_func() function in the select list and the invocations of a_func() in the WHERE clause. If an SQL statement does not contain any subqueries, PER_COMMAND memory lasts for the duration of the SQL statement; that is, the PER_COMMAND and PER_STMT_EXEC memory durations are the same.

Tip: You can obtain the name of the SQL command that invoked the current UDR with the mi_current_command_name() function.

The database server reclaims any PER_COMMAND memory in the memory context as follows:

For an SQL statement with no subqueries, the database server deallocates PER_COMMAND memory when the SQL statement completes.
For an SQL statement with one subquery, the database server deallocates PER_COMMAND memory as follows:
- For the main query, the database server frees PER_COMMAND memory after this main query completes.
- For a subquery, the database server frees PER_COMMAND memory each time the subquery finishes execution for one outer row of the main query, and after the main query completes.

The only exception to this rule is if this SQL statement is a cursor statement (DECLARE, OPEN, FETCH, UPDATE...WHERE CURRENT OF or DELETE...WHERE CURRENT OF, CLOSE), in which case the database server frees the PER_COMMAND memory when the cursor closes.

The PER_COMMAND memory duration is useful for accumulating calculations, in iterator functions, and for initialization of expensive resources. The most common way for UDR invocations within a routine instance to share information is to store this information in the user state of its MI_FPARAM structure. The routine manager allocates an MI_FPARAM structure for each C UDR instance. This MI_FPARAM structure has a PER_COMMAND memory duration. Therefore, to retain user state across a routine instance, a UDR can allocate PER_COMMAND memory and store its address in the MI_FPARAM structure. The UDR does not need to take special steps to preserve the address of this user-state memory. Each UDR invocation can use the mi_fp_funcstate() function to obtain the address from the MI_FPARAM structure.

For example, if a UDR calculates a total, PER_ROUTINE memory would not be adequate to hold this total because the memory would be freed after a single routine invocation. PER_COMMAND memory would be available for the entire routine instance, regardless of the number of invocations involved. For more information about the user state in MI_FPARAM, see Saving a user state.

Several DataBlade® API constructor functions allocate their DataBlade API data type structure with a PER_COMMAND memory duration. DataBlade API data type structures with a PER_COMMAND memory duration shows the DataBlade API data type structures that have a memory duration of PER_COMMAND.

Table 1. DataBlade API data type structures with a PER_COMMAND memory duration
DataBlade API data type structure	DataBlade API constructor function	DataBlade API destructor function
Function descriptor (MI_FUNC_DESC)	mi_cast_get(), mi_func_desc_by_typeid(), mi_routine_get(), mi_routine_get_by_typeid(), mi_td_cast_get()	mi_routine_end()
MI_FPARAM structure	Routine manager (when it invokes a UDR)	Routine manager (when it exits a UDR)
MI_FPARAM structure (user-defined)	mi_fparam_allocate(), mi_fparam_copy()	mi_fparam_free()

Switching the current memory duration before one of the constructor functions in DataBlade API data type structures with a PER_COMMAND memory duration does not change the PER_COMMAND memory duration of the allocated DataBlade API data type structure. These data type structures are freed by their destructor function or when the current SQL command completes. To retain access to some of these DataBlade API data type structures after the command completes, you must save them at the per-session level.

Tip: The DataBlade API supports the ability to save information at a per-session level. This ability, however, is an advanced feature of the DataBlade API. For more information, see Obtain a session-duration connection descriptor.