DB2最新维护手册
DB2 日常维护_官方
| August 9, 2005
© 2005 IBM Corporation
数据库监视器堆
命令：db2 get snapshot for dbm 输出： 内存池类型 = 数据库监视器堆 当前大小（以字节计） = 245760 高水位标记（以字节计） = 327680 已配置的大小（以字节计） = 278528
| August 9, 2005
© 2005 IBM Corporation
数据库编码
关于数据库的编码 db2 get db cfg for sample 数据库地域 数据库代码页 数据库代码集 数据库国家／地区代码 db2set DB2CODEPAGE=1386 = CN = 1386 = GBK = 86
| August 9, 2005
© 2005 IBM Corporation
表空间利用率及状态
命令：db2 list tablespaces show detail 输出： 表空间标识 =5 名称 = DMSTBS 类型 = 数据库管理空间 内容 = 任何数据 状态 = 0x0000 详细解释： 正常 总计页数 = 1000 可用页数 = 960 已用页数 = 96 可用页数 = 864 高水位标记（页） = 96 页大小（以字节计） = 4096 扩展数据块大小（页） = 32 预取大小（页） = 32 容器数 =1 最小恢复时间 = 2005-06-08-03.13.40.000000
利用率＝当前大小/已配置的大小
| August 9, 2005
© 2005 IBM Corporation
数据库日常维护
常用命令 数据库健康状况分析 数据维护 注意事项 Q&A
| August 9, 2005
IBM DB2 数据库恢复手册说明书
Faster + Easier = Improved DB2 Disaster Recovery(using FlashCopy)Judy Ruby-Brown For years users have known that DFSMSdss could not be used for taking disaster recovery backups for DB2 data for two reasons. First, to insure good performance, DB2 does not update data base pages at the completion of a transaction. Instead, it writes the log records for the updates and holds the updated pages in its buffer pools in anticipation of re-reference. Pages are written to DASD when buffer pool thresholds are reached and, eventually, when DB2 is stopped. Second, there was no way to stop the writing of the log buffers to the DB2 log. Customers who wanted to employ DFSMSdss had to wait until DB2 was stopped, and this caused a lengthy outage as well as operation al problems.Most users implemented DB2-only disaster recovery procedures, which have been documented in the DB2 Administration Guide, under the topic “Remote Site Recovery from Disaster at the Local Site”. Image copies of all tables and indexes and the archive logs covering the image copy interval were transported to a disaster recovery site. Following a conditional restart of DB2, the entire subsystem and application data were recovered, a process which consumed many hours and required deep recovery skills on the part of DB2 systems and DBA staffs.DB2 UDB for OS/390 V6 provided the SET LOG SUSPEND command. This command allows user to temporarily "freeze" a DB2 subsystem by stopping updates to the log. The logs and database can be quickly copied using IBM's Enterprise Storage Server FlashCopy or equivalent vendor products to produce ‘instant’ copies. The outage can be reduced to a few minutes and minimizes operational disruptions to current users and operations staff. After shipping tapes, created from the backup copy, to a remote site,DB2 users can implement a simplified disaster recovery scenario, which also reduces the recovery time to that necessary to restore the tape dumps.This scenario assumes a single local site. No remote copy technology is used. No transmission capabilities are employed. The following procedure should be performed ata point of low activity, since there is a short outage to obtain the ‘instant’ copy.1. Stop DB2 updates.Enter –SET LOG SUSPEND on the OS/390 console2. FlashCopy all DB2 volumes. When DSNJ372I is displayed on the OS/390console, the DFSMSdss dumps can be started. Include any ICF Catalogs used byDB2, as well as active logs and BSDSs. When the jobs end, the logicalrelationship has been established3. Resume normal DB2 activity.Enter -SET LOG RESUME on the OS/390 console4. Make tape copies and transport off site. The tape copy of the secondary volumescan begin as soon as the FlashCopy relationship is established.When the -SET LOG SUSPEND is issued from the OS/390 console, DB2 suspends logging and update activity for the current DB2 subsystem until a subsequent -SET LOG RESUME request is issued. A highlighted DSNJ372I message is issued and will remain on the console until update activity has been resumed. Note: All work in progress will remain in its current state as of that moment; activity does not come to a commit point. There will be in-flight units of recovery, but they will be handled at the recovery site when DB2 is restarted.This function is in the base DB2 V7 code. It was added to DB2 V6 as PQ31492 (onF9911 as UQ36695). Note: It is available as a usermod for DB2 V5. It can be requested from the IBM Support Center. Since there is no -SET LOG command in V5, the suspend is implemented as -SET TIME(0) to suspend logging and -SET TIME(1) to resume logging.These commands have member scope. For Data Sharing, the command must be issued on each data sharing member.After DSNJ372I is issued, logging is suspended and the FlashCopy can be started. Step 2: FlashCopy all DB2 volumesAll DB2 volumes in the DB2 subsystem can be dumped using DFSMSdss. Hardware which supports ‘instant’ copy is required because the copies must complete in only a few minutes. Units of recovery (UR), which are waiting on the log latch, hold locks. Transactions, which arrive later, may time out waiting on locks held by the log latch waiters.Sample DFSMSdss JCL is shown://COPYJOB JOB...//INSTIMG EXEC PGM=ADRDSSU//SYSPRINT DD SYSOUT=*//SYSUDUMP DD SYSOUT=V,OUTLIM=3000//SYSIN DD *COPY FULL INDYNAM (srcvol) OUTDYNAM (tgtvol) DUMPCONDITIONING /*This step is ended when the copies are logically complete (the relationship established). At that point the copies from ‘srcvol’ to ‘tgtvol’ proceed independently in the DASD subsystem (not in the operating system environment).Note: The copies may be written directly to tape by using the TSO interface to FlashCopy and specifying a mode of NOCOPY. The relationship persists until the tape copy is complete. This option is intended for disaster recovery backup, as no copy is available for local FlashBack. A description of this option may be referenced in the document listed at the end of this paper.Step 3: Resume normal DB2 activityWhen –SET LOG RESUME operator command is issued, normal DB2 update activity resumes.Step 4: Make tape copies for off site transportThe copies must be dumped to tape and taken offsite. This is not a trivial task and can take hours. The amount of time required is a function of the number of volumes to be dumped and the availability of tape devices.The effect of DFSMSdss COPY statement options1. COPYVOLID – It is used for SMS-managed volumes, but tape dumps must bemade on another OS/390 image. The secondary device will be varied offline when the VOLID is copied, as requested in the COPYVOLID parameter.2. NOCOPYVOLID without DUMPCONDITIONING – It can be used for non-SMS managed volumes. The tape dumps can be made on the same OS/390 image as the original because the volume serial is different. At the recovery site, thevolume serial must be clipped to the original one.3. NOCOPYVOLID with DUMPCONDITIONING – It can be used for SMSmanaged volumes if OW45674 and OW48234 have been applied. The secondary copy becomes an ‘interim’ copy. The tape dumps can be made on the sameOS/390 image as the original volume and the resulting dump tape will appear tohave been made with the source volume serial.A copy of a source volume (VOL001 for example) to a target volume (VOL002for example) with DUMPCONDITIONING specified, followed by a tape dumpof the target volume (VOL002), results in a dump data set that looks like it wascreated by dumping the source volume (VOL001). For more information on this option, please see WSC Flash 10092 at/support/techdocs/atsmastr.nsf/PubAllNum/Flash10092 These considerations are well known to the storage systems staffAt the Recovery SiteThe goal of this scenario is to make it appear that DB2 crashed locally.1. Use DFSMSdss to restore the FlashCopy data sets to DASD. Restoration willprobably take in excess of an hour, depending on the number of volumes, whichmust be restored, and the hardware configuration available at the recovery site(tape drives, channels etc.).If NOCOPYVOLID without DUMPCONDITIONING had been specified on theCOPY statement, the volume label must be clipped to the original volume serialfollowing restoration of the tape.For either COPYVOLID or NOCOPYVOLID with DUMPCONDITIONING, the volume is already labeled correctly.2. Issue the START DB2 command using the local DSNZPARM member. A normalrestart is performed. All in-flight and in-abort URs will back out at restart. In-doubts will remain until the coordinator is restarted. This is standard function for DB2 restart.3. When restart is complete, new work can begin.4. All utilities must be terminated, since they cannot be restarted.Data Sharing ConsiderationsLocal Site: The –SET LOG SUSPEND command must be issued on each member of the data sharing group. FlashCopy cannot be performed until all members have DSNJ372I displayed on their consoles.Recovery Site: All DB2 structures in the Coupling Facility must be forced using SETXCF commands before DB2 is restarted. All members may be then be started, and DB2 will perform a group restart to populate the SCA and LOCK structures; the Group Buffer Pools will be allocated. Following restart, all data sets, which were in the Group Buffer Pools at the time of the FlashCopy, are set to GRECP exception state (Group Buffer Pool Recovery Pending). The user must issue –START DB commands to perform GRECP recovery before any new work involving those data sets can begin. Comparison to Traditional Disaster RecoverySince this scenario assumes there is no transmission capability, could it be compared to the traditional DB2 D/R scenario, where image copies and archive logs are transported from which the entire subsystem is recovered?Issues FlashCopy Traditional DB2 D/R Computer Infrastructure atrecovery site?None NoneTransmission capability None NoneTransport Daily DailyImage Copies and Archives required? No, except inline copies foractive REORG/LOAD jobsYes, and inline imagecopies for activeLOAD/REORG jobsDFSMSdss tape dumps Yes No Recovery Point Objective <24 hours <24 hours Recovery Time Objective 1-2 hours (# of volumes) <24 hoursProcedures required? Simple – DFSMSdss restoreand DB2 restart Complex – DB2 conditional restart and recoverySkills needed DASD – storage systems Skilled DB2 systems/DBA Outage incurred for backup Few minutes NoneConclusion: The FlashCopy restore and restart provides an attractive alternative to the traditional DB2 recovery, at comparable financial cost. The recovery point objective (RPO) is the same, but the recovery time objective (RTO) is greatly reduced, to perhaps an hour.It requires no image copy decisions (full vs. incremental vs. mergecopy) for D/R.It requires suspending log for a couple of minutes to provide backup.It eliminates labor costs of manual procedures. It can eliminate software costs for vendor tools for recovery of the DB2 subsystem, application table spaces, andindexes.It transfers recovery responsibility from DB2 systems and DBA staffs to thestorage systems team, for whom disaster recovery is a standard priority. Additional Reference:Implementing ESS Copy Services on S/390, SG24-5680, a red book downloadable from/redbooks. See pages 163-165 or 4.19 “Using FlashCopy with DB2”. For NOCOPY option see pages 148-149 or 4.5.2 “TSO commands“..The End。
DB2 DBA手册
1数据库设计时优化1.1选择数据页的大小注意：在DB2 8版本以下是这样限制, 每页上最多行不能超过255个。
如果在页大小为32KB的表空间放行长度为12字节的表，它大约只能每个页的10%，即(255 x 12字节+91字节开销)= 3KB。
造成空间的浪费。
根据大表选择大页的表空间，小表选择小页的表空间，选择是由数据行的大小估算出来的。
1.2规范化数据库被规范化后，减少了数据冗余，数据量变小，数据行变窄。
这样DB2的每一页可以包括更多行，那么每一区里的数据量更多，从而加速表的扫描，改进了单个表的查询性能。
但是，当查询涉及多个表的时候，需要用很多连接操作把信息从各个表中组合在一起，导致更高的CPU和I/O花销。
那么，有很多时候需要在规范化和非规范化之间保持平衡，用适当的冗余信息来减少系统开销，用空间代价来换取时间代价。
1.3选择数据类型对每一属性选择什么样的数据类型很大程度上依据表的要求，但是在不违背表要求的前提下，选择适当的数据类型可以提高系统性能。
比如有text 列存放一本书的信息，用BLOB而不是character(1024)，BLOB存放的是指针或者文件参照变量，真正的文本信息可以放在数据库之外，从而减少数据库存储空间，使得程序运行的速度提高。
DB2提供了UDT（User Defined Datatypes）功能，用户可以根据自己的需要定义自己的数据类型。
当然我们不提倡大量使用自定义数据类型。
使用了用户自定义类型之后，编程时需要进行数据类型的转换，会导致应用性能降低。
1.4选择索引索引是数据库中重要的数据结构，它的根本目的就是为了提高查询效率。
现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。
使用索引可以快速、直接、有序的存取数据。
索引的建立虽然加快了查询，另一方面却将低了数据更新的速度，因为新数据不仅要增加到表中，也要增加到索引中。
另外，索引还需要额外的磁盘空间和维护开销。
因此，要合理使用索引：✧在经常进行连接，但是没有指定为外键的属性列上建立索引。
DB2 简明运维手册
DB2 简明运维手册数据库启动数据库正常启动的流程包括两个步骤，首先启动数据库实例，在root用户下切换到实例用户su - db2inst1，执行命令db2start然后激活对应的数据库，执行命令: db2 activate db 数据库名。
直到出现：则数据库成功启动。
数据库停止停止数据库使用如下命令：在root用户下切换到实例用户su - db2inst1，执行命令db2stop force，直到出现：则数据库停止成功。
数据库参数DB2的参数分为实例级参数和数据库级参数，以及实例注册变量实例级参数:主要设置实例使用的TCP/IP端口，查看实例端口通过命令：db2 get dbm cfg数据库实例注册变量：确认设置了通信协议为TCPIP，命令如下：如果没有设置则通过命令db2set DB2COMM=tcpip进行设置。
数据库参数确认内存自动调整已经打开，否则连接到数据库并执行db2 update db cfg for sample usingSELF_TUNING_MEM ON设置数据库的缺省日志参数为如果需要修改日志参数，可以通过命令db2 udpate db cfg for 数据库名using 参数名参数值例如增大备用日志文件数量到50，则可以通过命令修改创建数据库在实例用户下，执行db2 "create <数据库名> on <目标路径> using codeset UTF-8 territory cn"这样创建的数据库缺省页面大小（pagesize）为4K（4096），字符集为UTF-8，如果要使用GBK字符集，则把UTF-8修改为GBK即可。
创建缓冲池（bufferpool）为了使用与缺省页面大小不一致的表空间，例如缺省页面大小为4K，但是需要使用32K页的表空间，就必须先创建页面大小为32K的缓冲池，命令如下：db2 "create bufferpool bp32k pagesize 32768"bp32k为缓冲池的名字，通常每种页面大小创建一个缓冲池即可，例如8K页面的缓冲池可以命名为bp8k。
中国移动DB2数据库安全配置手册.doc
密级：文档编号：项目代号：中国移动DB2数据库安全配置手册Version 1.0中国移动通信有限公司二零零四年拟制: 审核: 批准: 会签: 标准化:版本控制分发控制目录第一章目的与范围 (1)1.1目的 (1)1.2适用范围 (1)1.3数据库类型 (1)第二章数据库安全规范 (1)2.1操作系统安全 (1)2.2帐户安全 (2)2.3密码安全 (2)2.4访问权限安全 (2)2.5日志记录 (2)2.6加密 (3)2.7管理员客户端安全 (3)2.8安全补丁 (3)2.9审计 (3)第三章数据库安全配置手册 (4)3.1DB2数据库安全配置方法 (4)3.1.1 基本漏洞加固方法 (4)3.1.2 特定漏洞加固方法 (10)第四章附录：数据库安全问题及解决方案 (13)4.1数据库安全问题 (13)4.1.1 数据安全基本需求 (13)4.1.2 数据安全风险 (15)4.1.3 业界采用的安全技术 (17)1.1.4DB2的安全解决之道 (18)4.2DB2安全解决方案–提供端到端的安全体系结构 (19)4.2.1 DB2 安全机制 (19)4.2.2 托管环境的安全 (21)4.2.3 网络中的安全——基于标准的公共密钥体系结构(PKI) (21)4.2.4 先进的用户和安全策略管理 (23)第一章目的与范围1.1 目的为了加强中国移动集团下属各公司的网络系统安全管理，全面提高中国移动集团下属各公司业务网和办公网的网络安全水平，保证网络通信畅通和信息系统的正常运营，提高网络服务质量，特制定本方法。
本文档旨在于规范中国移动集团下属各公司对DB2数据库进行的安全加固。
1.2适用范围本手册适用于对中国移动集团下属各公司业务网和办公网系统的数据库系统加固进行指导。
1.3数据库类型数据库类型为DB2 EEE。
第二章数据库安全规范2.1 操作系统安全要使数据库安全，首先要使其所在的平台和网络安全。
然后就要考虑操作系统的安全性。
DB2数据库存储管理与维护
载入阶段：数据存在在表中；收集索引并排序 构建阶段：基于在载入阶段收集的索引键创建索引 删除阶段：删除违反唯一或主键约束的行 索引拷贝阶段：将源自引数据拷回原来的表空间20
二、数据移动工具(6 of 7)
载入工具— LOAD
21
二、数据移动工具(7 of 7)
RUNSTATS收集的信息用于显示数据的物理结构，并给 DB2优化器提供所需信息，以便在执行SQL时选择最佳访 问路径。
23
本章小结
数据存储管理 表空间设计和维护 移动数据格式
DEL ASC IXF WSF
数据移动工具
EXPORT IMPORT LOAD
17
二、数据移动工具(3 of 7)
导入数据—IMPORT
18
二、数据移动工具(4 of 7)
导入数据的例子
19
二、数据移动工具(5 of 7)
载入工具-LOAD
载入工具使用从输入文件读出的行构建页，插据数据 已有的索引在数据页插入后重建 在载入期间，单个记录不记录在日志文件中
9
二、表空间设计和维护(2 of 4)
DB2建立的物理文件不能以任何方式从DB2外直 接访问
不能直接改变这些文件。它们只能使用存档的API或者 实现那些API的工具来单独访问。
不可以删除或者移动这些文件。 除了DB2，其他任何请求均不能直接访问DB2文件和
目录。 备份数据库或者表空间的唯一方式是通过BACKUP
WSF— 工作表格文件
Lotus 1-2-3和Symphony产品使用这种文件格式类 型导出或导入数据
Lotus 1-2-3和Symphony产品使用这种文件格式类 型导出或导入数据
XXX工商局DB2 Q复制配置维护手册
1、Q复制规划1.1主机和DB2的相关设置信息模式（归档日志模式）1.2Websphere MQ的相关配置信息1.3Q复制的配置信息1.4复制队列映射属性1.5通道测试cd /usr/mqm/samp/bin./amqsput SYSA.SENDQ QMSYSA./amqsget SYSB.RECVQ QMSYSB2、mq用户创建创建用户mqm和组mqm,并把组mqm加入到用户db2inst1和db2fenc1中。
3、mq software install解压缩mq软件，用smitty installp安装,创建大小50G的mqmvg 、mqmlv和文件系统mqm,挂载点/var/mqm，/var/mqm/log4、mq 队列和通道创建在dbsvr04上执行setclock dbsvr01进行与dbsvr01的时间同步，需要在/etc/hosts中添加dbsvr01和IP。
由于MQ的需要在dbsvr01和dbsvr04中/etc/hosts添加相互的IP和name 信息。
Service ip 和service name 也要添加。
Dbsvr04:/etc/hosts:10.0.1.41 dbsvr0110.0.1.44 dbsvr0410.0.1.45 dbserver //hacmp 中service IPMQ测试：Dbsvr01:$ crtmqm -q venus.queue.managerWebSphere MQ queue manager created.Creating or replacing default objects for venus.queue.manager.Default objects statistics : 58 created. 0 replaced. 0 failed.Completing setup.Setup completed.$ strmqmWebSphere MQ queue manager 'venus.queue.manager' starting.5 log records accessed on queue manager 'venus.queue.manager' during the log replay phase.Log replay for queue manager 'venus.queue.manager' complete.Transaction manager state recovered for queue manager 'venus.queue.manager'. WebSphere MQ queue manager 'venus.queue.manager' started.$ runmqsc5724-H72 (C) Copyright IBM Corp. 1994, 2008. ALL RIGHTS RESERVED.Starting MQSC for queue manager venus.queue.manager.define qlocal (orange.queue)1 : define qlocal (orange.queue)AMQ8006: WebSphere MQ queue created.end2 : endOne MQSC command read.No commands have a syntax error.All valid MQSC commands were processed.$ cd /usr/mqm/samp/bin/$ ./amqsput ORANGE.QUEUESample AMQSPUT0 starttarget queue is ORANGE.QUEUEabcddsfsdsdfdsenddbsvr04:$ crtmqm -q venus.queue.managerWebSphere MQ queue manager created.Creating or replacing default objects for venus.queue.manager.Default objects statistics : 58 created. 0 replaced. 0 failed.Completing setup.Setup completed.$ $ strmqmWebSphere MQ queue manager 'venus.queue.manager' starting.5 log records accessed on queue manager 'venus.queue.manager' during the log replay phase.Log replay for queue manager 'venus.queue.manager' complete.Transaction manager state recovered for queue manager 'venus.queue.manager'. WebSphere MQ queue manager 'venus.queue.manager' started.$ runmqsc5724-H72 (C) Copyright IBM Corp. 1994, 2008. ALL RIGHTS RESERVED.Starting MQSC for queue manager venus.queue.manager.define qlocal (orange.queue)1 : define qlocal (orange.queue)AMQ8006: WebSphere MQ queue created.:::end2 : endOne MQSC command read.No commands have a syntax error.All valid MQSC commands were processed.$ cd /usr/mqm/samp/bin$ ./amqsput ORANGE.QUEUESample AMQSPUT0 starttarget queue is ORANGE.QUEUEadsfsdfdsfsdfsdfsSample AMQSPUT0 end$$ ./amqsget ORANGE.QUEUESample AMQSGET0 startmessage <adsfsdfdsfsdfsdfs>dbsvr01:crtmqm -lc -d SYSA.XMITQ -u DEADLETTER -lp 20 -ls 10 -lf 10240 QMSYSA //创建MQ队列strmqm QMSYSA //起队列管理器endmqlsr -m QMSYSA //停队列管理器ps -ef | grep mq //查看队列管理器nohup runmqlsr -t tcp -p 1453 -m QMSYSA & //起监听ps –ef|grep lsr // 查看监听进程endmqm QMSYSA //停队列endmqlsr //停监听dltmqm QMSYSA//删除管理队列runmqsc QMSYSA //起MQ资源DEFINE QREMOTE('SYSA.SENDQ') RNAME('SYSB.RECVQ') RQMNAME('QMSYSB') XMITQ('SYSA.XMITQ')DEFINE QLOCAL('SYSA.XMITQ') USAGE(XMITQ) MAXDEPTH(1000000) DEFPSIST(YES) DEFINE QLOCAL('DEADLETTER') USAGE(NORMAL) MAXDEPTH(500000)ALTER QMGR DEADQ('DEADLETTER')DEFINE CHL ('SYSAtoSYSB') CHLTYPE(SDR) TRPTYPE(TCP) CONNAME('10.0.1.44(1454)') XMITQ('SYSA.XMITQ') DISCINT (0) BATCHSZ (200)START CHANNEL ('SYSAtoSYSB')DEFINE CHL ('SYSBtoSYSA') CHLTYPE(RCVR) TRPTYPE(TCP) BATCHSZ(200)DEFINE QLOCAL('SYSA.ADMINQ') MAXDEPTH(500000) DEFPSIST(YES)DEFINE QLOCAL('SYSA.RESTARTQ') MAXDEPTH(500000) DEFPSIST(YES)End*************************runmqsc QMSYSAdis chstatus('SYSAtoSYSB') //显示running 通道状态正常dis chstatus('SYSAtoSYSB')1 : dis chstatus('SYSBtoSYSA')AMQ8417: Display Channel Status details.CHANNEL(SYSBtoSYSA) CHLTYPE(RCVR)CONNAME(10.0.1.44) CURRENTRQMNAME(QMSYSB) STATUS(RUNNING)SUBSTATE(RECEIVE)dis chstatus('SYSBtoSYSA')2 : dis chstatus('SYSBtoSYSA')AMQ8417: Display Channel Status details.CHANNEL(SYSBtoSYSA) CHLTYPE(RCVR)CONNAME(10.0.1.44) CURRENTRQMNAME(QMSYSB) STATUS(RUNNING)SUBSTATE(RECEIVE)*************************runmqsc QMSYSBdis chstatus('SYSBtoSYSA')runmqchl -c SYSAtoSYSB -m QMSYSA //如果没有错误信息显示，表明该channel成功运行runmqchl -c SYSBtoSYSA -m QMSYSA //如果没有错误信息显示，表明该channel成功运行********************************修改queue manager的CCSID:strmqmrunmqscdisplay qmgr // 检查当前queue manager的CCSID值alter qmgr ccsid(437)end*************************dbsvr04:crtmqm -lc -d SYSB.XMITQ -u DEADLETTER -lp 20 -ls 10 -lf 10240 QMSYSBstrmqm QMSYSBnohup runmqlsr -t tcp -p 1454 -m QMSYSB &runmqsc QMSYSBDEFINE QLOCAL('SYSB.RECVQ') MAXDEPTH(1000000) DEFPSIST(YES)DEFINE QREMOTE('SYSA.ADMINQ') RNAME('SYSA.ADMINQ') RQMNAME('QMSYSA') XMITQ('SYSB.XMITQ')DEFINE QLOCAL('SYSB.XMITQ') MAXDEPTH(1000000) USAGE(XMITQ) DEFPSIST(YES)DEFINE QLOCAL('DEADLETTER') USAGE(NORMAL) MAXDEPTH(500000)ALTER QMGR DEADQ('DEADLETTER')DEFINE CHL ('SYSBtoSYSA') CHLTYPE(SDR) TRPTYPE(TCP) CONNAME('10.0.1.45(1453)') XMITQ('SYSB.XMITQ') DISCINT (0) BATCHSZ (200)START CHANNEL ('SYSBtoSYSA')DEFINE CHL ('SYSAtoSYSB') CHLTYPE(RCVR) TRPTYPE(TCP) BATCHSZ(200)DEFINE QMODEL('IBMQREP.SPILL.MODELQ') DEFSOPT(SHARED) MAXDEPTH(1000000) MSGDLVSQ(FIFO) DEFTYPE(PERMDYN)end****************************nohup runmqlsr -t tcp -p 1454 -m QMSYSB & //起监听runmqsc QMSYSBdis chstatus('SYSBtoSYSA') //显示running 通道状态正常dis chstatus('SYSBtoSYSA')dis chstatus('SYSAtoSYSB')1 : dis chstatus('SYSAtoSYSB')AMQ8417: Display Channel Status details.CHANNEL(SYSAtoSYSB) CHLTYPE(RCVR)CONNAME(10.0.1.45) CURRENTRQMNAME(QMSYSA) STATUS(RUNNING)SUBSTATE(RECEIVE)dis chstatus('SYSBtoSYSA')2 : dis chstatus('SYSBtoSYSA')AMQ8417: Display Channel Status details.CHANNEL(SYSBtoSYSA) CHLTYPE(SDR)CONNAME(10.0.1.45(1453)) CURRENTRQMNAME(QMSYSA) STATUS(RUNNING)SUBSTATE(MQGET) XMITQ(SYSB.XMITQ)EN)*********************************************修改队列管理器中的字符集命令：首先打开命令行窗口。
IBM DB2 for i 7.1 用户指南说明书
DB2 for i:7.1 OverviewOn April 13, 2010, IBM announced IBM i 7.1, a new version of the popular operating system that includes DB2 for i. The DB2 for i 7.1 and additional DB2 related product enhancements cover a broad range of new function, including:•Enhanced SQL and XML standards support, providing productivity for developers and more flexible integration between DB2 and XML data•New performance, auto tuning and management features•More applications will be able to leverage the performance and scalability of DB2’s SQL Query Engine (SQE)•Improved compatibility with other database management systems making porting and cross DBMS development easierNew XML Integration SupportDB2 for i 7.1 makes it much easier to develop applications that require data interchange between DB2 and XML documents. This new level of DB2 adds support for an XML data type. This includes defining it as a column, passing it as parameters to routines, using it as a variable, etc. It also includes the ability to search these documents using the enhanced XML capability that exists in the OmniFind Text Search Server (5733-OMF) product.In addition, XML documents can be decomposed (shredded) into relational columns based on annotations within an XML Schema. An XML Schema Repository (XSR) is introduced to store information about an XML Schema to allow for document validation or decomposition. DB2 for i 7.1 also contains built-in publishing functions needed to generate XML from relational information. These XML publishing functions enable a developer to retrieve information from the relational database and generate/publish this information as a well formed XML document, including the elements, attributes, and comments typically found in an XML document. Column Level Encryption through new Field Procedure featureDB2 for i is also adding the ability to call a Field Procedure (exit routine) that can modify the contents and size of the data on insert, update and read operations . This capability can be used in many ways, but the most popular way will be to provide encryption or obfuscation of sensitive data. For example, adding an encryption/decryption field procedure to a specific column of a file would then cause that column to be encrypted with no application changes. At insert or update time, the fieldproc will encrypt the data, while at read time, the fieldproc would choose to decrypt. Third-party encryption plug-ins for the DB2 fieldproc support are available from Linoma Software and Patrick Townsend & Associates.Application Integration of Stored Procedure Result SetsApplications running natively on IBM i can now fully utilize the capabilities of DB2 stored procedures with the new embedded SQL support for result set consumption. Direct integration of stored procedure result set data allows host applications written in RPG and COBOL to take a leap forward with improved exploitation of stored procedures.SQL enhancements make porting databases easier!DB2 for i SQL enhancements in 7.1 make DB2 for i more compatible with other relational database management systems such as Oracle®. Enhancements such as MERGE, Array support, Global Variables, and the REPLACE option on CREATE allows for more powerful and efficient programming. The new “currently committed” setting allows an application to avoid update locks while still retrieving a consistent view of the data by having the database find the version of the data that has already been committed and not waiting for the current update to commit or roll back. Greater scalability will result from this reduction of lock wait conditions.Crank up performance without intervention with new Adaptive Query ProcessingAs with every release, 7.1 has delivered another significant set of performance enhancements for DB2. Adaptive Query Processing, or AQP, builds on top of the leading edge SQL Query Engine (SQE) to improve performance without requiring database administration.Adaptive Query Processing, built into DB2, enables the DB2 for i query optimizer to make real-time adjustments to its execution plan, such as changing the join order or utilizing a new index, while the SQL request is running. These adjustments are implemented without any disruption to the application or requiring any manual tuning effort!Traditional applications meet SQE!Many more applications will be able to leverage the significant performance boosts from use of the SQL Query Engine with its new support for select/omit logical files! Because of the heavy use of select/omit logical files in many IBM i applications, this is great news, as those applications can now benefit from the huge performance gains that SQE can bring without having to alter the applications!More Data Warehousing EnhancementsEncoded Vector Indexes (EVIs) are IBM patented indexing technology that has been included in DB2 for i for many years now and can significantly accelerate query performance in large complex query environments. EVIs have been enhanced to contain summary information, allowing for superior indexing strategies, particularly in a data warehousing environment. Combine DB2 for i 7.1 and the latest POWER7 processor based systems for great performance gains in a data warehouse or Business Intelligence environment. This combination has produced an 80% performance improvement (over a similar configuration on POWER6 and DB2 for i 6.1) in the certified SAP Business Intelligence Mixed Workload benchmark1 (see figure below).Solid support for Solid State Disk (SSD) DrivesIn addition to the ability to place tables on Solid State Disk (SSD), which has been in place since last year, a file can now be marked as in-memory to reduce the IO cost when reading that file. IBM i automatically moves most active data to SSDs, and clients and ISVs can easily optimize applications for SSD.Usability and Self Managing FeaturesFor the administrator, new tooling has been added to monitor long running operations such as an index build, a text search index build, ALTER TABLE and reorganize. The type of IO (Random or Sequential) done against a file is now available through the GUI, providing more information when making the determination about whether to place a certain file on SSD. Partitioned tables are available as an option in more environments in 7.1 as tables with identity columns and referential constraints can now be partitioned, which can benefit very large database environments in the areas of growth or management (e.g., backups).DB2 Web Query continues to evolveThe popular DB2 Web Query for i product is enhanced in the April time frame (this support also available in IBM i 5.4 and 6.1) to provide better security over metadata and allow users to change passwords from within the DB2 Web Query interface. Change management functions make it easier to deploy DB2 Web Query objects across development, test, and production environments.Additional Information on DB2 for i:DB2 for i Home Page:/systems/i/db2Note 1: SAP certified results are found at /benchmark。
DB2 Automation Tool v4.1 for z OS 自动化数据库维护指南说明书
Automated End-to-End Database MaintenanceOctober 2011Automated End-to-EndDatabase MaintenanceHow to reduce your DB2 workloadand improve data availability usingDB2 Automation Tool v4.1 for z/OSJennifer NelsonProduct Specialist / Product ManagerRocket Software, Inc.C ONTENTSList of Figures (iii)1Today’s data challenges (1)2Automating database maintenance is the solution (1)2.1Conditional Maintenance (2)2.2Generate utility JCL automatically (4)2.3Using the DB2 administrative task scheduler to automatically executeutility JCL (8)3Evolve your strategy with an intelligent solution (12)References (xiii)L IST OF F IGURESFigure 1: Exceptions enable you to find what objects to include (2)Figure 2: Example - REORG exceptions (3)Figure 3: Integrating custom exceptions (4)Figure 4: Job Profiles are comprised of objects, exceptions and utilities (5)Figure 5: Build your Job Profile in batch or online (5)Figure 6: Automatically generating utility JCL (6)Figure 7: Result of batch Job Profile (6)Figure 8: Objects that match the exceptions are written to the TRIGGERS DD (7)Figure 9: Generated utility JCL contains only objects that match the exceptions (7)Figure 10: All utilities in the Utility Profile are generated into the utility JCL (8)Figure 11: DB2 Administrative Task Scheduler accessible via DB2 Automation Tool v4.1 (9)Figure 12: Adding JCL job to the scheduler (9)Figure 13: Generating JCL in DB2 Automation Tool and adding to Scheduler (10)Figure 14: Execute JCL at a specific time or during an interval (10)Figure 15: Utility JCL can also be added to the Scheduler (11)Figure 16: Schedule the resultant utility JCL with different parameters (12)1 Today’s data challengesAccording to industry experts, the amount of data is exploding in structured data, replicated data and unstructured data – all of which can be stored in a database such as DB2 forz/OS The amount of data stored in DB2 for z/OS is more than doubling every two years..At the same time, most DB2 for z/OS applications are global non-stop, requiring almost100% accessibility. These circumstances place heavy demands on the amount of timerequired to execute critical maintenance that, if not done, affects the performance of yourbusiness critical applications and your bottom line.Add to the challenge of exponential data growth, the current economic environment isshrinking the operational budgets of most companies. You need to justify each and everysoftware purchase. Companies are often operating with reduced IT staff dealing with anincreased workload. Maintaining optimal performance of your databases is as important to data application availability as ensuring a network remains online and available for non-stop business use. Database maintenance can be more time-consuming than you mightthink. Consider the following questions as they apply to your database maintenance:•Is your database maintenance routine? Routine database maintenance doesn’t mean that it is easy, quick or cost-effective.•Do you run a third party application? Often times, packaged vendor JCL is not optimized or timely. For example, your vendor JCL could use syntax that does notfollow best practices or routinely collect statistics, take an image copy or performREORGs on objects whether they’re needed or not.•What if an object doesn’t need specific maintenance, such as an image copy? Do you take one anyway just in case?•What if an object needs maintenance, but is either being skipped for maintenance or has the wrong maintenance run? Do you run the utility now, or do you wait untillater?•Will you be able to fit all of your critical maintenance jobs within the batch window?What happens if objects don’t have maintenance applied?•Is your maintenance process automated? How much time and effort is required by your staff?• Are there situations that require your DBA staff to interrupt other tasks to intervene and manage the maintenance window?2 Automating database maintenance is thesolutionCreating a strategy for running routine maintenance and automating that maintenance can help your DBA staff focus on more business critical tasks, while application data remainsavailable for business. The IBM DB2 Automation Tool for z/OS provides the solution toyour data maintenance challenges and moreover lets you have control of your objects,data availability and applications. It determines which objects need what maintenancewhen according to your business specifications. Using exception criteria to detect whichobjects or group of objects need what maintenance is an efficient way to locate only those objects in need; you no longer need to run packaged vendor utility JCL. With the DB2Automation Tool v4.1, exception jobs can be automatically run at intervals you determineby integrating with the DB2 Administrative Task Scheduler. By scheduling the exceptionjob to execute automatically at regular intervals to evaluate your objects, you will alwaysknow what objects need which maintenance. And, you will be able to automate executingthe utility jobs, eliminating any unnecessary DBA intervention. This paper will help anyone who is familiar with the DB2 Automation Tool learn how to use the DB2 AdministrativeTask Scheduler to fully automate database maintenance.2.1 Conditional MaintenanceWith over 180 exceptions, DB2 Automation Tool can help you find just those objects thatneed maintenance. Do you need to trigger an image copy only when certain conditionsoccur? Or do you need to trigger a reorg with a combination of conditions? The DB2Automation Tool Exceptions Profiles can help. Exception Profiles are simply a re-usablecollection of exceptions, or conditions, against which to evaluate objects. They can be used with any set of objects, called Object Profiles, to generate any kind of utility JCL.For example, the real time stats exceptions CLUSTERSENS and SCANACCESS can beused to evaluate when a REORG should take place. In the screen shot below, the column S contains an ‘A’ for the SCANACCESS exception, which is an AND condition. This means that both the CLUSTERSENS condition and the SCANACCESS condition must be metbefore the object will be included in the utility.Figure 1: Exceptions enable you to find what objects to include.Scrolling right will display an explanation of the exceptions, as shown below. With acomplete description of the exception conditions, you can make a comprehensive decision on which exceptions to set to trigger just the objects you need.Figure 2: Example - REORG exceptions.Select any combination of DB2 catalog statistics, SYSCOPY information, MVS catalog statistics, DB2 display status information or real time exception conditions to trigger just the objects that meet those criteria. Optionally, you can have the DB2 Automation Tool run a RUNSTATS utility to evaluate your object using current statistics. You can elect to store the new statistics by setting UPDATE to ALL, ACCESSPATH, or SPACE. You can also set UPDATE to NONE to enable only the Exception Profile to evaluate current statistics but not store those values in the catalog.The Automation Tool is also flexible enough to allow you to supply input that is not defined in the product. This can be a user-supplied formula, user-written REXX exec, assembled load module or stored procedure with your customized exceptions to evaluate against your objects. You provide the load module name, REXX exec name, or stored procedure name you want to execute as shown in the example below. The load module or REXX exec is loaded into memory once and called for each object within the associated Object Profile. The stored procedure can be called in three different ways, depending on what you want it to do and how you want it to process your objects. You may elect to process your stored procedure either before the DB2 Automation Tool exception criteria evaluation occurs, as the criteria and objects are being evaluated, or after the evaluation has completed. Enter your stored procedure name in the appropriate field where you want the DB2 AutomationTool to run the stored procedure.Figure 3: Integrating custom exceptions.Combine your own exceptions in a load module, REXX exec or stored procedure with any of the exceptions provided by DB2 Automation Tool to meet your needs. This level ofscalability and flexibility lets you tailor the DB2 Automation Tool to meet your company’sneeds.2.2 Generate utility JCL automaticallyThe DB2 Automation Tool can help with the frequency of when your objects are evaluated whether it’s once a week or daily for your business-critical objects. The DB2 AutomationTool can be executed as needed or placed in a scheduler to be executed at regularintervals with no intervention from the DBA.To generate utility JCL, a Job Profile must contain at least one Object Profile, which is a re-usable list of objects, and one Utility Profile, which is a list of utilities for which you want to generate JCL. This enables you to generate a utility for all objects within an Object Profile.But to conditionally generate utility JCL for only objects that meet or exceed exceptioncriteria, an Exception Profile must be added to the Job Profile. Using this method, you can easily generate utility JCL for only those objects that need maintenance, enabling you tosave on CPU consumption and reducing the amount of elapsed time required to runmaintenance, thus improving data availability.If, for example, you must take an image copy each week for just those objects that need it, you can re-run the Job Profile that contains the exception criterion for running an imagecopy and see which objects are generated into the utility JCL. That utility JCL can then be added to a job scheduler, such as the DB2 administrative task scheduler, to run during the next maintenance window.In this example below, the Job Profile contains three pieces of information: the ObjectProfile called “Reorg Avoidance Objs”, a set of utilities and corresponding options in aUtility Profile called “Reorg Avoidance Util”, and a set of exception criteria in an Exception Profile called “Reorg Avoidance Excp”.Figure 4: Job Profiles are comprised of objects, exceptions and utilities.In this example, the Job Profile “Batch Evaluation” is being generated to run in batch mode. This means that each time this Job Profile is “built”, a batch job will be created. This batch job, called REORGB, will evaluate the objects in the Object Profile against the exception criteria in the Exception Profile. Any object that matches the exceptions will be generatedinto the utility JCL, which is also created when running this batch job.Figure 5: Build your Job Profile in batch or online.The second pop-up window enables you to specify the PDS data set and member nameinto which the resultant utility JCL will be generated.Figure 6: Automatically generating utility JCL.The JCL to evaluate the objects is presented in an ISPF edit panel to review. It is this JCL that you can add to a scheduler to execute at regular intervals. By running this batch JCL job regularly, you can be sure that only those objects that need a REORG run will be included.Figure 7: Result of batch Job Profile.When the JCL is executed, DB2 Automation Tool evaluates the objects against the exceptions defined in the Exception Profile. In the screen shot below, the TRIGGERS DD in the job output lists the exceptions defined in the Exception Profile, and lists those objects that met or exceeded the defined exception. In this example, the tablespace AUOCOPY met both the CLUSTERSENS exception and the SCANACCESS exception and will therefore be included in the utility JCL.Figure 8: Objects that match the exceptions are written to the TRIGGERS DD.As a result, the utility JCL is built with the tablespace AUOCOPY included in the utility JCL.First, a RUNSTATS utility is generated, as shown in the screen shot below.Figure 9: Generated utility JCL contains only objects that match the exceptions.Further down in the utility JCL, the REORG TABLESPACE utility is generated and showstablespace AUOCOPY included in the REORG TABLESPACE utility as well. The utilitiesRUNSTATS and REORG TABLESPACE were inlcuded in the Utility Profile. Anycombination of the available DB2 utilities can be generated.Figure 10: All utilities in the Utility Profile are generated into the utility JCL.The resultant utility JCL can then be executed on demand, or added to a job scheduler to run during a specific batch window. Job Profiles can be created to build utility JCL fortaking image copies, for updating statistics, for running REORGs or for any other routinemaintenance your company requires.By running a Job Profile at regular intervals to look for objects that need maintenance and to generate utility JCL, routine database maintenance can be much less manual and error-prone and allow your DBA staff to pursue other tasks.2.3 Using the DB2 administrative task scheduler toautomatically execute utility JCLDo you have a job scheduler to run the Job Profiles or the resultant utility JCL in batch? Or do you manually run the utility JCL as needed? The generated utility jobs created by DB2 Automation Tool can be manually added to any job scheduler by the user. But if you arerunning on DB2 v8 or higher, you can now integrate DB2 Automation Tool v4.1 with theDB2 Administrative Task Scheduler and automatically execute any JCL. By selectingoption 12 from the main DB2 Automation Tool menu, you can invoke the Db2administrative task scheduler to add a job to the scheduler.Figure 11: DB2 Administrative Task Scheduler accessible via DB2 Automation Tool v4.1.From the DB2 Admin Task Scheduler panel, you can create a new batch job to execute during a specific time interval, view the results of a task that has already run, update atask, or delete a task.Figure 12: Adding JCL job to the scheduler.The benefit of integrating with the DB2 administrative task scheduler is when you buildyour Job Profile as shown in the screen shot below.Figure 13: Generating JCL in DB2 Automation Tool and adding to Scheduler.When building your Job Profile that evaluates exceptions and generates maintenance utility JCL, the options ‘Schedule Job’ and ‘Update Options’ enables you to add your Job Profile to the DB2 administrative task scheduler so that it will run at regular intervals, or just once; which ever method you choose.In the example below, the Job Profile “Batch Evaluation” is being added to the DB2 administrative task scheduler. The options presented on this panel are specific to the DB2 administrative task scheduler, and their descriptions can be found in the DB2 Administration Guide.The fields Begin Timestamp and End Timestampe enable you to define the exact time you want to execute this JCL. However, since we want this Job Profile to run at regular intervals, the Interval Options have been defined instead. This batch JCL will execute every Saturday of the month throughout the year at 12 noon to evaluate the objects in the corresponding Object Profile that need a RUNSTATS and a REORG run.Figure 14: Execute JCL at a specific time or during an interval.The values in the Point in Time fields are:Position 1: Minute. Valid values are 0 through 59.Position 2: Hour. Valid values are 1 through 23.Position 3: Day of the Month. Valid values are 1-31.Position 4: Month. Valid values are 1 through 12, where 1 is January. Three-character strings are also allowed, i.e., JAN, FEB, etc.Position 5: Day of the week. Valid values are 0 through 7, where 0 or 7 is Sunday. Three-character strings are also allowed, i.e., SUN, SAT, etc.Ranges and lists are allowed. Ranges are simply two values separated by a hyphen, and mean all values are inclusive. Lists are simply two or more values separated by a comma, and mean only those values are included.When you have completed adding the Job Profile to the DB2 administrative task scheduler, you can then add the utility JCL jobs to the DB2 administrative task scheduler, too. You will see the same 2 options as before, ‘Schedule Job’ and ‘Update Options’.Figure 15: Utility JCL can also be added to the Scheduler.When the panel Schedule DB2 Admin Task is displayed, you will specify when the resultant maintenance utility JCL should be run. Remember that the previous DB2 admin task panel enabled you to define when to run the Job Profile that evaluates your obejcts against the exception criteria and generates utility JCL. This time, you are defining when to run the actual utilities themselves.In this example, the generated maintenance utility jobs will run between the hours of midnight and 3 AM each Sunday. The field ‘Exectution Threads’ specifies that up to 5 jobs can run at once.Figure 16: Schedule the resultant utility JCL with different parameters.3 Evolve your strategy with an intelligentsolutionThe maintenance utility jobs that were generated by DB2 Autiomation Tool can now bescheduled and automatically run with DB2 Automation Tool’s interface to the DB2administrative task scheduler. Now, you can automate your database maintenancestrategy with DB2 Automation Tool from start to finish; from finding which objects to include to generating and submitting utility JCL. And your DBA intervention can be reduced to aminimum, allowing you to spend your time on other business critical tasks, while stillrunning important database maintenance in the background. The combination of DB2Automation Tool v4.1 for z/OS and the DB2 Administrative Scheduler allow you to fullyautomate your database maintenance to achieve maximum control and availability of your critical DB2 objects, while minimizing the system and staff resources that cost you time andmoney.R EFERENCESIBM DB2 Automation Tool for z/OS v4.1 User’s Guide, SC19-3494-00IBM DB2 Version 9.1 for z/OS Administration Guide, SC18-9840-00IBM DB2 10 for z/OS Administration Guide, SC19-2968-00®© Copyright IBM Corporation 2011IBM United States of AmericaProduced in the United States of AmericaAll Rights ReservedThe e-business logo, the eServer logo, IBM, the IBM logo,OS/390, zSeries, SecureWay, S/390, Tivoli, DB2, Lotus and WebSphere are trademarks of International Business Machines Corporation in the United States, other countries or both.Lotus, Lotus Discovery Server, Lotus QuickPlace, Lotus Notes, Domino, and Sametime are trademarks of Lotus Development Corporation and/or IBM Corporation.Java and all Java-based trademarks and logos are trademarks of Sun Microsystems, Inc. in the United States, other countries or both.Other company, product and service names may be trademarks or service marks of others.INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS PAPER “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or implied warranties in certain transactions, therefore, this statement may not apply to you. Information in this paper as to the availability of products (including portlets) was believed accurate as of the time of publication. IBM cannot guarantee that identified products (including portlets) will continue to be made available by their suppliers.This information could include technical inaccuracies or typographical errors. Changes may be made periodically to the information herein; these changes may be incorporated in subsequent versions of the paper. IBM may make improvements and/or changes in the product(s) and/or the program(s) described in this paper at any time without notice. Any references in this document to non-IBM Web sites are provided for convenience only and do not in any manner serve as an endorsement of those Web sites. The materials at those Web sites are not part of the materials for this IBM product and use of those Web sites is at your own risk.IBM may have patents or pending patent applications covering subject matter described in this document. The furnishing of this document does not give you any license to these patents. You can send license inquiries, in writing, to:IBM Director of LicensingIBM Corporation4205 South Miami BoulevardResearch Triangle Park, NC 27709 U.S.A.。
db2日常维护
本文主要介绍数据库管理员（DBA）在日常维护中如何形成自己的维护规范，一个完整的日常维护规范可以帮助DBA 理顺每天需要的操作，以便更好的监控和维护数据库，保证数据库的正常、安全、高效运行，防止一些错误重复发生。
“ DB2 日常维护指南, 第1 部分”具体包括检查管理服务器（DAS）是否启动、检查DB2 实例是否启动、查看表空间的状态是否正常、检查表的状态是否正常、查看磁盘空间是否有异常、查看存储管理软件、数据库备份、日志归档是否正常等。
通过明确日常中的每一天该做什么，形成完善的日常管理维护规范，从而更好对数据库进行维护。
检查管理服务器是否启动DB2 管理服务器（DAS ）是用于辅助DB2 实例上任务的一个控制点，如果想使用DB2 提供的图形化工具（如“控制中心”、“配置顾问程序”等）、工具目录数据库（ToolsCatalog ）、数据库发现等功能，则必须有一个正在运行的DAS 。
DAS 主要有以下功能：1.企业DB2 数据库实例的远程管理。
2.提供用于作业管理的工具，包括调度DB2 数据库管理器和操作系统命令脚本运行的能力，这些命令脚本是用户定义的。
3.使用“任务中心”来对DAS 的远程或本地做到定义作业的安排，查看已完成作业的结果以及执行其他管理任务。
4.与DB2 发现实用程序一起提供一种查找关于DB2 实例、数据库以及其他DB2 管理服务器配置信息的方法。
“配置助手”和“控制中心”使用此信息来简化和自动执行客户机与DB2 数据库的连接配置。
一个数据库物理服务器包括一个管理服务器（Admin Server ）以及一个或多个实例，每个实例( 数据库管理器database manager) 是一个逻辑服务器，可以拥有一个到多个数据库。
每个数据库服务器有且只能有一个DAS ，如果没有DAS ，需要发出dascrt 命令（这个命令只是在UNIX 平台有效，在Windows 平台需要使用“ db2admin create ”命令）或者db2admin create 命令（在windows 和UNIX 平台都可以使用）来创建一个DAS 。
DB2最新维护手册
DB2维护手册目录DB2维护手册 (1)一、DB2入门-数据库实例 (5)二、DB2日常维护日操作 (20)1、检查管理服务器是否启动 (21)2、检查DB2实例是否已经启动 (21)3、查看表空间状态是否正常 (21)4、查看表的状态 (22)5、查看磁盘空间 (23)6、检查存储管理软件是否正常 (23)7、检查数据库备份是否正常 (24)8、检查归档日志是否正确归档了 (24)9、查看缓冲池的命中率 (24)10、查看当前运行最频繁的SQL，其命中率是否正常 (24)11、查看当前连接的应用程序，有没有非法连接 (25)12、检查有没有死锁 (25)13、对表和索引进行RUNSTATS (25)14、检查表是否需要重组 (25)15、对需要重组的表进行重组 (26)三、DB2日常维护月操作 (27)1、查看DB2日志 (27)2、检查备份和日志是否都保存好了 (27)四、DB2日常维护季度操作 (27)1、通过快照监控器，查看系统性能如何 (27)2、数据库补丁级别 (28)五、注意事项 (28)1、不要删除活动日志文件 (28)2、注意交易日志存储空间 (28)3、按照系统的实际工作量配置日志空间 (29)4、设置正确数据库代码页 (29)5、检查许可证（L ICENSE）安装情况 (30)6、创建数据库前调整好系统时间 (30)7、不要随便执行CHOWN (CHMOD)–R（UNIX/L INUX） (30)8、在归档日志模式下使用LOAD记得加NONRECOVERABLE参数 (31)六、附：以脱机方式重组表 (31)七、附：索引重组 (32)八、收集和更新统计信息的准则 (35)九、附：使用CLP 捕获数据库运行状况快照 (39)十、IBM DB2 日常维护汇总 (41)十一、DB2常用命令集 (51)一、DB2入门-数据库实例在本文中，我使用DB2 来指代DB2 通用数据库V8.1 for UNIX、Linux 和Windows。
某工商局DB2Q复制配置维护手册范本
1、Q复制规划1.1主机和DB2的相关设置信息logging模式（归档日志模式）1.2Websphere MQ的相关配置信息1.3Q复制的配置信息1.4复制队列映射属性1.5通道测试cd /usr/mqm/samp/bin./amqsput SYSA.SENDQ QMSYSA./amqsget SYSB.RECVQ QMSYSB2、mq用户创建创建用户mqm和组mqm,并把组mqm加入到用户db2inst1和db2fenc1中。
3、mq software install解压缩mq软件，用smitty installp安装,创建大小50G的mqmvg 、mqmlv和文件系统mqm,挂载点/var/mqm，/var/mqm/log4、mq 队列和通道创建在dbsvr04上执行setclock dbsvr01进行与dbsvr01的时间同步，需要在/etc/hosts中添加dbsvr01和IP。
由于MQ的需要在dbsvr01和dbsvr04中/etc/hosts添加相互的IP和name 信息。
Service ip 和 service name 也要添加。
Dbsvr04:/etc/hosts:10.0.1.41 dbsvr0110.0.1.44 dbsvr0410.0.1.45 dbserver //hacmp 中service IPMQ测试：Dbsvr01:$ crtmqm -q venus.queue.managerWebSphere MQ queue manager created.Creating or replacing default objects for venus.queue.manager.Default objects statistics : 58 created. 0 replaced. 0 failed. Completing setup.Setup completed.$ strmqmWebSphere MQ queue manager 'venus.queue.manager' starting.5 log records accessed on queue manager 'venus.queue.manager' during the log replay phase.Log replay for queue manager 'venus.queue.manager' complete. Transaction manager state recovered for queue manager 'venus.queue.manager'.WebSphere MQ queue manager 'venus.queue.manager' started.$ runmqsc5724-H72 (C) Copyright IBM Corp. 1994, 2008. ALL RIGHTS RESERVED. Starting MQSC for queue manager venus.queue.manager.define qlocal (orange.queue)1 : define qlocal (orange.queue)AMQ8006: WebSphere MQ queue created.end2 : endOne MQSC command read.No commands have a syntax error.All valid MQSC commands were processed.$ cd /usr/mqm/samp/bin/$ ./amqsput ORANGE.QUEUESample AMQSPUT0 starttarget queue is ORANGE.QUEUEabcddsfsdsdfdsenddbsvr04:$ crtmqm -q venus.queue.managerWebSphere MQ queue manager created.Creating or replacing default objects for venus.queue.manager.Default objects statistics : 58 created. 0 replaced. 0 failed. Completing setup.Setup completed.$ $ strmqmWebSphere MQ queue manager 'venus.queue.manager' starting.5 log records accessed on queue manager 'venus.queue.manager' during the log replay phase.Log replay for queue manager 'venus.queue.manager' complete. Transaction manager state recovered for queue manager'venus.queue.manager'.WebSphere MQ queue manager 'venus.queue.manager' started.$ runmqsc5724-H72 (C) Copyright IBM Corp. 1994, 2008. ALL RIGHTS RESERVED. Starting MQSC for queue manager venus.queue.manager.define qlocal (orange.queue)1 : define qlocal (orange.queue)AMQ8006: WebSphere MQ queue created.:::end2 : endOne MQSC command read.No commands have a syntax error.All valid MQSC commands were processed.$ cd /usr/mqm/samp/bin$ ./amqsput ORANGE.QUEUESample AMQSPUT0 starttarget queue is ORANGE.QUEUEadsfsdfdsfsdfsdfsSample AMQSPUT0 end$$ ./amqsget ORANGE.QUEUESample AMQSGET0 startmessage <adsfsdfdsfsdfsdfs>dbsvr01:crtmqm -lc -d SYSA.XMITQ -u DEADLETTER -lp 20 -ls 10 -lf 10240 QMSYSA //创建MQ队列strmqm QMSYSA //起队列管理器endmqlsr -m QMSYSA //停队列管理器ps -ef | grep mq //查看队列管理器nohup runmqlsr -t tcp -p 1453 -m QMSYSA & //起监听ps –ef|grep lsr // 查看监听进程endmqm QMSYSA //停队列endmqlsr //停监听dltmqm QMSYSA //删除管理队列runmqsc QMSYSA //起MQ资源DEFINE QREMOTE('SYSA.SENDQ') RNAME('SYSB.RECVQ') RQMNAME('QMSYSB') XMITQ('SYSA.XMITQ')DEFINE QLOCAL('SYSA.XMITQ') USAGE(XMITQ) MAXDEPTH(1000000) DEFPSIST(YES)DEFINE QLOCAL('DEADLETTER') USAGE(NORMAL) MAXDEPTH(500000)ALTER QMGR DEADQ('DEADLETTER')DEFINE CHL ('SYSAtoSYSB') CHLTYPE(SDR) TRPTYPE(TCP) CONNAME('10.0.1.44(1454)') XMITQ('SYSA.XMITQ') DISCINT (0) BATCHSZ (200)START CHANNEL ('SYSAtoSYSB')DEFINE CHL ('SYSBtoSYSA') CHLTYPE(RCVR) TRPTYPE(TCP) BATCHSZ(200)DEFINE QLOCAL('SYSA.ADMINQ') MAXDEPTH(500000) DEFPSIST(YES)DEFINE QLOCAL('SYSA.RESTARTQ') MAXDEPTH(500000) DEFPSIST(YES)End*************************runmqsc QMSYSAdis chstatus('SYSAtoSYSB') //显示running 通道状态正常dis chstatus('SYSAtoSYSB')1 : dis chstatus('SYSBtoSYSA')AMQ8417: Display Channel Status details.CHANNEL(SYSBtoSYSA) CHLTYPE(RCVR)CONNAME(10.0.1.44) CURRENTRQMNAME(QMSYSB) STATUS(RUNNING)SUBSTATE(RECEIVE)dis chstatus('SYSBtoSYSA')2 : dis chstatus('SYSBtoSYSA')AMQ8417: Display Channel Status details.CHANNEL(SYSBtoSYSA) CHLTYPE(RCVR)CONNAME(10.0.1.44) CURRENTRQMNAME(QMSYSB) STATUS(RUNNING)SUBSTATE(RECEIVE)*************************runmqsc QMSYSBdis chstatus('SYSBtoSYSA')runmqchl -c SYSAtoSYSB -m QMSYSA //如果没有错误信息显示，表明该channel成功运行runmqchl -c SYSBtoSYSA -m QMSYSA //如果没有错误信息显示，表明该channel 成功运行********************************修改queue manager的CCSID:strmqmrunmqscdisplay qmgr // 检查当前queue manager的CCSID值alter qmgr ccsid(437)end*************************dbsvr04:crtmqm -lc -d SYSB.XMITQ -u DEADLETTER -lp 20 -ls 10 -lf 10240 QMSYSB strmqm QMSYSBnohup runmqlsr -t tcp -p 1454 -m QMSYSB &runmqsc QMSYSBDEFINE QLOCAL('SYSB.RECVQ') MAXDEPTH(1000000) DEFPSIST(YES)DEFINE QREMOTE('SYSA.ADMINQ') RNAME('SYSA.ADMINQ') RQMNAME('QMSYSA') XMITQ('SYSB.XMITQ')DEFINE QLOCAL('SYSB.XMITQ') MAXDEPTH(1000000) USAGE(XMITQ) DEFPSIST(YES)DEFINE QLOCAL('DEADLETTER') USAGE(NORMAL) MAXDEPTH(500000)ALTER QMGR DEADQ('DEADLETTER')DEFINE CHL ('SYSBtoSYSA') CHLTYPE(SDR) TRPTYPE(TCP) CONNAME('10.0.1.45(1453)') XMITQ('SYSB.XMITQ') DISCINT (0) BATCHSZ (200)START CHANNEL ('SYSBtoSYSA')DEFINE CHL ('SYSAtoSYSB') CHLTYPE(RCVR) TRPTYPE(TCP) BATCHSZ(200)DEFINE QMODEL('IBMQREP.SPILL.MODELQ') DEFSOPT(SHARED) MAXDEPTH(1000000) MSGDLVSQ(FIFO) DEFTYPE(PERMDYN)end****************************nohup runmqlsr -t tcp -p 1454 -m QMSYSB & //起监听runmqsc QMSYSBdis chstatus('SYSBtoSYSA') //显示running 通道状态正常dis chstatus('SYSBtoSYSA')dis chstatus('SYSAtoSYSB')1 : dis chstatus('SYSAtoSYSB')AMQ8417: Display Channel Status details.CHANNEL(SYSAtoSYSB) CHLTYPE(RCVR)CONNAME(10.0.1.45) CURRENTRQMNAME(QMSYSA) STATUS(RUNNING)SUBSTATE(RECEIVE)dis chstatus('SYSBtoSYSA')2 : dis chstatus('SYSBtoSYSA')AMQ8417: Display Channel Status details.CHANNEL(SYSBtoSYSA) CHLTYPE(SDR)CONNAME(10.0.1.45(1453)) CURRENTRQMNAME(QMSYSA) STATUS(RUNNING)SUBSTATE(MQGET) XMITQ(SYSB.XMITQ)EN)*********************************************修改队列管理器中的字符集命令：首先打开命令行窗口。
最新db2基础操作手册
16 db2命令交互
db2cmd
命令行交互模式，可以直接输入Db2命令，操作系统命令前加！ 命令行模式，可以直接识别操作系统命令，db2命令前加db2
执行脚本
Db2 –f myfile.clp 脚本中用--做注释
22 模式（schema）
模式是一组数据库对象的集合，他提供了数据库对象的逻 辑分类
如果不指定模式名，则用当前用户名 切换当前模式可以用set current schema xxx或set current sqlid xxx
23 系统模式
系统模式：
SYSIBM 基本的编目信息，不建议直接访问 SYSCAT 编目信息的只读视图，所有人具有查询权限，推荐的获取编目信 息的途径 SYSSTAT 编目信息可更新视图，可以影响优化器 SYSFUN 用户定义函数
配置数据库update db cfg for db_name using discover_db enable
14 连接到数据库
客户端可供选择方式之二
概要文件
15 连接到数据库
客户端可供选择方式之三
手工配置 编目节点
Catalog tcpip node node_name remote ip地址 Server 50000
17 命令行可执行的命令
18 命令行选项和获得帮助
命令行选项 Db2 list command options 获得帮助 Db2 ? Db2 ? Command Db2 ? Sqlnnnn Db2 ? db2nnnn
19 命令中心
可以在控制中心调用 图形化的命令行处理器，功能类似 特殊的功能
db2日志维护
db2⽇志维护1、查看数据库配置get db cfg for hdxtdb 查看数据库参数db2 => get db cfg for hdxtdbDatabase Configuration for Database hdxtdb数据库 hdxtdb的数据库配置Database configuration release level数据库配置发⾏版级别= 0x0a00Database release level 数据库发⾏版级别 = 0x0a00Database territory 数据库地域 = cnDatabase code page 数据库代码页 = 1386Database code set 数据库代码集 = gbkDatabase country/region code 数据库国家／地区代码 = 86Database collating sequence 数据库整理顺序 = UNIQUEAlternate collating sequence (ALT_COLLATE) =备⽤整理顺序Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE动态 SQL 查询管理Discovery support for this database (DISCOVER_DB) = ENABLE对此数据库的发现⽀持 (DISCOVER_DB)Default query optimization class (DFT_QUERYOPT) = 5缺省查询优化类 (DFT_QUERYOPT)Degree of parallelism 并⾏度 (DFT_DEGREE) = 1Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO 在算术异常时继续 Default refresh age (DFT_REFRESH_AGE) = 0 缺省刷新有效期Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM缺省维护的选项（DFT_MTTB_TYPES）的表类型Number of frequent values retained (NUM_FREQVALUES) = 10保留的⾼频值的数⽬ Number of quantiles retained (NUM_QUANTILES) = 20保留的分位点数⽬Backup pending = NO备份暂挂Database is consistent = NO数据库是⼀致的Rollforward pending = NO前滚暂挂Restore pending = NO复原暂挂Multi-page file allocation enabled = YES启⽤的多页⽂件分配Log retain for recovery status 恢复状态的⽇志保留 = NOUser exit for logging status = NO⽇志记录状态的⽤户出⼝Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60Data Links 标记到期时间间隔（秒）Data Links Write Token Init Expiry Intvl(DL_WT_IEXPINT) = 60Data Links 写标记初始时间间隔Data Links Number of Copies (DL_NUM_COPIES) = 1副本的 Data Links 数⽬Data Links Time after Drop (days) (DL_TIME_DROP) = 1删除后的 Data Links 时间（天数）Data Links Token in Uppercase (DL_UPPER) = NO⼤写的 Data Links 标记Data Links Token Algorithm (DL_TOKEN) = MAC0Data Links 标记算法Database heap (4KB) 数据库堆（4KB (DBHEAP) = 1200Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC数据库共享内存⼤⼩（4KB）Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4) ⽬录⾼速缓存⼤⼩（4KB）Log buffer size (4KB) (LOGBUFSZ) = 512⽇志缓冲区⼤⼩（4KB）Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000实⽤程序堆⼤⼩（4KB）Buffer pool size (pages) (BUFFPAGE) = 1000缓冲池⼤⼩（页）Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000扩充存储段⼤⼩（4KB）Number of extended storage segments (NUM_ESTORE_SEGS) = 0扩充存储段的数⽬Max storage for lock list (4KB) (LOCKLIST) = 40960锁定列表的最⼤存储量（4KBMax size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 30000应⽤程序组内存集的最⼤⼤⼩（4KB）Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70应⽤程序组堆的内存百分⽐Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 128最⼤应⽤程序控制堆⼤⼩（4KB）Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES) 共享排序的排序堆域值（4KB）Sort list heap (4KB) (SORTHEAP) = 4096排序列表堆（4KB）SQL statement heap (4KB) SQL 语句堆（4KB） (STMTHEAP) = 2048Default application heap (4KB) (APPLHEAPSZ) = 4096缺省应⽤程序堆（4KB）Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8) 程序包⾼速缓存⼤⼩（4KB）Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384统计信息堆⼤⼩（4KB）Interval for checking deadlock (ms) (DLCHKTIME) = 10000检查死锁的时间间隔（毫秒）Percent. of lock lists per application (MAXLOCKS) = 30每个应⽤程序的锁定百分⽐列表Lock timeout (sec) (LOCKTIMEOUT) = 60锁定超时（秒）Changed pages threshold (CHNGPGS_THRESH) = 60更改的页阈值Number of asynchronous page cleaners (NUM_IOCLEANERS) = 2异步页清除程序的数⽬Number of I/O servers (NUM_IOSERVERS) = 7I/O 服务器的数⽬Index sort flag (INDEXSORT) = YES索引排序标志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 活动应⽤程序的最⼤数⽬Average number of active applications (AVG_APPLS) = 1活动应⽤程序的平均数⽬Max DB files open per application (MAXFILOP) = 64每个应⽤程序的最⼤打开数据库⽂件数Log file size (4KB) (LOGFILSIZ) = 20000⽇志⽂件⼤⼩（4KB）Number of primary log files (LOGPRIMARY) = 30主⽇志⽂件的数⽬Number of secondary log files (LOGSECOND) = 20辅助⽇志⽂件的数⽬Changed path to log files (NEWLOGPATH) =已更改的⾄⽇志⽂件的路径Path to log files ⽇志⽂件路径 = /home/hdxtdb/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磁盘上已满的块⽇志Percent of max active log space by transaction(MAX_LOG) = 0事务使⽤的最⼤活动⽇志空间的百分⽐Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0 1 个活动 UOW 的活动⽇志⽂件的数⽬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 HADR 数据库⾓⾊HADR local host name (HADR_LOCAL_HOST) =HADR 本地主机名HADR local service name (HADR_LOCAL_SVC) =HADR 本地服务名称HADR remote host name (HADR_REMOTE_HOST) =HADR 远程主机名HADR remote service name (HADR_REMOTE_SVC) =HADR 远程服务名称HADR instance name of remote server (HADR_REMOTE_INST) =远程服务器的 HADR 实例名HADR timeout value (HADR_TIMEOUT) = 120 HADR 超时值 (HADR_TIMEOUT)HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC HADR ⽇志写同步⽅式 (HADR_SYNCMODE) First log archive method (LOGARCHMETH1) = OFF第⼀个⽇志归档⽅法Options for logarchmeth1 (LOGARCHOPT1) = logarchmeth1 的选项Second log archive method (LOGARCHMETH2) = OFF第⼆个⽇志归档⽅法Options for logarchmeth2 (LOGARCHOPT2) = Logarchmeth2 的选项Failover log archive path (FAILARCHPATH) =故障转移⽇志归档路径Number of log archive retries on error (NUMARCHRETRY) = 5错误时重试⽇志归档次数Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20⽇志归档重试延迟（秒）Vendor options (VENDOROPT) =供应商选项 (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) = 1loadrec 会话的缺省数⽬Number of database backups to retain (NUM_DB_BACKUPS) = 12要保留的数据库备份的数⽬Recovery history retention (days) (REC_HIS_RETENTN) = 366恢复历史保留时间（天数）TSM management class TSM 管理类 (TSM_MGMTCLASS) =TSM node name TSM 节点名 (TSM_NODENAME) =TSM owner TSM 所有者 (TSM_OWNER) =TSM password TSM 密码 (TSM_PASSWORD) =Automatic maintenance ⾃动维护 (AUTO_MAINT) = OFFAutomatic database backup (AUTO_DB_BACKUP) = OFF⾃动数据库备份Automatic table maintenance (AUTO_TBL_MAINT) = OFF⾃动表维护Automatic runstats (AUTO_RUNSTATS) = OFF⾃动 runstatsAutomatic statistics profiling (AUTO_STATS_PROF) = OFF⾃动统计信息概要分析Automatic profile updates (AUTO_PROF_UPD) = OFF⾃动概要⽂件更新Automatic reorganization (AUTO_REORG) = OFF⾃动重组db2 =>修改对应的参数，使⽤命令：update db cfg [fordbname] using <参数名> <参数值>db2 => update db cfg using logretain onDB20000I UPDATE DATABASE CONFIGURATION命令成功完成。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
DB2维护手册目录DB2维护手册 (1)一、DB2入门-数据库实例 (5)二、DB2日常维护日操作 (20)1、检查管理服务器是否启动 (21)2、检查DB2实例是否已经启动 (21)3、查看表空间状态是否正常 (21)4、查看表的状态 (22)5、查看磁盘空间 (23)6、检查存储管理软件是否正常 (23)7、检查数据库备份是否正常 (24)8、检查归档日志是否正确归档了 (24)9、查看缓冲池的命中率 (24)10、查看当前运行最频繁的SQL，其命中率是否正常 (24)11、查看当前连接的应用程序，有没有非法连接 (25)12、检查有没有死锁 (25)13、对表和索引进行RUNSTATS (25)14、检查表是否需要重组 (25)15、对需要重组的表进行重组 (26)三、DB2日常维护月操作 (27)1、查看DB2日志 (27)2、检查备份和日志是否都保存好了 (27)四、DB2日常维护季度操作 (27)1、通过快照监控器，查看系统性能如何 (27)2、数据库补丁级别 (28)五、注意事项 (28)1、不要删除活动日志文件 (28)2、注意交易日志存储空间 (28)3、按照系统的实际工作量配置日志空间 (29)4、设置正确数据库代码页 (29)5、检查许可证（L ICENSE）安装情况 (30)6、创建数据库前调整好系统时间 (30)7、不要随便执行CHOWN (CHMOD)–R（UNIX/L INUX） (30)8、在归档日志模式下使用LOAD记得加NONRECOVERABLE参数 (31)六、附：以脱机方式重组表 (31)七、附：索引重组 (32)八、收集和更新统计信息的准则 (35)九、附：使用CLP 捕获数据库运行状况快照 (39)十、IBM DB2 日常维护汇总 (41)十一、DB2常用命令集 (51)一、DB2入门-数据库实例在本文中，我使用DB2 来指代DB2 通用数据库V8.1 for UNIX、Linux 和Windows。
在DB2 中，实例提供了独立的环境，可在其中创建数据库对象并针对这些对象运行应用程序。
由于这些环境是独立的，所以两个或更多的单独实例的对象可以有相同的名称。
在图1中，有一个名为“MYDB2”的数据库，它与实例“DB2”相关；还有一个同名的数据库，但它与实例“MyInst”相关。
实例允许用户具有用于生产、测试和开发目的的不同环境。
以下是一些与实例相关的命令：通过从命令行处理器（CLP）发出下列命令来显式地创建实例：db2icrt <instance name>在Windows 平台上首次安装DB2 时，缺省情况下会创建一个称为“DB2”的实例。
在UNIX 环境中，如果您选择创建实例，那么缺省的实例名称为“db2inst1”。
要删除实例，请使用这条命令：db2idrop <instance name>要启动实例，请使用这条命令：db2start要停止实例，请使用这条命令：db2stop创建了实例后，就会生成到DB2 代码的链接。
对于图1中的示例而言，已创建了实例“DB2”和实例“MyInst”，两者都链接至同一段DB2 代码。
V8 之前，曾有两种实例类型：“DB2”类型和“DAS”类型。
V8 中已经除去了DAS 类型的实例；它的功能已经被作为服务器进程集成到产品中了。
和许多其它关系数据库管理系统（RDBMS）一样，DB2 使用不同的机制来管理、监视和控制DB2 系统的行为。
这些机制包括：环境变量环境变量是在操作系统级别上定义的变量。
例如，在Windows 2000® 平台中，您可以为一个变量创建新的项，或者通过选择Control Panel --> System --> Advanced Tab --> Environment Variables 来编辑现有变量的值。
在UNIX 中，您通常可以将安装了DB2 之后所提供的脚本db2profile （Bourne 或Korn shell）或db2cshrc （C shell）添加到.login 或.profile UNIX 初始化文件中。
db2profile/db2cshrc 文件包含了“export”UNIX 命令，这些命令能确保每次调用shell 时就传递一个UNIX 环境变量。
最常使用的DB2 环境变量是“DB2INSTANCE”变量。
该环境变量允许您指定当前活动实例，所有命令都将应用于该实例。
对于本例而言，如果将DB2INSTANCE 设置成“MyInst”，那么发出命令“createdatabase mydb ”会创建出与实例“MyInst”相关的数据库。
但是如果您想创建与实例“DB2”相关的该数据库，那么首先您必须将DB2INSTANCE 变量的值更改成“DB2”。
使用控制面板（在Windows 中）／db2profile（在UNIX 中）来设置环境变量的值，将保证您下次打开窗口／会话时该值不变；但是，如果您想在给定的窗口／会话中临时更改该值，那么在Windows 中您可以使用操作系统的“set ”命令，或者在UNIX 中使用“export”命令。
例如，在Windows 平台中，下面这个命令：set DB2INSTANCE=DB2会将环境变量DB2INSTANCE 的值设置成“DB2”。
使用set 命令时常犯的错误是在等号（=）前后留有空格。
绝对不能有空格！要查看该变量的当前设置，您可以使用下面三个方法中的任何一个：echo %DB2INSTANCE% (Windows only)set DB2INSTANCEdb2 get instance要获取系统中您可以执行的所有可用实例的列表，请发出下面这个命令：db2ilistDB2 概要注册表在Windows 平台上使用DB2 时，单词“注册表（Registry）”的使用往往会造成混淆。
DB2 概要注册表（Profile Registry）变量与Windows 注册表变量没有任何关系。
过去，对某些环境变量进行更改时，会要求用户重新引导机器。
如果所有DB2 变量都已被定义成环境变量，那么对变量值进行更改将会很麻烦。
因此，当时决定将大多数DB2 变量组成一个专门的DB2 注册表，在其中进行更改无需重新引导机器。
DB2 概要注册表分成四类；但是下面这两个是最常用的：DB2 全局级概要注册表（Global-Level Profile Registry）DB2 实例级概要注册表（Instance-Level Profile Registry）两者的主要区别（您从它们的名称就可以看出）是变量适用的级别。
全局级概要注册表变量的值适用于所有实例。
因此，可以从图中看到，该注册表画在了两个实例框的外面。
实例级概要注册表变量的值适用于特定的实例。
因此，您可以看到图中那两个实例每个内部都有单独的“Instance-Level Profile Registry”框。
要查看所设置的当前注册表变量，请从CLP 发出下面这个命令：db2set -all您可能会得到类似下面这样的输出：[i] DB2INSTPROF=C:\PROGRAM FILES\SQLLIB[g] DB2SYSTEM=RAULCHONG正如您可能已经猜测到的那样，[i] 表明该变量是在实例级上定义的，而[g] 表明它是在全局级上定义的。
以下是一些与变量相关的命令：要查看可以在DB2 中进行定义的所有注册表变量，请使用这个命令：db2set -lr要在全局级上设置特定变量（在这个示例中为DB2INSTPROF ）的值，请使用：db2set DB2INSTPROF="C:\PROGRAM FILES\SQLLIB" -g要在实例级上为实例“MyInst”设置变量，请使用：db2set DB2INSTPROF="C:\MY FILES\SQLLIB" -i MyInst请注意上面的示例，在两个级别（实例级和全局级）上设置了同一个变量。
当注册表变量在不同级别上进行定义时，DB2 总是会选择最低级别的值；在本例中，它将选择实例级的值。
db2set 命令和前一节中的set 命令一样，等号（=）前后不该留有空格。
某些注册表变量为了使更改生效，要求您停止和启动实例（db2stop/db2start ）。
另一些注册表变量则没有这个需求。
为了安全起见，建议您在对注册表变量作了更改后总是停止和启动实例。
配置参数配置参数是在两个不同的级别（实例级和数据库级）上定义的。
每个级别上的变量都是不同的（不象注册表变量那样可以在不同级别上定义相同的变量）。
在实例级上，变量被存储在数据库管理器配置文件（dbm cfg）中；对这些变量所作的更改会影响与该实例相关的“所有”数据库，这就是图中显示了dbm cfg 框（每个实例定义了一个），并且该框都显示在数据库框外面的原因。
以下是一些与配置参数相关的命令：要从CLP 查看dbm cfg 的内容，请发出下面这个命令：db2 get dbm cfg要更新特定变量的值，请发出下面这个命令：db2 update dbm cfg using <parameter> <value>例如：db2 update dbm cfg using INTRA_PARALLEL YESV8 之前，对“所有”dbm cfg 变量所作的更改都要求您停止和启动实例（db2stop/db2start ）。
现在，V8 中有大约40％的参数是“可联机配置的”参数；也就是说，不再需要停止和启动实例了。
请参考“DB2 V8 管理指南（DB2 V8 Administration Guide）”以获取更多详细信息。
在数据库级别上，变量被存储在数据库配置文件（db cfg）中；对这些变量所作的更改会影响特定的数据库。
从图1中，您可以看到每个定义的数据库内部都有一个db cfg 框。
要查看db cfg 的内容，请从CLP 发出下面这个命令：db2 get db cfg for <dbname>例如：db2 get db cfg for mydb1要更新特定变量的值，请发出下面这个命令：db2 update db cfg for <dbname> using <parameter> <value>例如：db2 update db cfg for mydb1 using MINCOMMIT 3V8 之前，对“所有”db cfg 变量进行更改都要求您断开与该数据库的所有连接；然后，在进行第一个新连接时，这些更改将生效。
V8 中有大约50％的参数可以进行联机配置；也就是说，无需为了使更改生效而断开到数据库的所有连接。