MaxPrepares
Attributes
This parameter has the following attributes:
Attribute | Value |
|---|---|
Section | [Agent] |
Abbreviation | None |
Required? | No |
Valid values | 0 to 32767 |
Default value | 50 |
Usage
Specify a number that represents the maximum number of prepared statements for High-speed Apply Engine.
Consider the following points about 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
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.