Batch Statement Execution
The OpenAPI function IIapi_batch() (see IIapi_batch() Function--Execute Batch Statements on page 9) lets you execute a set of statements in a batch.
You can execute batch statements with or without parameters, or with multiple parameter sets. Each of these scenarios is described in the following sections.
How You Can Maximize Performance
You can improve performance generally by following these guidelines:
• If you have large batches of inserts, we recommend that you use prepared statements. Using prepared insert statements with large batches (of more than 100) can significantly improve performance.
• If you are using prepared statements for batch, we recommend that you make your batches as large as possible. Larger batch sizes can make a significant difference with insert performance—even 2 or 3 times faster. In fact, when using prepared insert statements, the larger the batch, the better the performance. Batch sizes up to 100,000 have been noted to significantly improve performance.
• If you must use small batches (of less than 100), then you should avoid using prepared statements. The DBMS optimization works well only for large batches; for small batches, you can achieve better performance by batching non-prepared inserts.
Faster inserts also can be achieved if the following conditions are met:
• Inserts must be into a base table (not a view or index).
• The table must not have any rules or integrities defined on it.
• The table must not be a gateway table (for example, an IMA table, security audit log file, or an Enterprise Access table).
• The inserts must be batched.
• The batched statements must be an execution of a prepared dynamic insert where the dynamic parameters exactly match the values being inserted.
• All but the first statement in the batch should be sent without query text, to achieve an additional performance boost. The prepared statement should be executed in batch as a single statement with multiple parameter sets (see How Batch Statements with Multiple Parameter Sets Are Repeated).
How Batch Statements without Parameters Are Executed
A set of n statements may be executed as a batch by calling IIapi_batch() n times, once for each statement in the batch. For the first statement in the batch, the statement handle passed to IIapi_batch() is set to NULL and a new statement handle is returned after the call to IIapi_batch(). The returned statement handle is used for all subsequent calls associated with the batch.
After the last statement in the batch is submitted, the statement execution results may be retrieved by calling IIapi_getQueryInfo(). In general, IIapi_getQueryInfo() should be called once for each query in the batch. The results for each query in the batch will be returned in the order the statements were submitted for execution:
IIapi_getQueryInfo() returns one of the following statuses to indicate the status of the associated statement executed in the batch:
• IIAPI_ST_SUCCESS
• IIAPI_ST_MESSAGE
• IIAPI_ST_WARNING
• IIAPI_ST_ERROR
Errors caused by how the application calls IIapi_getQueryInfo() are indicated by a status of:
• IIAPI_ST_FAILURE
• IIAPI_ST_NOT_INITIALIZED
• IIAPI_ST_INVALID_HANDLE
• IIAPI_ST_OUT_OF_MEMORY
Certain errors during batch processing may result in fewer results being returned than the number of statements contained in the batch. When this occurs, IIapi_getQueryInfo() returns a status of IIAPI_ST_NO_DATA after all the available results are returned. Even if a critical error does not occur, IIapi_getQueryInfo() returns a status of IIAPI_ST_NO_DATA after the results for all statements in the batch are returned.
How Batch Statements with Parameters Are Executed
For statements that contain parameter markers (~V), dynamic parameter markers (?), or database procedure parameters, the parameter meta-data and values can be provided by calling IIapi_setDescriptor() and IIapi_putParms() after the call to IIapi_batch() for a particular statement:
After all parameter values for a statement have been provided by calling IIapi_putParms(), the next batch statement may be submitted by calling IIapi_batch() or batch results retrieved by calling IIapi_getQueryInfo().
How Batch Statements with Multiple Parameter Sets Are Repeated
Certain statements such as insert statements, prepared statements, and database procedures, are likely to be executed multiple times in a batch with a different parameter set for each execution. Rather than calling IIapi_batch() with the same statement information for each parameter set, you can submit multiple parameter sets for a statement by calling IIapi_setDescriptor() and IIapi_putParms() for each parameter set after the call to IIapi_batch():
Each parameter set begins with a call to IIapi_setDescriptor() and ends when all parameter values have been provided with IIapi_putParms(). The statement is executed for each parameter set provided, and execution results are returned for each execution/parameter set. Providing a statement and n parameter sets is the same as providing the same statement n times, each with a single parameter set, except that the former may be optimized to reduce or eliminate redundant operations.
After the last parameter set is provided, you can submit the next batch statement by calling IIapi_batch() or retrieve the batch results by calling IIapi_getQueryInfo().
Note: The database procedure name and, optionally, the procedure owner, are provided as service parameters in the parameter set. Even though these will be the same for each repeated execution of a database procedure, these service parameters must still be included with each parameter set.
Last modified date: 01/30/2023