db2pd in a Nutshell

1
db2pd in a Nutshell
Platform DB2 for Linux, UNIX, and Windows
Phil Gunning Principal Consultant, Gunning
Technology Solutions, LLC Session D04 25
October 2005 0830 0930
2
db2pd Background

• Developed based on OnStat utility from Informix
• Problem determination and monitoring tool
• Integrated into DB2 V8.2 (Stinger)
• Does not yet contain all options from OnStat
• Documented in the Command Reference
• Low monitoring overhead (latch free), preferred
  where possible over snapshots or event monitors
• Command line driven which makes it excellent for
  use with scripts
• Not dependent on monitor switches

3
db2pd Background

• db2pd returns information without acquiring any
• locks or use of DB2 engine resources
• Since no locks are obtained, data returned by
  dbp2d may not be completely current or accurate
• Zero use of database engine resources however is
  a good trade-off
• It is important to become familiar with db2pd as
  there is much information provided by db2pd that
  cannot be obtained through snapshot or event
  monitoring
• db2pd contains 34 options
• We will cover these new monitoring and problem
  determination information elements in this
  presentation

4
Using db2pd

• Command line tool
• Requires SYSADM authority and on UNIX/Linux must
  be instance owner
• No required connection or instance attachment
• For database level information to be retrieved,
  database must be active
• Standard options are
• -c command, read commands from input file
• -r repeat, num sec count
• -i interactive
• -file, specifies output file

5
db2pd Option Categories

• db2pd options can be divided into three
  categories in terms of information provided
• Administrative/Status
• OS Info
• DBM and DB Configuration Information
• DB2 and OS Version, virtual and physical memory
• Monitoring and Tuning
• Problem Determination/Troubleshooting

6
-osinfo option

• db2pd osinfo
• Operating System Information
• OSName WIN32_NT
• NodeName GTSLT1
• Version 5.1
• Release Service Pack 2
• Machine x86 Family 6, model 10, stepping 0
• CPU Information
• TotalCPU OnlineCPU ConfigCPU Speed(MHz)
  HMTDegree Cores/Socket
• 1 1 1
  1856 1
  1
• Physical Memory and Swap (Megabytes)
• TotalMem FreeMem AvailMem TotalSwap
  FreeSwap
• 703 326 326
  1016 1159
• Virtual Memory (Megabytes)
• Total Reserved Available Free
• 1719 n/a n/a 1485
  

7
Instance and Database Scope

• db2pd options are further broken down by Instance
  and Database scope
• Instance scope options return information at the
  Instance level
• -agents, -fcm, -mempools, -memsets, -dbmcfg,
  -sysplex
• -utilities

8
Database Scope Options

• 1. storagepaths
• 2. transactions
• Returns detailed information on transactions
• 3. locks
• Returns detailed lock information
• 4. tablespaces
• Returns detailed tablespace information
• 5. dynamic
• Returns detailed information about dynamic SQL
• 6. static
• Returns information about static SQL

9
Database Scope Options

• 7. mempools
• Returns information about memory pools
• 8. memsets
• Returns information about memory sets
• 9. -catalogcache
• Returns detailed information about catalog table
  activity
• 10. tcbstats
• Returns detailed information about tables and
  indexes
• 11. activestatements

10
Database Scope Options

• 12. applications
• 13. bufferpools
• 14. -logs
• 15. hadr
• 16. reorgs
• 17. -reopts
• 18. recovery
• 19. dbcfg
• 20. dbmcfg

11
-inst option

• Returns all instance-scope information
• db2pd -inst
• Version, level, bitness, fixpak information
• Operating System Information
• CPU Information, including hardware
  multithreading
• Physical Memory, Swap and Virtual Memory
  Configuration and usage
• Agent Information Idle, Active, Coordinator
• Memory Pool information Actual physical size,
  upper bound, high water mark
• Memory Set Summary
• Database Manager Configuration Settings In
  memory and on disk
• Utility status and progress

12
-dbmcfg option

• Command db2pd dbmcfg file/tmp/dbmcfg.txt
• Database Partition 0 -- Active -- Up 0 days
  183603
• Database Manager Configuration Settings
• Description Memory Value
  Disk Value
• RELEASE 0xa00
  0xa00
• CPUSPEED 1.141499e-006
  1.141499e-006
• COMM_BANDWIDTH 1.000000e002
  1.000000e002
• NUMDB 8
  8
• DATALINKS NO
  NO

13
-dbcfg option

• Command db2pd db GTSTST3 dbcfg
• Returns Database Configuration information
• DB CFG parameter settings in memory and on disk

14
-database option

• Command gtdb2pd -db sample -file c\dbase.txt
• Sending all options output to c\dbase.txt
• Returns database information
• Automatic storage status
• Storage path information
• HADR status
• Table and index activity
• Active table scans
• Index splits
• Active statement list
• Without options specified, runs all database
  scope options
• -alldbs runs all options for all databases

15
-database Example

• Cgtdb2pd -db sample -file c\dbase.txt
• Sending all options output to c\dbase.txt
• Database Partition 0 -- Database SAMPLE -- Active
  -- Up 0 days 004221
• Applications
• Address AppHandl nod-index NumAgents
  CoorTid Status C-AnchID
  C-StmtUID L-AnchID L-StmtUID Appid
  
• 0x003FBEA0 448 000-00448 1 5376
  ConnectCompleted 0 0 0
  0 LOCAL.DB2.050901015508
  
• Database Partition 0 -- Database SAMPLE -- Active
  -- Up 0 days 004221
• Transactions
• Address AppHandl nod-index TranHdl Locks
  State Tflag Tflag2 Firstlsn
  Lastlsn LogSpace SpaceReserved
  TID AxRegCnt GXID
• 0x04531580 448 000-00448 2 0
  READ 0x00000000 0x00000000 0x000000000000
  0x000000000000 0 0
  0x000000000BFE 1 0
• Database Partition 0 -- Database SAMPLE -- Active
  -- Up 0 days 004221
• BufferPools
• First Active Pool ID 1
• Max Bufferpool ID 1
• Max Bufferpool ID on Disk 1
• Num Bufferpools 5
• Address Id Name PageSz
  PA-NumPgs BA-NumPgs BlkSize ES NumTbsp
  PgsLeft CurrentSz
  PostAlter SuspndTSCt
• 0x0448C980 1 IBMDEFAULTBP 4096
  250 0 0 N 4
  0 250
  250 0

16
-transactions option

• Command db2pd db db2mon trans
• Database Partition 0 -- Database DB2MON -- Active
  -- Up 1 days 042709
• Transactions
• Address AppHandl nod-index TranHdl Locks
  State Tflag Tflag2 Firstlsn
  Lastlsn LogSpace SpaceReserved
  TID AxRegCnt GXID
• 0x027D1000 599 000-00599 99 0
  READ 0x00000000 0x00000000 0x000000000000
  0x000000000000 0 0
  0x000000009016 1 0
• 0x027D1A80 591 000-00591 100 0
  READ 0x00000000 0x00000000 0x000000000000
  0x000000000000 0 0
  0x000000008FF6 1 0
• 0x027D2500 589 000-00589 101 0
  READ 0x00000000 0x00000000 0x000000000000
  0x000000000000 0 0
  0x000000008FFD 1 0
• 0x027D2F80 588 000-00588 102 0
  READ 0x00000000 0x00000000 0x000000000000
  0x000000000000 0 0
  0x000000009012 1 0
• 0x027D3A00 9 000-00009 103 2
  READ 0x00000000 0x00000000 0x000000000000
  0x000000000000 0 0
  0x0000000003FA 1 0
• 0x027D4480 8 000-00008 104 0
  READ 0x00000000 0x00000000 0x000000000000
  0x000000000000 0 0
  0x000000009014 1 0

17
-locks option

• Command db2pd db db2mon locks
• Database Partition 0 -- Database DB2MON -- Active
  -- Up 1 days 044450
• Locks
• Address TranHdl Lockname
  Type Mode Sts Owner
  Dur HldCnt Att ReleaseFlg
• 0x032DE790 103 4442324143434553BBCB10EC41
  Internal P ..S G 103 1 0
  0x0000 0x40000000
• 0x032DE740 103 53514C4445464C5428DD630641
  Internal P ..S G 103 1 0
  0x0000 0x40000000
• Snippet from previous trans output
• 0x027D3A00 9 000-00009 103 2
  READ 0x00000000 0x00000000 0x000000000000
  0x000000000000 0 0
  0x0000000003FA 1 0
• 0x027D4480 8 000-00008 104 0
  READ 0x00000000 0x00000000 0x000000000000
  0x000000000000 0 0
  0x000000009014 1 0

18
Resolving Lock Contention with db2pd

• Database Partition 0 -- Database SAMPLE -- Active
  -- Up 0 days 163933
• db2pd db GTSTST1 locks file /tmp/lockc.txt
• Locks
• Address TranHdl Lockname
  Type Mode
  Sts Owner Dur HldCnt Att
  ReleaseFlg
• 0x0459C510 2 53514C4332453036BD4A32C841
  Internal P .. S G 2
  1 0 0x0000 0x40000000
  
• 0x0459CA10 3 53514C4332453036BD4A32C841
  Internal P .. S G 3
  1 0 0x0000 0x40000000
• 0x0459CA60 3 010000000100000001007B0056
  Internal V . S G 3
  1 0 0x0000
  0x40000000
• 0x0459C9E8 3 53514C4445464C5428DD630641
  Internal P . S G 3
  1 0 0x0000 0x40000000
  
• 0x0459EF90 2 02000300270000000000000052
  Row . X G 2
  1 0 0x0008
  0x40000002
• 0x0459CAB0 3 02000300270000000000000052
  Row .NS W 2
  1 0 0x0000
  0x00000001
• 0x0459C8F8 2 02000300000000000000000054
  Table .IX G 2
  1 0 0x0000
  0x40000002
• 0x0459CA88 3 02000300000000000000000054
  Table .IS G 3
  1 0 0x0000
  0x00000001

TranHdl 3 is waiting on a lock held by TranHdl 2
Type of lock
TranHdl 2 has an X lock on this row
Lock mode
19
-locks showlocks option

• Locks
• Address TranHdl Lockname
  Type Mode Sts Owner Dur HldCnt Att
  ReleaseFlg
• 0x0459C510 2 53514C4332453036BD4A32C841
  Internal P ..S G 2 1 0 0x0000
  0x40000000 Pkg UniqueID 434c5153 36304532 Name
  c8324abd Loading 0
• 0x0459CA10 3 53514C4332453036BD4A32C841
  Internal P ..S G 3 1 0 0x0000
  0x40000000 Pkg UniqueID 434c5153 36304532 Name
  c8324abd Loading 0
• 0x0459CA60 3 010000000100000001007B0056
  Internal V ..S G 3 1 0 0x0000
  0x40000000 Anchor 123 Stmt 1 Env 1 Var 1 Loading
  0
• 0x0459C9E8 3 53514C4445464C5428DD630641
  Internal P ..S G 3 1 0 0x0000
  0x40000000 Pkg UniqueID 444c5153 544c4645 Name
  0663dd28 Loading 0
• 0x0459EF90 2 02000300270000000000000052 Row
  ..X G 2 1 0 0x0008
  0x40000002 TbspaceID 2 TableID 3 RecordID 0x27
• 0x0459CAB0 3 02000300270000000000000052 Row
  .NS W 2 1 0 0x0000
  0x00000001 TbspaceID 2 TableID 3 RecordID 0x27
• 0x0459C8F8 2 02000300000000000000000054
  Table .IX G 2 1 0 0x0000
  0x40000002 TbspaceID 2 TableID 3
• 0x0459CA88 3 02000300000000000000000054
  Table .IS G 3 1 0 0x0000
  0x00000001 TbspaceID 2 TableID 3

20
-applications option

• Command db2pd db db2mon applications
• Applications
• Address AppHandl nod-index NumAgents
  CoorTid Status C-AnchID
  C-StmtUID L-AnchID L-StmtUID Appid
  
• 0x02B9B120 1086 000-01086 1 3148
  UOW-Waiting 0 0
  79 180 LOCAL.DB2.050901041938
  
• 0x01B686D0 599 000-00599 1 1664
  UOW-Waiting 0 0
  84 216 LOCAL.DB2.050901022803
  
• 0x0196FB80 591 000-00591 1 1984
  UOW-Waiting 0 0 0
  0 LOCAL.DB2.050901022720
  
• 0x003FBF30 589 000-00589 1 2928
  UOW-Waiting 0 0 0
  0 LOCAL.DB2.050901022718
  
• 0x01B6BD70 588 000-00588 1 2520
  UOW-Waiting 0 0 0
  0 LOCAL.DB2.050901022717
  
• 0x003FB490 9 000-00009 1 2172
  UOW-Waiting 0 0 0
  0 LOCAL.DB2.050830232506
  
• 0x0196F520 8 000-00008 1 2244
  UOW-Waiting 0 0 0
  0 LOCAL.DB2.050830232351
  

21
-tcbstats option

• Contains over 21 output data elements that report
  on previously unavailable table, index, and table
  space information
• One of the most important db2pd options
• Command db2pd db db2mon -tcbstats

22
-tcbstats Example
23
-tcbstats index option

• Command db2pd db db2mon tcbstats index

24
-tablespaces option

• Command and output db2pd -db db2mon -tablespaces
  tablespace
• 2 filec\tsid.txt Sending -tablespaces output to
  c\tsid.txt.
• Database Partition 0 -- Database DB2MON -- Active
  -- Up 1 days 212846
• Tablespace 2 Configuration
• Address Type Content PageSz ExtentSz Prefetch
  BufID BufIDDisk FSC NumCntrs MaxStripe
  LastConsecPg Name
• 0x03ADE9D0 SMS Any 4096 32 0
  1 1 On 1 0 31
  USERSPACE1
• Tablespace 2 Statistics
• Address TotPages UsablePgs UsedPgs
  PndFreePgs FreePgs HWM State
  MinRecTime NQuiescers
• 0x03ADE9D0 37403 37403 37403 0
  0 0 0x00000000 0
  0
• Tablespace 2 Autoresize Statistics
• Address AS AR Auto InitSize IncSize
  IIP MaxSize LastResize LRF
• 0x03ADE9D0 No No Yes 0 0
  No 0 None No
• Containers
• Address ContainNum Type TotalPages
  UseablePgs StripeSet Container

25
-activestatements option

• Command db2pd db db2mon activestatements
• Database Partition 0 -- Database DB2MON -- Active
  -- Up 1 days 045627
• Active Statement List
• Address AppHandl nod-index UOW-ID StmtID
  AnchID StmtUID EffISO EffLockTOut
  EffDegree StartTime
  LastRefTime
• 0x02D279E0 1086 000-01086 3
  1 79 180 1
  -2 0 Thu Sep 01 001954 2005 Thu
  Sep 01 001954 2005
• Snippet from dynamic option
• Dynamic SQL Statements
• Address AnchID StmtUID
  NumEnv NumVar NumRef NumExe Text
• x03F2D9B0 79 180
  0 0 1
  1 select from
• sysibm.systables

26
Putting it All Together

• Locks
• Address TranHdl Lockname
  Type Mode Sts Owner Dur HldCnt Att
  ReleaseFlg
• 0x0485E3B0 3 02000300270000000000000052 Row
  .NS W 2 1 0 0x0000
  0x00000001
• 0x0485C998 2 02000300000000000000000054
  Table .IX G 2 1 0 0x0000
  0x40000000
• Transactions
• Address AppHandl nod-index TranHdl Locks
  State Tflag Tflag2 Firstlsn
  Lastlsn LogSpace SpaceReserved
  TID AxRegCnt GXID
• 0x045B1580 236 000-00236 2 8
  WRITE 0x00000000 0x00000000 0x000003A9800C
  0x000003AA27B6 234 572
  0x0000000014DD 1 0
• 0x045B2000 425 000-00425 3 5
  READ 0x00000000 0x00000000 0x000000000000
  0x000000000000 0 0
  0x000000001716 1 0
• 0x045B2A80 339 000-00339 4 0
  READ 0x00000000 0x00000000 0x000000000000
  0x000000000000 0 0
  0x00000000168C 1 0
• Applications
• Address AppHandl nod-index NumAgents
  CoorTid Status C-AnchID
  C-StmtUID L-AnchID L-StmtUID Appid
  
• 0x01B19950 425 000-00425 1 3652
  Lock-wait 123 1 0
  0 LOCAL.DB2.050920034220
• 0x0090BE70 236 000-00236 1 2452
  UOW-Waiting 0 0 149
  1 LOCAL.DB2.050920021447
• Dynamic SQL Statements
• Address AnchID StmtUID NumEnv NumVar
  NumRef NumExe Text
• 0x05E289C0 123 1 1 1
  1 1 select from staff
• 0x05E26FB0 129 1 1 1
  1 1 SELECT SCHEMA, NAME,
• STATS_DETAIL, STATS_STATE, STATS_TIME FROM
  SYSTOOLS.HMON_ATM_INFO WHERE (STATS_STATE 2 OR
  STATS_STATE 6) AND STATS_FLAG

27
-bufferpools option

• Command db2pd database sample bufferpools
• Returns bufferpool activity such as size in
  pages, post alter size in pages, number of blocks
  being used for block-based bufferpools
• Element of interest is table spaces suspended for
  I/O
• Indicated by non-zero value
• NOT as good as bufferpool snapshot

28
db2pd Problem Determination

• -catch option
• Can be used to catch any sqlcode, ZRC code, or
  ecf code and capture information needed to help
  solve the problem
• Can be used in conjunction with the db2cos
  callout script which can be called to run any
  db2pd or OS command

29
db2cos

• The DB2 callout script, db2cos is shipped with
  DB2 and is located in the sqllib/cfg directory
  and must be moved to the DB2 /bin directory
  before it can be used
• db2cos default template provided

30
db2pd Problem Determination Scenarios

• Command db2pd catch -911,68 db2cos
• Output
• Error Catch 2
• Sqlcode -911
• ReasonCode 68
• ZRC 0
• ECF 0
• Component ID 0
• LockName Not Set
• LockType Not Set
• Current Count 0
• Max Count 255
• Bitmap 0x261
• Action Error code catch flag enabled
• Action Execute sqllib/db2cos callout script

31
db2pd Problem Determination Scenarios

• db2diag.log entry
• 2005-09-01-17.49.59.065000-240 I2998718H296
  LEVEL Event
• PID 220 TID 6024
  PROC db2syscs.exe
• INSTANCE DB2 NODE 000
• FUNCTION DB2 UDB, RAS/PD component,
  pdErrorCatch, probe30
• START Error catch set for sqlCode -911
  reasonCode 68
• Lock timeout received
• C\Documents and Settings\Phil Gunninggtdb2
  "select from department"
• SQL0911N The current transaction has been rolled
  back because of a deadlock
• or timeout. Reason code "68". SQLSTATE40001
• Reset catch flags as follows db2pd catch clear
  all

32
Stack Trace

• The stack trace has been incorporated in db2pd as
  of V8.2.2
• Issue the following command to produce a stack
  trace for a process or thread
• db2pd stack 2632
• Command response
• gtdb2pd -stack 2632
• Attempting to dump stack trace for pid 2632.
• See current DIAGPATH for trapfile.

33
Summary

• db2pd is a lightweight monitoring tool that
  provides information that is not available
  through other means
• Use it with the understanding that output may not
  be exact due to changing data
• Best used via a script that formats the output
  into a more readable format
• Run on a regular schedule, save to a file or
  insert into DB2 tables and review and make tuning
  adjustments as necessary
• Familiarize yourself with the extensive options
  and look for continued improvements via Fixpaks
  and new releases

34
Summary

• What db2pd is not
• As of DB2 V8.2.3, it is not a replacement for
  snapshot monitoring or event monitoring
• First version of it goes a long way in providing
  new monitoring and problem determination
  capabilities

35
References

• DB2 UDB Command Reference V8.2, SC09-4828-01
• http//publib.boulder.ibm.com/infocenter/db2help/i
  ndex.jsp?topic/com.ibm.db2.udb.doc/core/r0011729.
  htm

36
db2pd in a NutshellSession D04
THANK YOU! Phil Gunning Gunning Technology
Solutions, LLC pgunning_at_gunningts.com