DB2 LUW Routines and Views: Document and Improve Your Configuration and Performance

Another one of the benefits to migrating to DB2 LUW for Version 10 are the improvements in the DB2 LUW Routines and Views that can be called for all types of information using simple SQL statements.  With over 200+ DB2 LUW Routines and Views available and many new built-in functions within DB2 there is a lot of automation built into DB2 LUW Version 10.  Many new DB2 LUW Routines and Views provide additional DB2 administration, system information, performance, operational WLM, auditing and other detailed information and most can now be called directly within SQL statements instead of an old-style command line.  Information  about these new and existing routines and views can be found in the Administrative Routines and Views Manual.

Since these DB2 LUW routines and views are built-in they can be used by everyone and will provide consistent results, metrics and messages that everyone can depend on.  Also since they can be referenced from SQL statements they can be incorporated in a variety of new and interesting ways.

Also this manual lists all the deprecated SQL administrative routines and views that are on their way out of support.  Most of these have been replaced with new versions of the routines and you should check your environment against the list.  Your system should use the latest version of the DB2 LUW Routines and View to get the best information possible.

One of the DB2 LUW routines that is still included in Version 10 is the AUTOCONFIGURE command.  By going to a simple DB2 Command Line and using the “AUTOCONFIGURE APPLY NONE” DB2 will provide a listing of Database Manager, Database Configuration and bufferpool sizing recommendations.  These recommendations are based on the standard IBM configurations and each value recommended needs to be evaluated for your workload, but usually the recommendations can improve your overall performance.  Be careful as your mileage may vary.  Make sure to use the “APPLY NONE” to keep your current settings and see the recommendations.  The first command below shows the syntax and the next shows the output piped to an output file of a small test DB2 LUW environment.

db2 ? autoconfigure

AUTOCONFIGURE [USING config-keyword value [{config-keyword value}…]]


NOTE: From the operating system prompt, prefix commands with ‘db2’.
Special characters MAY require an escape sequence (\), for example:
db2 \? change database
db2 ? change database xxx comment with \”text\”

db2 autoconfigure apply none >> E:\temp\autoconfigure.out

Current and Recommended Values for Database Manager Configuration

Description                 Parameter                      Current Value      Recommended
Agent stack size            (AGENT_STACK_SZ) =                    16                  16
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) =                         32767               32767
Sort heap threshold (4KB)   (SHEAPTHRES) =                        250                  0

Current and Recommended Values for Database Configuration

Description               Parameter                       Current Value       Recommended
Default application
heap (4KB)             (APPLHEAPSZ) =                        256                 256
Catalog cache size (4KB) (CATALOGCACHE_SZ) =                (MAXAPPLS*5)           564
Changed pages threshold  (CHNGPGS_THRESH) =                     60                  80
Database heap (4KB)      (DBHEAP) =                            600                3442
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                1024
Number of primary
log files             (LOGPRIMARY) =                          3                  10
Number of secondary
log files             (LOGSECOND) =                          10                  12
Max number of active
applications           (MAXAPPLS) =                        AUTOMATIC          AUTOMATIC
Percent. of lock lists
per application       (MAXLOCKS) =                           22              AUTOMATIC
Number of asynchronous
page cleaners         (NUM_IOCLEANERS) =                      4                   2
Number of I/O servers    (NUM_IOSERVERS) =                       3                  37
Package cache size (4KB) (PCKCACHESZ) =                      (MAXAPPLS*8)       AUTOMATIC
Percent log file
reclaimed before soft
chckpt                (SOFTMAX) =                           100                 400
Sort list heap (4KB)     (SORTHEAP) =                          256              AUTOMATIC
SQL statement heap (4KB) (STMTHEAP) =                         2048                2048
Statistics heap size(4KB)(STAT_HEAP_SZ) =                     4384                4384
Utilities heap size (4KB)(UTIL_HEAP_SZ) =                     5000               31909
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                2149

Current and Recommended Values for Bufferpool(s)

Description               Parameter                       Current Value       Recommended
IBMDEFAULTBP              Bufferpool size =                    250               47864       

In addition to the AUTOCONFIGURE command getting the Database Manager Configuration (DBM) and the Database Configuration (DB) settings can now be retrieved using simple SQL Statements.  After your migration to DB2 LUW Version 10 make sure to do these SQL statements below to verify all your DBM and DB settings.

Especially remember to check your DB Configuration locktimeout setting and make sure that it is properly set.  Remember the default of -1 tells DB2 that you want to wait forever for getting your locks which may not be the desired setting and may be causing all your deadlocks in your application system.  Check the Database Administration Concepts and Configuration Reference Manual for the settings available.  Usually checking all the DBM and DB configuration -1 defaults is a good idea since they might need to be improved for your system situation or workload performance.



DB2 LUW Routines and Views have been improved in Version 10 with the simple SQL statement interface.  The SQL statement interface provides an easy way to get all your system/database configuration information, perform DB2 administration, do performance monitoring, WLM adjust operations, auditing and other operations and detailed informational tasks for making your DB2 LUW performance better.

Merry Christmas and Happy New Year!


Dave Beulke is an internationally recognized DB2 consultant, DB2 trainer and education instructor.  Dave helps his clients improve their strategic direction, dramatically improve DB2 performance and reduce their CPU demand saving millions in their systems, databases and application areas within their mainframe, UNIX and Windows environments.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>