Because a C UDR executes in the memory space of the database server, its dynamic memory allocations can increase the memory usage of the database server. For this reason, it is very important that a UDR release its dynamically allocated memory as soon as it no longer needs to access this memory.
To help ensure that unneeded memory is freed, the database server associates a memory duration with memory allocation made from its shared memory. The portion of shared memory that the database server provides for dynamic allocation by C UDRs is organized into several memory pools. Each memory pool is associated with a memory duration, which specifies the lifetime of the memory allocated from the pool. Keeping related memory allocations in one pool helps to reduce memory fragmentation.
Figure 85 shows a schematic representation of the shared memory of the database server, including the memory-duration memory pools.
When the database server calls a UDR, it creates a memory context. This context records all of the allocations that the UDR makes before the routine returns. The UDR might run for some time, calling other UDRs or DataBlade API functions. The database server automatically reclaims shared memory based on its memory duration. When a particular memory duration expires, the database server marks the associated memory pool for deallocation.
The DataBlade API provides the following regular and advanced groups of memory durations for dynamically allocated memory in C UDRs:
Most memory allocations can be allocated with a regular memory duration.
These memory durations are quite long and therefore increase the chance of memory leaks.
The DataBlade API memory-management functions support several public memory durations. A UDR can use a public memory duration for most dynamic allocations of memory. The DataBlade API provides the public memory durations that Table 92 shows.
The PER_ROUTINE and PER_COMMAND memory durations are the most common for C UDRs. The memory-duration constants in Table 92 are of type MI_MEMORY_DURATION, which the memdur.h header file defines. All memory-duration constants in Table 92 are also declared in the memdur.h header file.
A PER_ROUTINE memory pool is associated with each UDR invocation. A routine invocation is one single execution of a UDR within a routine instance.
When a C UDR allocates PER_ROUTINE memory, this memory is available to code within that single routine invocation of that UDR. The database server reclaims any PER_ROUTINE memory in the memory context when a single invocation of a UDR completes. This memory is actually freed on entry to the next routine invocation. The database server does not reclaim any memory in the memory context with a higher duration than PER_ROUTINE.
In a C UDR, the PER_ROUTINE memory duration is useful for information required for a single UDR invocation. A UDR cannot allocate memory, save a pointer to this memory in static space, and expect the pointer to be valid for the next routine invocation. To save information across invocations, use the user-state pointer of the MI_FPARAM structure. For more information, see Saving a User State.
Several DataBlade API constructor functions allocate their DataBlade API data type structure with a PER_ROUTINE memory duration. Table 93 shows the DataBlade API data type structures that have a memory duration of PER_ROUTINE.
The current memory duration is initialized to this default memory duration. The default memory duration is PER_ROUTINE. For more information, see Managing the 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:
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);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 as well as 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.
The database server reclaims any PER_COMMAND memory in the memory context as follows:
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 on 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. Table 94 shows the DataBlade API data type structures that have a memory duration of PER_COMMAND.
Switching the current memory duration before one of the constructor functions in Table 94 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.
A PER_STATEMENT memory pool can be associated with each SQL statement, until execution of the statement is complete and for a prepared statement, until the statement terminates. The statement includes any SQL commands that the SQL statement initiates.
When a C UDR allocates memory with the PER_STATEMENT memory duration, this memory is available to all routine instances that execute in the same SQL statement.
A PER_STMT_EXEC memory pool is associated with the execution of each SQL statement. A statement is the entire SQL statement plus any SQL commands that the SQL statement initiates, as follows:
When a C UDR allocates memory with the PER_STMT_EXEC memory duration, this memory is available to all routine instances that execute in the same SQL statement. For example, suppose that the following SELECT statement invokes the a_func2( ) user-defined function:
SELECT a_func2(x) FROM table1 WHERE y > 7;
Suppose also that the a_func2( ) function calls mi_exec( ) to execute a SELECT that also invokes a_func2( ), as follows:
mi_integer a_func2(arg)
mi_integer arg;
{
...
mi_exec(
"select a_func2(y) from table2 where b_func(y) > 7;", ...)
The SELECT query in the call to mi_exec( ) is a separate SQL command from the main SELECT query. All invocations of the a_func2( ) function in the mi_exec( ) SELECT statement can share any PER_STMT_EXEC memory that this instance of a_func2( ) allocates. They can also share any PER_STMT_EXEC memory that the b_func( ) function (in the WHERE clause) allocates.
The invocations of a_func2( ) in the SELECT on table1 have their own PER_STMT_EXEC memory pool. They would not share it with invocations of a_func2( ) in the mi_exec( ) call.
The database server reclaims any PER_STMT_EXEC memory in the current memory context as follows:
At the completion of execution of a statement, the database server does not reclaim any memory in the memory context with a duration higher than PER_STMT_EXEC. The database server reclaims any PER_STMT_EXEC memory when the SQL statement completes execution, as follows:
This memory is actually freed on entry to the next execution of an SQL statement. After the last (or only) execution of the SQL statement, the database server deallocates the PER_STMT_EXEC memory after sending the status of the SQL statement to the client application. If a statement completes before the status is returned, the database server schedules the memory for release but does not free it until the return value is sent to the client application.
This memory is actually freed on entry to the next open of the cursor. After the last (or only) open of the cursor, the database server deallocates the memory after sending the status of the closed cursor to the client application.
For example, suppose the a_func( ) user-defined function allocates PER_STMT_EXEC memory. The code fragment in Figure 86 shows a UDR that calls a_func( ) in a noncursor statement that executes twice.
mi_integer udr_with_prepared_stmt( )
{
...
stmt3 = mi_prepare(conn,
"insert into tab3 values (a_func(87));", NULL);
/* 1st execution of prepared INSERT */
mi_exec_prepared_statement(stmt3, ...);
/* Code that needs to access PER_STMT_EXEC memory is here */
...
/* 2nd execution of prepared INSERT */
mi_exec_prepared_statement(stmt3, ...);
...
return stat;
}PER_STMT_EXEC memory that a_func( ) allocates in the first call to mi_exec_prepared_statement( ) is released just before the second execution of the prepared INSERT statement begins. Any code after the first mi_exec_prepared_statement( ) call that needs to access this memory can do so. The PER_STMT_EXEC memory that a_func( ) allocates in the second call to mi_exec_prepared_statement( ) remains allocated until the database server returns to the client application the status of the SQL statement that has called the udr_with_prepared_stmt( ) UDR.
The code fragment in Figure 87 shows use of a_func( ) in a cursor statement.
mi_integer get_orders(start_with_cust, end_with_cust)
mi_integer start_with_cust;
mi_integer end_with_cust;
{
mi_string *cmd =
"select order_num, a_func(order_num) from orders \
where customer_num = ?;";
MI_STATEMENT *stmt;
mi_integer i;
...
if ( (stmt = mi_prepare(conn, cmd, NULL)) == NULL )
mi_db_error_raise(NULL, MI_EXCEPTION,
"mi_prepare( ) failed");
if ( start_with_cust > end_with_cust )
mi_db_error_raise(NULL, MI_EXCEPTION,
"Arguments invalid.");
for ( i = start_with_cust; i <= end_with_cust; i++)
{
values[0] = i;
types[0] = "integer";
lengths[0] = 0;
nulls[0] = MI_FALSE;
/* Open the read-only cursor to hold the query rows */
if ( mi_open_prepared_statement(stmt, MI_SEND_READ,
MI_TRUE, 1, values, lengths, nulls, types,
"cust_select", retlen, rettypes)
!= MI_OK )
mi_db_error_raise(NULL, MI_EXCEPTION,
"mi_open_prepared_statement( ) failed");
/* Fetch the retrieved rows into the cursor */
if ( mi_fetch_statement(stmt, MI_CURSOR_NEXT, 0, 3)
!= MI_OK )
mi_db_error_raise(NULL, MI_EXCEPTION,
"mi_fetch_statement( ) failed");
if ( mi_get_result(conn) != MI_ROWS )
mi_db_error_raise(NULL, MI_EXCEPTION,
"mi_get_result( ) failed or found non-query statement");
/* Retrieve the query rows from the cursor */
if ( !(get_data(conn)) )
mi_db_error_raise(NULL, MI_EXCEPTION,
"get_data( ) failed");
/* Close the cursor */
if ( mi_close_statement(stmt) == MI_ERROR )
mi_db_error_raise(NULL, MI_EXCEPTION,
"mi_close_statement( ) failed");
/* Code that needs to access PER_STMT_EXEC memory is here. */
...
} /* end for */
/* Release resources */
if ( mi_drop_prepared_statement(stmt) == MI_ERROR )
mi_db_error_raise(NULL, MI_EXCEPTION,
"mi_drop_prepared_statement( ) failed");
if ( mi_close(conn) == MI_ERROR )
mi_db_error_raise(NULL, MI_EXCEPTION,
"mi_close( ) failed");
}PER_STMT_EXEC memory that a_func( ) allocated is released just before the cursor is reopened. Therefore, any code after the mi_close_statement( ) function that needs to access this memory can do so. However, once the cursor is reopened, code can no longer access this same PER_STMT_EXEC memory. The PER_STMT_EXEC memory that a_func( ) allocates in the previous (or only) open of the cursor remains allocated until the database server returns to the client application the status of the SQL statement that has called the get_orders( ) UDR.
The PER_STMT_EXEC memory duration is useful for communications between UDRs, parallel execution, user-defined aggregates, and named memory, and for memory allocations within an end-of-statement callback (if you have information to pass to the callback).
Several DataBlade API constructor functions allocate their DataBlade API data type structure with a PER_STMT_EXEC memory duration. Table 95 lists DataBlade API data type structures that have a memory duration of PER_STMT_EXEC.
Switching the current memory duration before one of the constructor functions in Table 95 does not change the PER_STMT_EXEC memory duration of the allocated DataBlade API structure. These data type structures are freed by their destructor function or when execution of the current SQL statement completes. To retain access to some of these DataBlade API data type structures after the statement completes, you must save them at the per-session level.
A PER_STMT_PREP memory pool is associated with each prepared SQL 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 Figure 87, 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.
The DataBlade API memory-management functions also support several advanced memory durations, which Table 96 shows.
As with the public memory-duration constants, the advanced memory-duration constants in Table 96 are of type MI_MEMORY_DURATION. However, these constants are declared in the minmdur.h header file, not the memdur.h header file. The minmmem.h header file automatically includes the minmdur.h header file. The mi.h header file, however, does not automatically include minmmem.h. To access advanced memory durations, you must include minmmem.h in any DataBlade API routine that uses these memory durations.
A PER_TRANSACTION memory pool can be associated with either of the following:
If the UDR makes a PER_TRANSACTION allocation during a client transaction, the database server uses memory from the PER_TRANSACTION memory pool. The way that a transaction begins and ends depends on whether the database is ANSI-compliant and whether it uses logging. (For more information, see Transaction Management.)
Statements within a cursor are considered a type of transaction. If the UDR makes a PER_TRANSACTION allocation within a cursor, the database server allocates memory from a special PER_CURSOR memory pool, which lasts from the open to the close of the cursor.
The PER_CURSOR memory duration is for internal use only. However, you might see information about the PER_CURSOR memory pool in the output of onstat -g mem. The database server creates a PER_CURSOR memory pool for each cursor in a transaction.
When a C UDR allocates PER_TRANSACTION memory, this memory is available to all routine instances that execute before the current transaction closes. The database server reclaims any PER_TRANSACTION shared memory in the memory context in either of the following situations:
If a hold cursor is open when the transaction ends, the database server does not deallocate PER_TRANSACTION memory. However, it does deallocate PER_TRANSACTION memory whenever a hold cursor closes.
If the UDR allocated PER_TRANSACTION memory within a cursor, the database server reclaims this memory when the cursor closes.
At this time, the database server does not reclaim any memory in the memory context with a duration higher than PER_TRANSACTION.
The PER_TRANSACTION memory duration is useful for the following tasks:
Allocate PER_TRANSACTION memory as named memory because this memory requires locking. To access it, a C UDR must know the name of the memory and it must be within the scope of the transaction. Such a UDR can explicitly free this memory with the mi_named_free( ) function. However, consider PER_TRANSACTION memory as permanent to the current transaction. For more information, see Managing Named Memory.
No DataBlade API constructor function allocates its data type structure with a memory duration of PER_TRANSACTION.
A PER_SESSION memory pool is associated with each session. A session begins when a client connects to the database server, and it ends when the connection terminates. When a C UDR allocates PER_SESSION memory, this memory is available to all routine instances that execute before the current session ends. When the current session ends, the database server reclaims any PER_SESSION shared memory in the memory context. It does not reclaim any memory in the memory context with a duration higher than PER_SESSION.
The PER_SESSION memory duration is useful for the following tasks:
Allocate PER_SESSION memory as named memory because this memory requires locking. To access it, a C UDR must know the name of the memory and it must be within the scope of the session. Such a UDR can explicitly free this memory with the mi_named_free( ) function. However, consider PER_SESSION memory as permanent to the session. For more information, see Managing Named Memory.
Several DataBlade API constructor functions allocate their DataBlade API data type structures with a PER_SESSION memory duration. Table 97 shows the DataBlade API data type structures that have a memory duration of PER_SESSION.
Switching the current memory duration before one of the constructor functions in Table 97 does not change the PER_SESSION memory duration of the allocated DataBlade API structure. These data type structures are freed by their destructor function or when the current session ends.
A PER_SYSTEM memory pool is associated with the database server instance. A database server instance begins when the oninit utility (or its equivalent) initializes the database server, and it ends when the database server is brought down. When a C UDR allocates PER_SYSTEM memory, this memory is available to all routine instances that execute before the database server instance is shut down. As the database server shuts down, it frees any PER_SYSTEM shared memory.
The PER_SYSTEM memory duration is useful for system-wide caching and resource initialization. Allocate PER_SYSTEM memory as named memory because this memory requires locking. To access it, a C UDR must know the name of the memory. The UDR can explicitly free this memory with the mi_named_free( ) function. However, consider PER_SYSTEM memory as permanent to the database server. For more information, see Managing Named Memory.
No DataBlade API constructor function allocates its data type structure with a memory duration of PER_SYSTEM.
When a UDR needs to allocate memory dynamically, it must take the following actions:
When the UDR allocates memory, it must ensure that this memory has a appropriate memory duration. Choose a memory duration on the basis of which UDR instances need to share the information stored in the memory. Make sure you choose a memory duration that is appropriate to the use of the allocated memory. An inappropriate memory duration can cause the following problems:
For example, if you allocate PER_ROUTINE memory and store its address in the MI_FPARAM structure (which has a PER_COMMAND duration), the memory is freed after one invocation of the UDR, causing the address in the MI_FPARAM to be no longer valid.
Memory leakage can occur when you allocate memory that has a higher duration than the structure that holds its address. For more information, see Monitoring Shared Memory.
Whenever possible, use the following public memory-management features of the DataBlade API:
| mi_alloc( )
mi_dalloc( ) |
mi_zalloc(
mi_free( ) |
mi_switch_mem_duration( )
mi_realloc( ) |
These public functions are appropriate for a UDR that executes in the context of just one SQL statement. The current memory duration, which these functions use, is a useful way to ensure that all allocations occur with the same duration. For more information, see Managing the Memory Duration.
|
PER_ROUTINE
|
PER_STMT_EXEC
|
For more information, see Public Memory Durations. Advanced memory durations are necessary only in certain situations.
In addition to ensuring that the allocated memory has an appropriate memory duration, you must ensure that the UDR can obtain the address of this memory when it needs to access the information within the memory. For example, if you allocate PER_COMMAND memory within a UDR but only store its address in a local variable, this address is deallocated when the UDR completes.
The following table summarizes common ways to save a memory address.