DB2-V9维护手册.doc

DB2DB2 维护手册维护手册 第 2 页 共 65 页 目录 DB2 维护维护手册手册 1 一 一 DB2 日常维护日操作日常维护日操作 3 1 检查管理服务器是否启动 3 2 检查 DB2 实例是否已经启动 3 3 查看表空间状态是否正常 3 4 查看表的状态 4 5 查看磁盘空间 4 6 检查存储管理软件是否正常 4 7 检查数据库备份是否正常 5 8 检查归档日志是否正确归档了 5 9 查看缓冲池的命中率 5 10 查看当前运行最频繁的 SQL 其命中率是否正常 5 11 查看当前连接的应用程序 有没有非法连接 5 12 检查有没有死锁 6 13 对表和索引进行RUNSTATS 6 14 检查表是否需要重组 6 15 对需要重组的表进行重组 7 二 二 DB2 日常维护月操作日常维护月操作 7 1 查看 DB2 日志 7 2 检查备份和日志是否都保存好了 7 三 三 DB2 日常维护季度操作日常维护季度操作 7 1 通过快照监控器 查看系统性能如何 7 2 数据库补丁级别 8 四 四 注意事项注意事项 8 1 不要删除活动日志文件 8 2 注意交易日志存储空间 8 3 按照系统的实际工作量配置日志空间 8 4 设置正确数据库代码页 9 5 检查许可证 LICENSE 安装情况 9 6 创建数据库前调整好系统时间 9 7 不要随便执行 CHOWN CHMOD R UNIX LINUX 9 8 在归档日志模式下使用 LOAD 记得加 NONRECOVERABLE 参数 9 五 五 附 以脱机方式重组表附 以脱机方式重组表 9 六 六 附 索引重组附 索引重组 10 七 七 附 收集和更新统计信息的准则附 收集和更新统计信息的准则 11 八 八 附 使用附 使用 CLP 捕获数据捕获数据库库运行状况快照运行状况快照 13 第 3 页 共 65 页 一 一 DB2 日常维护日操作日常维护日操作 1 检查管理服务器是否启动检查管理服务器是否启动 用 ps 命令查看是否有 dasusr1 后台进程 ps ef grep dasusr1 请确保管理服务器已经启动 如果没有启动 则按以下步骤启动管理服务器 如果没有启动 则按以下步骤启动管理服务器 以管理服务器用户 UNIX 默认是 DASUSR1 登录 发出 db2admin start 命令 如果是 HA 环境 则要保证在脚本中正确配置了启动命令 2 检查检查 DB2 实例是否已经启动实例是否已经启动 用 ps 命令查看是否有 db2sysc 后台进程 ps ef grep db2sysc 也可以以 DB2 实例所有者登录 通过发出 db2start 命令来确保启动了实例 如果实例已经启动 则会告知 SQL1026N 数据库管理器已激活 否则 将把实例启动起来 3 查看表空间状态是否正常查看表空间状态是否正常 以 db2 实例所有者登录 db2 list tablespaces show detail 在单分区上查看表空间的状态 正常返回 0 x0000 db2 all list tablespaces show detail 在所有分区上查看表空间的状态 可以使用 LISTLIST TABLESPACESTABLESPACES 命令确定连接数据库中表空间的当前状态 可以 使用 SHOWSHOW DETAILDETAIL 选项查看表空间的详细信息 比如 我们连上 SAMPLE 数据库 执行 db2 list tablespaces show detail 可以看到状态返回值是 0 x0000 此时 使用 db2tbstdb2tbst 可以查看状态编号对于的状态含义 具体语法如下 db2tbst 可以查看编号所代表的状态 例如 第 4 页 共 65 页 db2inst1 localhost db2 list tablespaces show detail Tablespaces for Current Database Tablespace ID 0 Name SYSCATSPACE Type Database managed space Contents All permanent data Regular table space State 0 x0000 Detailed explanation Normal Total pages 12288 Useable pages 12284 Used pages 9804 Free pages 2480 High water mark pages 9804 Page size bytes 8192 Extent size pages 4 Prefetch size pages 4 Number of containers 1 Tablespace ID 1 Name TEMPSPACE1 Type System managed space Contents System Temporary data State 0 x0000 Detailed explanation Normal Total pages 1 Useable pages 1 Used pages 1 Free pages Not applicable High water mark pages Not applicable Page size bytes 8192 Extent size pages 32 Prefetch size pages 32 Number of containers 1 Tablespace ID 2 Name USERSPACE1 Type Database managed space Contents All permanent data Large table space State 0 x0000 第 5 页 共 65 页 Detailed explanation Normal Total pages 4096 Useable pages 4064 Used pages 1824 Free pages 2240 High water mark pages 1824 Page size bytes 8192 Extent size pages 32 Prefetch size pages 32 Number of containers 1 Tablespace ID 3 Name IBMDB2SAMPLEREL Type Database managed space Contents All permanent data Large table space State 0 x0000 Detailed explanation Normal Total pages 4096 Useable pages 4064 Used pages 608 Free pages 3456 High water mark pages 608 Page size bytes 8192 Extent size pages 32 Prefetch size pages 32 Number of containers 1 Tablespace ID 4 Name IBMDB2SAMPLEXML Type Database managed space Contents All permanent data Large table space State 0 x0000 Detailed explanation Normal Total pages 4096 Useable pages 4064 Used pages 1440 Free pages 2624 High water mark pages 1440 Page size bytes 8192 Extent size pages 32 Prefetch size pages 32 第 6 页 共 65 页 Number of containers 1 Tablespace ID 5 Name SYSTOOLSPACE Type Database managed space Contents All permanent data Large table space State 0 x0000 Detailed explanation Normal Total pages 4096 Useable pages 4092 Used pages 104 Free pages 3988 High water mark pages 104 Page size bytes 8192 Extent size pages 4 Prefetch size pages 4 Number of containers 1 db2inst1 localhost db2tbstdb2tbst 0 x00000 x0000 State Normal db2inst1 localhost db2tbst 命令接收十六进制的状态值 并返回相应的表空间状态 例如 命令 db2tbst 0 x0008 返回 State Load Pending 而该十六进制的状态值反过来又是 LIST TABLESPACES 命令输出的组成部分 表空间的外部可见状态是由单个表空间的外部可见状态是由单个 状态值的十六进制总和构成的状态值的十六进制总和构成的 例如 如果表空间的状态是 Backup Pending 和 Load in Progress 那么所返回的十六进制值就是 0 x20020 0 x00020 0 x20000 用 db2 list tablespaces show detail 可以查看已使用的表空间和分配的表空间大 小 比如 表空间标识 3 名称 NNC DATA01 类型 数据库管理空间 内容 所有持久数据 常规表空间 状态 0 x0000 详细解释 正常 总计页数总计页数 102400 可用页数 102392 已用页数已用页数 2176 第 7 页 共 65 页 可用页数 100216 高水位标记 页 2176 页大小 以字节计 16384 扩展数据块大小 页 8 预取大小 页 8 容器数 1 表空间的大小不要超过表空间的大小不要超过 90 当空间超过 当空间超过 90 的时候需要添加表空间 的时候需要添加表空间 4 查看表的状态查看表的状态 查询系统目录视图以获得关于数据库的有用信息 查询系统目录视图以获得关于数据库的有用信息 例如 下面的语句使用 NOTNOT LIKELIKE 断言 返回在 SYSCAT TABLESSYSCAT TABLES 中有项的所有用户定义的表的名称 以及每个表的列数和表的状态 N N 正常 正常 C C 待审核 待审核 checkcheck pendingpending db2 db2 select select tabname tabname colcount colcount statusstatus FROMFROM syscat tablessyscat tables WHEREWHERE tabschematabschema NOTNOT LIKELIKE SYS SYS ORDERORDER BYBY tabname tabname 例如 db2inst1 localhost db2inst1 localhost db2db2 select select tabname tabname colcount colcount statusstatus FROMFROM syscat tablessyscat tables WHEREWHERE tabschematabschema NOTNOT LIKELIKE SYS SYS ORDERORDER BYBY tabname tabname TABNAME COLCOUNT STATUS ACT 3 N ADEFUSR 2 N CATALOG 2 N CL SCHED 4 N CUSTOMER 3 N DEPARTMENT 5 N DEPT 0 N VPSTRDE2 12 N VSTAFAC1 11 N 第 8 页 共 65 页 VSTAFAC2 11 N 47 record s selected db2inst1 localhost 也可以使用 loadload queryquery 命令查看单个表的状态 比如对表 TEST1 我们可以发出如下命令 db2db2 loadload queryquery tabletable test1 5 查看磁盘空间查看磁盘空间 查看数据库活动日志目录是否已满 活动日志目录可以使用 db2 get db cfg 查看 注 意一定不要手工删除活动日志 db2 get db cfg more df k 例如 db2inst1 localhost db2 connect to sample Database Connection Information Database server DB2 LINUX 9 7 0 SQL authorization ID DB2INST1 Local database alias SAMPLE db2inst1 localhost db2 get db cfg more Database Configuration for Database Database configuration release level 0 x0d00 Database release level 0 x0d00 Database territory CN 第 9 页 共 65 页 Database code page 1208 Database code set UTF 8 Database country region code 86 Database collating sequence IDENTITY Alternate collating sequence ALT COLLATE Number compatibility OFF Varchar2 compatibility OFF Date compatibility OFF Database page size 8192 Dynamic SQL Query management DYN QUERY MGMT DISABLE Statement concentrator STMT CONC OFF Discovery support for this database DISCOVER DB ENABLE Restrict access NO Default query optimization class DFT QUERYOPT 5 Degree of parallelism DFT DEGREE 1 Continue upon arithmetic exceptions DFT SQLMATHWARN NO Default refresh age DFT REFRESH AGE 0 Default maintained table types for opt DFT MTTB TYPES SYSTEM Number of frequent values retained NUM FREQVALUES 10 Number of quantiles retained NUM QUANTILES 20 Decimal floating point rounding mode DECFLT ROUNDING ROUND HALF EVEN Backup pending NO All committed transactions have been written to disk YES Rollforward pending NO Restore pending NO 第 10 页 共 65 页 Multi page file allocation enabled YES Log retain for recovery status NO User exit for logging status NO Self tuning memory SELF TUNING MEM OFF Size of database shared memory 4KB DATABASE MEMORY AUTOMATIC 31520 Database memory threshold DB MEM THRESH 10 Max storage for lock list 4KB LOCKLIST 4096 Percent of lock lists per application MAXLOCKS 10 Package cache size 4KB PCKCACHESZ MAXAPPLS 8 Sort heap thres for shared sorts 4KB SHEAPTHRES SHR 5000 Sort list heap 4KB SORTHEAP 256 Database heap 4KB DBHEAP AUTOMATIC 1200 Catalog cache size 4KB CATALOGCACHE SZ MAXAPPLS 5 Log buffer size 4KB LOGBUFSZ 256 Utilities heap size 4KB UTIL HEAP SZ 5000 Buffer pool size pages BUFFPAGE 1000 SQL statement heap 4KB STMTHEAP AUTOMATIC 2048 Default application heap 4KB APPLHEAPSZ AUTOMATIC 256 Application Memory Size 4KB APPL MEMORY AUTOMATIC 10000 Statistics heap size 4KB STAT HEAP SZ AUTOMATIC 4384 Interval for checking deadlock ms DLCHKTIME 10000 Lock timeout sec LOCKTIMEOUT 1 Changed pages threshold CHNGPGS THRESH 60 Number of asynchronous page cleaners NUM IOCLEANERS AUTOMATIC 1 Number of I O servers NUM IOSERVERS AUTOMATIC 3 Index sort flag INDEXSORT YES 第 11 页 共 65 页 Sequential detect flag SEQDETECT YES Default prefetch size pages DFT PREFETCH SZ AUTOMATIC Track modified pages TRACKMOD OFF Default number of containers 1 Default tablespace extentsize pages DFT EXTENT SZ 32 Max number of active applications MAXAPPLS AUTOMATIC 40 Average number of active applications AVG APPLS AUTOMATIC 1 Max DB files open per application MAXFILOP 30720 Log file size 4KB LOGFILSIZ 1000 Number of primary log files LOGPRIMARY 3 Number of secondary log files LOGSECOND 2 Changed path to log files NEWLOGPATH Path to log files home db2inst1 db2inst1 NODE0000 SQL00001 SQLOGDIR home db2inst1 db2inst1 NODE0000 SQL00001 SQLOGDIR Overflow log path OVERFLOWLOGPATH Mirror log path MIRRORLOGPATH First active log file Block log on disk full BLK LOG DSK FUL NO Block non logged operations BLOCKNONLOGGED NO Percent max primary log space by transaction MAX LOG 0 Num of active log files for 1 active UOW NUM LOG SPAN 0 Group commit count MINCOMMIT 1 Percent log file reclaimed before soft chckpt SOFTMAX 100 Log retain for recovery enabled LOGRETAIN OFF User exit for logging enabled USEREXIT OFF HADR database role STANDARD 第 12 页 共 65 页 HADR local host name HADR LOCAL HOST HADR local service name HADR LOCAL SVC HADR remote host name HADR REMOTE HOST HADR remote service name HADR REMOTE SVC HADR instance name of remote server HADR REMOTE INST HADR timeout value HADR TIMEOUT 120 HADR log write synchronization mode HADR SYNCMODE NEARSYNC HADR peer window duration seconds HADR PEER WINDOW 0 First log archive method LOGARCHMETH1 OFF Options for logarchmeth1 LOGARCHOPT1 Second log archive method LOGARCHMETH2 OFF Options for logarchmeth2 LOGARCHOPT2 Failover log archive path FAILARCHPATH Number of log archive retries on error NUMARCHRETRY 5 Log archive retry Delay secs ARCHRETRYDELAY 20 Vendor options VENDOROPT Auto restart enabled AUTORESTART ON Index re creation time and redo index build INDEXREC SYSTEM RESTART Log pages during index build LOGINDEXBUILD OFF Default number of loadrec sessions DFT LOADREC SES 1 Number of database backups to retain NUM DB BACKUPS 12 Recovery history retention days REC HIS RETENTN 366 Auto deletion of recovery objects AUTO DEL REC OBJ OFF TSM management class TSM MGMTCLASS TSM node name TSM NODENAME TSM owner TSM OWNER TSM password TSM PASSWORD Automatic maintenance AUTO MAINT ON 第 13 页 共 65 页 Automatic database backup AUTO DB BACKUP OFF Automatic table maintenance AUTO TBL MAINT ON Automatic runstats AUTO RUNSTATS ON Automatic statement statistics AUTO STMT STATS ON Automatic statistics profiling AUTO STATS PROF OFF Automatic profile updates AUTO PROF UPD OFF Automatic reorganization AUTO REORG OFF Auto Revalidation AUTO REVAL DEFERRED Currently Committed CUR COMMIT ON CHAR output with DECIMAL input DEC TO CHAR FMT NEW Enable XML Character operations ENABLE XMLCHAR YES WLM Collection Interval minutes WLM COLLECT INT 0 Monitor Collect Settings Request metrics MON REQ METRICS BASE Activity metrics MON ACT METRICS BASE Object metrics MON OBJ METRICS BASE Unit of work events MON UOW DATA NONE Lock timeout events MON LOCKTIMEOUT NONE Deadlock events MON DEADLOCK WITHOUT HIST Lock wait events MON LOCKWAIT NONE Lock wait event threshold MON LW THRESH 5000000 SMTP Server SMTP SERVER db2inst1 localhost dfdf k k home db2inst1 db2inst1 NODE0000 SQL00001 SQLOGDIR home db2inst1 db2inst1 NODE0000 SQL00001 SQLOGDIR 文件系统 1K 块 已用 可用 已用 挂载点 dev sda3 28794584 11696844 15635024 43 db2inst1 localhost 查看 SMS 表空间对应的容器目录空间是否满了 第 14 页 共 65 页 db2 list tablespaces show detail 例如 db2inst1 localhost db2 list tablespaces show detail Tablespaces for Current Database Tablespace ID 0 Name SYSCATSPACE Type Database managed space Contents All permanent data Regular table space State 0 x0000 Detailed explanation Normal Total pages 12288 Useable pages 12284 Used pages 10648 Free pages 1636 High water mark pages 10648 Page size bytes 8192 Extent size pages 4 Prefetch size pages 4 Number of containers 1 Tablespace ID 1 Name TEMPSPACE1 Type System managed space Contents System Temporary data State 0 x0000 Detailed explanation Normal Total pages 1 第 15 页 共 65 页 Useable pages 1 Used pages 1 Free pages Not applicable High water mark pages Not applicable Page size bytes 8192 Extent size pages 32 Prefetch size pages 32 Number of containers 1 Tablespace ID 2 Name USERSPACE1 Type Database managed space Contents All permanent data Large table space State 0 x0000 Detailed explanation Normal Total pages 4096 Useable pages 4064 Used pages 1824 Free pages 2240 High water mark pages 1824 Page size bytes 8192 Extent size pages 32 Prefetch size pages 32 Number of containers 1 Tablespace ID 3 Name IBMDB2SAMPLEREL Type Database managed space Contents All permanent data Large table space State 0 x0000 第 16 页 共 65 页 Detailed explanation Normal Total pages 4096 Useable pages 4064 Used pages 608 Free pages 3456 High water mark pages 608 Page size bytes 8192 Extent size pages 32 Prefetch size pages 32 Number of containers 1 Tablespace ID 4 Name IBMDB2SAMPLEXML Type Database managed space Contents All permanent data Large table space State 0 x0000 Detailed explanation Normal Total pages 4096 Useable pages 4064 Used pages 1440 Free pages 2624 High water mark pages 1440 Page size bytes 8192 Extent size pages 32 Prefetch size pages 32 Number of containers 1 Tablespace ID 5 Name SYSTOOLSPACE 第 17 页 共 65 页 Type Database managed space Contents All permanent data Large table space State 0 x0000 Detailed explanation Normal Total pages 4096 Useable pages 4092 Used pages 108 Free pages 3984 High water mark pages 108 Page size bytes 8192 Extent size pages 4 Prefetch size pages 4 Number of containers 1 Tablespace ID 6 Name SYSTOOLSTMPSPACE Type System managed space Contents User Temporary data State 0 x0000 Detailed explanation Normal Total pages 1 Useable pages 1 Used pages 1 Free pages Not applicable High water mark pages Not applicable Page size bytes 8192 Extent size pages 4 Prefetch size pages 4 Number of containers 1 第 18 页 共 65 页 db2inst1 localhost 查看 DMS 表空间中是否还有可用页 db2 list tablespaces show detail 在单分区上查看表空间的是否还有可用 页 db2 all list tablespaces show detail 在所有分区上查看表空间是否还有可用 页 6 检查存储管理软件是否正常检查存储管理软件是否正常 请检查 TSM 或其他存储管理软件是否正常 以及磁带机是否运行正常 7 检查数据库备份是否正常检查数据库备份是否正常 请查看 TSM 或第三方存储管理软件 看备份映像文件是否完整的保存到了磁带机上了 想在 DB2 上查看备份情况 可以使用 LIST 命令 db2 list history backup all for 数据库名 例如 db2inst1 localhost backup db2 list history backup all for sample List History File for sample Number of matching file entries 1 Op Obj Timestamp Sequence Type Dev Earliest Log Current Log Backup ID B D 20091013150454001 F D S0000000 LOG S0000000 LOG Contains 5 tablespace s 00001 SYSCATSPACE 00002 USERSPACE1 00003 IBMDB2SAMPLEREL 00004 IBMDB2SAMPLEXML 第 19 页 共 65 页 00005 SYSTOOLSPACE Comment DB2 BACKUP SAMPLE OFFLINE Start Time 20091013150454 End Time 20091013150501 Status A EID 4 Location backup db2inst1 localhost backup 8 检查归档日志是否正确归档了检查归档日志是否正确归档了 请确保活动日志目录下没有的日志文件都已经正确归档到了带机上 查看 TSM 或第三方 存储管理软件 查看活动目录里的日志文件 ls l 9 查看缓冲池的命中率查看缓冲池的命中率 db2 get snapshot for bufferpools on 数据库名 例如 db2inst1 localhost db2 get snapshot for bufferpools on sample Bufferpool Snapshot Bufferpool name IBMDEFAULTBP Database name SAMPLE Database path home db2inst1 db2inst1 NODE0000 SQL00003 Input database alias SAMPLE Snapshot timestamp 10 13 2009 14 38 13 955609 第 20 页 共 65 页 Buffer pool data logical reads Not Collected Buffer pool data physical reads Not Collected Buffer pool temporary data logical reads Not Collected Buffer pool temporary data physical reads Not Collected Buffer pool data writes Not Collected Buffer pool index logical reads Not Collected Buffer pool index physical reads Not Collected Buffer pool temporary index logical reads Not Collected Buffer pool temporary index physical reads Not Collected Buffer pool xda logical reads Not Collected Buffer pool xda physical reads Not Collected Buffer pool temporary xda logical reads Not Collected Buffer pool temporary xda physical reads Not Collected Buffer pool xda writes Not Collected Total buffer pool read time milliseconds Not Collected Total buffer pool write time milliseconds Not Collected Asynchronous pool data page reads Not Collected Asynchronous pool data page writes Not Collected Buffer pool index writes Not Collected Asynchronous pool index page reads Not Collected Asynchronous pool index page writes Not Collected Asynchronous pool xda page reads Not Collected Asynchronous pool xda page writes Not Collected Total elapsed asynchronous read time Not Collected Total elapsed asynchronous write time Not Collected Asynchronous data read requests Not Collected Asynchronous index read requests Not Collected Asynchronous xda read requests Not Collected No victim buffers available Not Collected Direct reads Not Collected Direct writes Not Collected Direct read requests Not Collected 第 21 页 共 65 页 Direct write requests Not Collected Direct reads elapsed time ms Not Collected Direct write elapsed time ms Not Collected Database files closed Not Collected Unread prefetch pages Not Collected Vectored IOs Not Collected Pages from vectored IOs Not Collected Block IOs Not Collected Pages from block IOs Not Collected Node number 0 Tablespaces using bufferpool 6 Alter bufferpool information Pages left to remove 0 Current size 1000 Post alter size 1000 Bufferpool Snapshot Bufferpool name IBMSYSTEMBP4K Database name SAMPLE Database path home db2inst1 db2inst1 NODE0000 SQL00003 Input database alias SAMPLE Snapshot timestamp 10 13 2009 14 38 13 955609 Buffer pool data logical reads Not Collected Buffer pool data physical reads Not Collected Buffer pool temporary data logical reads Not Collected Buffer pool temporary data physical reads Not Collected Buffer pool data writes Not Collected 第 22 页 共 65 页 Buffer pool index logical reads Not Collected Buffer pool index physical reads Not Collected Buffer pool temporary index logical reads Not Collected Buffer pool temporary index physical reads Not Collected Buffer pool xda logical reads Not Collected Buffer pool xda physical reads Not Collected Buffer pool temporary xda logical reads Not Collected Buffer pool temporary xda physical reads Not Collected Buffer pool xda writes Not Collected