Example: Requesting configuration recommendations using the
Configuration Advisor
This scenario demonstrates to run the Configuration Advisor
from the command line to generate recommendations and shows the output
that the Configuration Advisor produces.
To run the Configuration Advisor:
Connect to the PERSONL database by specifying the following command
from the command line:
DB2 CONNECT TO PERSONL
Issue the AUTOCONFIGURE command from the CLP,
specifying how the database is used. As shown in the following example,
set a value of NONE for the APPLY option
to indicate that you want to view the configuration recommendations
but not apply them:
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.
The recommendations generated by the AUTOCONFIGURE command
are displayed on the screen in table format, as shown in Figure 1Figure 1. Configuration Advisor sample output
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.