DB2数据库表分区和迁移测试报告公开版本Word格式文档下载.docx
- 文档编号:21254093
- 上传时间:2023-01-28
- 格式:DOCX
- 页数:26
- 大小:26.84KB
DB2数据库表分区和迁移测试报告公开版本Word格式文档下载.docx
《DB2数据库表分区和迁移测试报告公开版本Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《DB2数据库表分区和迁移测试报告公开版本Word格式文档下载.docx(26页珍藏版)》请在冰豆网上搜索。
数据库软件
DB2/AIX649.5.0
1.3实施时间表
2010-11-11/16
测试表迁移和表分区
3人天
1.4系统概要描述
分类名称
名称
配置值
描述
硬件环境
主机型号
IBM,9119-595
CPULPARInfo:
10nhdb09-new
prtconf
CPU个数
4颗2102MHz
Memory大小
10240MB
存储环境
EMCSymmetrix
网络
IPAddress:
150.18.31.39
软件环境
操作系统版本
AIX5300-09
oslevel-r
DB2版本
db2level
DB2license
Db2licm-l
JAVA版本和目录
javaversion"
1.4.2"
Java(TM)2RuntimeEnvironment,StandardEdition(build1.4.2)
ClassicVM(build1.4.2,J2RE1.4.2IBMAIXbuildca142-20080515(SR11)(JITenabled:
jitc))
Java-version
实例(db2sjck)
节点个数
2
数据库(ssfx)大小
其他
2.测试方案
1选择一个较大的表,从原表空间分离出来,从表空间TS_LARGE_DATA01移出,放入新的表空间。
2使用表分区技术,按照“管理机关市级代码(GLJGSJ_DM)”字段分区存储,广州市按区级分区,其他按地市级划分。
3使用表压缩技术。
3.测试步骤
3.1创建新的表空间
3.1.1检查现有的存储空间
在不增加新硬盘的状况下,目前三个卷组ssfxfsvg,ssfxvg01,ssfxvg02剩余的空间分别为30GB,40GB,50GB,一共120GB。
测试系统有两个数据库节点,因此每个节点60GB,再建立一个新的数据表空间40GB,再建立一个新的索引表空间20GB。
3.1.2先建立逻辑卷,每个10GB,共12个
mklv-traw-Udb2sjck-Gdb2adm-ex-yL_DT3_LV00_01ssfxfsvg80
mklv-traw-Udb2sjck-Gdb2adm-ex-yL_DT3_LV00_02ssfxfsvg80
mklv-traw-Udb2sjck-Gdb2adm-ex-yL_DT3_LV00_03ssfxfsvg80
mklv-traw-Udb2sjck-Gdb2adm-ex-yL_DT3_LV00_04ssfxvg0180
mklv-traw-Udb2sjck-Gdb2adm-ex-yL_DT3_LV01_01ssfxvg0180
mklv-traw-Udb2sjck-Gdb2adm-ex-yL_DT3_LV01_02ssfxvg0180
mklv-traw-Udb2sjck-Gdb2adm-ex-yL_DT3_LV01_03ssfxvg0180
mklv-traw-Udb2sjck-Gdb2adm-ex-yL_DT3_LV01_04ssfxvg0280
mklv-traw-Udb2sjck-Gdb2adm-ex-yL_IND3_LV00_01ssfxvg0280
mklv-traw-Udb2sjck-Gdb2adm-ex-yL_IND3_LV00_02ssfxvg0280
mklv-traw-Udb2sjck-Gdb2adm-ex-yL_IND3_LV01_01ssfxvg0280
mklv-traw-Udb2sjck-Gdb2adm-ex-yL_IND3_LV01_02ssfxvg0280
3.1.3定义缓冲池(每节点512MB)
CREATEDATABASEPARTITIONGROUPdbpg_largeONDBPARTITIONNUMS(0to1)
CREATEBUFFERPOOLdbpg_largeIMMEDIATEDATABASEPARTITIONGROUPdbpg_largeSIZE16384AUTOMATICPAGESIZE32K
3.1.4创建数据表空间(每个节点40GB,共80GB)
db2=>
CREATELARGETABLESPACETS_LARGE_DATA03INDATABASEPARTITIONGROUPdbpg_largePAGESIZE32KMANAGEDBYDATABASEUSING(device'
/dev/rL_DT3_LV00_01'
10200m,device'
/dev/rL_DT3_LV00_02'
/dev/rL_DT3_LV00_03'
/dev/rL_DT3_LV00_04'
10200m)ONDBPARTITIONNUMS(0)USING(device'
/dev/rL_DT3_LV01_01'
/dev/rL_DT3_LV01_02'
/dev/rL_DT3_LV01_03'
/dev/rL_DT3_LV01_04'
10200m)ONDBPARTITIONNUMS
(1)BUFFERPOOLdbpg_large
DB20000ITheSQLcommandcompletedsuccessfully.
3.1.5创建索引表空间:
CREATELARGETABLESPACETS_LARGE_IND03INDATABASEPARTITIONGROUPdbpg_largePAGESIZE32KMANAGEDBYDATABASEUSING(device'
/dev/rL_IND3_LV00_01'
/dev/rL_IND3_LV00_02'
/dev/rL_IND3_LV01_01'
/dev/rL_IND3_LV01_02'
10200m)ONDBPARTITIONNUMS
(1)BUFFERPOOLdbpg_large
3.2创建新的分区表和迁移表
3.2.1运行脚本创建新表,该表使用分区和压缩技术,赋权限。
[nhdb_test02:
db2sjck]db2-tvf./crt_table_sbzsmx_new.sql
CREATETABLE"
DB_SSFX"
."
T_FX_SBZSMX_NEW"
(
"
SJRQ"
VARCHAR(8),
YZFSJE_JE"
DECIMAL(18,2),
YZZYJ_JE"
YZSJ_JE"
YZDSJ_JE"
YZQXJ_JE"
YZXZJ_JE"
JKFSJE_JE"
JKZYJ_JE"
JKSJ_JE"
JKDSJ_JE"
JKQXJ_JE"
JKXZJ_JE"
PZ_XH"
DECIMAL(16,0)NOTNULL,
YZMX_XH"
DECIMAL(6,0)NOTNULL,
JK_XH"
DECIMAL(16,0),
JKLSMXH_XH"
DECIMAL(6,0),
ZSXM_DM"
VARCHAR
(2),
ZSPM_DM"
VARCHAR(6),
SFSSQ_QSRQ"
DATE,
SFSSQ_ZZRQ"
SB_RQ"
YZFS_RQ"
JK_RQ"
RK_RQ"
JGRJ_SJ"
TF_SJ"
JK_QX"
SBFS_DM"
ZSFS_DM"
ZSDLFS_DM"
JKFS_DM"
SBSX_DM"
VARCHAR
(1),
SKSX1_DM"
SKSX2_DM"
YSFPBL_DM"
VARCHAR(4),
SJGXSX_DM"
SXGXSX_DM"
PZZL_DM"
VARCHAR(5),
JKPZLX_DM"
WSZL_DM"
JMYY_DM"
TZLX_DM"
DJLX_DM"
DZSX_DM"
ZSJG_DM"
VARCHAR(11),
HSJG_DM"
JCJG_DM"
XZ_DM"
VARCHAR(10),
SBRY_DM"
ZSRY_DM"
SGY_DM"
NSRBM"
VARCHAR(20),
NSRNBM"
DECIMAL(10,0),
NSRFLM"
NSR_MC"
VARCHAR(200),
LXDH_DH"
VARCHAR(30),
FDDBR_MC"
VARCHAR(50),
BSY_MC"
SJJY_DZ"
DZSPH_XH"
PIAOZZL_DM"
PZZB_DM"
PZHM"
VARCHAR(12),
RJ_XH"
ZCLX_DM"
VARCHAR(3),
HYML_DM"
HYDL_DM"
HYZXL_DM"
LSGX_DM"
TSQYLB"
ZDSY_BZ"
DKDJ_DM"
SDH_BZ"
WZ_BZ"
GSLX_DM"
SKYYZHID"
KGLX_DM"
YKP_BJ"
ZF_BJ"
YHHB_DM"
SF_BZ"
YSKM_DM"
VARCHAR(9),
JS_YJ"
LR_SJ"
TIMESTAMP,
XG_SJ"
DJFS_RQ"
ZTFS_RQ"
JM_RQ"
DJ_RQ"
SJJYDZSZXZQY_DM"
DJ_ZT"
GLJGSJ_DM"
GLJGQXJ_DM"
GLJGXZJ_DM"
SZSJGH_BZ"
YCXSY_BZ"
VARCHAR
(1)DEFAULT'
0'
HZSK_BZ"
DZZR_BZ"
QQLX_DM"
YQNSJK_BZ"
JMDJWSH"
JMDJWSMX_XH"
KQ_BZ"
TKXZ_DM"
CHARACTER
(1),
XGLJG_DM"
YSGY_DM"
XZDSY_BZ"
KCSE_JE"
ZNTS"
SL"
DECIMAL(16,6),
PZSL"
KPRY_DM"
TFZSJG_DM"
YH_DM"
YH_ZH"
SKYH_DM"
SKYH_ZH"
KJ_RQ"
QS_RQ"
YJK_QX"
DWSBH"
VARCHAR(18),
SBGLJG_DM"
SFSX_DM"
VARCHAR
(2)
)
INDEXINTS_LARGE_IND03PARTITIONBYRANGE(GLJGSJ_DMNULLSLAST)(PARTITIONSBZSMX_FQSHJISTARTINGFROM('
244000000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQZHSHUSTARTINGFROM('
244009000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQGZDSSTARTINGFROM('
244010200'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQGZLWSTARTINGFROM('
244010300'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQGZYXSTARTINGFROM('
244010400'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQGZHZSTARTINGFROM('
244010500'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQGZTHSTARTINGFROM('
244010600'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQGZFCSTARTINGFROM('
244010700'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQGZBYSTARTINGFROM('
244011100'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQGZHPSTARTINGFROM('
244011200'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQGZPYSTARTINGFROM('
244011300'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQGZHDSTARTINGFROM('
244011400'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQGZNSSTARTINGFROM('
244011500'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQGZLGSTARTINGFROM('
244011600'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQGZDJSTARTINGFROM('
244017000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQGZNKSTARTINGFROM('
244017900'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQGZZCSTARTINGFROM('
244018300'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQGZCHSTARTINGFROM('
244018400'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQGZKFSTARTINGFROM('
244019700'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQSGSTARTINGFROM('
244020000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQSZSTARTINGFROM('
244030000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQZHSTARTINGFROM('
244040000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQSTSTARTINGFROM('
244050000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQFSSTARTINGFROM('
244060000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQJMSTARTINGFROM('
244070000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQZZSTARTINGFROM('
244080000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQMMSTARTINGFROM('
244090000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQZQSTARTINGFROM('
244120000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQHZSTARTINGFROM('
244130000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQMZSTARTINGFROM('
244140000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQSWSTARTINGFROM('
244150000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQHYSTARTINGFROM('
244160000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQYJSTARTINGFROM('
244170000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQQYSTARTINGFROM('
244180000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQDGSTARTINGFROM('
244190000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQZSSTARTINGFROM('
244200000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQCZSTARTINGFROM('
244510000'
)INCLUSIVEINTS_LARGE_DATA03,PARTITIONSBZSMX_FQJYSTARTINGFROM('
244520000'
)INCLUSIVEINTS_LAR
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- DB2 数据库 分区 迁移 测试报告 公开 版本
![提示](https://static.bdocx.com/images/bang_tan.gif)