MaxPrepares
The MaxPrepares parameter defines limits on dynamic SQL processing that the High-speed Apply Engine performs during the apply request. It defines the number of prepared statements that High-speed Apply Engine maintains in memory at the same time.
Attributes
This parameter has the following attributes:
Attribute | Value |
|---|---|
Section | [Agent] |
Abbreviation | None |
DBMS | Db2, Db2 LUW, Oracle |
Required? | No |
Valid values |
|
Default value | 50 |
Usage
Specify a number that represents the maximum number of prepared statements for High-speed Apply Engine.
Consider the following points regarding the MaxPrepares parameter:
- Set this parameter to a lower value to:
- Limit the amount of cached memory that High-speed Apply Engine uses for dynamic SQL processing
Prevent High-speed Apply Engine from needlessly reusing the cached memory for dynamic SQL processing
(For example, if your input does not have many repeated distinct statement types, there is no performance benefit to retaining prepared statements in memory.)
Disable dynamic SQL with prepared statements
(If you set this value to zero, High-speed Apply Engine will process all statements as EXECUTE IMMEDIATE until it binds static SQL.)
- Set this parameter to a higher value to:
- Match the dynamic SQL processing of High-speed Apply Engine to the number of distinct statement types in your input
- Improve dynamic SQL processing during the time before it binds static SQL
For Db2 and Db2 LUW, Db2 buffer pool limitations might force prepared statements to be discarded regardless of the value that you specify for MaxPrepares. When it discards prepared statements because of buffer pool limitations, High-speed Apply Engine re-prepares the statement.
High-speed Apply Engine uses the value of the MaxPrepares parameter to create a database request module (DBRM) or bind file that contains PREPARE/EXECUTE pairs for unique SQL statements in the apply request. If you specify zero, High-speed Apply Engine executes each statement with EXECUTE IMMEDIATE until the statement is bound in a static SQL package (if MaxPackages is greater than 0). For more information, see MaxPackages.
For Oracle targets, the value of MaxPrepares represents the number of cursors that High-speed Apply Engine opens for each agent. High-speed Apply Engine uses the open cursors to create statement handles for unique SQL statements in the apply request. The value that you specify must be between one and the number of open cursors allowed for your Oracle session. The number of open cursors is determined by the OPEN_CURSORS initialization parameter specified for the Oracle session.
If you specify a value for MaxPrepares that is greater than the value of OPEN_CURSORS, High-speed Apply Engine uses the value of OPEN_CURSORS for MaxPrepares. For more information about the OPEN_CURSORS parameter, consult the Oracle server documentation.