The AUTOCONFIGURE command calculates and displays initial values for the buffer pool size, database configuration and database manager configuration parameters, with the option of applying these reported values.
- USING input-keyword param-value
Table 1. Valid input keywords and parameter values Keyword Valid values Default value Explanation mem_percent 1-100
- When analytics is not enabled, the default value is 25
- When analytics is enabled, mem_percent defaults to the higher of the following
- 80% divided by the number of database instances
Percentage of instance memory that is assigned to the database. However, if the CREATE DATABASE command invokes the configuration advisor and you do not specify a value for mem_percent, the percentage is calculated based on memory usage in the instance and the system up to a maximum of 25% of the instance memory. workload_type simple, mixed, complex mixed Simple workloads tend to be I/O intensive and mostly transactions, whereas complex workloads tend to be CPU intensive and mostly queries. num_stmts 1-1 000 000 10 Number of statements per unit of work tpm 1-200 000 60 Transactions per minute admin_priority performance, recovery, both both Optimize for better performance (more transactions per minute) or better recovery time is_populated yes, no yes Is the database populated with data? num_local_apps 0-5 000 0 Number of connected local applications num_remote_apps 0-5 000 10 Number of connected remote applications isolation RR, RS, CS, UR RR Maximum isolation level of applications connecting to this database (Repeatable Read, Read Stability, Cursor Stability, Uncommitted Read). It is only used to determine values of other configuration parameters. Nothing is set to restrict the applications to a particular isolation level and it is safe to use the default value. bp_resizeable yes, no yes Are buffer pools resizeable? grp_tolerance_percent 1-100 5 Percentage of plus or minus tolerance to be used for the Summary Report when comparing configuration variable values to determine what group a member belongs. analytics_env automatic, yes, no automatic Make recommendations for an analytic environment?
- The Configuration Advisor will ignore the DB2_WORKLOAD registry variable and use this hint to determine recommendations for an analytics environment.
- The Configuration Advisor will not make recommendations.
- The Db2® Configuration Advisor will use the value specified for the DB2_WORKLOAD registry variable to determine recommendations for an analytics environment.
- DB ONLY
- Displays the recommended values for the database configuration and the buffer pool settings based on the current database manager configuration. Applies the recommended changes to the database configuration and the buffer pool settings.
- DB AND DBM
- Displays and applies the recommended changes to the database manager configuration, the database configuration, and the buffer pool settings.
- Displays the recommended changes, but does not apply them.
- ON CURRENT MEMBER
- In a partitioned database environment or Db2
environment, the Configuration Advisor updates the database configuration on all members by default.
Specifying the ON CURRENT MEMBER option causes the Configuration Advisor to set
the member-level configuration parameters on the current member determined by your connection, while
the global-level configuration parameters, that can be configured to be functional at only the
global level, are set and affect all
The buffer pool changes are always applied to the system catalogs. Thus, all members are affected. The ON CURRENT MEMBER option is ignored for buffer pool recommendations.
- REPORT FOR
- Specifies the members to include in the report when used in a partitioned database environment or Db2 pureScale environment.
- MEMBER -1
In a partitioned database environment or Db2 pureScale environment, the Configuration Advisor will report the member level configuration parameters computed changes recommended or made on the current member determined by your connection. This is the default if the REPORT FOR clause is not specified.
- MEMBER -2
Indicates that the Configuration Advisor is to report the computed recommendations or changes for all members.
- MEMBER member-number1
Specifies the number of the member the Configuration Advisor is to report the computed recommendations or changes for.
- MEMBERS (member-number2…)
Specifies the numbers for each of the members the Configuration Advisor is to report the computed recommendations or changes for.
- This command makes configuration recommendations for the currently connected database and assumes that the database is the only active database on the instance. If you have not enabled the self tuning memory manager and you have more than one active database on the instance, specify a mem_percent value that reflects the database memory distribution. For example, if you have two active databases on the instance that should use 80% of the instance memory and should share the resources equally, specify 40% (80% divided by 2 databases) as the mem_percent value.
- If you have multiple instances on the same computer and the self tuning memory manager is not enabled, you should set a fixed value for instance_memory on each instance or specify a mem_percent value that reflects the database memory distribution. For example, if all active databases should use 80% of the computer memory and there are 4 instances each with one database, specify 20% (80% divided by 4 databases) as the mem_percent value.
- When explicitly invoking the Configuration Advisor with the AUTOCONFIGURE command, the setting of the DB2_ENABLE_AUTOCONFIG_DEFAULT registry variable will be ignored.
- Running the AUTOCONFIGURE command on a database will recommend enablement of the Self Tuning Memory Manager. However, if you run the AUTOCONFIGURE command on a database in an instance where sheapthres is not zero, sort memory tuning (sortheap) will not be enabled automatically. To enable sort memory tuning (sortheap), you must set sheapthres equal to zero using the UPDATE DATABASE MANAGER CONFIGURATION command. Note that changing the value of sheapthres may affect the sort memory usage in your previously existing databases.
- NODE and DBPARTITIONNUM can be specified in place of MEMBER, except when the DB2_ENFORCE_MEMBER_SYNTAX registry variable is set to ON.