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。
- 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％的参数可以进行联机配置；也就是说，无需为了使更改生效而断开到数据库的所有连接。