MAX KEPT DYN STMTS field (MAXKEEPD subsystem parameter)
The MAXKEEPD subsystem parameter specifies the maximum number of prepared, dynamic SQL statements that are to be saved past the point of commit or rollback. This parameter applies to applications that run with the KEEPDYNAMIC(YES) bind option.
| Acceptable values: | 0–204800 |
|---|---|
| Default: | 5000 |
| Update: | option 19 on panel DSNTIPB |
| DSNZPxxx: | DSN6SPRM MAXKEEPD |
This is a system-wide limit. This parameter does not limit the size of the dynamic cache itself.
When many applications that are bound with KEEPDYNAMIC(YES) run in a system that has the dynamic statement cache active, they can use a considerable amount of storage in the database services address space (ssnmDBM1). This parameter helps limit the amount of storage that these applications use by limiting the total number of prepared statements held by these applications past the point of commit or rollback. If this limit is exceeded, Db2 honors the KEEPDYNAMIC(YES) behavior, but "implicit" prepares might be necessary to rebuild the executable version of some SQL statements when they are executed after the point of commit or rollback.
If you enter 0, Db2 cannot keep the executable version of dynamic SQL statements past the point of commit or rollback. To retain the KEEPDYNAMIC(YES) behavior after the point of commit or rollback, Db2 implicitly prepares dynamic SQL statements to rebuild the executable version.