This scenario demonstrates to run the Configuration Advisor from the command line to generate recommendations and shows the output that the Configuration Advisor produces.
DB2 CONNECT TO PERSONL
DB2 AUTOCONFIGURE USING
MEM_PERCENT 60
WORKLOAD_TYPE MIXED
NUM_STMTS 500
ADMIN_PRIORITY BOTH
IS_POPULATED YES
NUM_LOCAL_APPS 0
NUM_REMOTE_APPS 20
ISOLATION RR
BP_RESIZEABLE YES
APPLY NONE
If
you are unsure about the value of a parameter for the command, you
can omit it, and the default will be used. You can pass up to 10 parameters
without values: MEM_PERCENT, WORKLOAD_TYPE,
and so on, as shown in the previous example.Current and Recommended Values for Database Manager Configuration
Description Parameter Current Value Recommended Value
-------------------------------------------------------------------------------------------------
Application support layer heap size (4KB) (ASLHEAPSZ) = 15 15
No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC AUTOMATIC
Enable intra-partition parallelism (INTRA_PARALLEL) = NO NO
Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY 1
Agent pool size (NUM_POOLAGENTS) = AUTOMATIC(100) AUTOMATIC(100)
Initial number of agents in pool (NUM_INITAGENTS) = 0 0
Max requester I/O block size (bytes) (RQRIOBLK) = 65535 65535
Sort heap threshold (4KB) (SHEAPTHRES) = 0 0
Current and Recommended Values for Database Configuration
Description Parameter Current Value Recommended Value
-------------------------------------------------------------------------------------------------
Default application heap (4KB) (APPLHEAPSZ) = 256 256
Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*5) 419
Changed pages threshold (CHNGPGS_THRESH) = 60 80
Database heap (4KB) (DBHEAP) = 1200 20409
Degree of parallelism (DFT_DEGREE) = 1 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32 32
Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC AUTOMATIC
Default query optimization class (DFT_QUERYOPT) = 5 5
Max storage for lock list (4KB) (LOCKLIST) = 4096 AUTOMATIC
Log file size (4KB) (LOGFILSIZ) = 1000 4096
Number of primary log files (LOGPRIMARY) = 3 29
Number of secondary log files (LOGSECOND) = 10 0
Max number of active applications (MAXAPPLS) = AUTOMATIC AUTOMATIC
Percent. of lock lists per application (MAXLOCKS) = 10 AUTOMATIC
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1 3
Number of I/O servers (NUM_IOSERVERS) = 16 3
Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8) AUTOMATIC
Sort list heap (4KB) (SORTHEAP) = 256 AUTOMATIC
SQL statement heap (4KB) (STMTHEAP) = 8192 8192
Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384 4384
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000 5000
Self tuning memory (SELF_TUNING_MEM) = OFF ON
Automatic runstats (AUTO_RUNSTATS) = ON ON
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = 5000 AUTOMATIC
Log buffer size (4KB) (LOGBUFSZ) = 256 16683
Default table organization (DFT_TABLE_ORG) = ROW ROW
Database memory threshold (DB_MEM_THRESH) = 100 100
Current and Recommended Values for Bufferpool(s)
Description Parameter Current Value Recommended Value
-------------------------------------------------------------------------------------------------
IBMDEFAULTBP Bufferpool size = 1000 1000
Current and Recommended Values for System WLM Objects
Description Current Value Recommended Value
-------------------------------------------------------------------------------------------------
Work Action SYSMAPMANAGEDQUERIES Enabled = Y Y
Work Action Set SYSDEFAULTUSERWAS Enabled = Y Y
Work Class SYSMANAGEDQUERIES Timeroncost = 7.50000E+04 7.50000E+04
Threshold SYSDEFAULTCONCURRENT Enabled = N N
Threshold SYSDEFAULTCONCURRENT Maxvalue = 8 8
DB210205W The Configuration Advisor was unable to increase the sizes of the
buffer pools due to other memory requirements determined from your responses.
The buffer pool sizes are left unchanged. The use of the suggested set of
configuration values may cause paging on the server.
DB210203I AUTOCONFIGURE completed successfully. Database manager or database
configuration values may have been changed if you chose to apply changes. The
instance must be restarted before any such applied changes come into effect.
You may also want to rebind your packages after the new configuration
parameters take effect so that the new values will be used.
If you agree with all of the recommendations, either reissue the AUTOCONFIGURE command but specify that you want the recommended values to be applied by using the APPLY option, or update individual configuration parameters using the UPDATE DATABASE MANAGER CONFIGURATION command and the UPDATE DATABASE CONFIGURATION command.