db2pd:监控与排障数据库命令
db2pd命令常用语法:
>>-db2pd--+------------------------------------------+----------> '- -activestatements--+------------------+-' +-apphdl=appHandle-+ '-file=filename----' >--+----------------------------------------+-------------------> '- -addnode--+-------------+--+--------+-' '-oldviewapps-' '-detail-' >--+----------------+-------------------------------------------> +- -alldatabases-+ '- -alldbs-------' >--+--------------------------------------------------+---------> +- -alldbpartitionnums-----------------------------+ | .-,-------------------------. | | V | | +- -dbpartitionnum----database_partition_number-+--+ | (1) | +------- -global-----------------------------------+ | .-,--------. | | V | | +- -host----hostname-+-----------------------------+ | .-,------------------------------. | | V | | '- -member--+---member_number--|--member_range-+-+-' '-all--------------------------------' >--+---------------------------------------------------------------------------+--> '- -agents--+-------------+--+--------------+--+---------------+--+-------+-' '-db=database-' +-AgentId------+ '-file=filename-' '-event-' '-app=AppHandl-' >--+--------------------------------------------------------+---> | .-all------. .-all-----. | '- -apinfo--+----------+--+---------+--+---------------+-' '-AppHandl-' '-MaxStmt-' '-file=filename-' >--+------------------------------------------------------+-----> '- -applications--+---------------+--+---------------+-' +-AppHandl------+ '-file=filename-' '-agent=AgentId-' >--+--------------------------------------------+---------------> '- -bufferpools--+------+--+---------------+-' '-bpID-' '-file=filename-' >--+-----------------------------------+------------------------> '- -catalogcache--+---------------+-' '-file=filename-' >--+-----------------------------------+------------------------> '-+- -cleaner---------------------+-' '- -dirtypages--+-------------+-' +-bp=bpID-----+ +-count=count-+ +-summary-----+ +-temponly----+ '-fixedonly---' >--+---------------------+--------------------------------------> '- -command--filename-' >--+----------------------------------+-------------------------> | .-,------------. | | V | | '-+- -database-+----databasename-+-' +- -db-------+ '- -d--------' >--+----------------------------+-------------------------------> '- -dbcfg--+---------------+-' '-file=filename-' >--+-----------------------------+--+------------+--------------> '- -dbmcfg--+---------------+-' '- -dbptnmem-' '-file=filename-' >--+-----------------------------------------------------------------------------------------+--> '- -dump--+---------------------------+--+----------------------------------------------+-' +-all--+------------------+-+ '-dumpdir=directory--+-----------------------+-' | '-apphdl=AppHandle-' | '-timeout=timeout-value-' +-apphdl=AppHandle----------+ +-eduid-apphdl=AppHandle----+ '-pid-----------------------' >--+------------------------------+-----------------------------> '- -dynamic--+---------------+-' +-anch=anchID---+ '-file=filename-' >--+------------------------------------------------------+-----> '- -edus--+------------------------------------------+-' '-interval=interval--+-------+--+--------+-' '-top=n-' '-stacks-' >--+--------------+---------------------------------------------> '- -everything-' >--+------------------------------------------------+-----------> '- -fcm--+-----+--+---------+--+---------------+-' '-hwm-' '-numApps-' '-file=filename-' >--+------------------+--+-------+--+--------+------------------> '- -file--filename-' '- -fmp-' '- -full-' >--+-------------------------------------+----------------------> '- -fixstack--existing_stack_filename-' >--+----------------------------------------------------------------+--> '-+- -fmpexechistory-+--+--------------+--+------+--+----------+-' '- -fmpe-----------' '-+--pid=pid-+-' '--n=n-' '-genquery-' '--tid=tid-' >--+-----------------------------------------------------+------> '- -fvp--+-agent_eduid-+--+------+--+---------------+-' +-LAM1--------+ '-term-' '-file=filename-' +-LAM2--------+ '-LAM3--------' >--+-------+--+------+--+---------------------------+-----------> '- -gfw-' '- -ha-' '- -hadr--+---------------+-' '-file=filename-' >--+--------+--+--------+--+---------------+--------------------> '- -help-' '- -inst-' '- -interactive-' >--+-----------------------------------------+------------------> '- -latches--+-------+--+---------------+-' '-group-' '-file=filename-' >--+------------------------------------------------------------+--> '- -load--+-----------------+--+---------------+--+--------+-' '-loadID="LOADID"-' '-file=filename-' '-stacks-' >--+------------------------------------------------------------------+--> '- -locks--+---------+--+---------------+--+-----------+--+------+-' '-TranHdl-' '-file=filename-' '-showlocks-' '-wait-' >--+---------------------------+--------------------------------> '- -logs--+---------------+-' '-file=filename-' >--+--------------------------------------------------------------------------------------+--> | .-,-----------. | | V | | '- -memblocks----+-all-----+-+--+-----+--+--------+--+------+--+--------+--+---------+-' +-dbms----+ '-top-' '-blocks-' '-sort-' '-PoolID-' '-pid=pid-' +-fcm-----+ +-fmp-----+ +-appl----+ +-db------+ '-private-' >--+-------------------------------+----------------------------> '- -mempools--+---------------+-' '-file=filename-' >--+------------------------------+-----------------------------> '- -memsets--+---------------+-' '-file=filename-' >--+---------------------------------------+--------------------> '- -osinfo--+------+--+---------------+-' '-disk-' '-file=filename-' >--+---------------------------------------------------+--------> '- -pages--+------+--+---------+--+---------------+-' '-bpID-' '-summary-' '-file=filename-' >--+--------+--+-------------------------------+----------------> +- -q----+ '- -recovery--+---------------+-' +- -quit-+ '-file=filename-' +-q------+ '-quit---' >--+----------------------------+-------------------------------> '- -reopt--+---------------+-' '-file=filename-' >--+----------------------------------------+-------------------> '- -reorgs--+--------------------------+-' '-index--+---------------+-' '-file=filename-' >--+----------------------------------+--+------------+---------> '- -repeat--+---------+--+-------+-' '- -runstats-' '-num sec-' '-count-' >--+---------------------------------------------------------------------------+--> '--- -scansharing----+----------------------------------------------------+-' '-obj=objectID--pool=poolID--+---------------------+-' +-all-----------------+ '-index=--+-indexID-+-' '-all-----' >--+--------------------------------------+---------------------> '- -serviceclasses--+----------------+-' '-serviceclassID-' >--+------------------------------+-----------------------------> '- -sort--+------------------+-' '-apphdl=AppHandle-' >--+---------------------------------------------------------------------------------+--> '- -stack--+-all--------------+--+----------------------------------------------+-' +-apphdl=AppHandle-+ '-dumpdir=directory--+-----------------------+-' '-eduid------------' '-timeout=timeout-value-' >--+-----------------------------+------------------------------> '- -static--+---------------+-' '-file=filename-' >--+-----------------------------------------------------------------------------------------------------+--> '- -statisticscache--+--------------------+--+---------------+--+-----------------------------------+-' +- -db--databasename-+ '-file=filename-' +-summary---------------------------+ '- -alldbs-----------' +-detail----------------------------+ '-find schema=schema--object=object-' >--+----------------+-------------------------------------------> '- -storagepaths-' >--+-----------------------------------------------+------------> '- -sysplex--+-------------+--+---------------+-' '-db=database-' '-file=filename-' >--+-----------------------------------------------------------------------------------+--> '- -tablespaces--+---------------+--+-------+--+---------------+--+---------------+-' '-Tablespace ID-' '-group-' '-trackmodstate-' '-file=filename-' >--+------------------------------------------------------------------------------------------------------------+--> '- -tcbstats--+-----------+--+-------+--+-----------------------------------------------+--+---------------+-' '-nocatalog-' +-all---+ '-TbspaceID=tablespace_ID--+------------------+-' '-file=filename-' '-index-' '-TableID=table_ID-' >--+------------------------+-----------------------------------> '- -temptable--+-------+-' '-reset-' >--+-------------------------------+----------------------------> '- -thresholds--+-------------+-' '-thresholdID-' >--+-----------------------------------------------------+------> '- -transactions--+--------------+--+---------------+-' +-TranHdl------+ '-file=filename-' '-app=AppHandl-' >--+--------------------------------+--+-----------+------------> '- -utilities--+---------------+-' '- -version-' '-file=filename-' >--+-----------------------------+------------------------------> '- -wlocks--+---------------+-' '-file=filename-' >--+--------------------------------------------------+---------> '- -workactionsets--+-----------------+--+-------+-' '-workactionsetID-' '-group-' >--+------------------------------------------------+-----------> '- -workclasssets--+----------------+--+-------+-' '-workclasssetID-' '-group-' >--+----------------------------------------+------------------>< '- -workloads--+------------+--+-------+-' '-workloadID-' '-group-'
常用语法解释:
-edus:引擎分派单元(engine dispatchable unit,EDU)。在AIX或Linux系统中, ps -ef
将只显示所有服务器端进程(例如:代理程序、记录器、页面清除程序和预取程序)的 db2sysc 进程(主 DB2 引擎进程)。edus可以看作是进程下的多个线程。
db2pd -edus :显示实例instance中的所有线程edus。在一个持续的故障陷阱中,这一选项参数可以显示有问题的edu处于停顿状态
interval=interval 间隔(这一参数仅在UNIX系统平台有效) 单位:秒数
在指定的间隔秒数之间,会快速生成所有edus的前后两次快照,在edus的快照列表中会包含两列(USR DELTA)和( SYS DELTA )
USR delta:表示在间隔的两个时间之间用户CPU的增量
SYS DELTA :表示间隔时间内系统CPU的增量
top=n
显示占用CPU最多的前n条edu。
例如:AIX系统中用户CPU达到99% 系统运行很慢
vmstat 1 10 显示 r部分数值很大达到100左右
结合系统数据库运行情况判定
1、db2pd -edus interval=5 top=10
2、根据eduid也就是db2pd -db idbyf -agent 中的agenteduid找到 app handl
3、运行db2pd -db idbyf -apinfo 59294(apphandl)
确定SQL