DB2诊断工具

1） db2support
--数据库可连接
db2support  /db2  -d  sample  -c  -g  -s
--数据库无法连接
db2support  /db2  -g  -s
-s 收集系统和硬件信息
-g 捕获 dump 目录的文件 （排除核心）
-c 连接数据库
-h get all the supported options
The db2support tool collects the following information under all conditions:
? db2diag.log
? All trap files
? locklist files
? Dump files
? Various system related files
? Output from various system commands
? db2cli.ini
----------------------------------------------------------------------------------------------------------------
2）db2diag.log 
默认路径$HOME/sqllib/db2dump
DIAGPATH 实例级指定路径
错误级别：INFO WARNING ERROR SEVERE Event
DIAGLEVEL 实例级参数，值为 0-4，默认为 3
     0： 不记录任何日志
     1：记录严重错误 SEVERE
     2：记录严重错误 SEVERE 和错误信息 ERROR
     3：记录 SEVERE ERROR WARNING
     4：记录所有信息  SEVERE ERROR WARNING INFO

--列出 db2diag.log 中的错误信息 并 重定向到文件 db2diag_error.log
db2diag -l  "error,severe" -o  db2diag_error.log

--查看所有帮助
db2diag -h all

db2diag -h examples 查看示例 

--归档db2diag日志文件

db2diag -A 

-g 区分大小写

--按数据库名称过滤
db2diag -g db=sample

--按进程过滤,并在分区1，2上运行的进程，严重错误消息
db2diag -g level=Severe,pid=2200 -n 1,2

--格式化 db2diag 工具输出
db2diag -time 2006-01-01 -node "0,1,2" -level "Severe, Error" |
db2diag -fmt "Time: %{ts}
分区：%node Message Level：%{level} \nPid：%{pid}  Tid：%{tid}
实例：%{instance}\nMessage: @{msg}\n"

--过滤来自不同工具的消息
受支持的工具包括：
    ALL，这会返回来自所有工具的记录
    MAIN，这会返回来自 DB2? 常规诊断日志的记录，例如 db2diag 日志文件和管理通知日志
    OPTSTATS，这会返回与优化器统计信息有关的记录

1）读取来自 MAIN 工具的消息，请使用以下命令：
db2diag -facility MAIN

2）显示来自 OPTSTATS 工具的消息并滤出级别为 Severe 的记录：
db2diag -fac OPTSTATS -level Severe

3）显示 OPTSTATS 工具中级别为 Error 并且以特定格式输出时间戳记和 PID 字段的所有消息，请使用以下命令：

db2diag -fac optstats -level Error -fmt " Time :%{ts} Pid :%{pid}"

--查找应用程序句柄APPHDL为0-222在分区上的诊断日志
db2diag -g APPHDL="0-222",NODE=000

--查找所有FUNCTION中包含fetch的日志
db2diag -g FUNCTION;=fetch 

--查找所有component名称以"base sys"开头的诊断日志
db2diag -g "COMPONENT^=base sys"

--查找返回代码为"ZRC=0x80120086"的记录
db2diag -g RETCODE:=0x80120086 

----------------------------------------------------------------------------------------------------------------
3）db2pd 独立运行于数据库引擎之外的工具

--db2pd 监控的优势，相对快照，事件监视器来说，
1）db2pd 直接从DB2内存读取信息，不需要连接数据库，不占用数据库引擎资源
2）db2pd 收集信息不会请求任何锁，获取信息的速度快
3）db2pd 提供的信息比其他方式更加底层更加丰富

-file  fileout.txt 输出到文件
-repeat [num sec] [count]  重复执行命令，默认5秒执行一次
      Repeat every num seconds (default 5) count times

db2pd -everything 大部分公开的信息
db2pd -alldbs 所有数据库信息
db2pd -alldbp 所有分区的信息
db2pd -dbp 指定分区的信息
db2pd -db testdb -app 应用程序 等同于 db2 list applications show detail
db2pd -edus
db2pd -edus interval=3 （9.7 Fp4后）
db2pd -osinfo  操作系统信息
db2pd -inst 实例信息
db2pd -db testdb -bufferpool 缓冲池信息
db2pd -db testdb -logs 日志信息
db2pd -db testdb -tablespaces 表空间信息
db2pd -db testdb -locks 锁信息
db2pd -db testdb -tran 事务信息
db2pd -db testdb -app 应用程序信息
db2pd -db testdb -agents 代理信息
db2pd -db testdb -static 静态语句信息
db2pd -db testdb -dynamic 动态SQL信息
db2pd -db testdb -tcbstats 表状态信息  [ tablespaceid [ tableid ] ]
db2pd -db testdb -tcbstats index 索引状态信息
db2pd -db testdb -memsets  数据库级内存段
db2pd -db testdb  -mempools 数据库级内存池
db2pd -dbptnmem 实例总共占用的内存
db2pd -memsets  实例级内存段
db2pd -mempools 实例级内存池
db2pd -utilities 正在执行的实用程序
db2pd -db testdb -activestatements 正在执行的语句

----------------------------------------------------------------------------------------------------------------
4） db2trc 跟踪 DB2 内部运行
db2trc –h to display all the available options.

--打开跟踪工具
db2trc on -l 128M

--执行要跟踪的操作

--跟踪到信息写入文件 dump.trc
db2trc dump dump.trc

--关闭跟踪
db2trc off

--格式化跟踪文件，两种格式： fmt 生成格式文件   flw 生成流文件
db2trc  fmt  dump.trc   dump.fmt
or
db2trc flw  dump.trc  dump.flw

Be aware that tracing will slow down the DB2 instance.
The amount of performance degradation will depend on the type of
problem, your current workload, and how busy your system is during
the trace operation.

----------------------------------------------------------------------------------------------------------------
5) db2dart
You can use db2dart to inspect the whole databasea,table space in the database, or a single table.
You can only use the db2dart tool when the database is offline, so no connections are allowed
while the database is being inspected.

The following are some ways you can use db2dart.
? To perform an inspection on all objects in the sample database, issue
  db2dart sample
? To inspect table space USERSPACE1 in the sample database, issue
  db2dart sample /TSI 2
  where 2 is the table space ID for table space USERSPACE1. Table space IDs can be
  found in the LIST TABLESPACES command output.
? To inspect the sales table in the sample database, issue
  db2dart sample /TSI 2 /TN "sales"

If db2dart reports some data pages being corrupted, restore the database using a good backup image.

If db2dart reports some index pages being corrupted, you can fix this instead of having to restore from a backup.
If db2dart reports an index is corrupted, take the following steps to fix it:
1. Mark the index invalid using
db2dart dbalias /MI /OI objectID of the index /TSI tablespaceID
where both the objectID and tablespaceID can be found in the db2dart report.
2. Let DB2 automatically rebuild the index. When DB2 actually rebuilds this index
depends on the INDEXREC database configuration parameter setting. Its values can be
ACCESS, RESTART, or SYSTEM.
? ACCESS: DB2 rebuilds invalid indexes when they are accessed again for the first
time, after they have been invalidated by db2dart. With this method, the first user
who accesses this index will experience a longer wait while the index is re-created.
? RESTART: DB2 rebuilds invalid indexes when the database is restarted. With this
method, the time taken to restart the database will be longer due to index re-creation,
but normal processing is not impacted once the database has been brought back
online.
? SYSTEM: DB2 uses the setting in the INDEXREC at the database manager level.


----------------------------------------------------------------------------------------------------------------
6) The INSPECT Tool
The INSPECT tool is the online equivalent of the db2dart tool, which can run while the database is online.
The INSPECT tool inspects databases for architectural integrity and checks the pages
of the database for page consistency. The inspection checks that the structures of table objects
and table spaces are valid. However, it cannot be used to mark an index invalid or fix possible
data corruptions like the db2dart tool can.

The results file of the inspection is generated in the DB2 diagnostic data directory (i.e., where
the db2diag.log file is). It is a binary file that needs to be formatted with the DB2INSPF command.
If no errors are found, by default, the results file is erased after the inspect operation is
complete, unless the KEEP option is used.

? To inspect the SAMPLE database and write the results to a file called inspect.out, issue
CONNECT TO sample
INSPECT CHECK DATABASE RESULTS inspect.out

? To inspect the table space with table space ID 2 and keep the results and write it to the file inspect.out, issue
CONNECT TO sample
INSPECT CHECK TABLSPACE TBSPACEID 2 RESULTS KEEP inspect.out

? To format the results file, issue
DB2INSPF results_file output_file
where results_file is from the inspect command and output_file is the name of the
output file generated.

--DB2COS
Use the db2pdcfg command to configure how much information to
collect or how DB2 will handle certain problems or failures.

--DB2PDCFG
Use the db2pdcfg command to configure how much information to
collect or how DB2 will handle certain problems or failures.

--DB2FODC
Invoke the db2fodc tool manually during a hang condition where the
database and DB2 commands are unresponsive. The database
manager invokes the db2fodc tool when you set it to be used
automatically.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22661144/viewspace-1477179/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22661144/viewspace-1477179/