DB2日常维护手册
IBMdb2-日常维护技巧
IBMdb2-日常维护技巧IBM db2-日常维护技巧DB2是IBM一种分布式数据库解决方案。
说简单点：DB2就是IBM开发的一种大型关系型数据库平台。
它支持多用户或应用程序在同一条SQL 语句中查询不同database甚至不同DBMS中的数据。
下面是小编整理的IBM db2-日常维护技巧，欢迎大家阅读。
IBM db2-日常维护技巧1.DB2产品的级别有那些?企业版的NTERPRISEEDITION工作组版WORKGROUPEDITION企业扩展版ENTERPRISEEXTENDEDEDITION个人版的PERSONALEDITION卫星版的SATELLITEEDITION微型版的EVERYPLACE2.可以连接到DB2数据库的产品有哪些?DB2客户端DB2CONNECTDB2DATAPROPAGATORDB2NET.DATADB2DATAJOINERDB2RELATIONALCONNECTWEBSPHERE应用服务器等3.DB2支持的通讯协议有哪些?TCP/IPNETBIOSAPPGIPX/SPXNAMEPIPE等4.DB2客户端产品有哪些?DB2运行时间客户端DB2RUNTIMECLIENTDB2管理客户端DB2ADMINISTRATIONCLIENTDB2应用程序开发客户端DB2APPLICATIONDEVELOPMENTCLIENTDB2瘦客户端DB2THINCLIENT5.一个数据库是否可以安装在多个数据库服务器上?可以6.从哪个版本后存储过程可以用SQL语句来创建?7.1版后7.DB2提供哪些关系扩展器?文本扩展器TEXTEXTENDER图象扩展器IMAGEEXTENDER音频扩展器AUDIOEXTENDER视频扩展器VIDEOEXTENDER空间数据扩展器SPATIALEXTENDERXML扩展器XMLEXTENDER网络搜索扩展器NET.SEARCHEXTENDER8.WINDOWS和OS/2环境下的DB2安装目录结构?用SETUP.EXE来安装SQLLIB安装的根目录,包括README文件SQLLIBADSM包含ADSTAR分布式存储管理器文件SQLLIBBIN包含DB2工具的可执行文件SQLLIBBND包含DB2工具的绑定文件SQLLIBCC包含运行控制中心所需的文件SQLLIBCFG包含默认的系统配置文件SQLLIBCONV包含代码页转换表文件SQLLIBDB2默认的实例目录SQLLIBDB2DAS00缺省的DB2管理服务器目录SQLLIBDOC包含DB2联机手册SQLLIBFUNCTION默认的用户自定义函数目录SQLLIBFUNCTIONUNFENCED默认的非隔离用户自定义函授目录SQLLIBHELP联机帮助文件SQLLIBJAVADB2所需的JAVA类库JAVA12包含JDK1.2的支持程序SQLLIBMISC包含HTML搜索服务器文件SQLLIBMSGPRIME包含信息文件SQLLIBQP包含QUERYPATROLLER的客户端文件SQLLIBSAMPLES包含样例程序和样例脚本SQLLIBSPMLOG包含DB2同步点管理器日志文件SQLLIBTHNSETUP包含瘦客户端安装文件9.UNIX和LINUX环境下的DB2安装目录结构?用DB2SETUP.EXE来安装安装的根目录下还将创建以下目录:README安装的根目录,包括README文件ADM包含系统管理工具文件ADSM包含ADSTAR分布式存储管理器文件BIN包含DB2工具的二进制可执行文件BND包含DB2工具的绑定文件CC包含运行控制中心所需的文件CFG包含默认的系统配置文件CONV包含代码页转换表文件DOC包含DB2联机手册FUNCTION默认的用户自定义函数目录FUNCTIONUNFENCED默认的非隔离用户自定义函授目录INSTALL包含安装程序INSTANCE包含实例脚本JAVADB2所需的JAVA类库LIBDB2库文件MAP包含DB2CONNECT使用的映射文件MISC包含HTML搜索服务器文件SAMPLES包含样例程序和样例脚本MSG$L包含DB2信息文件10.AIX下用哪个命令来安装DB2? INSTALLP命令11.同一操作系统下可以安装多个DB2数据库? 可以的12.如何停止实例?DB2STOP13.如何启动实例?DB2START14.如何修改注册项的值?DB2SET可以修改如:设定当前实例的一个参数DB2SETPARAMETER=VALUE设定一个全局级的参数DB2SETPARAMETER=VALUE-G(小写)查看能在配置文件注册表中设置的所有变量的列表DB2SET-LR(小写)15.如何在CLP执行操作系统的命令?在命令前加"!"作为前缀DB2=>!DIRC:16.在CLP中命令过长怎么办?用""作为续行符号17.如何获得DB2的命令的语法相关信息?DB2?显示所有DB2命令DB2?COMMAND显示命令信息DB2?SQLnnnn显示这个SQLCODE的解释信息DB2?DB2nnnn显示这个DB2错误的解释信息18.如何查看当前CLP的设置?DB2=>LISTCOMANDOPTIONS19.如何更新当前CLP会话的特定项设置?DB2UPDATECOMMANDOPTIONSUSINGOPTIONS...MANDWINDOWS可以通过哪个命令调用?DB2CMD命令21.管理服务器的默认名为?UNIX下为DB2ASWINDOWS下为DB2DAS0022.常用管理DB2服务器实例的命令?DB2ADMINSTART启动DB2管理服务器实例DB2ADMINSTOP停止DB2管理服务器实例DASICRTUNIX下创建DB2管理服务器实例DASIDROPUNIX下删除DB2管理服务器实例DB2ADMINCREATEWINDOWSOROS/2下创建DB2管理服务器实例DB2ADMINDROPWINDOWSOROS/2下删除DB2管理服务器实例DB2GETADMINCFG显示DB2管理服务器的配置参数DB2UPDATEADMINCFG修改DB2管理服务器的配置参数DB2RESETADMINCFG将DB2管理服务器的配置参数设为默认值23.DB2目录分为哪几种?系统数据库目录本地数据库目录节点目录DCS目录管理节点目录24.如何查看系统数据库目录?LISTDBDIRECTORY25.如何查看数据库服务器目录?LISTNODEDIRECTORY26.DB2实例的服务器的默认端口是? 50000服务器名称为DB2CDB227.DB2UDB服务器端的认证类型有? SERVERSERVER_ENCRYPTCLIENTDCEDCE_SERVER_ENCRYPTKERBEROSKRB_SERVER_ENCRYPT28.DB2客户端的认证类型有?SERVERSERVER_ENCRYPTDCSDCS_ENCRYPTCLIENTDCE29.DB2中有哪几种类型的权限? SYSADM系统管理权限SYSCTRL系统控制权限SYSMAINT系统维护权限DBADM数据库管理权限LOAD对表进行LOAD操作的权限30.不能通过GRANT授权的权限有哪种? SYSAMSYSCTRLSYSMAINT要更该述权限必须修改数据库管理器配置参数31.表的类型有哪些?永久表(基表)临时表(说明表)临时表(派生表)32.如何知道一个用户有多少表?SELECT*FROMSYSIBM.SYSTABLESWHERECREATOR='USER' 33.如何知道用户下的函数?select*ERFUNCTIONselect*fromsysibm.SYSFUNCTIONS34.如何知道用户下的VIEW数?select*fromsysibm.sysviewsWHERECREATOR='USER' 35.如何知道当前DB2的版本?select*fromsysibm.sysvERSIONS36.如何知道用户下的TRIGGER数?select*fromsysibm.SYSTRIGGERSWHERESCHEMA='USER' 37.如何知道TABLESPACE的状况?select*fromsysibm.SYSTABLESPACES38.如何知道SEQUENCE的状况?select*fromsysibm.SYSSEQUENCES39.如何知道SCHEMA的状况?select*fromsysibm.SYSSCHEMATA40.如何知道INDEX的状况?select*fromsysibm.SYSINDEXES41.如何知道表的字段的状况?select*fromsysibm.SYSCOLUMNSWHERETBNAME='AAAA' 42.如何知道DB2的数据类型?select*fromsysibm.SYSDATATYPES43.如何知道BUFFERPOOLS状况?select*fromsysibm.SYSBUFFERPOOLS44.DB2表的字段的修改限制?只能修改VARCHAR2类型的并且只能增加不能减少.45.如何查看表的结构?DESCRIBLETABLETABLE_NAMEORDESCRIBLESELECT*FROMSCHEMA.TABLE_NAME46.如何快速清除一个大表?ALTERTABLETABLE_NAMEACTIVENOTLOGGEDINITALLYWITH EMPTYTABLEALTER TABLETABLE_NAME ACTIVE NOT LOGGED INITALLY WITH EMPTYTABLE两个commit之间的动作是不记日志的commitalter table ab activate not logged intiallyab where id >1000commit(创建的表ab不记录日志：create table ab (id int) not logged initially)47.如何查看数据库的包?select*fromsysCAT.PACKAGES48.如何查看数据库的存储过程?SELECT*FROMSYSCAT.PROCEDURES49.如何查看表的约束?SELECT*FROMSYSCAT.CHECKSWHERETABNAME='AAAA'50.如何查看表的引用完整约束?SELECT*FROMSYSCAT.REFERENCESWHERETABNAME='AAA A'51.安装DB2默认的事例名称是?在WINDOWS或OS/2中默认的是DB2在LINUX或UNIX环境下默认的是DB2INST152.安装后的默认帐户是?在WINDOWS或OS/2中默认的是DB2ADMIN 在LINUX或UNIX环境下默认的是DB2AS 53.事例的类型有哪些?CLIENT(客户)STANDALONE(独立的)SATELLITE(卫星)EEDB2EEE54.如何创建事例?DB2ICRTINSTNAME<...parameters>55.如何列出系统上的所有可用事例?DB2ILIST56.如何知道当前对话用的是哪个事例? GETINSTANCE57.如何更新事例的配置?DB2IUPDT58.如何删除事例?DB2IDROPINSTANCE_NAME具体步骤如下:停止事例上所有应用程序在所有打开的命令行上执行DB2TERMINATE运行DB2STOP备份DB2INSTPROF注册变量指出的事例目录退出事例所有登陆者使用DB2IDROP也可以删除ID59.如何列出本地系统上有许可信息的所有产品? DB2LICM-L60.如何增加一产品许可?DB2LICM-AFILENAME61.如何删除一个产品的许可?DB2LICM-RPRODUCTPASSWORD62.如何更新已购买的许可数量?DB2LICM-U63.如何强制只使用已经购买的数量?DB2LICM-EHARD64.如何更新系统上所使用的许可政策类型?DB2LICM-PREGISTEREDCONCURRENT65.如何更新系统上的处理器的数量?DB2LICM-N66.如何查询许可文件记录的版本信息?DB2LICM-V67.如何查询DB2LICM的帮助信息?DB2LICM-H68.一个数据库至少包括哪些表空间?一个目录表空间一个或多个用户表空间一个或多个临时表空间69.根据数据与存储空间之间移动的控制方式不同,分哪两种表空间? 系统管理的空间(SMS)数据库管理的空间(DMS)70.如何列出系统数据库目录的内容? LISTDATABASEDIRECTORY71.CREATEDATABASE是SQL命令吗?不是,是系统命令72.如何查看数据库ABC的配置文件的内容? GETDATABASECONFIGURATIONFORABC73.如何将数据库ABC的参数设置为默认数值? RESETDATABASECONFIGURATIONFORABC74.如何修改数据库ABC配置参数数值?UPDATEDATABASECONFIGURATIONFORABCUSING75.如何重新启动数据库?RESTARTDATABASEDATABASE_NAME76.如何激活数据库?ACTIVATEDATABASEDATABASE_NAME77.如何停止数据库?DEACTIVATEDATABASEDATABASE_NAME78.如何删除数据库?DROPDATABASEDATABASE_NAME79.如何建立模式?CREATESCHEMASCHEMA_NAME80.如何设置模式?SETSCHEMA=SCHEMA_NAME81.表的类型有哪些?基本表结果表概要表类型表子类型子表声明的临时表系统临时表82.如何定义序列?CREATESEQUENCEORDERSEQSTARTWITH1INCREMENTBY1 NOMAXVALUENOCYCLECACHE2483.如何将表置于检查挂起状态?SETINTEGRITYTABLE_NAMEOFF84.如何获得表的排斥锁?LOCKTABLETABLE_NAMEINEXCLUSIVEMODE85.如何把表调整为没有检查数据的在线状态?SETINTEGRITYFORTABLE_NAMEALLIMMEDIATEUNCHECKED86.如何解除表的锁定?COMMIT87.如何关闭表的日志?ALTERTABLETABLE_NAMEACTIVENOTLOGGEDINIALLY88.如何删除表?DROPSCHEMA.TABLE_NAME89.如何重命名表?RENAMETABLE_OLDTOTABLE_NEW90.如何取当前时间?SELECTCURRENTTIMESTAMPFROMSYSIBM.SYSDUMMY191.如何创建DB2的概要表?DB2的概要表功能类似于ORACLE的实体化视图!语法为:CREATESUMMARYTABLETABLE_NAMEAS(FULLSELECT)...例如:定义一个可以刷新的概要表:CREATESUMMARYTABLETABLE_NAMEAS(SELECT*FROMTAB LE_NAME1WHERECOL1='AAA')DATAINITIALLYDEFERREDREFRESHDEFERRED92.如何刷新概要表?REFRESHTABLESUM_TABLE其中SUM_TABLE为概要表.93.如何修改概要表?ALTERTABLESUM_TABLE...94.如何创建临时表?语法:DECLAREGLOBALTEMPORARYTABLETABLE_NAMEAS(FULLSELECT)DEFINITIONONLYEXCLUDINGIDENTITYCOLUMNATTRIBUTESONCOMMITDELETEROWSNOTLOGGED第一行规定临时表的名称.第二行规定临时表的列的定义.第三行规定不是从源结果表定义中复制的恒等列.第四行规定如果没有打开WITHGOLD光标,将会删除表的所有行.第五行规定不对表的改变进行记录.例如:DECLAREGLOBALTEMPORARYTABLEDEC_BSEMPMSAS(SELECT*FROMBSEMPMS)DEFINITIONONLYEXCLUDINGIDENTITYCOLUMNATTRIBUTESONCOMMITDELETEROWSNOTLOGGED95.视图的管理?如何创建视图:CREATEVIEWVIEW_NAMEASSELECT*FROMTABLE_NAMEWH ERE...删除视图:DROPVIEWVIEW_NAME96.如何知道视图定义的内容?SELECT*FROMSYSCAT.VIEWS中的TEXT列中.97.如何创建别名?CREATEALIASALIAS_NAMEFORPRO_NAME后面的PRO_NAME可以是TABLE,VIEW,ALIAS,NICKNAME等.98.如何创建序列?例如:CREATESEQUENCESEQUENCE_NAMESTARTWITHSTART_NUMBERINCREMENTBYVALUE1NOMAXVALUENOCYCLE CACHEMAXIMUMNUMBEROFSEQUENCEVALUES 第一行规定序列的名称.第二行规定序列的开始数值.第三行规定每次新增的幅度.第四行规定没有最大数值限制.第五行规定最大数值限制.99.如何更改序列? ALTERSEQUENCESEQUENCE_NAME...可以修改的参数STARTWITH的START_NUMBERINCREMENT的VALUE1NOMAXVALUE的数值NOCYCLE属性MAXIMUMNUMBEROFSEQUENCEVALUES最大数值100.如何删除序列? DROPSEQUENCESEQUENCE_NAME101.DB2支持导入(IMPORT)的文件格式有?有:DEL,ASC,IXF,WSF等102.DB2支持导出(EXPORT)的文件格式有?有:DEL,IXF,WSF等.不支持ASC格式.103.DB2支持载入(LOAD)的文件格式有?有:DEL,ASC,IXF等.不支持WSF格式.104.DB2支持DB2MOVE的文件格式有?有:IXF等.不支持ASC,DEL,WSF格式.105.DB2数据库监控的两个组成部分?快照监控(SNAPSHOTMONITOR)可返回特定时间点的数据库活动的快照.事件监控(EVENTMONITOR)记录事件发生的数据.106.系统监控的数据元素类型?计数器(COUNTER)记录活动发生的次数.测量(GAUGE)测量条目的当前值.水线(WATERMARK)从监控来元素达到的最大或最小数值.信息(INFORMATION)监控活动的参照类型细节.时间点(TIMESTAMP)活动发生的日期时间.时间(TIME)返回一个活动花费的时间.107.如何知道监控堆所需的页的数量?(NUMBEROFMONITORINGAPPLICATIONS+1)*(NUMBEROF DATABASES*(800+(NUMBEROFTABLESACCESSED*20)+((NUMBEROFAPPLICATIONSCONNECTED+1 )*(200+(NUMBEROFTABLESPACES*100)))))/4096其大小受参数MON_HEAD_SZ控制.108.如何建立事件监控器?CREATEEVENTMONITORTABLEMONFORTABLESWRITETOFIL E'D:TEMP'109.如何激活事件监控器?SETEVENTMONITORTABLEMONSTATE1110.如何停止事件监控器?SETEVENTMONITORTABLEMONSTATE0111.如何查询监控器的状态?SELECTEVMONNAME,EVENT_MON_STATE(EVMONNAME)FR OMSYSCAT.EVENTMONITORS112.如何删除事件监控器?DROPEVENTMONITORTABLEMON113.UNIX和WINDOWS上创建管道事件监控器(PIPE意EVNT见MONITOR)的不同?第一步:定义事件监控器UNIX:CONNECTTOSAMPLECREATEEVENTMONITORSTMB2FORSTATEMENTSWRITETOPI PE'/TMP/EVPIPE1'WINDOWS:CONNECTTOSAMPLECREATEEVENTMONITORSTMB2FORSTATEMENTSWRITETOPI PE'.TMPEVPIPE1'第二步:建立命名管道UNIX:可以使用MKFIFO()函数或者MKFIFO命令.WINDOWS:可以使用CREATENAMEDPIPE()函数,管道名称与CREATEEVENTMONITOR规定名称相同.第三步:打开命名管道UNIX:使用OPEN()函数.WINDOWS:使用CONNECTNAMEDPIPE()函数.也可以用DB2EVMON命令,如:DB2EVMON-DBSAMPLE-EVMSTMB2第四步:激活命名管道事件监控器除非自动激活命名管道事件监控器,否则SETEVENTMONITORSTMB2STATE1第五步:从命名管道读取数据UNIX:可以使用READ()函数.WINDOWS:可以使用READFILE()函数.第六步:停止事件监控器SETEVENTMONITORSTMB2STATE0第七步:关闭命名管道UNIX:可以使用CLOSE()函数.WINDOWS:可以使用DISCONNECTNAMEDPIPE()函数.第八步:删除命名管道UNIX:可以使用UNLINK()函数.WINDOWS:可以使用CLOSEHANDLE()函数.114.DB2的SQL语句的类别DCL:数据控制语言,提供对数据库对象的访问权限.DDL:数据定义语言,创建,修改,删除数据库对象的.DML:数据操纵语言,用来插入,更新,删除数据的.115.DCL的权限有哪些?CONTROL权限:如果用户创建一个对象,则改用户能完全访问该对象.GRANT语句将权限授予给用户.REVOKE语句撤销一个用户的权限.116.DDL有哪些?CREATEDECLAREALTERDROP等117.DML有哪些?INSERTUPDATEDELETE等118.DB2有没有布尔类型?没有119.如何查询DB2的内置函数?自带文档ADMINISTION-->SQLREFERENCE-->FUNCTIONS内120.如何执行DB2的脚本文件?DB2-VTFFILENAME121.DB2中象ORACLE的ROWNUM()是?ROW_NUMBER()OVER()122.DB2如何得到错误代码的说明?DB2?SQLCODE123.DB2中的VARCHAR转换为INTEGER的函数为?CAST()124.DB2中的INTEGER转换为VARCHAR的函数为?CHAR()125.DB2中的VARCHAR转换为DATE的函数为?DATE()126.DB2中的DATE转换为VARCHAR的函数为?CHAR()127.DB2中的TRIGGER能否修改?不能,只能删除重建128.WINDOWS下如何知道DB2的端口号? WINNTSYSTEM32DRIVERSETCSERVICES129.DB2如何执行存储过程?可以DB2CALLPROCEDURE_NAME130.如何进入DB2的DOS命令方式?131.如何得到DB2的进程号?DB2LISTAPPLICATIONS132.如何杀DB2的进程?FORCEAPPLICATION(ID) DB2函数135.AVG()返回一组数值的平均值.SELECTAVG(SALARY)FROMBSEMPMS;136.CORR(),CORRELATION()返回一对数值的关系系数. SELECTCORRELATION(SALARY,BONUS)FROMBSEMPMS; 137.COUNT()返回一组行或值的个数.SELECTCOUNT(*)FROMBSEMPMS;138.COVAR(),COVARIANCE()返回一对数值的协方差.SELECTCOVAR(SALARY,BONUS)FROMBSEMPMS; 139.MAX()返回一组数值中的最大值.SELECTMAX(SALARY)FROMBSEMPMS;140.MIN()返回一组数值中的最小值.SELECTMIN(SALARY)FROMBSEMPMS;141.STDDEV()返回一组数值的标准偏差.SELECTSTDDEV(SALARY)FROMBSEMPMS;142.SUM()返回一组数据的和.SELECTSUM(SALARY)FROMBSEMPMS;143.VAR(),VARIANCE()返回一组数值的方差.SELECTVARIANCE(SALARY)FROMBSEMPMS;144.ABS(),ABSVAL()返回参数的绝对值.SELECTABS(-3.4)FROMBSEMPMS;145.ACOS()返回参数的反余弦值.SELECTACOS(0.9)FROMBSEMPMS;146.ASCII()返回整数参数最左边的字符的ASCII码.SELECTASCII('R')FROMBSEMPMS;147.ASIN()返回用弧度表示的角度的参数的反正弦函数.SELECTASIN(0.9)FROMBSEMPMS;148.ATAN()返回参数的反正切值,该参数用弧度表示的角度的参数. SELECTATAN(0.9)FROMBSEMPMS;149.ATAN2()返回用弧度表示的角度的X和Y坐标的反正切值. SELECTATAN2(0.5,0.9)FROMBSEMPMS;150.BIGINT()返回整型常量中的数字或字符串的64位整数表示. SELECTBIGINT(EMP_NO)FROMBSEMPMS;151.CEILING()ORCEIL()返回比参数大或等于参数的最小的整数值. SELECTCEILING(3.56)FROMBSEMPMS;SELECTCEIL(4.67)FROMBSEMPMS;152.CHAR()返回日期时间型,字符串,整数,十进制或双精度浮点数的字符串表示. SELECTCHAR(SALARY,',')FROMBSEMPMS;153.CHR()返回具有由参数指定的ASCII码的字符.SELECTCHAR(167)FROMBSEMPMS;133.A用户安装DB2后,如何用B用户启动DATABASE?在B用户下的.PROFILE中加上./HOME/DB2INST/SQLLIB/DB2PROFILE134.DB2中类似ORACLE的快照是? SUMMARYTABLE154.CONCAT()返回两个字符串的连接.SELECTCONCAT(EMP_NO,EMP_NAM)FROMBSEMPMS; 155.YEAR()返回数值的年部分.SELECTYEAR('2003/01/02')FROMBSEMPMS;156.VARCHAR()返回字符串,日期型,图形串的可变长度的字符串表示. SELECTVARCHAR(EMP_NAM,50)FROMBSEMPMS; 157.UCASE()ORUPPER()返回字符串的大写.SELECTUCASE(EMP_NAM)FROMBSEMPMS; SELECTUPPER(EMP_NO)FROMBSEMPMS;158.TRUNCATE()ORTRUNC()从表达式小数点右边的位置开始截断并返回该数值. SELECTTRUNCATE(345.6789,2)FROMBSEMPMS;159.TIME()返回一个数值中的时间.SELECTTIME('2001-03-19.12.30.123456')FROMBSEMPMS; 160.SUBSTR(EXP1,EXP2)返回EXP1串自EXP2处开始的子串. SELECTSUBSTR('CDNJFDJFJD',5)FROMBSEMPMS;SELECTSUBSTR('CDNJFDJFJD',5,2)FROMBSEMPMS;161.SQRT()返回该参数的平方根.SELECTSQRT(36)FROMBSEMPMS;162.SPACE()返回由参数指定的长度,包含空格在内的字符串.SELECTSPACE(10)FROMBSEMPMS;163.SECOND()返回一个数值的秒部分.SELECTSECOND('18:34:32')FROMBSEMPMS;164.RTRIM()删除字符串尾部的空格.SELECTRTRIM('COMMENT')FROMBSEMPMS;165.ROUND(EXP1,EXP2)返回EXP1小数点右边的第EXP2位置处开始的四舍五入值.SELECTROUND(2345.6789,2)FROMBSEMPMS;166.REPLACE(EXP1,EXP2,EXP3)用EXP3替代EXP1中所有的EXP2SELECTCHAR(REPLACE('ROMANDD','NDD','CCB'),10)FROMB SEMPMS;167.REPEAT(EXP1,EXP2)返回EXP1重复EXP2次后的字符串.SELECTCHAR(REPEAT('REPEAT',3),21)FROMBSEMPMS;168.REAL()返回一个数值的单精度浮点数表示.SELECTREAL(10)FROMBSEMPMS;169.RAND()返回0和1之间的随机浮点数.SELECTRAND()FROMBSEMPMS;170.POWER(EXP1,EXP2)返回EXP1的EXP2次幂.SELECTPOWER(2,5)FROMBSEMPMS;171.POSSTR(EXP1,EXP2)返回EXP2在EXP1中的位置.SELECT('ABCDEFGH','D')FROMBSEMPMS;172.NULLIF(EXP1,EXP2)如果EXP1=EXP2,则为NULL,否则为EXP1173.NODENUMBER()返回行的分区号.SELECTNODENUMBER(EMP_NO)FROMBSEMPMS;174.MONTH()返回一个数值的月部分.SELECTMONTH('2003/10/20')FROMBSEMPMS;175.MOD(EXP1,EXP2)返回EXP1除以EXP2的余数.SELECTMOD(20,8)FROMBSEMPMS;176.MINUTE()返回一个数值的分钟部分.SELECTMINUTE('18:34:23')FROMBSEMPMS;177.LTRIM()删除字符串前面的空格.SELECTLTRIM('CDDD')FROMBSEMPMS;178.HOUR()返回一个数值的小时部分.SELECTHOUR('18:34:23')FROMBSEMPMS;179.DOUBLE()如果参数是一个数字表达式,返回与其相对应的浮点数,如果参数是字符串表达式,则返回该数的字符串表达式.SELECTDOUBLE('5678')FROMBSEMPMS;180.EXP()返回参数的指数函数.SELECTEXP(2)FROMBSEMPMS;181.FLOAT()返回一个数的浮点表示.SELECTFLOAT(789)FROMBSEMPMS;182.FLOOR()返回小于或等于参数的最大整数.SLECTFLOOR(88.93)FROMBSEMPMS;183.HEX()返回一个表示为字符串的值的16进制表示.SELECTHEX(16)FROMBSEMPMS;数据库介绍DB2是IBM公司开发的关系数据库管理系统，它有多种不同的版本，如：DB2工作组版（DB2Workgroup Edition）、DB2企业版（DB2 Enterprise Edition）、DB2个人版（DB2 Personal Edition）和DB2企业扩展版（DB2 Enterprise-Exended Edition）等，这些产品基本的数据管理功能是一样的，区别在于支持远程客户能力和分布式处理能力。
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
DB2数据库日常维护-REORG_TABLE
转）DB2日常维护——REORG TABLE命令优化数据库性能2009-04-24 16:18一个完整的日常维护规范可以帮助 DBA 理顺每天需要的操作，以便更好的监控和维护数据库，保证数据库的正常、安全、高效运行，防止一些错误重复发生。
由于DB2使用CBO作为数据库的优化器，数据库对象的状态信息对数据库使用合理的 ACCESS PLAN至关重要。
DB2 优化器使用目录统计信息来确定任何给定查询的最佳访问方案。
如果有关表或索引的统计信息已过时或者不完整，则会导致优化器选择不是最佳的方案，并且会降低执行查询的速度。
当数据库里某个表中的记录变化量很大时，需要在表上做REORG操作来优化数据库性能一、完整的REORG表的过程值得注意的是，针对数据库对象的大量操作，如反复地删除表，存储过程，会引起系统表中数据的频繁改变，在这种情况下，也要考虑对系统表进行REORG 操作。
一个完整的REORG表的过程应该是由下面的步骤组成的：RUNSTATS -> REORGCHK -> REORG -> RUNSTATS -> BIND或REBIND注：执行下面命令前要先连接数据库1 RUNSTATS由于在第二步中REORGCHK时可以对指定的表进行RUNSTATS操作(在REORGCHK时指定UPDATE STATISTICS)，所以第一步事实上是可以省略的。
2 REORGCHK在对表数据进行许多更改之后，逻辑上连续的数据可能会位于不连续的物理数据页上，在许多插入操作创建了溢出记录时尤其如此。
按这种方式组织数据时，数据库管理器必须执行其他读操作才能访问顺序数据。
另外，在删除大量行后，也需要执行其他的读操作。
表重组操作会整理数据碎片来减少浪费的空间，并对行进行重新排序以合并溢出记录，从而加快数据访问速度并最终提高查询性能。
还可以指定根据特定索引来重新排序数据，以便查询时通过最少次数据读取操作就可以访问数据。
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。
db2日常运维常用CLP命令和SQL语句
=============================================================== ===========================================================系统命令=========================================================================================================== ========================也就是直接在db2环境变量下直接敲就可以的，这些命令都可以再产品目录下找到●db2le vel 查看数据库版本●db2ilis t 查看实例●d b2icr t -aSERVE R -sese -p db2c_bnd8inst -ubnd8f enc b nd8in st 创建实例●d b2idr op xx inst删除实例●db2s tart启动数据库实例（数据库跟随实例启动，不能单独关闭和启动数据库）●db2st op 关闭数据库实例●d b2sto p for ce 强制停止●d b2iau to -o n tes tinst系统启动实例自动启动●db2iauto -off test inst系统启动实例不自动启动●db2samp l 创建例库●db2tbst十六进制状态码可以显示表空间状态●db2mo ve db nameexpor t 备份数据●db2move dbna me im port恢复数据●db2ck bkp -h BTA DB.0.btain st.NO DE0000.CAT N0000.20120206132720.001查看数据库备份。
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数据库系统日常管理和维护资料大全分析
Db2 V8 数据库系统日常管理和维护资料大全DB2中有关日期和时间的函数，及应用DAYNAME 返回一个大小写混合的字符串，对于参数的日部分，用星期表示这一天的名称（例如，Friday）。
DAYOFWEEK 返回参数中的星期几，用范围在1-7 的整数值表示，其中1 代表星期日。
DAYOFWEEK_ISO 返回参数中的星期几，用范围在1-7 的整数值表示，其中1 代表星期一。
DAYOFYEAR 返回参数中一年中的第几天，用范围在1-366 的整数值表示。
DAYS 返回日期的整数表示。
JULIAN_DAY 返回从公元前4712 年1 月1 日（儒略日历的开始日期）到参数中指定日期值之间的天数，用整数值表示。
MIDNIGHT_SECONDS 返回午夜和参数中指定的时间值之间的秒数，用范围在0 到86400 之间的整数值表示。
MONTHNAME 对于参数的月部分的月份，返回一个大小写混合的字符串（例如，January）。
TIMESTAMP_ISO 根据日期、时间或时间戳记参数而返回一个时间戳记值。
TIMESTAMP_FORMAT 从已使用字符模板解释的字符串返回时间戳记。
TIMESTAMPDIFF 根据两个时间戳记之间的时差，返回由第一个参数定义的类型表示的估计时差。
TO_CHAR 返回已用字符模板进行格式化的时间戳记的字符表示。
TO_CHAR 是V ARCHAR_FORMAT 的同义词。
TO_DA TE 从已使用字符模板解释过的字符串返回时间戳记。
TO_DA TE 是TIMESTAMP_FORMA T 的同义词。
WEEK 返回参数中一年的第几周，用范围在1-54 的整数值表示。
以星期日作为一周的开始。
WEEK_ISO 返回参数中一年的第几周，用范围在1-53 的整数值表示。
要使当前时间或当前时间戳记调整到GMT/CUT，则把当前的时间或时间戳记减去当前时区寄存器：current time - current timezonecurrent timestamp - current timezone给定了日期、时间或时间戳记，则使用适当的函数可以单独抽取出（如果适用的话）年、月、日、时、分、秒及微秒各部分：YEAR (current timestamp)MONTH (current timestamp)DAY (current timestamp)HOUR (current timestamp)MINUTE (current timestamp)SECOND (current timestamp)MICROSECOND (current timestamp)因为没有更好的术语，所以您还可以使用英语来执行日期和时间计算：current date + 1 YEARcurrent date + 3 YEARS + 2 MONTHS + 15 DAYScurrent time + 5 HOURS - 3 MINUTES + 10 SECONDS从时间戳记单独抽取出日期和时间也非常简单：DA TE (current timestamp)TIME (current timestamp)而以下示例描述了如何获得微秒部分归零的当前时间戳记：CURRENT TIMESTAMP - MICROSECOND (current timestamp) MICROSECONDS如果想将日期或时间值与其它文本相衔接，那么需要先将该值转换成字符串。
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)*********************************************修改队列管理器中的字符集命令：首先打开命令行窗口。
循序渐进db2笔记-日常维护
14.1.1 查看是否有僵尸进程
在UNIX中，若父进程在一定的时间内无法收集到状态信息，则系统就会残留一个defunct进程。因为defunct进程是已经停止的，所以使用杀死进程的方法来杀defunct进程是无效的。defunct进程不使用CPU或硬盘等系统资源，而只使用极少量的内存用于存储退出状态和资源使用信息。
select * from sysibmadm.TOP_DYNAMIC_SQL order by NUM_EXECUTIONS desc fetch first 5 rows only;
此语句返回执行频率最高的5个动态SQL语句的所有执行时间、排序执行次数和语句文本详细信息。
为了标识执行时间最长的动态SQL语句，请检查AVERAGE_EXECUTION_TIME_S值最大的5个查询：
select * from sysibmadm_EXECUTION_TIME_S desc fetch first 5 rows only;
14.2.7 监控排序次数最多的SQL语句
select STMT_SORTS,SORTS_PER_EXECUTION,substr(STMT_TEXT,1,60) as STMT_TEXT from TOP_DYNAMIC_SQL order by STMT_SORTS desc fetch first 5 rows only;
通过inspect命令检查数据库是否一致：
db2 inspect check database results keep db_check.out
db2inspf db_check.out db_check.txt #检查文件，查看数据库是否一致
14.1.3 查看诊断日志判断是否有异常
DB2常用管理维护命令
SELECT * FROM SYSCAT.PROCEDURES
绑定存储过程
db2 connect to bgj0 user db2 using pwd
db2 bind c:\dfplus.bnd
拷贝存储过程到服务器上的C:\sqllib\function目录中
=========DB2常用的命令======================================
启动数据库
db2start
停止数据库
db2stop
连接数据库
db2 connect to bgj0 user db2inst using password
读数据库管理程序配置
db2 rollforward db db3test to [timestamp] and complete
db2 rollforward db db3test to [timestamp] using local time and complete
========================================================================
db2expln -d 数据库名 -u 用户名 密码 -o 文件名|-t -c 生成包的模式名 -p 包名
=======================================================================================
要使用SQL调用语句重组表，使用admin_cmd过程发出执行命令：
=======================================================================
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⽇志维护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日常维护操作 ............................................................................................ - 1 -1.检查管理服务器是否启动.................................................................................... - 1 -2.检查DB2实例是否已经启动 ............................................................................... - 1 -3.查看表空间状态是否正常.................................................................................... - 1 -4.查看表的状态........................................................................................................ - 2 -5.查看磁盘空间........................................................................................................ - 2 -6.检查存储管理软件是否正常................................................................................ - 2 -7.检查数据库备份是否正常.................................................................................... - 2 -8.检查归档日志是否正确归档了............................................................................ - 3 -9.查看缓冲池的命中率............................................................................................ - 3 -10.查看当前运行最频繁的SQL，其命中率是否正常 ......................................... - 3 -11.查看当前连接的应用程序，有没有非法连接 ................................................ - 3 -12.检查有没有死锁 ................................................................................................ - 3 -13.对表和索引进行runstats .................................................................................. - 3 -14.检查表是否需要重组 ........................................................................................ - 4 -15.对需要重组的表进行重组 ................................................................................ - 4 -二、DB2日常维护月操作 ........................................................................................ - 4 -1.查看DB2日志 ....................................................................................................... - 4 -2.检查备份和日志是否都保存好了........................................................................ - 4 -三、DB2日常维护季度操作 .................................................................................... - 5 -1.通过快照监控器，查看系统性能如何 ................................................................ - 5 -2.数据库补丁级别.................................................................................................... - 5 -四、注意事项............................................................................................................ - 5 -1.不要删除活动日志文件........................................................................................ - 5 -2.注意交易日志存储空间........................................................................................ - 5 -3.按照系统的实际工作量配置日志空间 ................................................................ - 5 -4.设置正确数据库代码页........................................................................................ - 6 -5.检查许可证（License）安装情况 ........................................................................ - 6 -6.创建数据库前调整好系统时间............................................................................ - 6 -7.不要随便执行chown (chmod) –R （UNIX/Linux） ........................................... - 6 -8.归档模式设置........................................................................................................ - 6 -五、附：以脱机方式重组表 .................................................................................... - 7 -六、附：使用CLP 捕获数据库运行状况快照 ...................................................... - 7 -七、附：IBM DB2常用命令..................................................................................... - 8 -DB2日常维护手册一、DB2日常维护操作1.检查管理服务器是否启动用ps命令查看是否有dasusr1后台进程＃ps -ef | dasusr1请确保管理服务器已经启动，如果没有启动，则按以下步骤启动管理服务器：以管理服务器用户(UNIX默认是DASUSR1)登录发出db2admin start命令如果是HA环境，则要保证在脚本中正确配置了启动命令2.检查DB2实例是否已经启动用ps命令查看是否有db2sysc后台进程＃ps -ef | db2sysc也可以以DB2实例所有者登录，通过发出db2start命令来确保启动了实例（如果实例已经启动，则会告知SQL1026N 数据库管理器已激活；否则，将把实例启动起来）3.查看表空间状态是否正常以db2实例所有者登录#db2 list tablespaces show detail //在单分区上查看表空间的状态,正常返回0x0000 # db2_all list tablespaces show detail //在所有分区上查看表空间的状态可以使用LIST TABLESPACES 命令确定连接数据库中表空间的当前状态，可以使用SHOW DETAIL选项查看表空间的详细信息。
比如，我们连上SAMPLE数据库，执行list tablespaces show detail ，可以看到状态返回值是0x0000，此时，使用db2tbst可以查看状态编号对于的状态含义，具体语法如下：db2tbst <tablespace state>可以查看编号所代表的状态db2tbst 命令接收十六进制的状态值，并返回相应的表空间状态。
例如，命令db2tbst 0x0008 返回State = Load Pending 。
而该十六进制的状态值反过来又是LIST TABLESPACES 命令输出的组成部分。