db2pd -edus命令详解

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