14/3/25, 9:39 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 [Link] 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
[Link] 1/3
14/3/25, 9:39 Lotus Domino Administrator 8.5 Help - Generating a database monitor snapshot
>[Link]
db2 get snapshot for all on YourDatabase
>>[Link]
db2 list applications show detail
>>[Link]
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 >[Link]
Puts the dbm snapshot in the file [Link] and overwrites anything that was in that file.
db2 get snapshot for all on YourDatabase >>[Link]
Adds the database snapshot to [Link]
db2 list applications show detail >>[Link]
Adds information about applications to [Link]
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 [Link] -- 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 Times a page containing temporary data rows (for example, sorted data) was
=0 accessed without reading data from disk
Buffer pool temporary data physical Times a page containing temporary data rows (for example, sorted data) was
reads = 0 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 Times a page containing temporary index data (for example, sorted data) was
=0 accessed without reading data from disk
Buffer pool temporary index physical Times a page containing temporary index data (for example, sorted data) was
reads = 0 accessed and data was read from disk
Total execution time ([Link]) = 0.104943
(across all executions)
Total user cpu time ([Link]) = 0.100145
(")
Total system cpu time ([Link]) =
0.010015 (")
Statement text = SELECT nsfid, viewid, collation, branch, hassubcategory, refunid, COUNT(*) FROM
GRP3.ND002115F385256FF0 WHERE refunid IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
[Link] 2/3
14/3/25, 9:39 Lotus Domino Administrator 8.5 Help - Generating a database monitor snapshot
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) 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.
Related topics
Modifying the database sort heap and locklist parameter values
Improving Domino and DB2 Performance
Glossary Feedback on Help or Product Usability? Help on Help
[Link] 3/3