AUTOCONFIGURE command using the ADMIN_CMD procedure

Authorization

SYSADM

Required connection

Database

Command syntax

>>-AUTOCONFIGURE--+---------------------------------------+----->
                  |        .----------------------------. |   
                  |        V                            | |   
                  '-USING----input-keyword--param-value-+-'   

>--APPLY--+-DB ONLY----+--+-----------------+------------------><
          +-DB AND DBM-+  '-ON CURRENT NODE-'   
          '-NONE-------'                        

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	25	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?

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.

ON CURRENT NODE

In a partitioned database environment, the Configuration Advisor updates the database configuration on all nodes by default. Running with the ON CURRENT NODE option makes the advisor apply the recommended database configuration to the coordinator (connection) node only.

The buffer pool changes are always applied to the system catalogs. Thus, all nodes are affected. The ON CURRENT NODE option does not matter for buffer pool recommendations.

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.
• SQL executed in the ADMIN_CMD procedure on behalf of AUTOCONFIGURE is monitored by Query Patroller.
• 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.

Result set information