#!/bin/sh # # db2mon Monitor DB2 Common Server and provide tuning suggestions # where possible/appropriate # # It takes two parameters: the database name and the # measurement interval in seconds. # # Author: Allan Packer, # Sun Microsystems, Inc. # Date: June 3, 1996 # # if test $# -ne 2; then echo "usage: $0 dbname interval" exit 1 fi DBNAME=$1 INTERVAL=$2 SNAPFILE=/tmp/snap.log echo echo "Monitoring Database $DBNAME for $INTERVAL seconds" date echo # You should be able to inherit the following environment variables # from your environment # #DB2HOME=/db2/db2.new; export DB2HOME #PATH=$PATH:$DB2HOME/sqllib/bin:$DB2HOME/sqllib/adm:$DB2HOME/sqllib/misc #LD_LIBRARY_PATH=$DB2HOME/lib:/usr/lib; export LD_LIBRARY_PATH #DB2INSTANCE=db2new; export DB2INSTANCE db2 get monitor switches | nawk '{ if (index($0, "Buffer Pool Activity Information") > 0) bufferpool = $7 if (index($0, "Lock Information") > 0) lock = $5 if (index($0, "Sorting Information") > 0) sort = $5 } END { printf("BUFFERPOOL %s LOCK %s SORT %s\n", bufferpool, lock, sort) }' > /tmp/mon_switches db2 "update monitor switches using BUFFERPOOL on LOCK on SORT on" db2 reset monitor all db2 connect to $DBNAME sleep $INTERVAL db2 get snapshot for database on $DBNAME > $SNAPFILE db2 get snapshot for database manager >> $SNAPFILE db2 "update monitor switches using `cat /tmp/mon_switches`" rm -f /tmp/mon_switches db2 get db cfg for $DBNAME >> $SNAPFILE db2 get dbm cfg >> $SNAPFILE cat $SNAPFILE | nawk '{ if (index($0, "Buffer pool data logical reads") > 0) p_d_l_reads = $7 if (index($0, "Buffer pool data physical reads") > 0) p_d_p_reads = $7 if (index($0, "Buffer pool index logical reads") > 0) p_i_l_reads = $7 if (index($0, "Buffer pool index physical reads") > 0) p_i_p_reads = $7 if (index($0, "Buffer pool data writes") > 0) p_d_writes = $6 if (index($0, "Buffer pool index writes") > 0) p_i_writes = $6 if (index($0, "Asynchronous pool data page reads") > 0) p_a_d_reads = $7 if (index($0, "Asynchronous pool data page writes") > 0) p_a_d_writes = $7 if (index($0, "Asynchronous pool index page writes") > 0) p_a_i_writes = $7 if (index($0, "Asynchronous read requests") > 0) p_a_r_requests = $5 if (index($0, "Total elapsed asynchronous read time") > 0) p_a_r_time = $7 if (index($0, "Total buffer pool read time (ms)") > 0) p_d_r_time = $8 if (index($0, "Number of I/O servers") > 0) num_ioservers = $7 if (index($0, "Sequential detect flag") > 0) seqdetect = $6 if (index($0, "Default prefetch size (4KB)") > 0) dft_prefetch_sz = $7 if (index($0, "Default tablespace extentsize (4KB)") > 0) dft_extent_sz = $7 if (index($0, "High water mark for database heap") > 0) hwm_db_heap = $8 if (index($0, "Database heap (4KB)") > 0) db_heap = $6 if (index($0, "Catalog cache lookups") > 0) cat_cache_lookups = $5 if (index($0, "Catalog cache inserts") > 0) cat_cache_inserts = $5 if (index($0, "Catalog cache overflows") > 0) cat_cache_overflows = $5 if (index($0, "Catalog cache heap full") > 0) cat_cache_heap_full = $6 if (index($0, "DDL statements executed") > 0) ddl_sql_stmts = $5 if (index($0, "Catalog cache size (4KB)") > 0) catalogcache_sz = $7 if (index($0, "Log buffer size (4KB)") > 0) logbufsz = $7 if (index($0, "Maximum total log space used (Bytes)") > 0) log_space_used = $8 if (index($0, "Utilities heap size (4KB)") > 0) util_heap_sz = $7 if (index($0, "Backup buffer default size (4KB)") > 0) backbufsz = $8 if (index($0, "Restore buffer default size (4KB)") > 0) restbufsz = $8 if (index($0, "Sort list heap (4KB)") > 0) sortheap = $7 if (index($0, "Sort heap threshold (4KB)") > 0) sheapthres = $7 if (index($0, "Total sorts ") > 0) total_sorts = $4 if (index($0, "Total sort time (ms)") > 0) total_sort_time = $4 if (index($0, "Sort overflows") > 0) sort_overflows = $4 if (index($0, "Total sort heap allocated") > 0) sort_heap_allocated = $6 if (index($0, "Active sorts") > 0) active_sorts = $4 if (index($0, "Index sort flag") > 0) indexsort = $6 if (index($0, "SQL statement heap (4KB)") > 0) stmtheap = $7 if (index($0, "Default application heap (4KB)") > 0) applheapsz = $7 if (index($0, "Package cache size (4KB)") > 0) pckcachesz = $7 if (index($0, "Package cache lookups") > 0) pkg_cache_lookups = $5 if (index($0, "Package cache inserts") > 0) pkg_cache_inserts = $5 if (index($0, "Statistics heap size (4KB)") > 0) stat_heap_sz = $7 if (index($0, "Number of frequent values retained") > 0) num_freqvalues = $8 if (index($0, "Number of quantiles retained") > 0) num_quantiles = $7 if (index($0, "Query heap size (4KB)") > 0) query_heap_sz = $7 if (index($0, "Application support layer heap size (4KB)") > 0) aslheapsz = $9 if (index($0, "Max requester I/O block size (bytes)") > 0) rqrioblk = $9 if (index($0, "Database monitor heap size (4KB)") > 0) mon_heap_sz = $8 if (index($0, "Directory cache support") > 0) dir_cache = $6 if (index($0, "Interval for checking deadlock (ms)") > 0) dlchktime = $8 if (index($0, "Lock timeout (sec)") > 0) locktimeout = $6 if (index($0, "Percent. of lock lists per application") > 0) maxlocks = $9 if (index($0, "Max storage for lock lists (4KB)") > 0) locklist = $9 if (index($0, "Lock list memory in use (Bytes)") > 0) locklist_mem = $8 if (index($0, "Lock escalations") > 0) lock_escals = $4 if (index($0, "Exclusive lock escalations") > 0) x_lock_escals = $5 if (index($0, "Locks held currently") > 0) cur_locks = $5 if (index($0, "Current applications waiting on locks") > 0) cur_apps_locks = $7 if (index($0, "Lock timeouts") > 0) locktimeouts = $4 if (index($0, "Deadlocks detected") > 0) deadlocks = $4 if (index($0, "Lock waits") > 0) lock_waits = $4 if (index($0, "Time database waited on locks (ms)") > 0) lock_wait_time = $8 if (index($0, "Changed pages threshold") > 0) chngpgs_thresh = $6 if (index($0, "Number of asynchronous page cleaners") > 0) num_iocleaners = $8 if (index($0, "LSN Gap cleaner triggers") > 0) lsn_triggers = $6 if (index($0, "Dirty page steal cleaner triggers") > 0) dirty_steal_triggers = $7 if (index($0, "Dirty page threshold cleaner triggers") > 0) dirty_thresh_triggers = $7 if (index($0, "Percent log file reclaimed before soft chckpt") > 0) softmax = $10 if (index($0, "Group commit count") > 0) mincommit = $6 if (index($0, "Max number of existing agents") > 0) maxagents = $8 if (index($0, "Max number of concurrent agents") > 0) maxcagents = $8 if (index($0, "Maximum number of idle agents") > 0) max_idleagents = $8 if (index($0, "Idle agents") > 0) idleagents = $4 if (index($0, "Max number of active applications") > 0) maxappls = $8 if (index($0, "Average number of active applications") > 0) ave_appls = $8 if (index($0, "Max DB files open per application") > 0) maxfilop = $9 if (index($0, "Database files closed") > 0) files_closed = $5 if (index($0, "High water mark for connections") > 0) conns_registered_top = $7 if (index($0, "Agents registered") > 0) agents_registered = $4 if (index($0, "Agents waiting for a token") > 0) agents_waiting = $7 if (index($0, "High water mark for agents registered") > 0) agents_registered_top = $8 if (index($0, "High water mark for agents waiting for a token") > 0) agents_waiting_top = $11 if (index($0, "Remote connections to db manager") > 0) rem_cons = $7 if (index($0, "Local connections") > 0) local_cons = $4 if (index($0, "Application connects") > 0) appl_cons = $4 if (index($0, "Applications connected currently") > 0) appl_cur_cons = $5 if (index($0, "Appls. executing in db manager currently") > 0) appl_cur_exec = $8 if (index($0, "Remote connections executing in db manager") > 0) rem_cur_exec = $8 if (index($0, "Local connections executing in db manager") > 0) local_cur_exec = $8 if (index($0, "Keep DARI process") > 0) keepdari = $6 if (index($0, "Max number of DARI processes") > 0) maxdari = $8 if (index($0, "Log file size (4KB)") > 0) logfilsiz = $7 if (index($0, "Number of primary log files") > 0) logprimary = $8 if (index($0, "Number of secondary log files") > 0) logsecond = $8 if (index($0, "Log retain for recovery enabled") > 0) logretain = $8 if (index($0, "Secondary logs allocated currently") > 0) cur_sec_logs = $6 if (index($0, "Log pages read") > 0) logreads = $5 if (index($0, "Log pages written") > 0) logwrites = $5 if (index($0, "Commit statements attempted") > 0) commit_sql_stmts = $5 if (index($0, "Rollback statements attempted") > 0) rollback_sql_stmts = $5 if (index($0, "CPU speed (millisec/instruction)") > 0) cpuspeed = $6 if (index($0, "Auto restart enabled") > 0) autorestart = $6 if (index($0, "Index re-creation time") > 0) indexrec = $6 " " $7 if (index($0, "Default number of loadrec sessions") > 0) dft_loadrec_ses = $8 if (index($0, "Recovery history retention (days)") > 0) rec_his_retentn = $7 if (index($0, "Post threshold sorts") > 0) post_threshold_sorts = $5 if (index($0, "Piped sorts accepted") > 0) piped_sorts_accepted = $5 if (index($0, "Piped sorts requested") > 0) piped_sorts_requested = $5 } END { printf("\nMONITORING THE BUFFER POOL\n\n") printf("Buffer Pool and Index Pool Hit Rate - the higher, the better\n") printf("===================================\n") printf("%-40s = %d\n", "Buffer pool data logical reads", p_d_l_reads) printf("%-40s = %d\n", "Buffer pool data physical reads", p_d_p_reads) printf("%-40s = %d\n", "Buffer pool index logical reads", p_i_l_reads) printf("%-40s = %d\n", "Buffer pool index physical reads", p_i_p_reads) if ((p_d_l_reads+p_i_l_reads) > 0) { printf("\nBuffer Pool Hit Rate = %.2f %%\n", (1 - ((p_d_p_reads+p_i_p_reads)/(p_d_l_reads+p_i_l_reads)))*100) } else { printf("\nBuffer Pool Hit Rate = 0.00 %%\n") } if (p_d_l_reads > 0) { printf("Buffer Pool Data Hit Rate = %.2f %%\n", (1 - (p_d_p_reads/p_d_l_reads))*100) } else { printf("Buffer Pool Data Hit Rate = 0.00 %%\n") } if (p_i_l_reads > 0) { printf("Buffer Pool Index Hit Rate = %.2f %%\n", (1 - (p_i_p_reads/p_i_l_reads))*100) } else { printf("Buffer Pool Index Hit Rate = 0.00 %%\n") } printf("\nImpact of Prefetchers\n") printf("=====================\n") printf("%-40s = %d\n", "Number of I/O servers (prefetchers)", num_ioservers) printf("\n%-40s = %s\n", "Sequential Detect Flag", seqdetect) printf("If sequential detect is set to NO, prefetching takes place only if the\n") printf("database manager determines it will be useful for table sorts, table\n") printf("scans or list prefetch. If it is set to YES, prefetching will occur\n") printf("whenever the database manager detects sequential page reading.\n") printf("\n%-40s = %ld (%ld bytes)\n", "Default Prefetch Size (4KB)", dft_prefetch_sz, dft_prefetch_sz * 4096) printf("%-40s = %ld (%ld bytes)\n", "Default Tablespace Extentsize (4KB)", dft_extent_sz, dft_extent_sz * 4096) printf("The default prefetch/extent size is only used for a tablespace if\n") printf("PREFETCHSIZE/EXTENTSIZE was not specified during CREATE TABLESPACE.\n") printf("The prefetchsize and extentsize together determine the amount of\n") printf("prefetching that will be done for a tablespace.\n") printf("\nThe following hit rate does not include data read by the ") printf("prefetchers.\nIf this is much higher than the buffer pool hit ") printf("rate (above), the\nprefetchers may be causing inefficiencies.\n\n") printf("%-40s = %d\n", "Asynchronous pool data page reads", p_a_d_reads) printf("%-40s = %d\n", "Asynchronous read requests", p_a_r_requests) if ((p_d_l_reads+p_i_l_reads) > 0) { printf("Buffer Pool Hit Rate (excluding prefetched data) = %.2f %%\n", (1 - ((p_d_p_reads-p_a_d_reads+p_i_p_reads)/(p_d_l_reads+p_i_l_reads)))*100) } else { printf("Buffer Pool Hit Rate (excluding prefetched data) = 0.00 %%\n") } printf("\nThe following ratio helps determine how much I/O was done\n") printf("by the prefetchers per interaction:") if (p_a_r_requests > 0) { printf("\n%-40s = %d\n", "Average number of data pages read per prefetcher request", p_a_d_reads/p_a_r_requests) } else { printf("\n%-40s = 0\n", "Average number of data pages read per prefetcher request") } printf("\nOther Buffer Statistics\n") printf("=======================\n") printf("The following average read time should not increase ") printf("significantly as you\ntune the buffer pool.\n") if ((p_d_l_reads-p_a_d_reads+p_i_l_reads) > 0) { printf("\nAverage Synchronous I/O read time = %.2f ms\n", (p_d_r_time+p_a_r_time)/(p_d_l_reads+p_i_l_reads)) } else { printf("\nAverage Synchronous I/O read time = 0.00 ms\n") } printf("\nBuffer Pool pages are written to disk\n") printf(" - to free a page in the buffer pool so another data page") printf(" can be read, or\n") printf(" - to flush the data pool.\n") printf("It may be possible to increase the ratio of reads to writes ") printf("by increasing\n") printf("the size of the buffer pool. Refer to the ratios below.\n") printf("%-40s = %d\n", "Buffer pool data writes", p_d_writes) printf("%-40s = %d\n", "Buffer pool index writes", p_i_writes) if (p_d_writes > 0) { printf("\nBuffer Pool Data Physical Reads/Physical Writes = %.2f %%\n", p_d_p_reads/p_d_writes * 100.) } else { printf("\nBuffer Pool Data Physical Reads/Physical Writes = 100.00 %%\n") } if (p_i_writes > 0) { printf("Buffer Pool Index Physical Reads/Physical Writes = %.2f %%\n", p_i_p_reads/p_i_writes * 100.) } else { printf("Buffer Pool Index Physical Reads/Physical Writes = 100.00 %%\n") } printf("\nAsynchronous Page Cleaners") printf("\n==========================\n") printf("%-40s = %d %%\n", "Changed pages threshold", chngpgs_thresh) printf("This threshold specifies the percentage of changed pages at which\n") printf("the asynchronous page cleaners will be triggered.\n") printf("%-40s = %d\n", "Dirty Page Threshold Cleaner Triggers", dirty_thresh_triggers) printf("\n%-40s = %d\n", "No. of Page Cleaners", num_iocleaners) printf("Query-only databases do not require page cleaners. Large buffer pools\n") printf("and large numbers of disks typically require more page cleaners.\n") printf("\n%-40s = %d\n", "Buffer pool data writes", p_d_writes) printf("%-40s = %d\n", "Asynchronous pool data page writes", p_a_d_writes) if (p_d_writes > 0) { printf("Percentage of data page writes carried out by page cleaners = %.2f %%\n", (p_a_d_writes / p_d_writes) * 100.) } else { printf("Percentage of data page writes carried out by page cleaners = 0.00 %%\n") } printf("\n%-40s = %d\n", "Buffer pool index writes", p_i_writes) printf("%-40s = %d\n", "Asynchronous pool index page writes", p_a_i_writes) if (p_i_writes > 0) { printf("Percentage of index page writes carried out by page cleaners = %.2f %%\n", (p_a_i_writes / p_i_writes) * 100.) } else { printf("Percentage of index page writes carried out by page cleaners = 0.00 %%\n") } printf("\nIf most writes are not being carried out by the page cleaners (see above),\n") printf("increase NUM_IOCLEANERS (the number of page cleaners). In general, the\n") printf("more writes done by the page cleaners the better, although if pages\n") printf("are cleaned too vigorously, you may be unnecessarily flushing pages\n") printf("that will soon be dirtied again.\n") printf("%-40s = %d\n", "Dirty Page Steal Cleaner Triggers", dirty_steal_triggers) printf("The page cleaners will be triggered when an agent steals a dirty page\nbecause the page cleaners are not keeping up with demand.\n") printf("\n%-40s = %d\n", "LSN Gap cleaner triggers", lsn_triggers) printf("The page cleaners will be triggered if SOFTMAX (the LSN gap) is exceeded.\n") printf("\nSorts") printf("\n=====\n") printf("%-40s = %ld (%ld bytes)\n", "Sort List Heap (4KB)", sortheap, sortheap * 4096) printf("Sort List Heap defines the max no. of private memory pages to be used for") printf("\neach sort. This memory is deallocated after use. Bigger is usually better.\n") printf("\n%-40s = %ld (%ld bytes)\n", "Sort Heap Threshold (4KB)", sheapthres, sheapthres * 4096) printf("Sort Heap Threshold is not a hard limit, but if it is exceeded, the faster\n") printf("\"pipe\" sorts will not be performed. The threshold impacts the total amount\n") printf("of memory that can be allocated for all concurrent sort heaps.\n") printf("In general, the more Sort Heap Threshold memory the better.\n") printf("\n%-40s = %ld\n", "Active Sorts", active_sorts) printf("%-40s = %ld\n", "Total Sort Heap Allocated", sort_heap_allocated) printf("%-40s = %ld\n", "Total Sort Time (ms)", total_sort_time) printf("%-40s = %ld\n", "Total Sorts", total_sorts) if (total_sorts > 0) { printf("Average elapsed time per sort = %.2f ms\n", total_sort_time / total_sorts) printf("Sorts requiring merge phases = %.2f %%\n", 100. * sort_overflows / total_sorts) printf("i.e. started as piped sort, but required more memory than allocated.\n") } else { printf("Average elapsed time per sort = 0.00 ms\n") printf("Sorts requiring merge phases = 0.00 %%\n") printf("i.e. started as piped sort, but required more memory than allocated.\n") } printf("\n%-40s = %ld\n", "Post Threshold Sorts", post_threshold_sorts) if (post_threshold_sorts > 0) { printf("Percentage of Post Threshold Sorts = %.2f\n", 100. * post_threshold_sorts / total_sorts) printf("Increase the Sort Heap Threshold to eliminate Post Threshold Sorts.\n") } printf("\n%-40s = %ld\n", "Piped Sorts Requested", piped_sorts_requested) printf("%-40s = %ld\n", "Piped Sorts Accepted", piped_sorts_accepted) if (piped_sorts_requested > 0) { printf("Piped Sorts Serviced = %.2f %%\n", 100. * piped_sorts_accepted / piped_sorts_requested) } printf("\n%-40s = %s\n", "Index Sort Flag", indexsort) printf("The Index Sort Flag determines whether or not sorting of index keys\n") printf("will occur during index creation. Index creation will perform better\n") printf("by doing a sort first.\n") printf("\nLock Information") printf("\n================\n") printf("%-40s = %d\n", "Interval for Checking Deadlock (ms)", dlchktime) printf("A long deadlock checking interval means less frequent checks and therefore\n") printf("less system time, but also a longer wait for deadlocked applications.\n") printf("%-40s = %d\n", "Deadlocks Detected", deadlocks) printf("\n%-40s = %d\n", "Lock Timeout Interval (sec)", locktimeout) printf("A locktimeout of -1 means lock timeout detection is turned off.\n") printf("%-40s = %d\n", "Lock Timeouts", locktimeouts) printf("\nThe interval for deadlock checking should be less than the lock timeout\n") printf("interval, so deadlocks are eliminated before locks, that might be\n") printf("successfully received, are rolled back.\n") printf("\n%-40s = %d\n", "Percent. of Lock Lists per Application", maxlocks) printf("This parameter defines a percentage of the lock list that must be\n") printf("filled by a connected process before DB2 performs lock escalation.\n") printf("\n%-40s = %ld (%ld bytes)\n", "Max Storage for Lock Lists (4KB)", locklist, locklist * 4096) printf("If the lock list is too small, lock escalation can result.\n") printf("%-40s = %d bytes\n", "Lock list memory in use (Bytes)", locklist_mem) printf("%-40s = %d\n", "Lock Escalations", lock_escals) printf("%-40s = %d\n", "Exclusive Lock Escalations", x_lock_escals) printf("\n%-40s = %d\n", "Locks Held Currently", cur_locks) printf("%-40s = %d\n", "Current Applications Waiting on Locks", cur_apps_locks) printf("\n%-40s = %d\n", "Lock waits", lock_waits) printf("%-40s = %d\n", "Time database waited on locks (ms)", lock_wait_time) if (lock_waits > 0) { printf("Average wait time per lock = %.2f ms\n", lock_wait_time / lock_waits) } else { printf("Average wait time per lock = 0.00 ms\n") } printf("\nLogging") printf("\n=======\n") printf("%-40s = %s\n", "Log Retain for Recovery Enabled", logretain) printf("%-40s = %ld (%ld bytes)\n", "Log File Size (4KB)", logfilsiz, logfilsiz * 4096) printf("%-40s = %ld\n", "Number of Primary Log Files", logprimary) printf("%-40s = %ld\n", "Number of Secondary Log Files", logsecond) printf("The Log File Size applies to each log file. The primary log files are\n") printf("preallocated (although for raw logs, only the first 8K is allocated).\n") printf("If logretain is in use, a new primary log is allocated whenever a log\n") printf("file switch takes place. For circular logging, the existing primary\n") printf("logs are reused, and secondary logs are only allocated if the primary\nlogs prove temporarily insufficient.\n") printf("%-40s = %ld\n", "Secondary Logs Allocated Currently", cur_sec_logs) printf("\n%-40s = %ld\n", "Log pages read", logreads) printf("%-40s = %ld\n", "Log pages written", logwrites) printf("%-40s = %ld (%ld MB)\n", "Max Total Log Space Used", log_space_used, log_space_used / (1024*1024)) printf("%-40s = %.1f log files\n", " ", log_space_used/(4096 * logfilsiz)) printf("\n%-40s = %ld (%ld bytes)\n", "Log Buffer Size (4KB)", logbufsz, logbufsz * 4096) if (logbufsz < 128) { printf("\nIf you have enough memory, it may be worth increasing the Log Buffer Size\nto 128 (the maximum).\n") } printf("\n%-40s = %ld\n", "Group Commit Count", mincommit) printf("The Group Commit Count determines how many commits will be written to\n") printf("the log file at one time. The writing of log records to disk will be\n") printf("delayed until the count is reached or 1 second elapses. The use of\n") printf("group commit improves logging efficiency, sometimes at the cost of\n") printf("transaction delays. A setting of 3 may be a good starting point for\na busy system.\n") printf("\n%-40s = %ld\n", "Commit statements attempted", commit_sql_stmts) printf("%-40s = %ld\n", "Rollback statements attempted", rollback_sql_stmts) printf("\n%-40s = %ld %%\n", "Percent log file reclaimed before soft chckpt", softmax) printf("SOFTMAX fulfills two distinct purposes. Firstly, it determines how\n") printf("frequently the log control files are flushed to disk. This occurs\n") printf("when softmax percentage of one log file is reached. Secondly, it determines\n") printf("when the page cleaners are triggered based on LSN gap. This occurs when\n") printf("softmax percentage of the total primary log file space is reached.\n") printf("In general, a smaller softmax means a shorter recovery time.\n") printf("\nMONITORING OTHER STATISTICS") printf("\n===========================\n") printf("%-40s = %ld (%ld bytes)\n", "Database heap (4KB)", db_heap, db_heap * 4096) printf("%-40s = %d (%ld bytes) \n", "High water mark for database heap (4KB)", hwm_db_heap/4096, hwm_db_heap) printf("\nCatalog Cache Statistics\n") printf("\n%-40s = %ld (%ld bytes)\n", "Catalog Cache Size (4KB)", catalogcache_sz, catalogcache_sz * 4096) printf("%-40s = %d\n", "Catalog Cache Inserts", cat_cache_inserts) printf("%-40s = %d\n", "Catalog Cache Lookups", cat_cache_lookups) if (cat_cache_lookups > 0) { printf("\nCatalog Cache Miss Rate = %.2f %%\n\n", (cat_cache_inserts/cat_cache_lookups)*100.) } else { printf("\nCatalog Cache Miss Rate = 0.00 %%\n\n") } printf("%-40s = %d\n", "Catalog Cache Overflows", cat_cache_overflows) printf("%-40s = %d\n", "Catalog Cache Heap Full", cat_cache_heap_full) printf("\nIf the Catalog Miss Rate shown above is not small (less than 20 %%), or if\n") printf("the number of Overflows is high, try increasing the Catalog Cache Size.\n") printf("If the Heap Full count is high, increase dbheap or decrease catalogcache_sz.\n") printf("\nA large number of DDL statements can adversely affect the performance\n") printf("of the catalog cache:\n") printf("%-40s = %d\n", "DDL SQL Statements", ddl_sql_stmts) printf("\n%-40s = %ld (%ld bytes)\n", "Utilities Heap Size (4KB)", util_heap_sz, util_heap_sz * 4096) printf("\nRecovery, Load and Other Utilities") printf("\n==================================\n") printf("The Utilities heap is used by the backup, restore, load and load recovery\nutilities") printf(" and is the maximum amount of memory that can be used\n") printf("simultaneously by these utilities. It is also related to:\n") printf("\n%-40s = %ld (%ld bytes)\n", "Backup Buffer Default Size (4KB)", backbufsz, backbufsz * 4096) printf("%-40s = %ld (%ld bytes)\n", "Restore Buffer Default Size (4KB)", restbufsz, restbufsz * 4096) printf("\nThese values are used if the buffer size on the backup/restore commands\nare set to 0.") printf(" Recommendation: use buffer-size and num-buffers on the\n") printf("command instead. More smaller buffers are better than a few large buffers.\n") printf("\n%-40s = %s\n", "Auto Restart Enabled", autorestart) printf("When set on, the database manager automatically calls the restart\nutility if needed.\n") printf("\n%-40s = %s\n", "Index Re-creation Time", indexrec) printf("Indicates when the database manager will attempt to rebuild invalid\nindexes.\n") printf("\n%-40s = %ld\n", "Default Number of Loadrec Sessions", dft_loadrec_ses) printf("Specifies the default number of sessions to be used during recovery\n") printf("of a table load. This setting can be overriden with the DB2LOADREC\n") printf("environment variable. The default number of buffers for load retrieval\n") printf("is twice the value of this parameter.\n") printf("\n%-40s = %d\n", "Recovery History Retention (days)", rec_his_retentn) printf("\nSQL and Agent Private Memory") printf("\n============================\n") printf("%-40s = %ld (%ld bytes)\n", "SQL Statement Heap (4KB)", stmtheap, stmtheap * 4096) printf("SQL Statement Heap is only allocated as required. Increase if you\nencounter errors during compilation.\n") printf("%-40s = %ld (%ld bytes)\n", "Default Application Heap (4KB)", applheapsz, applheapsz * 4096) printf("Application Heap is allocated per agent upon connect. Increase if you\nencounter application errors relating to application heap.\n") printf("%-40s = %ld (%ld bytes)\n", "Package Cache Size (4KB)", pckcachesz, pckcachesz * 4096) printf("The Package Cache is allocated per agent to cache static and dynamic SQL\nstatements for packages.\n") printf("\n%-40s = %ld\n", "Package Cache Lookups", pkg_cache_lookups) printf("%-40s = %ld\n", "Package Cache Inserts", pkg_cache_inserts) if (pkg_cache_lookups > 0) { printf("Package Cache Hit Rate = %.2f %%\n", (1 - (pkg_cache_inserts / pkg_cache_lookups )) * 100.) } else { printf("Package Cache Hit Rate = 0.00 %%\n") } printf("If the Package Cache Hit Rate is low, consider increasing the package cache.\n") printf("Note that DDL statements negatively impact the performance of the package\ncache:\n") printf("%-40s = %d\n", "DDL SQL Statements", ddl_sql_stmts) printf("\n%-40s = %ld (%ld bytes)\n", "Query Heap Size (4KB)", query_heap_sz, query_heap_sz * 4096) printf("%-40s = %ld (%ld bytes)\n", "Application Support Layer Heap Size (4KB)", aslheapsz, aslheapsz * 4096) printf("The Query Heap should be at least 5 times the size of the Application\n") printf("Support Layer Heap. The initial query heap is set to the size of the\n") printf("application support layer heap, and after big queries, the query heap\n") printf("will again be reduced to the size of the asl heap size.\n") printf("\n%-40s = %ld\n", "Max Requester I/O Block Size (bytes)", rqrioblk) printf("The Max Requester I/O Block Size supports remote database connections,\n") printf("and is allocated per remote connection. It also determines whether blocking\n") printf("cursor requests can be accepted or not. The acceptance rate of blocking\n") printf("cursor requests can be monitored at the application level.\n") printf("\nData Statistics") printf("\n===============\n") printf("%-40s = %ld (%ld bytes)\n", "Statistics Heap Size (4KB)", stat_heap_sz, stat_heap_sz * 4096) printf("The Statistics Heap is used by runstats when collecting statistics.\n") printf("\n%-40s = %ld\n", "Number of Frequent Values Retained", num_freqvalues) printf("This parameter determines the number of \"most frequent values\"\n") printf("collected when the WITH DISTRIBUTION option of runstats is used.\n") printf("Increasing this value increases the statistics heap requirements.\n") printf("\n%-40s = %ld\n", "Number of Quantiles Retained", num_quantiles) printf("This parameter determines the number of quantiles that will be\n") printf("collected when the WITH DISTRIBUTION option of runstats is used.\n") printf("Increasing this value increases the statistics heap requirements.\n") printf("\nPer Instance Tunables") printf("\n=====================\n") printf("%-40s = %ld (%ld bytes)\n", "Database Monitor Heap Size (4KB)", mon_heap_sz, mon_heap_sz * 4096) printf("Database Monitor Heap is required for monitoring, snapshots etc.\n") printf("\n%-40s = %s\n", "Directory Cache Support", dir_cache) printf("The Directory Cache reduces connect costs by caching directory\n") printf("information related to the database, node and DCS directory files.\n") printf("\n%-40s = %s\n", "CPU speed (millisec/instruction)", cpuspeed) printf("The CPU speed parameter is used by the optimizer. Do not change it!\n") printf("\nApplication Concurrency") printf("\n=======================\n") printf("%-40s = %ld\n", "Max Number of Existing Agents", maxagents) printf("The max agents limits the total no. of agents that may connect to all\ndatabases for this instance.\n") printf("%-40s = %s\n", "Agents Registered", agents_registered) printf("%-40s = %s\n", "High Water Mark for Agents Registered", agents_registered_top) printf("\n%-40s = %s\n", "Max Number of Concurrent Agents", maxcagents) printf("The max concurrent agents limits the total no. of agents that may\n") printf("concurrently execute a database transaction. The default is -1 (the\nsame as maxagents).\n") printf("The maximum number of concurrent agents determines the maximum number\n") printf("of concurrently active users. Other users will have to wait for a token\nto become available.\n") printf("%-40s = %s\n", "Agents Waiting for a Token", agents_waiting) printf("%-40s = %s\n", "High Water Mark for Agents Waiting for a Token", agents_waiting_top) printf("If there are agents waiting for a token, increase MAXCAGENTS unless\nthere is a good reason not to.\n") printf("%-40s = %ld\n", "High water mark for connections", conns_registered_) printf("\n%-40s = %ld\n", "Max Number of Idle Agents", max_idleagents) printf("%-40s = %ld\n", "Idle Agents", idleagents) printf("After all user connections are terminated, a pool of idle agents\n") printf("(as defined above) will be retained to speed up new connections.\n") printf("Note that only local clients use idle agents; remote clients do not.\n") printf("\n%-40s = %ld\n", "Max Number of Active Applications", maxappls) printf("%-40s = %ld\n", "Average Number of Active Applications", ave_appls) printf("The ave. no. of active applications is used by the SQL optimizer to\n") printf("estimate how much buffer pool will be available at runtime for the\n") printf("application. Be conservative. Try setting it to the average number of\nheavy query applications.\n") printf("%-40s = %ld\n", "Application Connects", appl_cons) printf("%-40s = %ld\n", "Applications Connected Currently", appl_cur_cons) printf("%-40s = %ld\n", "Appls. Executing in DB Manager Currently", appl_cur_exec) printf("%-40s = %ld\n", "Remote Connections", rem_cons) printf("%-40s = %ld\n", "Remote Connections Executing in DB Manager Currently", rem_cur_exec) printf("%-40s = %ld\n", "Local Connections ", local_cons) printf("%-40s = %ld\n", "Local Connections Executing in DB Manager Currently", local_cur_exec) printf("\n%-40s = %ld\n", "Max DB Files Open per Application", maxfilop) printf("Tablespace containers are handled as files. Make sure this value is\n") printf("set high enough to cover the number of files each agent will need to\n") printf("have open. Monitor the number of files closed (0 is good):\n") printf("%-40s = %ld\n", "Database Files Closed", files_closed) printf("\n%-40s = %s\n", "Keep DARI process", keepdari) printf("You can decide to keep DARI (stored procedure) processes after the\n") printf("DARI calls are complete, which improves performance. This flag will\n") printf("be ignored if the max no. of DARI processes is set to 0.\n") printf("%-40s = %ld\n", "Max Number of DARI Processes", maxdari) printf("The default for max no. of DARI processes is -1 (the same as maxagents).\n") }' echo echo "RAW DATA" echo "========" echo cat $SNAPFILE rm -f $SNAPFILE