1 / 36

db2pd in a Nutshell

Platform: DB2 for Linux, UNIX, and Windows. db2pd in a Nutshell. Phil Gunning Principal Consultant, Gunning Technology Solutions, LLC Session: D04 25 October 2005 0830 – 0930. db2pd Background. Developed based on OnStat utility from Informix Problem determination and monitoring tool

elita
Download Presentation

db2pd in a Nutshell

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Platform: DB2 for Linux, UNIX, and Windows db2pd in a Nutshell • 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 18:36:03 Database Manager Configuration Settings: Description Memory Value Disk Value RELEASE 0xa00 0xa00 CPUSPEED 1.141499e-006 1.141499e-006 COMM_BANDWIDTH 1.000000e+002 1.000000e+002 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: >db2pd -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 C:>db2pd -db sample -file c:\dbase.txt Sending all options output to c:\dbase.txt Database Partition 0 -- Database SAMPLE -- Active -- Up 0 days 00:42:21 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 00:42:21 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 00:42:21 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 0x04483D60 4096 IBMHIDDENBP4K 4096 16 0 0 N 0 0 16 16 0 0x0448C200 4097 IBMHIDDENBP8K 8192 16 0 0 N 0 0 16 16 0 0x0448C480 4098 IBMHIDDENBP16K 16384 16 0 0 N 0 0 16 16 0

  16. -transactions option • Command: db2pd –db db2mon –trans Database Partition 0 -- Database DB2MON -- Active -- Up 1 days 04:27:09 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 04:44:50 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 16:39:33 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 file=c:\tsid.txt Sending -tablespaces output to c:\tsid.txt. Database Partition 0 -- Database DB2MON -- Active -- Up 1 days 21:28:46 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 0x03ADEFD0 0 Path 37403 37403 0 C:\DB2\NODE0000\SQL00003\SQLT0002.0

  25. -activestatements option • Command: db2pd –db db2mon –activestatements Database Partition 0 -- Database DB2MON -- Active -- Up 1 days 04:56:27 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 00:19:54 2005 Thu Sep 01 00:19:54 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 0x01B19950425 [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 = 'Y' 0x05E27E60 149 1 1 1 1 1 insert into department values('1b', 'Gunning','4', 'C00', NULL)

  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, probe:30 START : Error catch set for sqlCode -911 reasonCode 68 • Lock timeout received: C:\Documents and Settings\Phil Gunning>db2 "select * from department" SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "68". SQLSTATE=40001 • 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: >db2pd -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/index.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@gunningts.com

More Related