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}…]]
[APPLY {DB ONLY | DB ONLY ON CURRENT MEMBER | DB AND DBM
| DB AND DBM ON CURRENT MEMBER | NONE}]
config-keyword:
MEM_PERCENT, WORKLOAD_TYPE, NUM_STMTS, TPM, ADMIN_PRIORITY, IS_POPULATED
NUM_LOCAL_APPS, NUM_REMOTE_APPS, ISOLATION, BP_RESIZEABLE.
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
Value
————————————————————————————————-
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
Value
————————————————————————————————-
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
Value
————————————————————————————————-
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 SELECT * FROM SYSIBMADM.DBCFG >> E:\temp\DBCFG.txt
db2 SELECT * FROM SYSIBMADM.DBMCFG >> E:\temp\DBMCFG.txt
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