db2pd命令捕获死锁信息
db2解决死锁的方法
db2解决死锁的方法DB2是一种常见的关系型数据库管理系统，它被广泛应用于企业级应用程序中。
然而，随着应用程序的复杂性增加和并发访问的增加，死锁问题也变得越来越常见。
在本文中，我们将探讨一些使用DB2解决死锁问题的方法。
1. 死锁的定义和原因死锁是指两个或多个事务彼此等待对方持有的资源，从而导致所有事务无法继续执行的状态。
死锁通常发生在并发访问数据库时，其中一个事务正在使用某个资源，而另一个事务需要访问相同的资源。
死锁的发生原因可以归结为四个条件：互斥（资源只能被一个事务使用）、持有并等待（一个事务持有资源并等待另一个事务的资源）、不可剥夺（资源不能被其他事务抢占）、循环等待（多个事务形成循环等待资源）。
2. 使用锁机制避免死锁在DB2中，可以使用锁机制来避免死锁的发生。
锁是一种机制，用于协调并发事务对共享资源的访问。
DB2提供了两种类型的锁：共享锁和排他锁。
共享锁允许多个事务同时读取资源，但不允许写入资源；排他锁只允许一个事务同时读取或写入资源。
为了避免死锁，可以采取以下策略：- 在事务开始时，尽量将锁的范围缩小到最小，只锁定必要的资源。
- 在事务执行期间，尽量减少锁的持有时间，执行完操作后尽快释放锁。
- 避免循环等待，即事务在请求资源时按照统一的顺序进行，避免形成死锁的循环等待。
3. 设置适当的隔离级别DB2提供了多种隔离级别，用于控制事务之间的相互影响。
不同的隔离级别对并发访问的控制程度不同。
在选择隔离级别时，需要权衡事务的一致性和性能。
在避免死锁的角度考虑，可以选择较低的隔离级别，如读取已提交（Read Committed）。
较低的隔离级别可以减少锁的竞争，从而降低死锁的风险。
但同时，较低的隔离级别也可能导致数据不一致的问题，需要根据具体业务需求进行权衡。
4. 监控和诊断死锁DB2提供了一些工具和功能，用于监控和诊断死锁问题。
可以通过以下方式来实现：- 使用DB2的系统监控工具，如db2pd命令和db2top工具，可以实时查看数据库的锁和死锁情况。
DB2命令大全
1.12 备份数据库
备份表空间
Db2 “backup database databasename” tablespace tablespaceName to /path
Db2 backup database dbname to /path
db2 list db directory
1.16插入空值到表中
import from /dev/null of del replace into db2inst1.表名
1.17建立nickname
1、在db2命令窗口下，运行connect to 目标库 user 用户名 using 密码
Select stmt_text ,(stop_time-start_time) from stmt_ monitor_name Where stmt_operation not in (7,8,9,19) order by decimal(stop_time-start_time) desc fetch first 10 rows only
Lock Object Name = 1163533 ＃被锁对象名称
Object Type = Row ＃被锁对象类型
Tablespace Name = tbs_data ＃被锁对象所在的表空间
－LOCKTIMEOUT单位是秒，是锁等待最长时间，超过该时间仍未获得锁，则返回错误。
设置提示：
－缺省情况下，LOCKTIMEOUT是-1，意味着锁等待时间无限期，这和实际应用需求一般是不太相符的，需要将其值设为大于0的一个数。
－DLCHKTIME时间通常要设得比LOCKTIMEOUT时间小一些，否则未等发现死锁，就会被以锁等待超时而返回错误。
DB2使用命令监控锁的情况
作业：1.使用命令监控锁的情况，使用类型2.练习在CLP命令中使用不同的隔离级别3.使用快照监控数据库的使用情况4.使用表函数监控SQL语句的使用情况1.使用命令监控锁的情况，使用类型[myinst@ye ~]$ db2 activate database mydb3SQL1493N The application is already connected to an active database.#查看锁表--看应用在等什么[myinst@ye ~]$ db2pd -db mydb3 -locks showlock waitDatabase Member 0 -- Database MYDB3 -- Active -- Up 0 days 01:50:51 -- Date 2015-08-18-15.48.27.193864Locks:Address TranHdl Lockname Type Mode Sts Owner Dur HoldCount Att ReleaseFlg rrIID TableNm SchemaNm[myinst@ye ~]$ db2 get db cfg |grep DLCHKTIME#是DB2检查死锁的间隔时间，该值为10000msInterval for checking deadlock (ms) (DLCHKTIME) = 10000[myinst@ye ~]$ db2 get db cfg |grep LOCKTIMEOUT#锁等待最长时间,单位是秒Lock timeout (sec) (LOCKTIMEOUT) = -1Lock timeout events (MON_LOCKTIMEOUT) = NONE#查看当前的隔离级别[myinst@ye ~]$ db2 "SELECT CURRENT ISOLATION FROM SYSIBM.SYSDUMMY1"#查看锁表--用快照[myinst@ye ~]$ db2 "GET SNAPSHOT FOR LOCKS ON mydb3"Database Lock SnapshotDatabase name = MYDB3Database path = /www/db2/db2test/myinst/NODE0000/SQL00001/MEMBER0000/Input database alias = MYDB3Locks held = 0Applications currently connected = 1Agents currently waiting on locks = 0Snapshot timestamp = 08/18/2015 15:53:47.009051Application handle = 13Application ID = *LOCAL.DB2.150818055741Sequence number = 00001Application name = db2fw0CONNECT Authorization ID = MYINSTApplication status = Connect CompletedStatus change time = Not CollectedApplication code page = 1386Locks held = 0Total wait time (ms) = 0Application handle = 12Application ID = *LOCAL.DB2.150818055740Sequence number = 00001Application name = db2lusedCONNECT Authorization ID = MYINSTApplication status = Connect CompletedStatus change time = Not CollectedApplication code page = 1208Locks held = 0Total wait time (ms) = 0Application handle = 11Application ID = *LOCAL.DB2.150818055739Sequence number = 00001Application name = db2wlmdCONNECT Authorization ID = MYINSTApplication status = Connect CompletedStatus change time = Not CollectedApplication code page = 1208Locks held = 0Total wait time (ms) = 0Application handle = 10Application ID = *LOCAL.DB2.150818055738 Sequence number = 00001Application name = db2taskdCONNECT Authorization ID = MYINSTApplication status = Connect CompletedStatus change time = Not Collected Application code page = 1208Locks held = 0Total wait time (ms) = 02.练习在CLP命令中使用不同的隔离级别DB2隔离级别分为如下四种：a、可重复读（Repeatable Read，RR）b、读稳定性（Read Stability，RS）c、游标稳定性（Cursor Stability，CS）d、未提交读（Uncommitted Read，UR）DB2默认的隔离级别为：游标稳定性（CS）[myinst@ye ~]$ db2 list command optionsCommand Line Processor Option SettingsBackend process wait time (seconds) (DB2BQTIME) = 1No. of retries to connect to backend (DB2BQTRY) = 60 Request queue wait time (seconds) (DB2RQTIME) = 5Input queue wait time (seconds) (DB2IQTIME) = 5 Command options (DB2OPTIONS) =Option Description Current Setting ------ ---------------------------------------- ----------------a Display SQLCA OFF-b Auto-Bind ON-c Auto-Commit ON-d Retrieve and display XML declarations OFF-e Display SQLCODE/SQLSTATE OFF-f Read from input file OFF-i Display XML data with indentation OFF-j Return code for system calls OFF-l Log commands in history file OFF-m Display the number of rows affected OFF-n Remove new line character OFF-o Display output ON-p Display interactive input prompt ON-q Preserve whitespaces & linefeeds OFF-r Save output to report file OFF-s Stop execution on command error OFF-t Set statement termination character OFF-v Echo current command OFF-w Display FETCH/SELECT warning messages ON-x Suppress printing of column headings OFF-z Save all output to output file OFF[myinst@ye ~]$ db2 "update command options using c off"DB20000I The UPDATE COMMAND OPTIONS command completed successfully.[myinst@ye ~]$ db2 +c(c) Copyright IBM Corporation 1993,2007Command Line Processor for DB2 Client 10.5.5You can issue database manager commands and SQL statements from the command prompt. For example:db2 => connect to sampledb2 => bind sample.bnddb2 => select * from emp for update with rrID CNAME----------- --------------------1 a1 record(s) selected.db2 => select * from emp for update with csID CNAME----------- --------------------1 a1 record(s) selected.db2 => select * from emp for update with urID CNAME----------- --------------------1 a1 record(s) selected.db2 => select * from emp for update with rsID CNAME----------- --------------------1 a1 record(s) selected.[myinst@ye ~]$ db2 "create table lck(pid int, uid int, id int, name varchar(10))"; DB20000I The SQL command completed successfully.[myinst@ye ~]$ db2 "alter table lck alter column pid set not null";DB20000I The SQL command completed successfully.[myinst@ye ~]$ db2 "reorg table lck";DB20000I The REORG command completed successfully.db2 => create unique index uniqindx on lck(uid)DB20000I The SQL command completed successfully.db2 => create index normalindx on lck(id)DB20000I The SQL command completed successfully.db2 => insert into lck values(1,1,1,'test1')DB20000I The SQL command completed successfully.db2 => insert into lck values(2,2,2,'test2')DB20000I The SQL command completed successfully.db2 => insert into lck values(3,3,3,'test3')DB20000I The SQL command completed successfully.db2 => insert into lck values(4,4,3,'test3')DB20000I The SQL command completed successfully.db2 => insert into lck values(5,5,5,'test5')DB20000I The SQL command completed successfully.db2 => runstats on table lck and detailed indexes all shrlevel change DB20000I The RUNSTATS command completed successfully.[myinst@ye ~]$ db2 +cdb2 => select * from lck where pid=1 for update with rrPID UID ID NAME----------- ----------- ----------- ----------1 1 1 test11 record(s) selected.db2 => select * from lck where name='test2' for update with rrPID UID ID NAME----------- ----------- ----------- ----------2 2 2 test21 record(s) selected.db2 => select * from lck where name='test2' for update with rsPID UID ID NAME----------- ----------- ----------- ----------2 2 2 test21 record(s) selected.db2 => select * from lck where id=3 for update with rrPID UID ID NAME----------- ----------- ----------- ----------3 3 3 test34 4 3 test32 record(s) selected.db2 => select * from lck where id=3 for update with rsPID UID ID NAME----------- ----------- ----------- ----------3 3 3 test34 4 3 test3 2 record(s) selected.session2:[myinst@ye ~]$ db2 -t出现了表锁#可以查看表锁情况3.使用快照监控数据库的使用情况[myinst@ye ~]$ db2 get monitor switchesMonitor Recording SwitchesSwitch list for member 0Buffer Pool Activity Information (BUFFERPOOL) = OFFLock Information (LOCK) = OFFSorting Information (SORT) = OFFSQL Statement Information (STATEMENT) = OFFTable Activity Information (TABLE) = OFFTake Timestamp Information (TIMESTAMP) = ON 08/18/2015 13:54:41.614454 Unit of Work Information (UOW) = OFF[myinst@ye ~]$ db2 update monitor switches using lock on statement onDB20000I The UPDATE MONITOR SWITCHES command completed successfully. [myinst@ye ~]$ db2 get monitor switchesMonitor Recording SwitchesSwitch list for member 0Buffer Pool Activity Information (BUFFERPOOL) = OFFLock Information (LOCK) = ON 08/18/2015 13:59:57.793842 Sorting Information (SORT) = OFFSQL Statement Information (STATEMENT) = ON 08/18/2015 13:59:57.793842 Table Activity Information (TABLE) = OFFTake Timestamp Information (TIMESTAMP) = ON 08/18/2015 13:54:41.614454 Unit of Work Information (UOW) = OFF#查看快照信息--查看数据库管理器级别快照信息[myinst@ye ~]$ db2 get snapshot for dbmDatabase Manager SnapshotNode type = Enterprise Server Edition with local and remote clientsInstance name = myinstNumber of members in DB2 instance = 1Database manager status = ActiveProduct name = DB2 v10.5.0.5Service level = s141128 (IP23633)Private Sort heap allocated = 0Private Sort heap high water mark = 0Post threshold sorts = Not CollectedPiped sorts requested = 0Piped sorts accepted = 0Start Database Manager timestamp = 08/18/2015 13:54:41.614454Last reset timestamp =Snapshot timestamp = 08/18/2015 14:19:34.072548Remote connections to db manager = 7Remote connections executing in db manager = 0Local connections = 1Local connections executing in db manager = 0Active local databases = 1High water mark for agents registered = 12Agents registered = 12Idle agents = 0Committed private Memory (Bytes) = 24051712Switch list for member 0Buffer Pool Activity Information (BUFFERPOOL) = OFFLock Information (LOCK) = ON 08/18/2015 13:57:37.650306 Sorting Information (SORT) = OFFSQL Statement Information (STATEMENT) = ON 08/18/2015 13:59:57.860423 Table Activity Information (TABLE) = OFFTake Timestamp Information (TIMESTAMP) = ON 08/18/2015 13:54:41.614454Unit of Work Information (UOW) = OFFAgents assigned from pool = 23Agents created from empty pool = 15Agents stolen from another application = 0High water mark for coordinating agents = 12Hash joins after heap threshold exceeded = 0OLAP functions after heap threshold exceeded = 0[myinst@ye ~]$ db2 get snapshot for database on mydb3Database SnapshotDatabase name = MYDB3Database path = /www/db2/db2test/myinst/NODE0000/SQL00001/MEMBER0000/Input database alias = MYDB3Database status = ActiveCatalog database partition number = 0Catalog network node name = Operating system running at database server= LINUXAMD64Location of the database = LocalFirst database connect timestamp = 08/18/2015 13:57:36.024785Last reset timestamp =Last backup timestamp = 08/11/2015 14:21:39.000000Snapshot timestamp = 08/18/2015 14:22:19.669943Number of automatic storage paths = 1Automatic storage path = /www/db2/db2testNode number = 0State = In UseHigh water mark for connections = 9Application connects = 23Secondary connects total = 8Applications connected currently = 1Appls. executing in db manager currently = 0Agents associated with applications = 8Maximum agents associated with applications= 9Maximum coordinating agents = 9Number of Threshold Violations = 0Locks held currently = 0Lock waits = 0Time database waited on locks (ms) = 0Lock list memory in use (Bytes) = 38400Deadlocks detected = 0Lock escalations = 0Exclusive lock escalations = 0Agents currently waiting on locks = 0Lock Timeouts = 0Number of indoubt transactions = 0#查看应用级别快照信息#db2 get snapshot for application agentid appl-handler#注:appl-handler可以从list applicaitions的输出中得到[myinst@ye ~]$ db2 "get snapshot for application agentid 8"Application SnapshotApplication handle = 8Application status = UOW WaitingStatus change time = Not CollectedApplication code page = 1208Application country/region code = 1DUOW correlation token = *LOCAL.myinst.150818055736 Application name = db2bpApplication ID = *LOCAL.myinst.150818055736 Sequence number = 00003TP Monitor client user ID =TP Monitor client workstation name =TP Monitor client application name =TP Monitor client accounting string =Connection request start timestamp = 08/18/2015 13:57:36.024785 Connect request completion timestamp = 08/18/2015 13:57:37.664637 Application idle time = 6 minutes 39 seconds CONNECT Authorization ID = MYINSTClient login ID = myinstConfiguration NNAME of client = Client database manager product ID = SQL10055Process ID of client application = 2658Platform of client application = LINUXAMD64Communication protocol of client = Local ClientInbound communication address = *LOCAL.myinstDatabase name = MYDB3Database path = /www/db2/db2test/myinst/NODE0000/SQL00001/MEMBER0000/Client database alias = MYDB3Input database alias =Last reset timestamp =Snapshot timestamp = 08/18/2015 14:48:27.627417 Authorization level granted =User authority:DBADM authoritySECADM authorityDATAACCESS authorityACCESSCTRL authorityGroup authority:SYSADM authorityCREATETAB authorityBINDADD authorityCONNECT authorityIMPLICIT_SCHEMA authorityCoordinator member number = 0Current member number = 0Coordinator agent process or thread ID = 21Current Workload ID = 1Agents stolen = 0Agents waiting on locks = 0Maximum associated agents = 1Priority at which application agents work = 0Priority type = DynamicLock timeout (seconds) = -1Locks held by application = 0Lock waits since connect = 0Time application waited on locks (ms) = 0Deadlocks detected = 0Lock escalations = 0Exclusive lock escalations = 0Number of Lock Timeouts since connected = 0Total time UOW waited on locks (ms) = Not CollectedTotal sorts = 0Total sort time (ms) = Not CollectedTotal sort overflows = 0#查看表级别快照信息#db2 get snapshot for tables on dbname#注:需要把tables快照开关设为ON才会有作用[myinst@ye ~]$ db2 "get snapshot for tables on mydb3"Table SnapshotFirst database connect timestamp = 08/18/2015 13:57:36.024785Last reset timestamp =Snapshot timestamp = 08/18/2015 14:46:14.123659Database name = MYDB3Database path = /www/db2/db2test/myinst/NODE0000/SQL00001/MEMBER0000/Input database alias = MYDB3Number of accessed tables = 6Table ListTable Schema = SYSIBMTable Name = SYSTABLESTable Type = CatalogData Object Pages = 8Index Object Pages = 20LOB Object pages = 120Rows Read = Not CollectedRows Written = 40Overflows = 0Page Reorgs = 0Table Schema = SYSIBMTable Name = SYSINDEXESTable Type = CatalogData Object Pages = 5Index Object Pages = 10LOB Object pages = 1Rows Read = Not CollectedRows Written = 42Overflows = 0Page Reorgs = 0Table Schema = SYSTOOLSTable Name = HMON_ATM_INFOTable Type = UserData Object Pages = 2Index Object Pages = 4Rows Read = Not CollectedRows Written = 0Overflows = 1Page Reorgs = 0Table Schema = SYSIBMTable Name = SYSDATAPARTITIONSTable Type = CatalogData Object Pages = 1Index Object Pages = 14Rows Read = Not CollectedRows Written = 1Overflows = 0Page Reorgs = 0Table Schema = SYSIBMTable Name = SYSPLANTable Type = CatalogData Object Pages = 6Index Object Pages = 10LOB Object pages = 56Rows Read = Not CollectedRows Written = 4Overflows = 0Page Reorgs = 0Table Schema = SYSIBMTable Name = SYSCOLDISTTable Type = CatalogData Object Pages = 123Index Object Pages = 84Rows Read = Not CollectedRows Written = 120Overflows = 0Page Reorgs = 0#查看锁快照信息#db2 get snapshot for locks on dbname 这条命令很有用，可以查看具体有哪些锁。
db2pd用法
(2)-fcm FCM信息
(3)-mempools 输出内存池相关信息。如：
************************************************************
交互方式只要在db2pd命令行下输入相关参数即可。本例输出结果为实例的内存池使用相关信息。
以上介绍的交互式执行方式如果改用在系统命令行直接执行的话：
************************************************************
[db2inst1@localhost ~]$ db2pd -mempools
0x10175AF0 457 [000-00457] 29669 0 Coord Active 0 db2jccTP 38169 0 NotSet DB2DB
0x10176D70 201 [000-00201] 31400 0 Coord Active 0 db2jccma 95758 0 NotSet DB2DB
0x100006E8 DBMS apmh 70 0 109258 1622016 114378 131072 1622016 131072 Ovf 86 n/a
0x10000650 DBMS kerh 52 96 27260 360448 27684 49152 360448 49152 Ovf 11 n/a
-ins 输出实例相关信息
-db dbname 输出数据库dbname相关信息,用于单一指定的数据库。
-alldb 输出所有数据库信息。
DB2数据库常用命令数据库学习
db2 list active databases 列出所有活动的数据库
db2 list tables for all 列出当前数据库下所有的表
db2 list tables for schema btp 列出当前数据库中schema为btp的表
注：appl-handler可以从list applicaitions的输出中得到
-查看表级别快照信息
DB2 get snapshot for tables on dbname
注：需要把tables快照开关设为ON才会有作用
-查看锁快照信息
DB2 get snapshot for locks on dbname
1、 以默认分隔符加载,默认为“,”号
db2 "import from btpoper.txt of del insert into btpoper"
2、 以指定分隔符“|”加载
db2 "import from btpoper.txt of del modified by coldel| insert into btpoper"
可以使用时间查看器收集锁事件，SQL语句事件，从而根据事件分析锁原因。
事件类型
使用事件监控器，首先要选定所关注的事件类型，DB2中有很多事件类型，可以用于锁分析的通常会用到以下三种：
DEADLOCKS
DEADLOCKS WITH DETAILS
STATEMENTS
db2 list tablespaces show detail 查看当前数据库表空间分配状况
db2 list tablespace containers for 2 show detail 查看tablespace id=2使用容器所在目录
db2pd命令捕获死锁信息
本文通过一个实例讲解了在DB2版本9以后，如何使用db2pd命令捕获死锁信息死锁经常会存在于我们的应用系统中，如何捕获死锁信息并解决死锁问题，是一个比较复杂的问题。
DB2提供了死锁事件监控器来获取死锁信息，可以非常方便地获取死锁信息。
从DB2版本8.2.2开始，DB2也可以使用db2pd命令和db2cos脚本来获取死锁信息，提供了一种新的途径来获取死锁信息。
从DB2版本9开始，我们可以使用db2pd -catch 命令来捕获错误信息，然后调用一个sqllib/db2cos 的脚本收集出错时的现场信息。
该命令的使用语法如下：Usage:-catch clear | status | <errorCode> [<action>] [count=<count>]Sets catchFlag to catch error or warning.Error Codes:<sqlCode>[,<reasonCode>] / sqlcode=<sqlCode>[,<reasonCode>]ZRC (hex or integer)ECF (hex or integer)"deadlock" or "locktimeout"Actions:[db2cos] (default) Run sqllib/db2cos callout script[lockname=<lockname>] Lockname for catching specific lock(lockname=000200030000001F0000000052)[locktype=<locktype>] Locktype for catching specific lock(locktype=R or locktype=52)下面我们通过一个实例来讲解如何使用db2pd -catch命令获取死锁信息。
Lab 1-4 db2pd对锁的监控
db2pd对锁的监控（30分钟）目的：掌握db2pd对于锁信息监控的使用技巧1．打开3个db2cmd窗口，并分别连接到sample数据库2．在第一个窗口中输入3．在第二个窗口中输入此时，这条命令会等待。
下面我们用db2pd来分析这种等待的情况。
4．在第三个窗口中输入db2pd -db sample -locks wait showlocksdb2pd报告了有两个交易(TranHdl: Transaction Handler)产生了锁等待，其中一个交易(TranHdl=6)的锁状态(Mode)为G，表示该锁已被授予(Granted)，另外一个交易(TranHdl=2)的锁为等待(W, Waiting)。
5．为了查出在那张表上产生了锁等待，我们在第三个窗口中输入db2 "SELECT TABSCHEMA, TABNAME FROM SYSCAT.TABLES WHERE TBSPACEID = 2 AND TABLEID = 6"由此，我们查出在EMPLOYEE这张表上产生了锁等待。
由此，我们完成了从事务到应用的对应，我们发现事务2,6分别属于应用30, 34，而且这两个应用都处于Write的状态。
因此，我们确定是写操作产生了锁等待。
7．我们需要近一步查出产生锁等待的应用程序的程序名，我们在第三个窗口中输入db2pd -agents由此，我们也找出了是哪两个客户端进程(Process ID)产生了锁等待。
8．能否近一步看出是什么样的SQL语句产生了锁？首先通过以下语句获得应用程序的更多信息，其中L-Anch ID表示上次执行的SQL，C-Anch ID表示当前执行的SQL：db2pd -db sample -applications9．得到上述信息后，我们再通过下述语句，就可以找出产生锁等待的SQL语句了db2pd -db sample -dynamic结果如下图所示：。
db2pd简介及使用方法
1.打开 db2pd – Monitor and Troubleshoot DB Command，如图 3 所示。
图 3. DB2 Information Center 中关于 db2pd 工具的信息调用 db2pd 工具有两种方式。
可以用交互模式调用 db2pd 工具，或者直接在操作系统命令提示符下运行。
要是用交互模式执行该工具，可以在操作系统命令提示符下输入 db2pd –interactive 或者直接输入 db2pd，这样将看到 db2pd 命令提示符db2pd>，可以输入命令选项。
使用–help 选项可以获得帮助信息。
退出 db2pd 命令提示符只需要输入 quit 或者 q。
图 4 中的例子说明了如何使用交互模式显示当前的代理。
图 4. 用交互模式调用 db2pd在操作系统命令提示符下调用该工具可以输入带有命令选项的 db2pd 命令。
下面的例子（图 5）使用 -agents 选项显示了所有的活动代理。
图 5. 在操作系统命令提示符下调用 db2pd此外，还可以通过将选项保存在文件中或者在 DB2PDOPT 环境变量中设置选项来控制该命令。
下面的例子（图 6）说明可以将 -agents 选项保存在一个（在该例中）名叫file.out的文件中，然后使用 db2pd –command file.out 执行选项。
图 6. 将 db2pd 选项保存在文件中如果要使用 DB2PDOPT 环境变量，可以将 DB2PDOPT 设成需要的选项然后像下面这样调用 db2pd：图 7. 在 DB2PDOPT 环境变量中设置 db2pd 选项更好的是，可以指定–repeat 参数重复该命令。
比方说，下面的命令每 2 秒钟显示一次 DB2 内存信息，共 5 次：db2pd –mempools –repeat 2 5此外，通过 file= 参数还可以将特定 db2pd 命令选项的结果保存到文件中。
file 和 repeat 参数可以结合使用：回页首监控的例子下面这些例子说明了如何用 db2pd 工具监控您的数据库环境。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
本文通过一个实例讲解了在DB2版本9以后，如何使用db2pd命令捕获死锁信息死锁经常会存在于我们的应用系统中，如何捕获死锁信息并解决死锁问题，是一个比较复杂的问题。
DB2提供了死锁事件监控器来获取死锁信息，可以非常方便地获取死锁信息。
从DB2版本8.2.2开始，DB2也可以使用db2pd命令和db2cos脚本来获取死锁信息，提供了一种新的途径来获取死锁信息。
从DB2版本9开始，我们可以使用db2pd -catch 命令来捕获错误信息，然后调用一个sqllib/db2cos 的脚本收集出错时的现场信息。
该命令的使用语法如下：Usage:-catch clear | status | <errorCode> [<action>] [count=<count>]Sets catchFlag to catch error or warning.Error Codes:<sqlCode>[,<reasonCode>] / sqlcode=<sqlCode>[,<reasonCode>]ZRC (hex or integer)ECF (hex or integer)"deadlock" or "locktimeout"Actions:[db2cos] (default) Run sqllib/db2cos callout script[lockname=<lockname>] Lockname for catching specific lock(lockname=000200030000001F0000000052)[locktype=<locktype>] Locktype for catching specific lock(locktype=R or locktype=52)下面我们通过一个实例来讲解如何使用db2pd -catch命令获取死锁信息。
如无特殊说明，命令均使用DB2实例用户执行。
1、将$HOME/sqllib/cfg/db2cos例子脚本拷贝到$HOME/sqllib下,并改变属性为实例用户添加执行权限：cp $HOME/sqllib/cfg/db2cos $HOME/sqllibchmodu+x $HOME/sqllib/db2cos2、设置db2pd -catch捕获死锁信息,当死锁出现的时候调用db2cos命令。
可以使用如下命令之一：1）db2pd -catch deadlock2）db2pd -catch -911,2例子输出如下：$ db2pd -catch deadlockError Catch #1Sqlcode: 0ReasonCode: 0ZRC: -2146435070ECF: 0Component ID: 0LockName: Not SetLockType: Not SetCurrent Count: 0Max Count: 255Bitmap: 0xA1Action: Error code catch flag enabledAction: Execute sqllib/db2cos callout script此时查看db2diag.log的输出，可以看到类似信息：2006-03-30-17.23.01.128996+480 I25427C274 LEVEL: EventPID : 1773620 TID : 1 PROC : db2pdINSTANCE: db2fp9i1 NODE : 000FUNCTION: DB2 UDB, RAS/PD component, pdErrorCatch, probe:30 START : Error catch set for ZRC -2146435070我们可以看到错误捕获机制已经启动。
3、新开一个命令窗口，我们称之为窗口1，输入如下命令：$db2 +cdb2 => connect to sampleDatabase Connection InformationDatabase server = DB2/6000 8.2.2SQL authorization ID = DB2FP9I1Local database alias = SAMPLEdb2 => create table tstdlock1 (id int, name char(10))DB20000I The SQL command completed successfully.db2 => commitDB20000I The SQL command completed successfully.db2 => insert into tstdlock1 values(1,'test1')DB20000I The SQL command completed successfully.4、再新开一个命令窗口，我们称之为窗口2，输入如下命令：$db2 +cdb2 => connect to sampleDatabase Connection InformationDatabase server = DB2/6000 8.2.2SQL authorization ID = DB2FP9I1Local database alias = SAMPLEdb2 => create table tstdlock2 (id int, name char(10))DB20000I The SQL command completed successfully.db2 => commitDB20000I The SQL command completed successfully.db2 => insert into tstdlock2 values(2,'test2')DB20000I The SQL command completed successfully.db2 => select * from tstdlock1此时该命令会挂起，处于锁等待状态，等待窗口1中的insert语句完成后才能继续进行。
5、切换到窗口1，输入如下命令：db2 => select * from tstdlock2此时该命令也会挂起，处于锁等待状态，等待窗口2中的insert语句完成后才能继续进行。
6、等待一段时间，取决于数据库的配置参数DLCHKTIME的设置，默认为10秒。
就会发现窗口2中的事务因为死锁回滚：SQL0911N The current transaction has been rolled back because of a deadlockor timeout. Reason code "2". SQLSTATE=40001而窗口1中的命令执行成功：db2 => select * from tstdlock2ID NAME----------- ----------0 record(s) selected.注意：在实际的测试中，也可能是窗口1中的事务回滚。
此时查看db2diag.log文件，会看到如下信息：2006-03-30-17.29.05.273286+480 I28093C411 LEVEL: EventPID : 1597606 TID : 1 PROC : db2agent (SAMPLE) 0INSTANCE: db2fp9i1 NODE : 000 DB : SAMPLEAPPHDL : 0-8 APPID: *LOCAL.db2fp9i1.060330092553FUNCTION: DB2 UDB, trace services, pdInvokeCalloutScript, probe:10START : Invoking sqllib/db2cos script from global services sqlzeMapZrc2006-03-30-17.29.05.868458+480 I28505C388 LEVEL: EventPID : 1597606 TID : 1 PROC : db2agent (SAMPLE) 0INSTANCE: db2fp9i1 NODE : 000 DB : SAMPLEAPPHDL : 0-8 APPID: *LOCAL.db2fp9i1.060330092553FUNCTION: DB2 UDB, trace services, pdInvokeCalloutScript, probe:20STOP : Completed invoking sqllib/db2cos script说明我们的错误捕获机制已经成功捕获到死锁信息，并且调用了db2cos脚本。
此时查看$HOME/sqllib/db2dump目录，会看到db2cos.rpt文件。
注意，对于出现死锁的情况，我们的错误捕获机制会两次调用db2cos脚本，第一次是在事务回滚前，第二此则是事务回滚后。
查看死锁的信息，我们应该关注第一次调用db2cos脚本的输出。
现在我们看一下db2cos例子脚本的内容，可以看到出现死锁时我们的处理机之："DEADLOCK")echo "Lock Deadlock Caught" >> $HOME/sqllib/db2dump/db2cos.rptdate>> $HOME/sqllib/db2dump/db2cos.rptecho "Instance " $instance >> $HOME/sqllib/db2dump/db2cos.rptecho "Datbase: " $database >> $HOME/sqllib/db2dump/db2cos.rptecho "Partition Number:" $dbpart>> $HOME/sqllib/db2dump/db2cos.rptecho "PID: " $pid>> $HOME/sqllib/db2dump/db2cos.rptecho "TID: " $tid>> $HOME/sqllib/db2dump/db2cos.rptecho "Function: " $function >> $HOME/sqllib/db2dump/db2cos.rptecho "Component: " $component >> $HOME/sqllib/db2dump/db2cos.rptecho "Probe: " $probe >> $HOME/sqllib/db2dump/db2cos.rptecho "Timestamp: " $timestamp >> $HOME/sqllib/db2dump/db2cos.rptecho "AppID: " $appid>> $HOME/sqllib/db2dump/db2cos.rptecho "AppHdl: " $apphld>> $HOME/sqllib/db2dump/db2cos.rptdb2pd -db $database >> $HOME/sqllib/db2dump/db2cos.rpt;;我们看到，如果捕获到的错误是死锁（DEADLOCK）的话，我们将执行db2pd -db $database获取数据库的所有db2pd输出。