数据库空间调整方案.doc

此文档收集于网络，如有侵权，请联系网站删除IBM DB2 UDB数据库空间调整方案 江苏苏源高科技有限公司1背景概述江苏电力公司营销系统的DB2数据库中，用户数据存放在一个page size为8K页大小的表空间USERSPACE1中，对于page size为8K页大小的表空间，受到DB2本身的限制，其单个表空间的最大容量为128G，超过128G数据库将不能正常运行。目前，营销系统的用户数据量已经接近这个极限，为避免出现表空间可用容量被耗尽的情况，必须对营销系统的DB2数据库USERSPACE1表空间进行调整。2联系人及电话单位姓名联系电话E-mail地址苏源高科黄苏源高科宋IBM公司3迁移建议新建一个8K page size的用户表空间USERSPACE2，将原表空间USERSPACE1上的用户数据部分迁移到新的表空间USERSPACE2上，以达到分散数据分布，增加用户表空间可用容量的目的。此方案风险性小，迁移过程不过于复杂，对数据库系统影响较小。另外经过和ibm工程师咨询在目前不改变存储配置的情况下，对性能不会有什么影响，因此打算选择数据量比较大的表作迁移，这样可以适当减少工作量。4实施细则4.1迁移步骤4.1.1制定迁移计划如下：1.备份数据库 对原数据库进行备份，万一出错可以恢复。2.导出数据将相关表的数据导出，文件格式IXF删除与相关表有关的对象删除了索引，视图和触发器将表重新命名为了安全，并没有将表删除。3.在新的表空间中创建表并指定索引位于新建的表空间中创建表向新的表中装入数据使用load装入。4.创建在步骤3中删除的对象创建视图，触发器创建索引和主键5.收集统计信息绑定包重新绑定包。验证数据和程序。6.验证数据和程序的正确性应用人员测试一切正常，删除原表。意外处理如果遇到了无法继续进行的情况，或者无法解决的问题，将利用数据全备份恢复数据库，保证第二天的正常生产不受到影响。4.1.2实施的具体过程收集表的统计信息，得到每张表占用空间的估计值db2 “select tabname, npages from syscat.tables”从npages乘以8K可以得到每张表所占用空间的估计值。经过检查发现三张表DB2.DFTZ、DB2.DFTDL、DB2.DFTSS，大小分别为6915M、6405M、7784M，迁移这三张表及相关的索引到新建的用户表空间中，原来的表空间可以腾出约40G的存储空间，这样，就可以满足当前的系统的数据增长的要求。1.备份数据库 db2 backup db yddb to /dbbackup2/bak1,/dbbackup2/bak2 with 2 buffers buffer 16384 parallelism 42.导出数据db2 export to dftz.ixf of ixf select * from dftz db2 export to dftdl.ixf of ixf select * from dftdl db2 export to dftss.ixf of ixf select * from dftss 3.删除与这两张表有关的对象 db2 -tvf drop.sql4.将表重新命名rename table dftz to dftzbakrename table dftdl to dftdlbakrename table dftss to dftsslbak5.在新的表空间中创建表db2 -tvf table.sql6.向新的表中装入数据load from dftz.ixf of ixf insert into dftz nonrecoverable data buffer 81920 CPU_PARALLELISM 12 DISK_PARALLELISM 6load from dftdl.ixf of ixf insert into dftdl nonrecoverable data buffer 16384 CPU_PARALLELISM 12 DISK_PARALLELISM 6load from dftss.ixf of ixf insert into dftss nonrecoverable data buffer 16384 CPU_PARALLELISM 12 DISK_PARALLELISM 67.创建在步骤3中删除的对象db2 -tvf trigger_view.sql8.创建索引和主键db2 -tvf index.sql9.收集统计信息db2 runstats on table db2.dftz and indexes all db2 runstats on table db2.dftdl and indexes alldb2 runstats on table db2.dftss and indexes all 10.绑定包db2rbind yddb -l bind.log11.验证数据和程序应用人员进行了验证，一切正常。12.删除原改句表drop table dftzbakdrop table dftdlbakdrop table dftssbak4.2迁移后的用户表空间情况数据迁移成功，具体表空间数据如下：表空间USERSPACE1USERSPACE2迁移前可用空间（估算）4000M128G迁移后可用空间（估算）40G80G5风险性评估对于这次数据迁移，苏源高科、IBM全球服务部双方都做好了方案的准备及具体实施过程的测试工作，并与徐州供电公司业务人员进行了多次交流。在实施的过程中充分考虑了意外情况的发生，并做好了数据库的备份工作，防止意外情况下数据库能正常恢复。迁移的三张表并没有直接地进行删除，而是做了表重命名，避免了在数据的丢失和意外情况下恢复的时间控制。6相关脚本附件1.USERPACE2表空间的创建语句： CREATE REGULAR TABLESPACE USERSPACE2 IN NODEGROUP IBMDEFAULTGROUP PAGESIZE 8192 MANAGED BY DATABASE USING (DEVICE /dev/ryddata_vpath3 10485740) EXTENTSIZE 8 PREFETCHSIZE 144 BUFFERPOOL DB2BP8K OVERHEAD 8.300000 TRANSFERRATE 0.250000; 2.drop.sqldrop view DB2.ZERODL_V;drop TRIGGER DB2.TAD_DFTZ;drop TRIGGER DB2.TAD_YDD_BERORE;drop TRIGGER DB2.TAI_DFTZ;drop TRIGGER DB2.TAU_DFTZ;drop TRIGGER DB2.TAD_DFTDL;drop TRIGGER DB2.TAI_DFTDL;drop TRIGGER DB2.TAU_DFTDL;drop TRIGGER DB2.TAD_DFTSS;drop TRIGGER DB2.TAU_DFTSS_BERORE;drop index DB2.DFTZ_1;drop index DB2.DFTZ_IND1;drop index DB2YD.DFTZ_D_J_D_H;drop index DB2.DFTSS_1;drop index DB2.DFTSS_GZSJ1;alter table db2.dftz drop primary key;alter table db2.dftdl drop primary key;alter table db2.dftss drop primary key;3.tab.sql-DDL Statements for table DB2 - CREATE TABLE DB2 .DFTZ ( ZHH INTEGER NOT NULL , JSRQ TIMESTAMP NOT NULL , DH VARCHAR(4) , ZDL INTEGER , ZDF DECIMAL(12,2) , YSZDF DECIMAL(12,2) , YTBDF DECIMAL(12,2) , TJRQ DATE , SDRQ DATE , GZRQ DATE , LJSF DECIMAL(4,2) , TSH INTEGER , DWM SMALLINT , SDR INTEGER , LYBZ SMALLINT , SFFS INTEGER , JSY INTEGER ) IN USERSPACE2 index in “USERSPACE2” ;CREATE TABLE DB2 .DFTDF ( ZHH INTEGER NOT NULL , JSRQ TIMESTAMP NOT NULL , YYXH SMALLINT NOT NULL , DFLB INTEGER NOT NULL , DFXM INTEGER NOT NULL , DJ DECIMAL(8,4) , DF DECIMAL(10,2) , SM VARCHAR(255) , JSDF DECIMAL(10,2) , SSDW SMALLINT , HYM SMALLINT , DL INTEGER ) IN USERSPACE2 index in “USERSPACE2” ;CREATE TABLE DB2 .DFTSS ( ZHH INTEGER NOT NULL , SSLB INTEGER NOT NULL , YYXH SMALLINT NOT NULL , SSXM INTEGER NOT NULL , BH VARCHAR(9) NOT NULL , RQ TIMESTAMP NOT NULL , BZ SMALLINT , ZTR INTEGER , JSRQ TIMESTAMP , SS DECIMAL(9,3) , SL INTEGER , YCM SMALLINT , YWBZ SMALLINT , CBR INTEGER , LRFS SMALLINT , SRCS SMALLINT , GZSJ TIMESTAMP ) IN USERSPACE2 index in “USERSPACE2”;4.trigger_view.sql- DDL Statements for Views- create view DB2.ZERODL_V AS with temp1 as (select a.zhh as zhh,零电量 as sm from ydd a,dftz b where a.zhh=b.zhh and date(b.jsrq)2003-01-01 group by a.ssdw,a.zhh having (sum(b.zdl)=0 or sum(b.zdl) is null) union (select c.zhh as zhh,未抄表 as sm from ydd c where not exists (select * from dftz k where k.zhh=c.zhh and date(k.jsrq)2003-01-01) select d.zhh,dh,hm,dz,(select bh from ydd1111 where zhh=d.zhh and yyxh=1 and xw=2901 and blx=(select min(blx) from ydd1111 where zhh=d.zhh and yyxh=1) as BH_Z,(select bh from ydd1111 where zhh=d.zhh and yyxh=1 and xw=2902 and blx=(select min(blx) from ydd1111 where zhh=d.zhh and yyxh=1) as BH_A,(select bh from ydd1111 where zhh=d.zhh and yyxh=1 and xw=2903 and blx=(select min(blx) from ydd1111 where zhh=d.zhh and yyxh=1) as BH_B,(select bh from ydd1111 where zhh=d.zhh and yyxh=1 and xw=2904 and blx=(select min(blx) from ydd1111 where zhh=d.zhh and yyxh=1) as BH_C,sm,ssdw from ydd d, temp1 e where d.zhh=e.zhh and d.dh not in(销户) and (d.yhxz=1208 or d.yhxz is null) and date(d.jhr)current date and d.ssdw0 OR (SELECT COUNT(*) FROM DB2.DFZWYWS WHERE ZHH=O.ZHH)0 THEN SIGNAL SQLSTATE 此户还存在未收帐务,实际户信息不能删除; END IF;- IF (SELECT COUNT(*) FROM DB2.DFTZ WHERE ZHH=O.ZHH)0 THEN IF (SELECT COUNT(*) FROM DB2.DFTZ WHERE ZHH=O.ZHH AND DATE(JSRQ)(SELECT SDRQ FROM DB2.DFLOCK WHERE LX=1 AND DWDM=DB2.GET_SSDW(O.ZHH) ) 0 THEN SIGNAL SQLSTATE 此户还存在锁定日期之后的台帐信息,实际户信息不能删除; END IF;- END IF; - END ;CREATE TRIGGER DB2.tai_DFTZ AFTER INSERT ON DB2.DFTZ REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL BEGIN ATOMIC -已锁定日期不可再增加台帐记录 IF date(N.JSRQ) =(select SDRQ from DB2.DFLOCK where dwdm=DB2.GET_SSDW(N.ZHH) and LX=1) THEN SIGNAL SQLSTATE 所插台帐记录的计算日期已加日锁，不可增加！;- END IF;- END;CREATE TRIGGER DB2.tau_DFTZ AFTER UPDATE ON DB2.DFTZ REFERENCING OLD AS O NEW AS N FOR EACH ROW MODE DB2SQL BEGIN ATOMIC -已锁定日期不可再修改台帐记录 -IF N.TSH=O.TSH THEN - IF date(N.JSRQ) =(select SDRQ from DB2.DFLOCK where dwdm=DB2.GET_SSDW(N.ZHH) and LX=1) or date(O.JSRQ) =(select SDRQ from DB2.DFLOCK where dwdm=DB2.GET_SSDW(N.ZHH) and LX=1) THEN - SIGNAL SQLSTATE 不可修改已加锁计算日期，也不可修改计算日期至已加锁计算日期！;- - END IF;- -ELSE IF (N.ZHHO.ZHH) OR (N.JSRQO.JSRQ) OR (N.DHO.DH) OR (N.ZDLO.ZDL) OR (N.ZDFO.ZDF) OR (N.YSZDFO.YSZDF) OR (N.YTBDFO.YTBDF) OR (N.TJRQO.TJRQ) OR (N.SDRQO.SDRQ) OR (N.GZRQO.GZRQ) OR (N.LJSFO.LJSF) OR (N.DWMO.DWM) OR (N.SDRO.SDR) OR (N.LYBZO.LYBZ) OR (N.SFFSO.SFFS) OR (N.JSYO.JSY) THEN IF date(N.JSRQ) =(select SDRQ from DB2.DFLOCK where dwdm=DB2.GET_SSDW(N.ZHH) and LX=1) or date(O.JSRQ) =(select SDRQ from DB2.DFLOCK where dwdm=DB2.GET_SSDW(N.ZHH) and LX=1) THEN SIGNAL SQLSTATE 当前台帐记录已锁定，不可修改！;- -修改托收号失败，因为不可同时修改已加锁计算日期，也不可同时修改计算日期至已加锁计算日期;- END IF;- END IF;- -END IF;- END;CREATE TRIGGER DB2.tad_DFTDL AFTER DELETE ON DB2.DFTDL REFERENCING OLD AS O FOR EACH ROW MODE DB2SQL BEGIN ATOMIC IF date(O.JSRQ) =(select SDRQ from DB2.DFLOCK where dwdm=DB2.GET_SSDW(O.ZHH) and LX=1) THEN SIGNAL SQLSTATE 当前台帐记录已加锁，不可删除！;- END IF;- END;CREATE TRIGGER DB2.tai_DFTDL AFTER INSERT ON DB2.DFTDL REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL BEGIN ATOMIC -已锁定日期不可再增加台帐记录 IF date(N.JSRQ) =(select SDRQ from DB2.DFLOCK where dwdm=DB2.GET_SSDW(N.ZHH) and LX=1) THEN SIGNAL SQLSTATE 所插台帐记录的计算日期已加日锁，不可增加！;- END IF;- END;CREATE TRIGGER DB2.tau_DFTDL AFTER UPDATE ON DB2.DFTDL REFERENCING OLD AS O NEW AS N FOR EACH ROW MODE DB2SQL BEGIN ATOMIC -已锁定日期不可再修改台帐记录 IF date(N.JSRQ) =(select SDRQ from DB2.DFLOCK where dwdm=DB2.GET_SSDW(N.ZHH) and LX=1) or date(O.JSRQ) =(select SDRQ from DB2.DFLOCK where dwdm=DB2.GET_SSDW(O.ZHH) and LX=1) THEN SIGNAL SQLSTATE 当前台帐记录已加锁,数据不可修改！;- END IF;- END;CREATE TRIGGER DB2.tad_DFTSS AFTER DELETE ON DB2.DFTSS REFERENCING OLD AS O FOR EACH ROW MODE DB2SQL BEGIN ATOMIC -BZ=3时不可删除 IF O.BZ=3 THEN SIGNAL SQLSTATE 存在被锁定的示数纪录，不可删除！;- END IF;- END;CREATE TRIGGER DB2.tau_DFTSS_before NO CASCADE BEFORE UPDATE ON DB2.DFTSS REFERENCING OLD AS O NEW AS N FOR EACH ROW MODE DB2SQL BEGIN ATOMIC -BZ=3时不可更新,即:标志为已加锁的示数不可更新它的计算日期 IF O.