Lotus Domino Administrator 8.5 Help - Generating a database monitor snapshot

MAINTAINING THE DOMINO AND DB2 ENVIRONMENT

Generating a database monitor snapshot
A snapshot is a point-in-time view of the monitoring elements being analyzed, some of which are cumulative. Cumulative elements contain information relative to the start of monitoring and ending at the time of the snapshot. Other (noncumulative) elements reflect the elements of a gauge that varies according to the activity on your system.

IBM® DB2 Universal Database™ Enterprise Server Edition contains several default monitoring switches. Each of the monitoring switches gathers data for a specific configuration parameter. DB2 contains these default monitoring switches:

Configuration parameter Default database monitor switch

Buffer Pool DFT_MON_BUFPOOL = ON

Lock DFT_MON_LOCK = OFF

Sort DFT_MON_SORT = OFF

Statement DFT_MON_STMT = OFF

Table DFT_MON_TABLE = OFF

Unit of work DFT_MON_UOW = OFF

You can generate a list of these switches by entering the following command from the DB2 Command Line Processor (CLP):

DB2 GET MONITOR SWITCHES

Enable monitors using the same process you will later use to obtain the snapshot. On Microsoft® Windows® platforms, use a cmd.exe session, while on IBM® AIX®, Linux®, or UNIX®, use a shell.

By default, the timestamp parameter is on, but it is best to explicitly set timestamp to on because it is possible to turn timestamp off. This is the timestamp of the log being processed.

You can take a snapshot of the data that is gathered during the monitoring session for any configuration parameter whose switch is set to On. To generate a snapshot for diagnostic purposes, the DB2 monitors must be turned on.

To obtain a database monitor snapshot, enter this command from the CLP:

GET SNAPSHOT FOR DATABASE ON <service>

Dynamic SQL Snapshot Result

When you generate an SQL snapshot, the snapshot output displays on the computer monitor. It is not saved to a log file. To save the snapshot to a file so that you can review the information at a later time, use this command:

db2 connect to YourDatabase

db2 update monitor switches using sort on

lock on

table on

bufferpool on

uow on

statement on

timestamp on

To obtain a snapshot and to use I/O redirection to save the output to a file, issue the following commands during the same process:

Note The commands are also explained below.

db2 get snapshot for dbm

>db2mon.out

db2 get snapshot for all on YourDatabase

>>db2mon.out

db2 list applications show detail

>>db2mon.out

Explanation of commands

The symbol > (single greater than) writes the output of the command to a file. Text in the file is overwritten. The symbol >> (double greater than) appends the output of the command to a file. It does not overwrite existing text, it just appends new text to the end of the file.

This sequence of commands produces the following results:

db2 get snapshot for dbm >db2mon.out

Puts the dbm snapshot in the file db2mon.out and overwrites anything that was in that file.

db2 get snapshot for all on YourDatabase >>db2mon.out

Adds the database snapshot to db2mon.out

db2 list applications show detail >>db2mon.out

Adds information about applications to db2mon.out

The following is a sample of the output generated for one statement:

Output Explanation

Number of executions = 20 Times the query has run

Number of compilations = 1 Times the SQL compiler and optimizer have processed the statement

Worst preparation time (ms) = 3 Worst case, compiler and optimizer processing

Best preparation time (ms) = 3 Best case, compiler and optimizer processing)

Internal rows deleted = 0 Temp

Internal rows inserted = 0 Temp

Rows read = 0 Including temp

Internal rows updated = 0 Temp

Rows written = 0 Including temp

Statement sorts = 20 Times DB2 sorted data during the execution of this statement. Usually = Number of executions

Statement sort overflows = 0 Sort space spilling to disk (not good to even sort, but if this happens, it's really not good)

Total sort time = 0 Sec.ms -- shows the true cost of sorting across all executions. In this case, sorts are inexpensive.

Buffer pool data logical reads = 0 Times a page containing data rows was accessed without reading data from disk

Buffer pool data physical reads = 0 Times a page containing data rows was accessed and data was read from disk

Buffer pool temporary data logical reads = 0 Times a page containing temporary data rows (for example, sorted data) was accessed without reading data from disk

Buffer pool temporary data physical reads = 0 Times a page containing temporary data rows (for example, sorted data) was accessed and data was read from disk

Buffer pool index logical reads = 1300 Times a page containing index data was accessed without reading data from disk

Buffer pool index physical reads = 0 Times a page containing index data was accessed and data was read from disk

Buffer pool temporary index logical reads = 0 Times a page containing temporary index data (for example, sorted data) was accessed without reading data from disk

Buffer pool temporary index physical reads = 0 Times a page containing temporary index data (for example, sorted data) was accessed and data was read from disk

Total execution time (sec.ms) = 0.104943 (across all executions)

Total user cpu time (sec.ms) = 0.100145 (")

Total system cpu time (sec.ms) = 0.010015 (")

Statement text = SELECT nsfid, viewid, collation, branch, hassubcategory, refunid, COUNT(*) FROM GRP3.ND002115F385256FF0 WHERE refunid IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) AND nsfid=? AND viewid = ? AND collation = ? AND branch = ? AND hassubcategory = ? GROUP BY viewid, nsfid, collation, branch, hassubcategory, refunid ORDER BY viewid, nsfid, collation, branch, hassubcategory, refunid (the actual statement as DB2 saw it)

Using a snapshot to detect negative locking behavior

Use the data that is gathered in the snapshot to detect negative locking behavior. To do so, locate the section titled "Database Lock Snapshot." A lock entry in your snapshot will look similar to this example:

Lock Name = 0x0D000700000000000000000054

Lock Attributes = 0x00000000

Release Flags = 0x00000001

Lock Count = 1.00

Hold Count = 1.00

Lock Object Name = 7.00

Object Type = Table

Tablespace Name = GRP8

Table Schema = GRP8

Table Name = NSFNOTE

Mode = X

The data in this snapshot indicates that the locklist setting is too small. DB2 consolidated multiple row locks into one exclusive table lock. This has severe consequences for system performance.

If DB2 monitor snapshots show numerous row locks escalated to table locks, consider increasing the size of the locklist parameter.

Improving Domino and DB2 Performance

Glossary

Feedback on Help or Product Usability?

Help on Help

All Help Contents

Glossary

Configuration parameter	Default database monitor switch
Buffer Pool	DFT_MON_BUFPOOL = ON
Lock	DFT_MON_LOCK = OFF
Sort	DFT_MON_SORT = OFF
Statement	DFT_MON_STMT = OFF
Table	DFT_MON_TABLE = OFF
Unit of work	DFT_MON_UOW = OFF

Output	Explanation
Number of executions = 20	Times the query has run
Number of compilations = 1	Times the SQL compiler and optimizer have processed the statement
Worst preparation time (ms) = 3	Worst case, compiler and optimizer processing
Best preparation time (ms) = 3	Best case, compiler and optimizer processing)
Internal rows deleted = 0	Temp
Internal rows inserted = 0	Temp
Rows read = 0	Including temp
Internal rows updated = 0	Temp
Rows written = 0	Including temp
Statement sorts = 20	Times DB2 sorted data during the execution of this statement. Usually = Number of executions
Statement sort overflows = 0	Sort space spilling to disk (not good to even sort, but if this happens, it's really not good)
Total sort time = 0	Sec.ms -- shows the true cost of sorting across all executions. In this case, sorts are inexpensive.
Buffer pool data logical reads = 0	Times a page containing data rows was accessed without reading data from disk
Buffer pool data physical reads = 0	Times a page containing data rows was accessed and data was read from disk
Buffer pool temporary data logical reads = 0	Times a page containing temporary data rows (for example, sorted data) was accessed without reading data from disk
Buffer pool temporary data physical reads = 0	Times a page containing temporary data rows (for example, sorted data) was accessed and data was read from disk
Buffer pool index logical reads = 1300	Times a page containing index data was accessed without reading data from disk
Buffer pool index physical reads = 0	Times a page containing index data was accessed and data was read from disk
Buffer pool temporary index logical reads = 0	Times a page containing temporary index data (for example, sorted data) was accessed without reading data from disk
Buffer pool temporary index physical reads = 0	Times a page containing temporary index data (for example, sorted data) was accessed and data was read from disk
Total execution time (sec.ms) = 0.104943 (across all executions)
Total user cpu time (sec.ms) = 0.100145 (")
Total system cpu time (sec.ms) = 0.010015 (")

Lock Name =	0x0D000700000000000000000054
Lock Attributes =	0x00000000
Release Flags =	0x00000001
Lock Count =	1.00
Hold Count =	1.00
Lock Object Name =	7.00
Object Type =	Table
Tablespace Name =	GRP8
Table Schema =	GRP8
Table Name =	NSFNOTE
Mode =	X