Db2 Basics: Monitoring and
Troubleshooting Db2
Ember Crooks
Session code: B01
Db2
1
Agenda
• Db2 Documentation
• Error Messages
• Db2 Diagnostic Log
• Overview of Monitoring Tools:
• db2top
• dsmtop
• dmctop
• monreport Module
• mon_get Table Functions and Views
Troubleshooting Methodology
Take a Systemic Approach
Ember’s Foundations for Troubleshooting
Assume that the
Help others by
Don’t point fingers problem is within
finding clues
your control
Look in the common Ask extensive
Ask for help
places first questions
4
Methodical Troubleshooting
1. Prepare documentation and practice investigation ahead of time
2. Define the symptoms thoroughly – ASK QUESTIONS
3. Compile information from the environment
4. Search the web and IBM Knowledge Center
5. Form one or more hypotheses based on the details compiled
6. Test your hypotheses one at a time
5
Don’t :
Panic
Run code or commands from searches
that you don’t understand
Blindly run recommendations from
IBM support without asking questions
Change a lot of things at one time
Leave changes that did not work in
place
6
Extra Scrutiny for Performance Problems
• “The Database Is Slow”
• How do you know it’s the database?
• How do you know it’s slow?
• Can you show me the metrics from when it wasn’t slow?
• Can you show me metrics from now that are slower than your baseline?
• Has anything changed?
• ANY code change, no matter how small
• Usage patterns (Sales, time of month, time of year)
• Other workloads on the database
• Data
7
Bottlenecks
8
Db2 Documentation
Navigating the IBM Db2 Knowledge Center
IBM Db2 Knowledge Center – Search Box
10
Search – Inline Preview
11
IBM Db2 Knowledge Center – Version Dropdown
12
IBM Db2 Knowledge Center – Table of Contents
13
IBM Db2 Knowledge Center –
Navigating from the Table of Contents
14
Command Page
Plain text description of the command
Details about how this command can be
executed
What permissions or authorities are
needed to run this command
Syntax diagram
Additional tips on the effects of this
command and how it might be useful
15
Sample Db2 Syntax Diagram
16
How to Read a Db2 Syntax Diagram (1|4)
• Read the syntax diagrams from left to right and top to bottom,
following the path of the line.
• The >>-- symbol indicates the beginning of a syntax diagram.
• The --> symbol indicates that the syntax is continued on the next line.
• The >-- symbol indicates that the syntax is continued from the
previous line.
• The -->< symbol indicates the end of a syntax diagram.
• A word or phrase in a box indicates a parameter block.
17
How to Read a Db2 Syntax Diagram (2|4)
• If you must choose one of the
items, one item of the stack
appears on the main path.
• If one of the items is the
default, it will appear above
the main path, and the
remaining choices will be
shown below.
18
How to Read a Db2 Syntax Diagram (3|4)
• An arrow returning to the left,
above the main line, indicates an
item that can be repeated. In this
case, repeated items must be
separated by one or more blanks.
• If the repeat arrow contains a
comma, you must separate
repeated items with a comma.
19
How to Read a Db2 Syntax Diagram (4|4)
• Sometimes a single variable
represents a larger fragment of
the syntax. For example, in the
following diagram, the
variable parameter-
block represents the
whole syntax fragment that is
labeled parameter-block:
20
Configuration Parameter
Configuration Type
Plain text description of the parameter Online or Offline, and Boundary
Default and Range of Possible Values
Units Used
Description of the effects of the parameter
Guidelines and ideas for setting the
parameter
21
Useful Knowledge Center Pages
• SQL and XML Limits – How big can X be?
• CREATE TABLE - Data Types and how much space they consume
• Monitor Procedures and Functions – List monitoring data in
light-weight impact available via SQL
• SYSCAT.TABLES and SYSCAT.INDEXES – What was that column
name again?
• Syntax diagrams for BACKUP, RESTORE, REORG, etc - What order
do the clauses go in, again?
• Great starting page if you have the table of contents open
22
IBM Db2 Knowledge Center Problems
Wait until it
works again
Knowledge
Center Down! Try a different
browser
Search returns no Clear all IBM
Problems results Resolutions: cookies
Clear all cookies
Strange errors
Resort to the PDF
documentation
23
PDF Documentation
• Download before you need it
• Great for when:
• Internet access is not available
• Online documentation is down
• https://www.ibm.com/support/pages
/node/627743
• Different set of PDFs for each version
24
Db2 Error Messages
A Wealth of Information in a Small Package
Important Information in Error Message or SQLCA
Required: Optional:
• Error number • Object identifiers Full Error
• Basic text • Names Message
description of • Numbers
error • Reason Codes
26
Error Messages
Three letters, SQL 0911 N One Character indicating
usually SQL, the intensity of the issue.
but XXX NNNN X Commonly:
sometimes • I – Informational
others such • W – Warning
Usually 4
as DSN or • N – Statement Failure
digits,
DIA • C – System Failure
sometimes 5
• https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.messages.doc/doc/c0052007.html
Getting Information on Errors – Command Line (1|2)
$ db2 ? SQL0911N
SQL0911N The current transaction has been rolled back because of a
deadlock or timeout. Reason code "<reason-code>".
Explanation:
The current unit of work was involved in an unresolved contention for
use of an object and had to be rolled back.
The reason codes are as follows:
The transaction was rolled back due to a deadlock.
68
The transaction was rolled back due to a lock timeout.
72
…
28
Getting Information on Errors – Command Line (2|2)
$ db2 ? SQL0551N
SQL0551N The statement failed because the authorization ID does not
have the required authorization or privilege to perform the
operation. Authorization ID: "<authorization-ID>". Operation:
"<operation>". Object: "<object-name>".
Explanation:
The operation could not be performed on the specified object. In
general, this message is returned because the authorization ID does not
have the required authority or privilege to perform the operation. In
some cases, it is returned for an object that does not allow the
operation even when the authorization ID has an administrative
authority.
29
IBM Knowledge Center:
Anatomy of an Error
Error number and generic error text
Plain text description of the error and
any reason codes
What to try, sometimes specific to
reason codes, and other details
30
Db2 Diagnostic Log
The Preeminent Location for Information About System-Level Issues
Where is the Db2 Diagnostic Log?
• The default Linux/UNIX location for the DB2 diagnostic log:
$INSTHOME/sqllib/db2dump
• The default Windows (hidden!) location for the DB2 diagnostic
log: C:\ProgramData\IBM\DB2\<db2 copy name>\DB2
• To get the path where the DB2 diagnostic log is stored:
$ db2 get dbm cfg |grep DIAGPATH
Diagnostic data directory path (DIAGPATH) = /db2home/db2inst1/sqllib/db2dump
OR
db2 "select VALUE from SYSIBMADM.DBMCFG where NAME='diagpath’”
Db2 Error Log
LEVEL: Info, Warning, Error, Severe
PID: Process ID
INSTANCE: Which instance is affected
MESSAGE: Brief Description of problem
Db2 Error Log
db2diag –rc 800F006A
34
Parsing db2diag.log with the db2diag Tool
• To display all messages in the last hour:
db2diag –H 1h
• To display all error level messages:
db2diag -level error
• To display all error messages containing the DB2 ZRC return code 0x87040055, and the
application ID G916625D.NA8C.068149162729:
db2diag -g msg:=0x87040055 -l Error | db2diag -gi appid^=G916625D.NA
• To display all messages logged after the one with timestamp 2017-08-15-00.00.00.000000
inclusively:
db2diag -time 2017-08-15-00.00.00.000000
• To display severe errors logged for the last three days (using slightly different syntax for
the level of error:
db2diag -gi "level=severe" -H 3d
• To call db2diag from a Perl script using default settings, enter:
system("db2diag -readfile");
Searching and Formatting using db2diag
$ db2diag -e 4500 -fmt %dataobject
DATA #1 :
Package Cache Overflow
memory needed : 10592
current used size (OSS) : 134106751
maximum cache size (APM) : 130191196
maximum logical size (OSS): 134150417
maximum used size (OSS) : 215154688
owned size (OSS) : 137101312
number of overflows : 9400
Monitoring Tools
Many Methods to Understand what is Happening
Types of Monitoring
Historical Performance
Monitoring
Real-Time Monitoring
• Why was the database
slow at 2 PM on • What is happening right Monitoring to
Thursday? now? Generate Alerts
• What trends to we see in • Why is the database slow
right now? • What condition exists that
performance
risk imminent outage?
• What red flags do we see • What is causing this effect
indicating potential that someone is seeing?
performance problem
areas?
38
Real-Time Monitoring
db2top dmctop db2pd
• Old, but tried and true • New, uses lightweight interfaces • Works when nothing else will
• Very up-to-date data dsmtop • Must be installed • Works when you can’t get a
• Shows change over time • Still missing a bit of functionality connection
MON_GET* functions and MONREPORT GET SNAPSHOT SYSIBMADM.SNAP* views
views • Quick overview • Older, heavyweight metrics • Older, heavyweight metrics
• Great for scripting • Defined time frame
• Uses lightweight interfaces
39
Memory Metrics
Remote Access
Lightweight In-
Oldest Version
Reset Metrics
Update Info
Deprecated
Connection
Requires
Use SQL
db2top On-screen N N Y Y N Y <9.1, fp6>
dsmtop On-screen N Y Y N Y N 10.1
dmctop On-screen N Y Y Y Y N 11.1
db2pd -repeat opt N Y N N N N 8.2
MON_GET* Functions re-run Y Y Y N Y N 9.7+
MONREPORT.DBSUMMARY re-run N Y Y N Y N 9.7
GET SNAPSHOT re-run N N N Y N Y Dawn of
Time
SYSIBMADM.SNAP* Views re-run Y N Y N Y Y 9.7
40
db2top
• Start db2top using this syntax:
• db2top –d dbname
• Once you’re in, you can use different letters to get to
different screens.
• The screen to the left is the “d” screen showing overall
database information
• Other very useful screens include:
• U for lock information
• l for session information
• B for bottleneck information
• b for bufferpool information
• u for utility information
• Column order and sorting can be customized on many screens
• .db2toprc file can be edited to include CPU or other OS-level
information
41
db2top – Help Screen
• Pressing the h key in db2top will
get you the help screen, which
has a list of all the nifty options
42
db2top – Interval and Cumulative
• Default interval is 2 seconds
• A different interval can be specified with the –i option when db2top is
started
• db2top only reports the data for the last <interval> seconds (current view)
• db2top can also report cumulative values since the last db restart or reset
(cumulative view)
• Pressing k will toggle between the current view and the cumulative view
• Pressing R will reset the monitor data – for that session only
db2top – Lock Chaining
• Use U to get to the
lock screen:
44
db2top – Lock Chaining
• If locks are waiting, db2top can quickly tell which connection is
waiting on which connection
db2top - Scrolling
• Scrolling up and down is not possible
• Many screens have more columns that will fit on the screen
• Left and right scrolling can be done with > and <
dmctop
• Brand new as of Summer 2020
• Will be included in the product eventually
• Easy to install by downloading a file
• Can connect remotely, with or without TLS/SSL
• Does not yet have full feature parity with db2top
• Already better than dsmtop
• Not available on AIX/UNIX
47
dmctop
• Start with
dmctop –d dbname
• CPU usage not
always accurate
• Read efficiency!
• db2top shortcuts
work when the
Menu pane is
active
48
dmctop Time Modes
• Delta • Actual
• Refreshes every 10 seconds by • Refreshes every 10 seconds by
default default
• Values are • Values are always since database
• Since dmctop was started restart, even if you reset baseline
• Since that dmctop screen was first used
• Since baseline was reset
• Some values per minute or per
second
49
dmctop:
Time Spent
50
db2pd – a Few Useful Options
• mempools
• memsets
• osinfo
• wlocks
• repeat
• bufferpools
• hadr
MONREPORT.DBSUMMARY
• Good summary of data
• Snapshot-like format that uses more recent monitoring infrastructure
• Snipets…
Part 1 - System performance
Work volume and throughput
--------------------------------------------------------------------------------
Per second Total
--------------------- -----------------------
TOTAL_APP_COMMITS 0 3
ACT_COMPLETED_TOTAL 1 11
APP_RQSTS_COMPLETED_TOTAL 2 27
TOTAL_CPU_TIME = 44364
TOTAL_CPU_TIME per request = 1643
Row processing
ROWS_READ/ROWS_RETURNED = 2 (33/16)
ROWS_MODIFIED = 0
MONREPORT.DBSUMMARY Snippets
Buffer pool
-----------------------------------------------------
Buffer pool hit ratios
Type Ratio Formula
--------------- --------------- -------------------
Data 100 (1-(0+0-0)/(37+0))
Index 100 (1-(0+0-0)/(34+0))
XDA 0 (1-(0+0-0)/(0+0))
COL 0 (1-(0+0-0)/(0+0))
LBP Data 100 (37-0)/(37+0)
LBP Index 0 (0-0)/(34+0)
LBP XDA 0 (0-0)/(0+0)
LBP COL 0 (0-0)/(0+0)
GBP Data 0 (0 - 0)/0
GBP Index 0 (0 - 0)/0
GBP XDA 0 (0 - 0)/0
GBP COL 0 (0 - 0)/0
MONREPORT.DBSUMMARY Snippets
Per activity Total
------------------------------ ----------------------
LOCK_WAIT_TIME 0 0
LOCK_WAITS 0 0
LOCK_TIMEOUTS 0 0
DEADLOCKS 0 0
LOCK_ESCALS 0 0
<SNIP>
Sort
--------------------------------------------------------------------------------
TOTAL_SORTS = 4
SORT_OVERFLOWS = 0
POST_THRESHOLD_SORTS = 0
POST_SHRTHRESHOLD_SORTS = 0
MON_GET* Functions and Views
MON_GET_CONNECTION MON_GET_SERVICE_SUB MON_GET_UNIT_OF_WO MON_GET_WORKLOAD MON_GET_DATABASE
and CLASS and RK and and and MON_GET_APPL_LOCKW
MON_GET_BUFFERPOOL
MON_GET_CONNECTION MON_GET_SERVICE_SUB MON_GET_UNIT_OF_WO MON_GET_WORKLOAD_ MON_GET_DATABASE_D AIT
_DETAILS CLASS_DETAILS RK_DETAILS DETAILS ETAILS
MON_GET_EXTENDED_L MON_GET_PAGE_ACCESS
MON_GET_CONTAINER MON_GET_INDEX MON_GET_LOCKS MON_GET_TABLE MON_GET_TABLESPACE
ATCH_WAIT _INFO
MON_GET_FCM_CONNE MON_GET_TRANSACTIO MON_GET_INDEX_USAG
MON_GET_HADR MON_GET_SERVERLIST MON_GET_ROUTINE MON_GET_AGENT
CTION_LIST N_LOG E_LIST
MON_GET_PKG_CACHE_
MON_GET_TABLE_USAG STMT and MON_GET_AUTO_MAINT MON_GET_AUTO_RUNST MON_GET_EXTENT_MOV MON_GET_REBALANCE_S
MON_GET_RTS_RQST
E_LIST MON_GET_PKG_CACHE_ _QUEUE ATS_QUEUE EMENT_STATUS TATUS
STMT_DETAILS
MON_FORMAT_XML_CO
MON_FORMAT_XML_ME MON_FORMAT_XML_TI MON_FORMAT_XML_WA
MPONENT_TIMES_BY_R
TRICS_BY_ROW MES_BY_ROW IT_TIMES_BY_ROW
OW
55
Querying MON_GET* Table Functions
select * from table(mon_get_database(-2))
Member is
Can specify Name of the table
usually one of the
specific columns function
parameters
• Variable number of parameters depending on the object
• For example, mon_get_table requires tabschema and tabname
• Can use ‘’ or NULL for many of the parameters to return data on all
objects
Interacting with IBM Support
• Open a ticket via the support portal or phone
• Front-line support reps are sometimes good, but sometimes do not
seem to know much about Db2.
• IBM will ask for additional information. Respond quickly.
• Usually, you must push IBM to keep working on an issue by updating
the ticket regularly
• If a ticket is not getting needed attention, escalate to a duty manager
(call in and ask for this)
• If you did not get reasonable answers or responses, close a ticket as
“non-sat” – this gets attention, but often no further action. 57
Ember’s Foundations for Troubleshooting
Assume that the
Help others by
Don’t point fingers problem is within
finding clues
your control
Look in the common Ask extensive
Ask for help
places first questions
58
Ember Crooks
ember.crooks@gmail.com
59
