AUTOCONFIGURE command using the ADMIN_CMD procedure

Authorization

SYSADM

Required connection

Database

Command syntax

autoconfigure-clause:

apply-clause:

report-clause

Command parameters

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 values: 25 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? Yes The Configuration Advisor will ignore the DB2_WORKLOAD registry variable and use this hint to determine recommendations for an analytics environment. No The Configuration Advisor will not make recommendations. Automatic The Db2® Configuration Advisor will use the value specified for the DB2_WORKLOAD registry variable to determine recommendations for an analytics environment.

APPLY

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.

NONE

Displays the recommended changes, but does not apply them.

EXCLUDE SUMMARY

Indicates that the Configuration Advisor will not include a summary as part of the report.

INCLUDE SUMMARY

Indicates that the Configuration Advisor will include a summary of all the member groupings based on the computed recommendations as part of the report. This is the default option.

ON CURRENT MEMBER

In a partitioned database environment or Db2 pureScale® 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 members.

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.

REPORT EXISTING SUMMARY

Indicates that the Configuration Advisor will show a summary of all the member groupings based on the existing configuration values.

On pureScale it is recommended that the database should be activated on all members before using this option otherwise the groupings reported for non-activated members may not be accurate.

Example

CALL SYSPROC.ADMIN_CMD( 'AUTOCONFIGURE APPLY NONE' ) 

LEVEL     NAME                 VALUE       RECOMMENDED_VALUE  DATATYPE    
-----...- ----------------...- -------...- ------------------ --------...-
DBM       ASLHEAPSZ            15          15                 BIGINT      
DBM       FCM_NUM_BUFFERS      512         512                BIGINT      
...                                                                       
DB        APP_CTL_HEAP_SZ      128         144                INTEGER     
DB        APPGROUP_MEM_SZ      20000       14559              BIGINT      
...                                                                       
BP        IBMDEFAULTBP         1000        164182             BIGINT      

Usage notes

• 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.
• Command execution status is returned in the SQLCA resulting from the CALL statement.
• The AUTOCONFIGURE command issues a COMMIT statement at the end if its execution. In the case of Type-2 connections this will cause the ADMIN_CMD procedure to return SQL30090N with reason code 2.

Compatibilities

Result set information