精华内容
下载资源
问答
  • db2top命令详解

    2009-09-10 16:01:20
    IBM alphaworks提供了一个db2top的小工具,来帮助你实时监控你的db2数据库。 更为方便的是,除了能够支持分区数据库,也能够让你监控session级别的应用。
  • db2top命令,可以详细的查看DB2用到的资源,锁的情况,正在执行的语句等,非常实用的命令工具
  • db2top参数详解

    2021-04-20 14:07:33
    A 监控HADR集群的主要数据库或辅助数据库 a 转至代理程序的应用程序详细信息,db2top 命令将提示您输入代理程序表示 B 显示关键服务器资源的主要使用者(瓶颈分析) c 改变在屏幕上的显示顺序 b 转至缓冲屏幕 C ...

    按U 查看数据库锁状态 按l 查看数据进程状态
    按D 查看造成锁的sql语句 按d 查看数据库状态
    按t 查看表空间 按T 查看表状态
    按b 查看缓冲池状态 按a 显示程序的应用程序的详情
    按B 显示关键服务器资源的主要使用者 按c 打开或关闭快照数据收集器。
    按f 冻结屏幕 按F 在主服务器上监视联合查询
    按G 打开或关闭图标 按H 转至历史记录屏幕
    按 I 打开或关闭闲置会话 按 q 退出界面

    [Help]
    d - Database l - Sessions
    a - Details for agent t - Tablespaces
    b - Bufferpools T - Tables
    D - Dynamic SQL U - Locks
    m - Memory pools s - Statements
    u - Utilities p - Partitions
    A - HADR F - Federation
    B - Bottlenecks J - Skew detection
    C - Toggle collector on/off W - Watch user/agent
    / - Set regexp g - Toggle gauge on/off
    i - Toggle idle objects on/off G - Toggle local/global snapshot
    P - Select db partition X - Toggle extended mode on/off
    k - Toggle actual/delta values z - Descending sort
    Z - Ascending sort + - Longer default sort

      • Shorter default sort I - Set new snapshot interval
        R - Reset snapshot monitor S - Run native DB2 snapshot
    • Move right < - Move left
      c - Change columns order f - Freeze display
      ! - Goto to system prompt V - Set default explain schema
      O - Display settings w - Write parms to .db2toprc
      h - Help q - Quit

    DB2 Interactive Snapshot Monitor V2.0
    Licensed Materials - Property of IBM
    Copyright IBM Corp. 2005, 2006 All Rights Reserved.

    A 监控HADR集群的主要数据库或辅助数据库
    a 转至代理程序的应用程序详细信息,db2top 命令将提示您输入代理程序表示
    B 显示关键服务器资源的主要使用者(瓶颈分析)
    c 改变在屏幕上的显示顺序
    b 转至缓冲屏幕
    C 打开或关闭快照数据收集器
    d 转至数据库屏幕
    D 转至动态sql
    h 转至帮助屏幕
    H 转至历史记录屏幕
    f 冻结屏幕
    F 在主服务器上监视联合查询
    G 打开或关闭图标
    i 打开或关闭闲置会话
    k 切换实际值与增量值
    l 转至会话屏幕
    L 允许显示来自sql屏幕的完整查询文本
    m 显示内存池
    o 显示会话设置
    p 转至分区屏幕
    P 选择要打出快照的数据库分区
    q 退出top
    R 重置快照数据
    s 转至语句屏幕
    S 运行本机BD2快照
    t 转至表空间屏幕
    T 转至表屏幕
    u 显示活动的实际程序。并且跨数据库分区将他们聚集起来
    U 转至锁定屏幕
    v 设置缺省说明模式
    w 将会话设置写至db2toprc
    x 打开或关闭扩展方式
    z|Z 按升序或降序方式进行排序
    / 将表达式输入至过滤数据(正则表达式)
    r 返回至上一个函数
    R 切换自动刷新
    g 打开或关闭图表
    在这里插入图片描述

    展开全文
  • db2top操作手册

    千次阅读 2019-07-23 17:58:47
    1.db2top命令语法... 4 2.db2top运行模式... 5 2.1 交互模式... 5 2.2 批量模式... 6 3.db2top监控模式... 8 3.1 数据库监控 (d). 8 3.2 表空间监控 (t). 9 3.3 动态SQL监控(D). 10 3.4 会话监控 (l). 12 ...

    目录

     

    1.db2top命令语法... 4

    2.db2top运行模式... 5

    2.1 交互模式... 5

    2.2 批量模式... 6

    3.db2top监控模式... 8

    3.1 数据库监控 (d). 8

    3.2 表空间监控 (t). 9

    3.3 动态SQL监控(D). 10

    3.4 会话监控 (l). 12

    3.5 缓存池监控(b). 13

    3.6 锁监控(U). 14

    3.7 表监控 (T). 17

    3.8 瓶颈监控 (B). 17

    4.其他... 20

     

     

     

     

    1.db2top命令语法

     

    可使用命令行 db2top –h 查看,这里就不做赘述了。

     

    2.db2top运行模式

    db2top一般有两种运行模式, 交互模式批量模式

    交互模式下,用户可直接输入命令后,等待系统响应。注意键盘上的方向左键“←”和方向右键“→”,可用来滚动查看对应方向上的隐藏列。而批量模式下,可无需用户交互即可执行一系列操作。

     

    2.1 交互模式

    执行如下命令:

    db2top –d sample


    图1. 将db2top运行在交互模式

     

    图1中的屏幕,最上方有状态栏:

    [\]15:38:20, refresh=2secs(0.003) AIX, part=[1/1],SHENLI:SAMPLE

     

    · [/]: 当该值滚动时,代表db2top正处在2次快照之间,否则,代表db2top正在等待DB2的响应

    · 15:38:20: 当前时间

    · refresh=2secs: 刷新时间间隔

    · refresh=!secs: 如果出现感叹号,代表处理DB2快照用时比刷新时间间隔要长。这种情况下,db2top刷新时间间隔高了50%。如果由于数据库系统繁忙导致感叹号出现的太过频繁,你可以将刷新时间间隔改高(I选项),或者只监控单独的数据库分区(选项P),又或者关掉额外的监控展示模式(选项X)。

    · 0.003: DB2处理快照的内部耗时。

    · AIX: DB2运行平台

    · Inactive: 代表数据库还未启动,否则代表数据库已启动。

    · part=[1/1]: 启动的数据库分区数量/总数据库分区数量。举个例子, part=[2,3] 代表3个数据库分区中有1个数据库分区未启动(活动2, 总数3).

    · SHENLI: 实例名

    · SAMPLE: 数据库名

     

    [d=Y,a=N,e=N,p=ALL] [qp=off]

    · d=Y/N: 代表增量或累计快照指示(命令行 -k 或 选项 k)

    · a=Y/N: 代表只显示已启动对象或显示所有对象 (命令行-a 或者选项i)

    · e=Y/N: 代表是否显示额外项

    · p=ALL: 所有数据库分区

    · p=CUR: 当前数据库分区(命令行-P且未指定数据库分区号)

    · p=3: 目标数据库分区号: 如3

    · db2top 可用来监控DPF环境。如果命令行未指定-P,则将生成全局快照。

    · qp=off/on: 查询动态指示 (DYNMGMT 数据库配置参数) db2top所属的数据库分区

     

    状态栏下方有一个用户手册,可按对应按键选择

     

    2.2 批量模式

    你可使用db2top 的批量模式来静默地监控数据库。用户可在后台记录性能信息和存储历史数据,以供后续分析。

     

    下列代码列出了如何把db2top在批量模式运行一段时间(例如,总计运行8小时,每15秒收集一次快照数据):

     

    db2top -d sample -f collect.file -C -m 480 -i 15

     

    should I create a named pipe instead of a file [N/y]? N

    注意:请确保输入 N应答系统提出的这个问题

     

    数据收集至文件后,用户可用下列命令运行db2top的回放模式,来分析收集的数据:

    db2top -d sample -f collect.file -b l -A

    选项-A 支持自动性能分析。所以上面的命令将会分析大多数的活动会话,也会占用更多的CPU资源。

     

    也可用下列命令运行db2top的回放模式,来分析指定时间的数据:

    db2top -d sample -f collect.file /HH:MM:SS

     

    例如,如下命令,用户可重启db2top至回放模式,并跳转到上午2点时的数据:

    db2top -d sample -f collect.file /02:00:00

    之后,用户可输入I来分析当时会话的行为。

     

     

    3.db2top监控模式

    3.1 数据库监控 (d)

     

    图2.监控数据库

     

    数据库监控模式下,db2top为整个数据库提供一套性能要素的监控。用户可以监控活动会话(MaxActSess),排序内存(SortMemory),以及日志空间(LogUsed)。这些监控要素可以帮助用户识别这些要素的当前使用率。如果这些要素其中一个的使用率开始升高甚至达到百分之百,用户应当研究相关的原因。

     

    计算当前时间和Start Time差值,可知数据库已经启动了多久。这个数值在配合其他的监控要素,来解决已经持续一段时间的问题时,就会非常有用。锁使用(LockUsed) 和锁升级(LockEscals)对解决锁相关问题很有帮助。如果观察到大量的锁升级,最好改大数据库的两个参数:LOCKLIST 和MAXLOCKS,或者重点关注那些可能需要大量锁的错误语句。

     

    L_Reads, P_Reads,以及A_Reads分别代表逻辑读,物理读,和异步读。结合命中率(HitRatio),这些变量可重点用来衡量大多数读操作发生在内存I/O还是磁盘I/O。由于磁盘I/O比内存慢很多,用户应尽量通过内存使用数据。当看到命中率降低时,这就是关注缓存池是否不足,或者是否有需要太多表扫描和内存磁盘交换的错误查询的最好时机。

     

    类似读操作,A_Writes代表异步写,这表示在需要缓存池空间之前,数据页是通过异步页清除器代理执行写操作的。知道了db2top的刷新用时期间的写次,用户也可以了解数据库执行了多少写请求。这可以计算每次写操作的瓶颈用时,然后可以用来分析由I/O 瓶颈导致的性能问题。另外,用户可能希望通过计算A_Writes/Writes的最大值来得知的写I/O的最佳性能。

     

    SortOvf代表排序溢出。如果用户发现这个值非常高,这时最好看下查询语句。排序溢出发生在排序堆不足时,所以SORT或者 HashJoin操作可能把数据溢出到临时空间去。有时候这值会因排序堆调大而下降,但在其他的情况下,如果被排序的数据套比存收集到的排序堆的内存大很多,则会不起作用。在那种情况下,排序溢出会成为一个主要瓶颈。如果数据量需要比缓存池临时空间能承受大,就需要物理I/O来处理 SORT或者Hash Join。因此优化查询来减少排序溢出能够显著地提升系统性能。

     

    数据库监控模式的最后四个条目显示的是平均物理读取时间(AvgPRdTime),平均直接读取时间 (AvgDRdTime),平均物理写入时间(AvgPWrTime),以及平均直接写入时间(AvgDWrTime)。这四个条目直接反应了I/O子系统的性能。如果用户在各项读操作和写操作中,观察到不寻常的大量时间消耗,此时用户应当深入分析I/O子系统。

     

     

    3.2 表空间监控 (t)

     

    图3.表空间监控

     

    表空间监控模式为每一个表空间提供详细的监控信息。列Hit Ratio%和列Async Read%对很多用户来说非常重要。在数据库级别下只监控缓存池命中率,你可能得不到足够精确的信息。在包含许多表空间的环境下,一个发生在单个表空间的错误查询语句会因平均所有表空间的命中率而被掩盖。在表空间层级上监控列Hit Ratio%和Async Read% 可以有效分析系统的工作细节。

     

    增量逻辑读取(写入)和增量物理读取(写入) (Delta l_reads(writes) 和 Delta p_reads(writes))说明了那些表空间有多 "忙碌"。 一些表空间可能没有很高的缓存池命中率,但它们也可能没有太多活动。在大多数情况下,最好将更多的调优工作放在活动更多的表空间,而不是那些空闲的表空间中。

     

    键盘上的方向左键“←”和方向右键“→”可以将列向左或向右滚动。表空间监控模式和一些其他的监控模式可能有多个且不能显示在同一屏的列。通过按方向左键“←”或方向右键“→”,用户可以滚动屏幕以展示更多列。

     

    按方向左键“←”,用户可以看到更多的读/写条目。另外,平均读/写时间(vg RdTime/Avg WrTime)可被用于理解表空间中每次读/写平均耗时。

     

    列Space Used,列Total Size,以及列% Full能够简单方便地理解每个表空间的大小和使用率。

     

    同样还有几个列能用于了解表空间的类型,例如DMS或SMS,以及CIO/DIO是否启用。

     

     

    3.3 动态SQL监控(D)

     

    图4.动态SQL监控

     

    动态SQL监控模式提供了每一个缓存的SQL语句的详细信息。用户也可以用这个监控模式给指定查询生成db2expln和db2exfmta。

     

    执行次数(Num Execution) 和平均执行时间(Avg ExecTime) 可用于了解指定查询执行了多少次以及平均运行时间是多少。平均CPU时间(Num Execution)可用于与平均执行时间(Avg ExecTime)进行比较,以了解花在CPU活动上的时间百分比,或花在等待锁或I/O上的大部分时间。平均执行时间(Avg CpuTime)

     

    读取行和写入行对于理解查询的行为很有用。例如,如果用户看到一个select查询与大量的写入关联,这可能表明该查询可能存在排序(哈希连接)溢出,需要进一步调整以避免临时空间中的数据溢出。

     

    db2top工具还计算了数据、索引和临时l_reads的命中率(Hit%),以帮助用户轻松定位出是否需要调整缓冲池大小。平均每次执行排序(AvgSort PerExec) 和排序时间(Sort Time)是两个很好的指标,可以显示执行期间完成了多少排序。

     

    db2top工具还提供了生成db2expln或db2exfmt报告的功能,而无需手动运行命令。通过在动态SQL监控模式下输入大写L,它将提示您输入SQL对应的哈希字符串。SQL哈希字符串是在表的第一列中显示的字符串,例如“00000005429283171301468277”。用户可以复制该字符串并将其粘贴到提示中,然后单击Enter,如图5所示:

     

    图5.动态SQL监控模式-查询文本

     

    然后,选择此屏幕上的e选项生成db2expln输出,或者选择x选项生成db2exfmt输出(如果explain.ddl已导入数据库)。

     

    如果解释表不存在或与当前使用schema不同,将显示一个空屏幕。如果需要,用户可以执行以下命令生成解释表:

     

    3.4 会话监控 (l)

    图6.会话监控

     

    会话监控模式提供每个应用程序会话的详细信息。第一列显示Application Handle,以下三列:Cpu%Total、IO%Total、Mem%Total表示此应用程序正在使用的资源的百分比。在大多数情况下,每个会话表示来自应用程序端的一个连接。

     

    列Application Status和一些读写行的统计信息显示在这些列之后。用户还可以在此屏幕上看到LocksHeld,Sorts(sec)和LogUsed信息。当事务日志空间不足时,列LogUsed信息可能会对用户有所帮助。通过使用这个监视元素,用户可以了解哪些应用程序在占用更多的日志空间。

     

    会话监控模式下包含的信息与用户在数据库监控模式下可以看到的信息类似,但会话监控模式下的信息适用于每个应用程序。通常情况下,最好把不同监控模式下的数据组合起来进行性能分析。例如,通过查看会话监控模式和动态SQL监控模式,可以进一步分析显示在数据库监控模式的大量读取的问题,以便把问题涉及范围缩小到特定的应用程序或SQL。

     

    3.5 缓存池监控(b)

    图7.缓存池监控

     

    在此监控模式下,db2top提供有关每个缓冲池利用率的信息。用户可以看到缓冲池的一些基本信息,比如读取、写入和大小,还可以看到更高级的矩阵,如Hit Ratio%(缓冲池命中率)和(Async Reads%)。

     

    一般来说,缓冲池命中率可用如下公式计算:

     

    对应文本:

    1 - ((pool_data_p_reads + pool_xda_p_reads +

      pool_index_p_reads + pool_temp_data_p_reads

      + pool_temp_xda_p_reads + pool_temp_index_p_reads )

      / (pool_data_l_reads + pool_xda_l_reads + pool_index_l_reads +

      pool_temp_data_l_reads + pool_temp_xda_l_reads

      + pool_temp_index_l_reads )) * 100%

     

    3.6 锁监控(U)

     

    图8.锁监控

     

    锁定问题是应用程序诊断过程中最常见的问题之一。使用db2top工具,用户可以轻松列出应用程序中的锁。

    使用db2top分析锁等待问题也更容易。下面的图9、10和11是在db2bp应用程序正在等待另一个db2bp会话的测试场景中获取的。

     

    图9.锁等待--Application status

     

    在图9中,第一列Agent Id(State)中列出了两个代理(代理24和代理9)。你可以在第三列Application Status(应用程序状态)中看到,其中一个代理(代理24)处于锁定等待状态(Lock Waiting status)。

     

    图10.锁等待--Lock status

     

    如果用户希望在锁监控模式下中看到更多信息,通过按键盘上的左箭头“←”,会显示更多的列,如图10所示。在锁状态列(Lock Status)中,除一个锁外,所有锁都处于已授权状态(Granted status):“-”状态的锁是阻塞的锁。在锁模式列(Lock Mode)中,显示了包括请求的锁模式(S)和正在保持的锁(IX)等。

     

    图11.锁等待--Table name

     

    如图11所示,在这个特别的例子里,代理24正试图请求表TAOEWANG.T1的S锁,可它已被持有TAOEWANG.T1表上IX锁的代理9锁定。

     

    在锁监控模式中db2top提供的另一个非常有用的特性是锁链分析。如果问题涉及到多个应用程序,那么想找出锁等待关系就不那么容易。因此为了使用户更方便地理解应用程序之间的锁关系,db2top工具提供了一个有用的特性:动态地绘制锁链。

     

    通过输入大写L可展示锁链,如图12所示:

     

    图12.锁等待--Lock chain

     

     

    3.7 表监控 (T)

     

    图13.表监控

     

    表监控模式显示数据库中的表信息。在当前时间内未被访问的空闲表以白色显示。正在访问(活动)的表以绿色显示。

     

    列Delta RowsRead(Written)/s表示在使用时间内读写的行除以时间间隔。这个数字显示各表在当前时间的使用频率。

     

    另外还有关于表本身的信息。列数据页(Data Pages)和索引页(Index Pages)表示表中有多少页。表类型(Table Type)和表大小(Table Size)对于理解表的属性也很有用。

     

    另一个重要的列是Rows Overflows/s,它表示在当前时间内每秒多少行数据发生了溢出。溢出的行会产生数据碎片。如果这个列数值过高,用户应该通过使用REORG重新构建表来清除碎片,以提高表的性能。

     

    3.8 瓶颈监控 (B)

     

    图14.瓶颈监控

     

    瓶颈分析是DBA不能忽视的过程。他们想知道哪个代理(应用程序)严重限制了整个DB2系统中特定组件的性能或容量,而db2top通过显示关键服务器资源的主要消费方,可解决这个问题。而且工具中会显示消耗每个类别大部分资源的代理ID。

     

    标题“Bottleneck”下的方框用于各种数据库操作的时间分析:

     

    The elapsed time used to calculate the percentage of each operation = (wait_lock_time + sort_time + bp_read_time + bp_write_time + async_read_time + async_write_time + prefetch_waite_time + direct_read_time + direct_write_time).

    {用于计算每个操作的百分比所用的时间= (等待锁定时间+排序时间+bp读取时间+bp写入时间+异步读取时间+异步写入时间+预取等待时间+直接读取时间+直接写入时间) }

     

    下列是每个操作的预估百分比:

    · wait lock ms: (wait lock time)/(elapsed time) = 80%

    · sort ms : (sort time)/(elapsed time) = 0

    · bp r/w ms: (buffer pool read and write time)/(elapsed time) = 10%

    · async r/w ms: (async read and write)/(elapsed time) = 6%

    · pref wait ms: (prefetch_waite_time)/(elapsed time) = 2%

    · dir r/w ms: (direct read and write time)/(elapsed time) = 2%

    ·

    “Bottleneck”瓶颈监控模式下的主体显示每个服务器资源中哪个代理是瓶颈。

     

    “Bottleneck”瓶颈监控模式下的第一列,Server Resource,展示监控的服务器资源类型:

    · Cpu: Which agent consumes the most CPU time.

    · SessionCpu: Which application session consumes the most CPU time.

    · IO r/w: Which agent consumes the most I/O read and write.

    · Memory: Which agent consumes the most memory.

    · Lock: Which agent is holding the most locks.

    · Sorts: Which agent has executed the biggest number of sorting.

    · Sort Times: Which agent consumes the longest sorting time.

    · Log Used: Which agent consumes the most log space in the most recent unit of work.

    · Overflows: Which agent has the most number of sort overflows.

    · RowsRead: Which agent has read the most number of rows of records.

    · RowsWritten: Which agent has written the most number of rows of records.

    · TQ r/w: Which agent has sent and received most number of rows on table queues.

    · MaxQueryCost: Which agent has the max SQL execution time estimated by the compiler.

    · XDAPages: Which agent has the most number of pages for XDA data (available in V9.1GA and after releases).

     

    例如:图14显示了代理683,即db2bp(DB2后端进程),显然是瓶颈。

    至于内存使用瓶颈分析,您可以在图14中看到以下内容:


    这表明在所有的代理中,代理17,即另一个db2bp(DB2后端进程),消耗了最多的内存:17.11%,共计832.0K。

     

    4.其他

    db2top工具的设计理念和DB2 Health Monitor工具大不相同。DB2 Health Monitor设置了一组阈值,并持续的监控这些矩阵,一但达到阈值,DB2 Health Monitor则会触发告警机制。db2top是一款可以周期地获取快照基础工具,它让用户无需分析快照文件而直观地得出结果。

     

    db2top能让用户能够在文本构成的图形界面中监控DB2系统。它可用于确定DB2在一段时间的运行中内是否存在问题,并缩小问题的根因范围。用户会发现在监控实时系统和调试日常工作中的问题方面,这是一个很实用的工具。

    展开全文
  • db2常用命令

    2012-03-07 09:31:00
    db2常用命令 对于初学者 最好的帮助
  • DB2 监控工具 db2top 命令 介绍

    万次阅读 2012-12-27 15:20:51
    db2top 监视实用程序快速高效地监视复杂的 DB2® 环境。它结合来自所有数据库分区的 DB2 快照信息,使用基于文本的用户界面提供正在运行的 DB2 系统的动态实时视图。...db2top 命令将提示您输入代理


    db2top 监视实用程序快速高效地监视复杂的 DB2® 环境。它结合来自所有数据库分区的 DB2 快照信息,使用基于文本的用户界面提供正在运行的 DB2 系统的动态实时视图。

    以交互方式运行 db2top 时,您可以发出下列命令:

    A
    监视 HADR 集群中的主数据库或辅助数据库。
    a
    转至代理程序的应用程序详细信息(或在声明屏幕上限制代理程序)。 db2top 命令将提示您输入代理程序标识。
    B
    显示关键服务器资源的主要使用者(瓶颈分析)。
    c
    此选项允许您更改屏幕上显示的列的顺序。语法采用下列格式:1,2,3,...,其中 1,2,3 分别对应于所显示的第 1 列、第 2 列和第 3 列。这些是指定排序条件时要使用的列数。
    当使用  交换关键字时,将显示屏幕,指定屏幕上显示的列的顺序。屏幕的左侧部分显示缺省顺序和列数;屏幕右侧部分显示当前排序。要更改列的顺序,在屏幕底部文本字段中输入新的列顺序。接着,如左侧显示的那样,输入相对的列位置,用逗号对其分隔。不需要指定所有列。对于后续的  db2top 监视会话,可以通过选择 w 将此列排序保存在  $DB2TOPRC 中。您可以进行排序,并选择采用哪种顺序在屏幕上显示列。 .db2toprc 文件中列排序的有效关键字是:
    • sessions=
    • tables=
    • tablespaces=
    • bufferpools=
    • dynsql=
    • statements=
    • locks=
    • utilities=
    • federation=
    b
    转至缓冲池屏幕。
    C
    打开或关闭快照数据收集器。
    d
    转至数据库屏幕。
    D
    转至动态 SQL 屏幕。
    f
    冻结屏幕。
    F
    在主服务器上监视联合查询。
    G
    打开或关闭图表。
    h
    转至帮助屏幕
    H
    转至历史记录屏幕
    i
    打开或关闭闲置会话。
    k
    切换实际值与增量值。
    l
    转至会话屏幕。
    L
    允许显示来自 SQL 屏幕的完整查询文本。然后,可以使用 e 或 X 选项来运行常规 DB2 说明。
    m
    显示内存池。
    o
    显示会话设置。
    p
    转至分区屏幕。
    P
    选择要发出快照的数据库分区。
    q
    退出  db2top
    R
    重置快照数据。
    s
    转至语句屏幕。
    S
    运行本机 DB2 快照。
    t
    转至表空间屏幕。
    T
    转至表屏幕
    u
    显示活动的实用程序,并且跨数据库分区将它们聚集起来。
    U
    转至锁定屏幕。
    V
    设置缺省说明模式。
    w
    将会话设置写至 . db2toprc。
    W
    agent_id、os_user、db_user、应用程序或网络名的观看方式。会话快照(选项 l)返回的语句将写至 agent.sql、 os_user-agent.sql、db_user-agent.sql、application- agent.sql 或 netname-agent.sql。 当从动态 SQL 屏幕(选项 D)发出时,语句将采用与 db2advis 兼容的格式写至 db2adv.sql。
    X
    打开或关闭扩展方式。
    z|Z
    按升序或降序方式进行排序。
    /
    将表达式输入至过滤器数据。表达式必须符合正则表达式。您可以采用不同方法过滤每个函数(屏幕)。可对整行应用 regexp 检查。
    <|>
    移至屏幕的左侧或右侧。

    下列切换只适用于应用程序屏幕:

    r
    返回至上一函数。
    R
    切换自动刷新。
    g
    打开或关闭图表。
    X
    打开或关闭扩展方式。
    d
    显示代理程序。
    要以交互方式启动  db2top,可发出下列命令:
    db2top -d <database name>
    当输入
    db2top -d sample
    时,将显示下列输出:
    [\]11:57:10,refresh=2secs(0.000) Inactive,part=[1/1],<instanceName>:sample
    [d=Y,a=N,e=N,p=ALL] [qp=off]
    
    [/]:当旋转时,它表示 db2top 在两个快照之间等待,否则,它表示 db2top 在等待 DB2 的答复
    11:57:10:当前时间
    refresh=2secs:时间间隔
    refresh=!secs:感叹号表示 DB2 处理快照所需的时间超过时间间隔。在此情况下,db2top 将按
    50% 增加时间间隔。如果由于系统太忙而频繁发生此问题,那么您可以增加快照时间间隔
    (选项 I)、监视单一数据库分区(选项 P)或关闭扩展显示方式(选项 x)
    0.000:DB2 内部处理快照所花费的时间
    d=Y/N:增量或累积快照指示器(命令选项 -k 或选项 k)。
    a=Y/N:仅限于活动对象指示器的或所有对象指示器(-a 命令选项集或 i)
    e=Y/N:扩展显示指示器
    p=ALL:所有数据库分区
    p=CUR:当前数据库分区(-P 命令选项,未指定分区数)
    p=3:目标数据库分区数:例如,3
    
    Inactive:如果 DB2 没有在运行,那么会显示不活动,否则会显示运行 DB2 的平台
    part=[1/1]:活动数据库分区数与总计数据库分区数。例如,part=[2,3] 表示总共有 3 个
    数据库分区,其中有一个数据库分区停机(2 个数据库分区处于活动状态,共有 3 个)
    <instanceName>:实例名
    sample:数据库名称
    qp=off/on:已连接 db2top 的数据库分区的 Query Patroller 指示器(DYNMGMT 数据库
    配置参数)

    下列示例演示在分区数据库环境中以交互方式运行 db2top 监视实用程序:

    db2top -d TEST -n mynode -u user -p passwd -V skm4 -B -i 1
    命令参数如下所示:
    -d TEST     # 数据库名称
    -n mynode   #  节点名
    -u user     #  用户标识
    -p passwd   #  密码
    -V skm4     #  模式名称
    -B          #  启用粗体
    -i 1        #  屏幕更新时间间隔:1 秒
    展开全文
  • db2top详解

    千次阅读 2015-08-20 10:19:54
     ...There are several methods to collect information and diagnose DB2 system performance issues. The snapshot monitor is one of the most commonly used tools to collect information in
    

    Introduction

    There are several methods to collect information and diagnose DB2 system performance issues. The snapshot monitor is one of the most commonly used tools to collect information in order to narrow down a problem. However, most entries in snapshots are cumulative values and show the condition of the system at a point in time. Manual work is needed to get delta value for each entry from one snapshot to the next.

    The db2top tool comes with DB2, and can be used to calculate the delta values for those snapshot entries in real time. This tool provides a GUI under a command line mode, so that users can get a better understanding while reading each entry. This tool also integrates multiple types of DB2 snapshots, categorizes them, and presents them in different screens for the GUI environment.

    This article introduces some commonly used screens in db2top utility in daily performance monitoring and troubleshooting work. You'll have a chance to examine several examples that show how to use this tool to narrow down problems in real cases. After reading this article, you will be able to:

    • Understand how the db2top utility works
    • Interpret the most useful entries in several most commonly used screens
    • Monitor system performance, know whether there is something abnormal in daily operations, and be able to solve the problem by using db2top.

    Read on, or link directly to the section that interests you:

    Most entries or elements of interest are highlighted in red on figures or in bold text.

    All the screenshots are captured from running db2top in interactive mode.

    In this article, database "sample" will be used in each example and screenshot.

    db2top command syntax

    This article does not discuss the db2top command syntax in detail. Detailed command syntax and the user manual can be found in the DB2 Information Center.

     Usage: db2top [-d dbname] [-n nodename] [-u username] [-p password] [-V schema] [-i interval] [-P [part]] [-a] [-B] [-R] [-k] [-x] [-f file [+time] [/HH:MM:SS]] [-b options [-s [sample]] [-D separator] [-X] -o outfile] [-C] [-m duration] db2top -h -d : Database name (default DB2DBDFT) -n : Node name -u : User name -p : User password -V : Default explain schema -i : Interval in seconds between snapshots -b : background mode option: d=database, l=sessions, t=tablespaces, b=bufferpools, T=tables, D=Dynamic SQL, s=Statements, U=Locks, u=Utilities, F=Federation, m=Memory -X=XML Output, -L=Write queries to ALL.sql, -A=Performance analysis -o : output file for background mode
                                                                        w in db2top to generate the resource configuration file.

    Back to top

    How to start db2top

    db2top can be run in two modes, interactive mode or batch mode. In interactive mode, the user enters command directly at the terminal text user interface and waits for the system to respond. Note that the left and right arrow keys on the keyboard can be used to scroll columns to left or right, so that you can see the hidden columns on many screens in interactive mode. On the other hand, in batch mode a series of jobs are executed without user interaction.

    Run db2top in interactive mode

    Enter the following command from a command line to start db2top in interactive mode:

     db2top -d sample


    Figure 1. To run db2top in interactive mode
    To run in interactive mode  

    In Figure 1, field values are returned at the top of the screen:

    [\]15:38:20, refresh=2secs(0.003) AIX, part=[1/1],SHENLI:SAMPLE

    • [/]: When rotating, it means that db2top is waiting between two snapshots, otherwise, it means db2top is waiting for an answer from DB2.
    • 15:38:20: Current time
    • refresh=2secs: Time interval
    • refresh=!secs: The exclamation mark means the time to process the snapshot by DB2 is longer than the refresh interval. In this case, db2top increases the interval by 50 percent. If this occurs too often because the system is too busy, you can either increase the snapshot interval (option I), monitor a single database partition (option P), or turn off extended display mode (option x).
    • 0.003: Time spent inside DB2 to process the snapshot
    • AIX: Platform on which DB2 is running
    • Inactive: Means that the database has not been activated, otherwise it indicates that the database is activated.
    • part=[1/1]: Active database partition number versus total database partition number. For example, part=[2,3] means one database partition out of three is down (2 active, 3 total).
    • SHENLI: Instance name
    • SAMPLE: Database name

    [d=Y,a=N,e=N,p=ALL] [qp=off]

    • d=Y/N: Delta or cumulative snapshot indicator (command option -k or option k)
    • a=Y/N: Active only or all objects indicator (-a command option set or i)
    • e=Y/N: Extended display indicator
    • p=ALL: All database partitions
    • p=CUR: Current database partition (-P command option with no partition number specified)
    • p=3: Target database partition number: say 3
    • db2top can be used to monitor a DPF environment. If the -P command option is not specified, a global snapshot should be captured.
    • qp=off/on: Query patroller indicator (DYNMGMT database configuration parameter) for the database partition on which db2top is attached

    Below the status field, a user manual is displayed and can be selected by pressing keys on the keyboard.

    Run db2top in batch mode

    You can use db2top in batch mode to monitor a running database unattended. Users can record performance information using db2top in the background and the historical data is stored for further analysis.

    The following code listing shows how you would run db2top in collection mode for a long period (for example, eight hours in total, and a 15 seconds interval between each snapshot):

     db2top -d sample -f collect.file -C -m 480 -i 15 [11:36:02] Starting DB2 snapshot data collector, collection every 15 second(s), max duration 480 minute(s), max file growth/hour 100.0M, hit [CTRL+C] to cancel... [11:36:02] Writing to 'collect.file', should I create a named pipe instead of a file [N/y]? N

    Make sure N is input to answer the question.

    After the data has been collected into the file, users can use the following commands to run db2top in replay mode, in order to analyze the data gathered during the period of data collection:

     db2top -d sample -f collect.file -b l -A

    Option -A enables automatic performance analysis. So, the above command will analyze the most active sessions, which takes up the most CPU usage.

    The following command runs db2top in replay mode, jumping to the time of interest to analyze.

     db2top -d sample -f collect.file /HH:MM:SS

    For example, the user restarts db2top in replay mode and it jumps to 2am exactly:

     db2top -d sample -f collect.file /02:00:00

    then, the user enters l to analyze what the session was doing.

    Back to top

    What can be monitored by db2top?

    Database (d)


    Figure 2. Database screen
    Database screen  

    On the database screen, db2top provides a set of performance monitoring elements for the entire database.

    Users can monitor active session (MaxActSess), sort memory (SortMemory), and log space (LogUsed). These monitoring elements can help users identify what is the current percentage of usage for those elements. If one of those elements starts reaching high or even 100 percent, users should start to investigate what happened.

    The elapsed time between database Start Time and the current time can be used to understand how long the database has being activated. This value can be very useful when combined with other monitoring elements to investigate issues that have been floating around over a period of time.

    Lock usage (LockUsed) and escalation (LockEscals) can be very helpful to narrow down locking issues. If a huge number of lock escalations is observed, it is a good idea to increase the LOCKLIST and MAXLOCKS database parameters, or start looking at bad queries that may request a huge amount of locks.

    L_Reads, P_Reads, and A_Reads represent Logical Reads, Physical Reads, and Asynchronous Reads. Combined with the hit ratio (HitRatio) value, these variables are very important to evaluate whether most of the reads happened in memory or in disk I/O. Since disk I/O is much slower than in-memory-access, users may prefer to access data in memory as much as possible. When users see the HitRatio dropping low, it is then a good time to start looking at whether the bufferpools are not large enough, or if there is any bad query requesting too much table scans and flushing out other pages from memory to disk.

    Similarly with reads, A_Writes represents Asynchronous Writes, which indicates the data pages are written by an asynchronous page cleaner agent before the buffer pool space is required. By knowing the number of writes happened during the elapsed time of the refresh rate of db2top, users also know how many write requests have been made in the database. This could be useful to calculate the average time cost per write, which may be helpful in analyzing some performance issues caused by an I/O bottleneck. Users may expect a maximum ratio of A_Writes/Writes for best writing I/O performance.

    SortOvf represents Sort Overflow. If users find that this number goes very high, it might be good to look around queries. Sort Overflow happens when Sortheap is not large enough, so that a SORT or HashJoin operation may overflow the data into temp space. Sometime the value can be dropped by increasing the size of Sortheap, but in other cases, it may not help much if the data set being sorted is much larger than the memory that can be allocated to Sortheap. The sort overflow could be a major bottleneck in a case like that. It may require physical I/O to proceed SORT or Hash Join if the amount of data requested is larger than what the bufferpool can hold in temp space. Therefore, optimizing queries to reduce the number of sort overflows could significantly help the performance of the system.

    The last four entries in the Database screen show the Average Physical Read time (AvgPRdTime), Average Direct Read Time (AvgDRdTime), Average Physical Write time (AvgPWrTime), and Average Direct Write time (AvgDWrTime). These four entries directly reflect the performance of the I/O subsystem. If users observed an unexpected large amount of time spent on each Read or Write operation, further investigation should be made into the I/O subsystem.

    Tablespace (t)


    Figure 3. Tablespace screen
    Tablespace screen  

    The tablespace screen provides detailed information for each tablespace. The Hit Ratio% and Async Read% columns can be very important to many users. You may not get precise enough information by only monitoring the bufferpool hit ratio at the database level. In an environment that contains many tablespaces, a bad query occurring in one tablespace could be obscured by averaging the hit ratio over all tablespaces. Monitoring Hit Ratio% and Async Read% on each tablespace level can be useful to analyze how a system works in detail.

    Delta logical reads(writes) and Delta physical reads(writes) (Delta l_reads(writes) and Delta p_reads(writes)) illustrate how "busy" those tablespaces are. Some tablespaces may not have a very high bufferpool hit ratio but they may also not have much activity. It is good to put more tuning effort into the tablespaces that have more activity than those idle ones in most cases.

    The left and right arrow keys on the keyboard can be used to scroll columns to the left or right. The Tablespace screen and some other screens may have multiple columns that cannot be displayed within a single screen. By pressing the left or right arrow keys, users can scroll the screen to display more columns.

    By pressing the left arrow key, users can see more read/write entries. Also the average read/write time (vg RdTime / Avg WrTime) can be used to understand what is the average time cost per read/write in the tablespace.

    The Space Used, Total Size, and % Full are convenient entries that can be used to easily understand the size of each tablespace and their utilization.

    There are also several more columns that can be used to understand the types of tablespaces, for example DMS or SMS, and whether CIO/DIO are enabled or not.

    Dynamic SQL (D)


    Figure 4. Dynamic SQL screen
    Dynamic SQL screen  

    The Dynamic SQL screen provides detailed information for each cached SQL statement. Users can also use this screen to generate db2expln and db2exfmt output for a specific query.

    Number of Execution (Num Execution) and Average Execute Time (Avg ExecTime) can be used to understand how many times the specified query has been executed and what the average running time is. Average CPU Time (Avg CpuTime) can be used to compare with the Average Execute Time (Avg ExecTime) to understand what percentage of time is being spent on CPU activities, or most of the time being spent on waiting for locks or I/O.

    Rows read and Rows written are useful to understand the behavior of a query. For example, if users seeing a SELECT query associating with a huge number of writings, that may indicate the query may have sort (hash join) overflow and need to be further tuned to avoid data overflow in temp space.

    The hit ratio (Hit%) for Data, Index, and Temp l_reads are also calculated in db2top utility to help users easily address whether bufferpool size needs to be tuned. Average Sort Per Execution (AvgSort PerExec) and Sort Time are two good indicators to show how many sorts have been done during the execution.

    db2top utility also provides functionality to generate a db2expln or db2exfmt report without manually running the commands. By entering a capital L on the Dynamic SQL screen, it prompts you to enter a SQL hash string. The SQL hash string is the string showing in the first column of the table, for example "00000005429283171301468277." Users can copy the string and paste it into the prompt and click Enter, as shown in Figure 5:


    Figure 5. Dynamic SQL screen -- Query text
    Dynamic SQL screen -- Query text  

    Then, choosing the e option on this screen generates db2expln output, or choosing the x option generates db2exfmt output if the EXPLAIN.DDL has already been imported to the database.

    An empty screen is shown if explain tables do not exist or are under different schema than the one currently being used. Users could execute the following command to generate explain tables if necessary.

     db2 connect to [dbname] db2 set current schema [Schema name] db2 -tvf [instance home directory]/sqllib/misc/EXPLAIN.DDL db2 terminate

    Session (l)


    Figure 6. Session screen
    Session screen  

    The Session screen provides detailed information for each application session. The first column shows the Application Handle, and the following three columns: CPU% Total, IO% Total, Mem% Total represent the percentage of the resource this application is consuming. In most cases, each session represents one connection from the application side.

    Application Status, and some statistics of rows read and write are displayed after these columns. Users can also see LocksHeld, Sorts(sec), and LogUsed information on this screen. LogUsed information could be helpful to users when the transaction log is running out of space. By using this monitor element, users are able to get some ideas about which applications are consuming most of the log space.

    The Session screen contains the information similar to what users can see on the Database screen. However, the information on the Session screen is for each application. Usually it is good to combine the data from different screens to do performance analysis. For example, a high number of read problems showing on the Database screen can be further investigated by looking on the Session screen and Dynamic SQL screen in order to narrow it down to a particular application or SQL.

    Bufferpool (b)


    Figure 7. Bufferpool screen
    Bufferpool screen  

    On this screen, db2top provides information about utilization for each bufferpool. Users can see some basic information for bufferpools, such as reads, writes, and size, and can also see more advanced matrices, such as bufferpool Hit Ratio% and Async Reads%.

    Generally speaking, bufferpool the hit ratio can be defined like the following matrices:

     1 - ((pool_data_p_reads + pool_xda_p_reads + pool_index_p_reads + pool_temp_data_p_reads + pool_temp_xda_p_reads + pool_temp_index_p_reads ) / (pool_data_l_reads + pool_xda_l_reads + pool_index_l_reads + pool_temp_data_l_reads + pool_temp_xda_l_reads + pool_temp_index_l_reads )) * 100%

    Lock (U)


    Figure 8. Lock screen
    Lock screen  

    A locking issue is one of the most commonly seen issue during application diagnosis. With db2top utility, users can easily list the locks held by applications.

    It is also easier to analyze lock waiting problems using db2top. The following Figures 9, 10, and 11 were captured in a testing scenario where a db2bp application is waiting for another db2bp session.


    Figure 9. Lock waiting -- Application status
    Application status  

    In Figure 9, two agents(agent 24 and agent 9) are listed in the first column: Agent Id(State). You can see that in the third column, Application Status, one of the agents (agent 24) is stuck in Lock Waiting status.


    Figure 10. Lock waiting -- Lock status
    Lock status  

    If users want to see more information in the Lock, by pressing left arrow on the keyboard, more columns are displayed, as shown in Figure 10. From the Lock Status column, all locks are in Granted status except one: the lock with "-" status is the lock being blocked. And in the Lock Mode column, both the requested lock mode (S) and the lock that is being held (IX) are displayed.


    Figure 11. Lock waiting -- Table name
    Table name  

    In this particular example, as seen in Figure 11, agent 24 is trying to request the S lock on table TAOEWANG.T1 and it is being locked by agent 9, which is holding the IX lock on the object.

    Another very useful feature that db2top can provide in this screen is lock chain analysis. It is not always easy to figure out the lock waiting relationship if multiple applications are involved in the problem. The db2top utility provides a useful feature to dynamically draw the lock chain so that it is much easier for users to understand the locking relationship between applications.

    By entering a capital L, the lock chain is displayed. An example output could look similar to Figure 12:


    Figure 12. Lock waiting -- Lock chain
    Lock chain  

    Table (T)


    Figure 13. Table screen
    Table screen  

    The Table screen shows the table information in the database. The idle table that is not being accessed during the elapsed time is shown in a white color. The tables that are being accessed (active) are shown in a green color.

    The Delta RowsRead(Written)/s represent the rows being read and written during the elapsed time divided by the time interval. This number shows how often a particular table is used during the period.

    There is also information about the table itself. The columns Data Pages and Index Pages represent how many pages are in the table. Table Type and Table Size are also useful to understand the properties of the table.

    Another important column is Rows Overflows/s, which indicates how many row overflows happened every second during the elapsed time. The overflown rows indicate that data fragmentation has occurred. If this number is high, users should improve table performance by reorganizing the table using the REORG utility, which cleans up this fragmentation.

    Bottlenecks (B)


    Figure 14. Bottlenecks
    Bottlenecks  

    Bottleneck analysis is something that a DBA cannot ignore. They want to know which agent (application) severely limited the performance or capacity of a specific component in the entire DB2 system. db2top answers this call by displaying the main consumer of critical server resources. The agent ID consuming most resources for each category is shown on the screen.

    The square box right under the title "Bottleneck" is for the timing analysis of various database operations:

    The elapsed time used to calculate the percentage of each operation = (wait_lock_time + sort_time + bp_read_time + bp_write_time + async_read_time + async_write_time + prefetch_waite_time + direct_read_time + direct_write_time).

    The following is the estimated percentage for each operation:

    • wait lock ms: (wait lock time)/(elapsed time) = 80%
    • sort ms : (sort time)/(elapsed time) = 0
    • bp r/w ms: (buffer pool read and write time)/(elapsed time) = 10%
    • async r/w ms: (async read and write)/(elapsed time) = 6%
    • pref wait ms: (prefetch_waite_time)/(elapsed time) = 2%
    • dir r/w ms: (direct read and write time)/(elapsed time) = 2%

    The main body of the "Bottleneck" screen shows which agent is the bottleneck in each server resource.

    The first column, Server Resource, in the screen "Bottlenecks" shows what kind of server resource is monitored:

    • Cpu: Which agent consumes the most CPU time.
    • SessionCpu: Which application session consumes the most CPU time.
    • IO r/w: Which agent consumes the most I/O read and write.
    • Memory: Which agent consumes the most memory.
    • Lock: Which agent is holding the most locks.
    • Sorts: Which agent has executed the biggest number of sorting.
    • Sort Times: Which agent consumes the longest sorting time.
    • Log Used: Which agent consumes the most log space in the most recent unit of work.
    • Overflows: Which agent has the most number of sort overflows.
    • RowsRead: Which agent has read the most number of rows of records.
    • RowsWritten: Which agent has written the most number of rows of records.
    • TQ r/w: Which agent has sent and received most number of rows on table queues.
    • MaxQueryCost: Which agent has the max SQL execution time estimated by the compiler.
    • XDAPages: Which agent has the most number of pages for XDA data (available in V9.1GA and after releases).

    For example: Figure 14 shows that agent 683, which is db2bp (DB2 back end process), is apparently the bottleneck.

    As for memory usage bottleneck analysis, you can see the following in Figure 14:

     => Memory 7 17.11% 832.0K db2bp

     

    This says that among all the agents, agent 7, which is another db2bp (DB2 back end process), consumes the most memory: 17.11 percent or 832.0K.

    Back to top

    Case analysis

    Now that you've looked at the meaning of useful entries on some screens, here are two sample cases to illustrate how to use db2top in a working environment to quickly narrow down the root cause of problems in a system.

    The first example is about lock waiting. In this scenario, a heavy workload is running in the background, and a simulation program is trying to delete rows in a table, causing other sessions to be stuck in lock waiting status.

    The second case illustrates how to use db2top in replay mode to capture performance information over a period of time, so that a DBA is able to review the information afterward.

    Case 1: Lock waiting analysis in interactive mode

    By looking at the Bottleneck screen in db2top, you observed huge lock waiting, as showing in Figure 16:


    Figure 15. Case 1 -- Lock waiting
    Case 1 -- Lock waiting  

    By looking at the box shown at the top of the screen, it is clear that the entry "wait lock ms" took the most time, compared to the other operations. This screenshot tells you that some application(s) are stuck in lock waiting mode and waiting for locks to be released.

    Usually, it is useful to find out which application is holding most of the locks in this scenario. From Figure 16, application ID (appid) 7 is shown under the Top Agent column in the Locks row, and the "Resource Usage" column is showing "99.84%" of locks in the entire database are held by this application.

    Now, it is useful to look into this application to understand what exactly it was doing (by entering a), or it is also be helpful to look on the Session screen to see which application is waiting for locks (by entering l).

    Entering a on the Bottleneck screen prompts users to input the appid. In this case, "7" is input and it leads to the screen shown in Figure 16:


    Figure 16. Case 1 -- Lock holding application
    Case 1 -- Lock holding application  

    Figure 17 shows the query that was run by appid 7. In this case, the query is "DELETE FROM T1 WHERE EMPNO='000210'."

    It is also necessary to confirm whether this query is the one blocking other applications. Sometime it is possible that a lock waiting status occurs by waiting for table locks instead of row locks, which is held by an application with very few locks.

    Enter r to go back to the Bottleneck screen, and enter U to go to the Locks screen, as shown in Figure 17.


    Figure 17. Case 1 -- Locks
    Case 1 -- Locks  

    In Figure 17, appid 7 shows the "UOW Waiting" status and appid 11 is in the Lock Waiting status. By pressing the left-arrow key, the screen is scrolled to Figure 18:


    Figure 18. Case 1 Lock waiting
    Case 1 Lock waiting  

    In Figure 18, appid 7 is holding more than 5000 locks. Since the application was deleting rows from the table, there are 5119 X row locks being held by this application.

    By looking into appid 11, in the Locked By column, it shows that the locks that appid 11 is requesting are held by appid 7. In the second column, Lock Mode, "NS [X]" means that the application is holding an NS lock on one row and trying to convert into an X lock, and the Lock Status column shows "-",which means that the lock is not granted. Therefore, the Locked By column shows that the appid 7 is the one holding the lock and blocking appid 11 from getting it.

    Now it is much more clear what happened to the system. Users may want to know what appid 11 is doing in order to decide whether to let appid 7 continue holding the lock or force it.

    By entering a again, and then entering 11, db2top shows the query that was executed by appid 11, as shown in Figure 19.


    Figure 19. Case 1 -- Lock waiting application
    Case 1 -- Lock waiting application  

    In Figure 20, appid 11 seems to be doing a full query to the table (SELECT * FROM T1). The advice is to remove the locks by killing appid 7, which is running query DELETE FROM T1 WHERE EMPNO='000210'. Therefore, users can switch back to appid 7, enter r to get back to previous screen, enter a and 7 at the prompt, and enter f to force the application.

    Case 2: Performance analysis in replay mode

    Users can use db2top in replay mode to capture snapshot information over a period of time with the -C option:

     db2top -d sample -C -i 15 -m 240

    The above command captures a snapshot every 15 seconds for 240 minutes. The output file is saved with the default name of db2snap-[dbname]-[platform][bit].bin in the current directory.

    Users can use db2top to analyze the output data, or even export the data into delimit format where the columns are separated with ";" character.

    In this example, a user program was executed during a batch job running, which caused performance degradation. The data captured by db2top is used to narrow down which program caused the problem.

    After data being collected, the following commands can be used to dump data into delimit format:

     db2top -d [dbname] -f [filename] -b [screen sub options]

    For example, the following script can dump all screens into different files that can be used to analyze data, or even export data into a table or Microsoft Excel:

     db2top -d sample -f db2snap-sample-AIX64.bin -b d > dbout db2top -d sample -f db2snap-sample-AIX64.bin -b l > sessionout db2top -d sample -f db2snap-sample-AIX64.bin -b t > tbspaceout db2top -d sample -f db2snap-sample-AIX64.bin -b b > bpout db2top -d sample -f db2snap-sample-AIX64.bin -b T > tbout db2top -d sample -f db2snap-sample-AIX64.bin -b D > sqlout db2top -d sample -f db2snap-sample-AIX64.bin -b s > stmtout db2top -d sample -f db2snap-sample-AIX64.bin -b U > lockout db2top -d sample -f db2snap-sample-AIX64.bin -b u > utilout db2top -d sample -f db2snap-sample-AIX64.bin -b F > fedout db2top -d sample -f db2snap-sample-AIX64.bin -b m > memout

    There are several ways to narrow down the problem from these data. db2top provides a useful option -A for automatic performance analysis, as shown in Figure 20.

     db2top -d sample -f db2snap-sample-AIX64.bin -b l -A


    Figure 20. Case 2 -- Auto analysis
    Case 2 -- Auto analysis  

    Figure 20 is from the -b l option, which is for session analysis.

    The first section shows the top 20 applications consuming most of the CPU. In this case, appid 716 totally consumed almost 100 percent of the CPU from 18:58:59 to 19:14:46.

    The second section in the report (Figure 20) shows the top five applications consuming most of the CPU with about a five minute interval.

    It can be seen that between 18:52:59 and 18:58:14, there is no applications consuming significantly high CPU. However, between the time 18:58:14 and 19:13:31, appid 716 stayed on top of the list consuming 100 percent of the CPU. This could indicate that appid 716 was doing something odd and needed more analysis.

    More detailed information can be seen by piping the delimited output into a database or Microsoft Excel.

    Figure 21 was generated in Microsoft Excel from the file dbout, which was for the Database screen:


    Figure 21. Case 2 -- I/O spike
    Case 2 -- I/O spike  

    In Figure 21, there are two lines showing a spike in the graph. The red line represents physical reads and the blue line represents async writes.

    Therefore, you can conclude that the database was getting very busy during the time when CPU usage was high due to appid 716, which says that it is very possible that appid 716 caused high CPU and I/O usage.

    Next, it will be useful to understand exactly what appid 716 was doing when problem occured. db2top replay mode is helpful in this situation. From Figure 20, pick a time when the CPU was busy due to appid 716 (in this example 19:03:30 was chosen) then run the following command:

     db2top -d sample -f db2snap-sample-AIX64.bin /19:03:30

    By switching to Sessions screen (using l), Figure 22 shows the following information:


    Figure 22. Case 2 -- Session
    Case 2 -- Session  

    In Figure 22, it is clear that appid 716 was consuming a high amount of CPU and I/O.

    Then, entering t to go to the Tablespaces screen shown in Figure 23, shows that the temp space (TEMPSPACE1) usage was high.


    Figure 23. Case 2 -- Tablespace
    Case 2 -- Tablespace  

    Next, pressing T to go to the Table screen, as shown in Figure 24, the temp table ([716][SHENLI ].TEMP [00001_00002]) on top of the list has a pretty high I/O, and from the name of the table, it can be seen that the temp table was used by appid 716.


    Figure 24. Case 2 -- Table
    Case 2 -- Table  

    It is also helpful to understand what appid 716 was doing. By entering a and then entering 716, as shown in Figure 25, db2top displays the query that was executed by this application: SELECT * FROM T1 ORDER BY EMPNO


    Figure 25. Case 2 -- Statement
    Case 2 -- Statement  

    For now, the question is: why the statement caused significantly high CPU and I/O?

    By entering x on the above screen, it generates db2exfmt output, as shown in Figure 26.


    Figure 26. Case 2 -- db2exfmt
    Case 2 -- db2exfmt  

    From the explain output (Figures 26 and 27), TBSCAN was used against table T1, and the SORT operation happened on column EMPNO.


    Figure 27. Case 2 -- db2exfmt1
    Case 2 -- db2exfmt1  

    In Figure 27 (part of the explain output ), note that the NUMROWS entry shows "1412163," which indicates the SORT operation will sort the entire 1412163 rows in order to get the result. The SPILLED entry shows 154056, which represents a lot of page spilling for the sort operation. Going back to top of the db2exfmt output, Sort Heap shows "16" only, which indicates that the db2agent was trying to sort the entire 1412163 rows in a 16 page sort heap, which is apparently unable to hold all of the data. Therefore, sort spilling happened and temp space was over used. That means, the SORT operation caused high CPU and spilling caused high I/O usage in the temp space.

    Finally, users may ask how to solve this problem. Users can use the db2advis utility to get advice for this query. A typical output of the db2advis query can similar to the following format:

    Command:

     db2advis -d sample -s "SELECT * FROM T1 ORDER BY EMPNO" -m IMCP

    Output:

     -- -- -- LIST OF RECOMMENDED INDEXES -- =========================== -- index[1], 0.095MB CREATE INDEX "SHENLI "."IDX810261919380000" ON "SHENLI "."T1" ("EMPNO" ASC, "COMM" ASC, "BONUS" ASC, "SALARY" ASC, "BIRTHDATE" ASC, "SEX" ASC, "EDLEVEL" ASC, "JOB" ASC, "HIREDATE" ASC, "PHONENO" ASC, "WORKDEPT" ASC, "LASTNAME" ASC, "MIDINIT" ASC, "FIRSTNME" ASC) ALLOW REVERSE SCANS ; COMMIT WORK ; RUNSTATS ON TABLE "SHENLI "."T1" FOR INDEX "SHENLI "."IDX810261919380000" ; COMMIT WORK ;

    The advice is to create an index on table T1 as the query shown in the output.

    Back to top

    Conclusion

    The concept behind db2top is very different from DB2 Health Monitor. DB2 Health Monitor sets up a group of thresholds and keeps monitoring those matrices. Once any of the thresholds is reached, it will trigger the alarm. db2top is basically a tool to periodically capture snapshots and allow users to read the result visually instead of parsing snapshot files.

    The db2top utility is a quite useful utility that allows users to monitor a DB2 system in a text graphical interface. The utility can be used to identify whether there is problem during a period of time, and narrow down the root cause of the problem. Users will find this a handy utility for monitoring real-time system and debugging problems in their daily work.

    展开全文
  • db2pd -edus命令详解

    千次阅读 2017-04-18 16:02:43
    db2pd:监控与排障数据库命令 db2pd命令常用语法: >>-db2pd--+------------------------------------------+---------->  '- -activestatements--+------------------+-'   +-apphdl=a
  • (转)db2top详解

    2019-03-23 15:42:00
    原文:... https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_11.1.0/com.ibm.db2.luw.admin.mon.doc/doc/t0025223.html Introduction There are several ...
  • db2 load详解

    2021-01-14 04:23:12
    load 是将输入的数据文件直接格式化成数据页存储到数据库中,在装载数据过程中不会触发触发器,并且除了唯一...--load 常用命令db2 "load from xxx.del of del modified by codepage=1208identityignore / identit...
  • top命令

    2014-11-14 09:37:32
    top 命令是最流行的性能监视工具之一,我们必需了解。它是一个优秀的交互式工具,用于监视性能。它提供系统整体性能,但报告进程信 息才是 top 命令的长处。...Linux top命令界面 第一行显示的
  • db2top工具详解[转]

    千次阅读 2016-01-07 13:40:59
    db2top工具详解
  • db2监控工具      db2pd (最推荐使用)      snapshot 命令行监控 ... db2top 监视器表函数 MON_   (受DB参数 MON_REQ_METRICS,MON_ACT_METRICS,MON_OBJ_MET
  • db2运维命令

    2019-10-08 04:03:58
    ==========================================================================================================================系统命令=====================================================================....
  • Dynamic SQL Variation 可以认为是Db2内部的一种变量,存放在Dynamic SQL Cache(package cache的一部分)中,每个Variation对应一条编译的动态SQL语句,也就是说,每当Db2编译了一条动态SQL,SQL cache中就会多一个...
  • 如果实时事件,DBA 可以通过查看表信息、GET SNAPSHOT 或者 db2pd/db2top 查看数据库当前的锁状况;这毕竟少数,往往锁问题都是历史事件,应用通过查看日志才发现存在锁问题,这种情况下,需要数据库本身对锁事件...
  • docker命令详解

    2019-02-26 10:51:38
    docker查看该文件下的所有文件...首先进入容器 docker exec -it DB2ExpressC /bin/bash DB2ExpressC为容器的id docker images 查看容器id  查看命令,在docker容器中执行 ls -al命令 [root@localhost ~]# dock...
  • 2.0、存储驱动 2.1、镜像命令 二、docker常用命令 2.1、查看版本 [root@ansible-server ~]# docker --version Docker version 18.09.6, build 481bc77156 2.2、查看帮助 [root@ansible-server ~]# docker --help ...
  • Linux常用命令详解

    千次阅读 2017-08-30 17:30:46
    ◆ 系统管理相关命令:df、top、free、quota、at、lp、adduser、groupadd、kill、crontab; ◆ 网络操作命令:ifconfig、ip、ping、netstat、telnet、ftp、route、rlogin、rcp、finger、mail、 nslookup; ◆ ...
  • JVM监控命令详解(转)

    2019-09-11 09:20:05
    JVM监控命令详解(转) JVM监控命令基本就是jps、jstack、jmap、jhat、jstat几个命令的使用就可以了 JDK本身提供了很多方便的JVM性能调优监控工具,除了集成式的VisualVM和jConsole外,还有jps、jstack、jmap、jhat、...
  • 下面对Linux中最重要的个命令详解。根据它们在系统中的作用,分成六大部分: ◆ 安装和登录命令:login、shutdown、halt、reboot、install、mount、umount、chsh、exit、last;◆ 文件处理命令:file、mkdir、...
  • DB2跑得更快——DB2内部解析与性能优化 (DB2数据库领域的精彩强音,DB2技巧精髓的热心分享,资深数据库专家牛新庄、干毅民、成孜论、唐志刚联袂推荐!)  洪烨著 2013年10月出版 定价:79.00元   编辑...
  • mysqldump 参数命令详解

    2017-07-17 17:45:21
    OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] OR mysqldump [OPTIONS] --all-databases [OPTIONS] Default options are read from the following files in the given order: /etc/my...
  • redis info命令详解

    千次阅读 2016-09-30 09:21:35
    redis-cli连接服务器后,使用info命令查看Redis信息和状态: INFO [section] 以一种易于解释(parse)且易于阅读的格式,返回关于 Redis 服务器的各种信息和统计数值。
  • linux iostat命令详解

    2013-11-28 16:49:25
    Linux系统出现了性能问题,一般我们可以通过top、iostat、free、vmstat等命令来查看初步定位问题。其中iostat可以给我们提供丰富的IO状态数据。基本使用$iostat -d -k 1 10参数 -d 表示,显示设备(磁盘)使用状态;...
  • DB2版块精华知识分类索引

    千次阅读 2012-03-10 15:11:49
    ...DB2入门与认证 ...DB2入门指南 ...DB2 新手入门 ...db2 概念基础 ...DB2客户端配置 ...db2自学教程 ...DB2基础教程(中文版) ...DB2函数详解 ...DB2初学者刚开始必须掌握那些DB2的基本命令和知识 DB2安装

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,014
精华内容 405
关键字:

db2top命令详解