DB2数据库-表分区和迁移测试报告(公开版本)

D B2数据库
现场技术支持服务报告
目录
1.概述 (2)
1.1测试内容概述 (2)
1.2软件环境列表 (2)
1.3实施时间表 (2)
1.4系统概要描述 (2)
2.测试方案 (4)
3.测试步骤 (5)
3.1创建新的表空间 (5)
3.1.1检查现有的存储空间 (5)
3.1.2先建立逻辑卷，每个10GB，共12个 (5)
3.1.3定义缓冲池（每节点512MB） (5)
3.1.4创建数据表空间（每个节点40GB，共80GB） (5)
3.1.5创建索引表空间： (6)
3.2创建新的分区表和迁移表 (6)
3.2.1运行脚本创建新表，该表使用分区和压缩技术,赋权限。

(6)
3.2.2拷贝表的数据 (14)
3.2.3修改表名 (16)
3.2.4修改索引名 (17)
3.2.5创建索引 (17)
4.测试总结和分析 (23)
4.1测试总结 (23)
4.2分析 (23)
1.概述
1.1 测试内容概述
随着数据量的增长,数据库在初始设计时,把大部分数据都集中存放在表空间TS_LARGE_DATA01,导致该表空间急剧增长,并且大表和小表均存放在同一表空间,不利于IO负载的均衡。

将一个大表从原来的表空间迁移到新建的一个表空间，并对表进行分区和压缩。

1.2 软件环境列表
1.3 实施时间表
1.4 系统概要描述
1选择一个较大的表，从原表空间分离出来, 从表空间TS_LARGE_DA TA01移出，放入新的表空间。

2 使用表分区技术,按照“管理机关市级代码(GLJGSJ_DM)”字段分区存储，广州市按区级分区，其他按地市级划分。

3 使用表压缩技术。

3.1 创建新的表空间
3.1.1检查现有的存储空间
在不增加新硬盘的状况下，目前三个卷组ssfxfsvg,ssfxvg01,ssfxvg02剩余的空间分别为30GB，40GB，50GB，一共120GB。

测试系统有两个数据库节点，因此每个节点60GB，再建立一个新的数据表空间40GB，再建立一个新的索引表空间20GB。

3.1.2先建立逻辑卷，每个10GB，共12个
mklv -t raw -U db2sjck -G db2adm -e x -y L_DT3_LV00_01 ssfxfsvg 80
mklv -t raw -U db2sjck -G db2adm -e x -y L_DT3_LV00_02 ssfxfsvg 80
mklv -t raw -U db2sjck -G db2adm -e x -y L_DT3_LV00_03 ssfxfsvg 80
mklv -t raw -U db2sjck -G db2adm -e x -y L_DT3_LV00_04 ssfxvg01 80
mklv -t raw -U db2sjck -G db2adm -e x -y L_DT3_LV01_01 ssfxvg01 80
mklv -t raw -U db2sjck -G db2adm -e x -y L_DT3_LV01_02 ssfxvg01 80
mklv -t raw -U db2sjck -G db2adm -e x -y L_DT3_LV01_03 ssfxvg01 80
mklv -t raw -U db2sjck -G db2adm -e x -y L_DT3_LV01_04 ssfxvg02 80
mklv -t raw -U db2sjck -G db2adm -e x -y L_IND3_LV00_01 ssfxvg02 80
mklv -t raw -U db2sjck -G db2adm -e x -y L_IND3_LV00_02 ssfxvg02 80
mklv -t raw -U db2sjck -G db2adm -e x -y L_IND3_LV01_01 ssfxvg02 80
mklv -t raw -U db2sjck -G db2adm -e x -y L_IND3_LV01_02 ssfxvg02 80
3.1.3定义缓冲池（每节点512MB）
CREA TE DA TABASE PARTITION GROUP dbpg_large ON DBPARTITIONNUMS (0 to 1) CREA TE BUFFERPOOL dbpg_large IMMEDIA TE DA TABASE PARTITION GROUP dbpg_large SIZE 16384 AUTOMA TIC PAGESIZE 32 K
3.1.4创建数据表空间（每个节点40GB，共80GB）
db2 => CREA TE LARGE TABLESPACE TS_LARGE_DA TA03 IN DA TABASE PARTITION GROUP dbpg_large PAGESIZE 32 K MANAGED BY DA TABASE USING (device '/dev/rL_DT3_LV00_01' 10200m,device '/dev/rL_DT3_LV00_02' 10200m,device
'/dev/rL_DT3_LV00_03' 10200m,device '/dev/rL_DT3_LV00_04' 10200m) ON DBPARTITIONNUMS (0) USING (device '/dev/rL_DT3_LV01_01' 10200m,device '/dev/rL_DT3_LV01_02' 10200m,device '/dev/rL_DT3_LV01_03' 10200m,device '/dev/rL_DT3_LV01_04' 10200m) ON DBPARTITIONNUMS (1) BUFFERPOOL dbpg_large
DB20000I The SQL command completed successfully.
3.1.5创建索引表空间：
db2 => CREA TE LARGE TABLESPACE TS_LARGE_IND03 IN DA TABASE PARTITION GROUP dbpg_large PAGESIZE 32 K MANAGED BY DA TABASE USING (device '/dev/rL_IND3_LV00_01' 10200m,device '/dev/rL_IND3_LV00_02' 10200m) ON DBPARTITIONNUMS (0) USING (device '/dev/rL_IND3_LV01_01' 10200m,device '/dev/rL_IND3_LV01_02' 10200m) ON DBPARTITIONNUMS (1) BUFFERPOOL dbpg_large
DB20000I The SQL command completed successfully.
3.2 创建新的分区表和迁移表
3.2.1运行脚本创建新表，该表使用分区和压缩技术,赋权
限。

[nhdb_test02:db2sjck]db2 -tvf ./crt_table_sbzsmx_new.sql
CREATE TABLE "DB_SSFX"."T_FX_SBZSMX_NEW" (
"SJRQ" VARCHAR(8),
"YZFSJE_JE" DECIMAL(18,2),
"YZZYJ_JE" DECIMAL(18,2),
"YZSJ_JE" DECIMAL(18,2),
"YZDSJ_JE" DECIMAL(18,2),
"YZQXJ_JE" DECIMAL(18,2),
"YZXZJ_JE" DECIMAL(18,2),
"JKFSJE_JE" DECIMAL(18,2),
"JKZYJ_JE" DECIMAL(18,2),
"JKSJ_JE" DECIMAL(18,2),
"JKDSJ_JE" DECIMAL(18,2),
"JKQXJ_JE" DECIMAL(18,2),
"JKXZJ_JE" DECIMAL(18,2),
"PZ_XH" DECIMAL(16,0) NOT NULL, "YZMX_XH" DECIMAL(6,0) NOT NULL,
"JK_XH" DECIMAL(16,0),
"JKLSMXH_XH" DECIMAL(6,0),
"ZSXM_DM" VARCHAR(2),
"ZSPM_DM" VARCHAR(6),
"SFSSQ_QSRQ" DATE,
"SFSSQ_ZZRQ" DATE,
"SB_RQ" DATE,
"YZFS_RQ" DATE,
"JK_RQ" DATE,
"RK_RQ" DATE,
"JGRJ_SJ" DATE,
"TF_SJ" DATE,
"JK_QX" DATE,
"SBFS_DM" VARCHAR(2),
"ZSFS_DM" VARCHAR(2),
"ZSDLFS_DM" VARCHAR(2),
"JKFS_DM" VARCHAR(2),
"SBSX_DM" VARCHAR(1),
"SKSX1_DM" VARCHAR(2),
"SKSX2_DM" VARCHAR(2),
"YSFPBL_DM" VARCHAR(4),
"SJGXSX_DM" VARCHAR(1),
"SXGXSX_DM" VARCHAR(1),
"PZZL_DM" VARCHAR(5),
"JKPZLX_DM" VARCHAR(2),
"WSZL_DM" VARCHAR(6),
"JMYY_DM" VARCHAR(2),
"TZLX_DM" VARCHAR(2),
"DJLX_DM" VARCHAR(2),
"DZSX_DM" VARCHAR(2),
"ZSJG_DM" VARCHAR(11),
"HSJG_DM" VARCHAR(11),
"JCJG_DM" VARCHAR(11),
"XZ_DM" VARCHAR(10),
"SBRY_DM" VARCHAR(11),
"ZSRY_DM" VARCHAR(11),
"SGY_DM" VARCHAR(11),
"NSRBM" VARCHAR(20),
"NSRNBM" DECIMAL(10,0),
"NSRFLM" VARCHAR(20),
"NSR_MC" VARCHAR(200),
"LXDH_DH" VARCHAR(30),
"FDDBR_MC" VARCHAR(50),
"BSY_MC" VARCHAR(30),
"SJJY_DZ" VARCHAR(200),
"DZSPH_XH" DECIMAL(16,0),
"PIAOZZL_DM" VARCHAR(5),
"PZZB_DM" VARCHAR(6),
"PZHM" VARCHAR(12),
"RJ_XH" DECIMAL(16,0),
"ZCLX_DM" VARCHAR(3),
"HYML_DM" VARCHAR(2),
"HYDL_DM" VARCHAR(2),
"HYZXL_DM" VARCHAR(4),
"LSGX_DM" VARCHAR(2),
"TSQYLB" VARCHAR(2),
"ZDSY_BZ" VARCHAR(1),
"DKDJ_DM" VARCHAR(1),
"SDH_BZ" VARCHAR(1),
"WZ_BZ" VARCHAR(1),
"GSLX_DM" VARCHAR(2),
"SKYYZHID" VARCHAR(8),
"KGLX_DM" VARCHAR(2),
"YKP_BJ" VARCHAR(1),
"ZF_BJ" VARCHAR(1),
"YHHB_DM" VARCHAR(4),
"SF_BZ" VARCHAR(1),
"YSKM_DM" VARCHAR(9),
"JS_YJ" DECIMAL(18,2),
"LR_SJ" TIMESTAMP,
"XG_SJ" TIMESTAMP,
"DJFS_RQ" DATE,
"ZTFS_RQ" DATE,
"JM_RQ" DATE,
"DJ_RQ" DATE,
"SJJYDZSZXZQY_DM" VARCHAR(6),
"DJ_ZT" VARCHAR(2),
"GLJGSJ_DM" VARCHAR(11),
"GLJGQXJ_DM" VARCHAR(11),
"GLJGXZJ_DM" VARCHAR(11),
"SZSJGH_BZ" VARCHAR(1),
"YCXSY_BZ" VARCHAR(1) DEFAULT '0',
"HZSK_BZ" VARCHAR(1) DEFAULT '0',
"DZZR_BZ" VARCHAR(1) DEFAULT '0',
"QQLX_DM" VARCHAR(1) DEFAULT '0',
"YQNSJK_BZ" VARCHAR(1) DEFAULT '0',
"JMDJWSH" DECIMAL(10,0),
"JMDJWSMX_XH" DECIMAL(6,0),
"KQ_BZ" VARCHAR(1),
"TKXZ_DM" CHARACTER(1),
"XGLJG_DM" VARCHAR(11),
"YSGY_DM" VARCHAR(11),
"XZDSY_BZ" VARCHAR(1),
"KCSE_JE" DECIMAL(18,2),
"ZNTS" DECIMAL(6,0),
"SL" DECIMAL(16,6),
"PZSL" DECIMAL(6,0),
"KPRY_DM" VARCHAR(11),
"TFZSJG_DM" VARCHAR(11),
"YH_DM" VARCHAR(9),
"YH_ZH" VARCHAR(30),
"SKYH_DM" VARCHAR(9),
"SKYH_ZH" VARCHAR(30),
"KJ_RQ" DATE,
"QS_RQ" DATE,
"YJK_QX" DATE,
"DWSBH" VARCHAR(18),
"SBGLJG_DM" VARCHAR(11),
"SFSX_DM" VARCHAR(2)
)
INDEX IN TS_LARGE_IND03 PARTITION BY RANGE (GLJGSJ_DM NULLS LAST) (PARTITION SBZSMX_FQSHJI STARTING FROM ('244000000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQZHSHU STARTING FROM ('244009000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQGZDS STARTING FROM ('244010200') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQGZLW STARTING FROM ('244010300') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQGZYX STARTING FROM ('244010400') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQGZHZ STARTING FROM ('244010500') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQGZTH STARTING FROM ('244010600') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQGZFC STARTING FROM ('244010700') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQGZBY STARTING FROM ('244011100') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQGZHP STARTING FROM ('244011200') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQGZPY STARTING FROM ('244011300') INCLUSIVE IN TS_LARGE_DATA03 ,PARTIT ION SBZSMX_FQGZHD STARTING FROM ('244011400') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQGZNS STARTING FROM ('244011500') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQGZLG STARTING FROM ('244011600') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQGZDJ STARTING FROM ('244017000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQGZNK STARTING FROM ('244017900') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQGZZC STARTING FROM ('244018300') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQGZCH STARTING FROM ('244018400') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQGZKF STARTING FROM ('244019700') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION
SBZSMX_FQSG STARTING FROM ('244020000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQSZ STARTING FROM ('244030000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQZH STARTING FROM ('244040000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQST STARTING FROM ('244050000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQFS STARTING FROM ('244060000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQJM STARTING FROM ('244070000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQZZ STARTING FROM ('244080000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQMM STARTING FROM ('244090000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQZQ STARTING FROM ('244120000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQHZ STARTING FROM ('244130000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQMZ STARTING FROM ('244140000') INCLUSIVE IN TS_L ARGE_DATA03 ,PARTITION SBZSMX_FQSW STARTING FROM ('244150000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQHY STARTING FROM ('244160000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQYJ STARTING FROM ('244170000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQQY STARTING FROM ('244180000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQDG STARTING FROM ('244190000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQZS STARTING FROM ('244200000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQCZ STARTING FROM ('244510000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQJY STARTING FROM ('244520000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQYF STARTING FROM ('244530000') INCLUSIVE IN TS_LARGE_DATA03 ,PARTITION SBZSMX_FQSD STARTING FROM ('244810000') INCLUSIVE ENDING AT ('244810010') INCLUSIVE IN TS_LARGE_DATA03 ) compress yes DISTRIBUTE BY HASH ( "PZ_XH" );
ALTER TABLE "DB_SSFX"."T_FX_SBZSMX_NEW"
DATA CAPTURE NONE
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;
COMMENT ON TABLE "DB_SSFX"."T_FX_SBZSMX_NEW" IS '申报征收明细';
COMMENT ON "DB_SSFX"."T_FX_SBZSMX_NEW" (
"SJRQ" IS '数据日期是增量数据的发生日期(业务日期),格式：YYYYMMDD',
"YZFSJE_JE" IS '应征发生金额:应征税费合计',
"YZZYJ_JE" IS '应征中央级金额',
"YZSJ_JE" IS '应征省级金额',
"YZDSJ_JE" IS '应征地市级金额',
"YZQXJ_JE" IS '应征区县级金额',
"YZXZJ_JE" IS '应征乡镇级金额',
"JKFSJE_JE" IS '缴款发生金额：缴款发生金额的合计。

',
"JKZYJ_JE" IS '缴款中央级金额',
"JKSJ_JE" IS '缴款省级金额',
"JKDSJ_JE" IS '缴款地市级金额',
"JKQXJ_JE" IS '缴款区县级金额',
"JKXZJ_JE" IS '缴款乡镇级金额',
"PZ_XH" IS '凭证序号：关联表 DB_SBZS.T_ZS_YZMX 的 PZ_XH 字段。

它与YZMX_XH一起关联一条应征记录。

来自T_ZS_YZMX',
"YZMX_XH" IS '应征明细序号：关联表 DB_SBZS.T_ZS_YZMX 的 YZMX_XH 字段。

它与 PZ_XH 一起关联一条应征记录。

来自T_ZS_YZMX',
"JK_XH" IS '缴款流水号：关联表 DB_SBZS.T_ZS_XJJKMX/T_ZS_DJJKMX/T_ZS_ZJJKMX 的
JK_XH 字段。

它与 JKLSMXH_XH 一起关联一条缴款记录，来自各个缴款表', "JKLSMXH_XH" IS '缴款流水明细号：关联表
DB_SBZS.T_ZS_XJJKMX/T_ZS_DJJKMX/T_ZS_ZJJKMX 的 JKLSMXH_XH 字段。

它与 JK_XH 一起关联一条缴款记录，来自各个缴款表',
"ZSXM_DM" IS '征收项目维(税费种类)代码。

02-营业税 04-企业所得税 06-个人所得税
66-社保金 A6-残保金，来自T_ZS_YZMX',
"ZSPM_DM" IS '征收品目代码，来自T_ZS_YZMX',
"SFSSQ_QSRQ" IS '税费所属期起，来自T_ZS_YZMX',
"SFSSQ_ZZRQ" IS '税费所属期止，来自T_ZS_YZMX',
"SB_RQ" IS '申报日期:产生应征的申报表的申报日期。

来自T_SB_YZPZ',
"YZFS_RQ" IS '应征发生日期，来自T_ZS_YZMX',
"JK_RQ" IS '缴款日期，缴款发生日期，来自t_zs_xjjkmx.kj_rq；T_zs_djjkmx.jk_rq；T_zs_zjjkmx.rk_rq',
"RK_RQ" IS '入库日期。

来自T_ZS_ZJJKMX',
"JGRJ_SJ" IS '机构日结时间，来自T_ZS_JGRJXX',
"TF_SJ" IS '票证填发时间，来自T_ZS_PZSYQK',
"JK_QX" IS '缴款期限，来自T_ZS_YZMX',
"SBFS_DM" IS '申报方式代码：申报方式维的明细代码：10-上门申报 20-邮件申报 30-电子申报 31-电话申报 32-网络申报 33-e税通申报 40-代理申报 50-银行申报 60－简易申报等，来自T_SB_YZPZ',
"ZSFS_DM" IS '征收方式代码：征收方式维的代码：10-查帐征收 21-核定应税所得率（按收入） 22-核定应税所得率（按成本费用） 43-定期定率征收 23-定额加发票（应税所得率）41-定额加发票（带征）42-定期定额（定额加发票）50-其他。

来自T_ZS_YZMX', "ZSDLFS_DM" IS '征收代理方式维的代码，01-自行申报 02-无申报临时经营 03-其他 20-代扣(代收)代缴 30-委托代征数据来自T_SB_YZPZ',
"JKFS_DM" IS '现金、支票、缴款书、ETS、POS机、批量预储等，来自：各个缴款表', "SBSX_DM" IS '正常申报,自查补报,被查申报,延期申报预缴，来自T_SB_YZPZ',
"SKSX1_DM" IS '税款滞罚属性维的明细代码：税款属性1代码。

10-正税 20-滞纳金 30-罚款等，来自T_ZS_YZMX',
"SKSX2_DM" IS '查补属性维的明细代码：税款属性2代码。

1X-正常税款 20-评税税金 30-自查补税 40-稽查税款，来自T_ZS_YZMX',
"YSFPBL_DM" IS '收入共享维的明细代码：预算分配比例代码。

省级共享属性和市县共享属性的分组统计根据本代码进行分组。

数据来自:在同步应征的时候计算',
"SJGXSX_DM" IS '省级共享属性代码:0-非省级收入;1-省级固定收;2-省级共享收入',
"SXGXSX_DM" IS '市县共享属性代码:0-非市县收入;1-市县固定收入;2-市县共享收入', "PZZL_DM" IS '凭证种类代码:原始凭证维的明细代码：凭证种类代码。

10108-营业税申报表 11602-税务行政处罚决定书 10191-纳税申报表(综合),来自T_SB_YZPZ.PZZL_DM', "WSZL_DM" IS '文书种类代码，来自：减免抵缴对应的文书种类',
"JMYY_DM" IS '减免原因维的代码：01-校办工厂企业 02-民政福利企业 03-新办企业 04-涉外企业 05-高新技术企业 06-受灾企业 07-乡镇企业 08-劳动服务企业 09-老少边穷地区企业等数据来自T_SB_JMXX',
"TZLX_DM" IS '调帐类型维的明细代码：00-正常 10-被减免 11-被抵缴 12-被转至帐外呆帐 13-被确认为多缴 14-被欠税转移 15-被调帐 20-被分拆 22-被核销 23-被转入关停欠税
24-冲减免 25-冲退库等数据来自T_ZS_YZMX:（应征实际统计范围＝00＋10＋13）', "DJLX_DM" IS '抵缴类型维的明细代码：11-退税抵欠 12-退税抵缴 21-利息抵欠 22-利息抵缴，数据来自T_ZS_DJXX',
"DZSX_DM" IS '待征属性代码：01-未到期应缴税款 20-经批准缓征税款 31-关停企业欠税32-空壳企业欠税 11-往年陈欠 12-本年新欠 40-呆帐转入，数据来自T_ZS_YZMX', "ZSJG_DM" IS '征收机关代码：税务机关的代码。

来自T_ZS_YZMX',
"HSJG_DM" IS '核算机关代码：税务机关的代码。

来自T_ZS_YZMX',
"JCJG_DM" IS '稽查机关代码：税务机关的代码。

来自T_ZS_YZMX',
"XZ_DM" IS '乡镇代码：省内行政区域维的镇级代码（非明细代码）。

',
"SBRY_DM" IS '申报人员代码，来自T_ZS_YZMX.lrry_dm',
"ZSRY_DM" IS '征收人员代码，来自t_zs_xjjkmx.lrry_dm；T_zs_djjkmx.lrry_dm；
T_zs_zjjkmx.lrry_dm',
"SGY_DM" IS '税管员代码：帐目发生时期的税务登记信息之一，来自T_DJ_JGNSRFB', "NSRBM" IS '纳税人编码：帐目发生时期的税务登记信息之一',
"NSRNBM" IS '纳税人内部码，也可能是其他的编码，如车牌号，来自T_ZS_YZMX',
"NSRFLM" IS '纳税人分类码：帐目发生时期的税务登记信息之一，来自T_DJ_JGNSRFB', "NSR_MC" IS '纳税人名称：帐目发生时期的税务登记信息之一',
"LXDH_DH" IS '联系电话：帐目发生时期的税务登记信息之一',
"FDDBR_MC" IS '法定代表人、董事长、负责人、业主姓名，来自T_DJ_JGNSR',
"BSY_MC" IS '办税员姓名，来自T_DJ_JGNSR',
"SJJY_DZ" IS '实际经营地址：帐目发生时期的税务登记信息之一，来自T_DJ_JGNSR', "DZSPH_XH" IS '电子税票号，来自T_ZS_PZSYQK',
"PIAOZZL_DM" IS '票证种类维的明细代码:012-税收通用缴款书(电脑平推) 021-税收通用完税证(大额手写)，来自T_ZS_PZSYQK',
"PZZB_DM" IS '票证字别维的代码：00000-没有字别 20041-(200401)粤地 20051-(2005-1)粤地，来自T_ZS_PZSYQK',
"PZHM" IS '票证号码，来自T_ZS_PZSYQK',
"RJ_XH" IS '日结流水号，来自T_ZS_YZMX',
"ZCLX_DM" IS '注册类型代码：帐目发生时期的税务登记信息之一，来自T_ZS_YZMX', "HYML_DM" IS '行业门类代码：帐目发生时期的税务登记信息之一，来自T_ZS_YZMX', "HYDL_DM" IS '行业大类代码：帐目发生时期的税务登记信息之一，来自T_ZS_YZMX', "HYZXL_DM" IS '行业中小类代码：帐目发生时期的税务登记信息之一，来自T_ZS_YZMX', "LSGX_DM" IS '隶属关系代码：帐目发生时期的税务登记信息之一，来自T_ZS_YZMX', "TSQYLB" IS '特殊企业类别：帐目发生时期的税务登记信息之一，来自T_DJ_JGNSRFB', "ZDSY_BZ" IS '重点税源标志：帐目发生时期的税务登记信息之一，来自T_DJ_JGNSRFB',
"DKDJ_DM" IS '*代扣代缴代码：表示纳税人税种核定事项中代扣代缴情况。

0-非代扣代缴1-兼代扣代缴 2-仅代扣代缴（需要通过核定表确定）',
"SDH_BZ" IS '双定户标志：1-是 0-不是数据来自：纳税人基本信息相关表中',
"WZ_BZ" IS '+外资标志：1-是 0-否。

数据来自：纳税人基本信息相关表中',
"GSLX_DM" IS '公司类型维的明细代码：10-跨国公司 11-跨国公司上市股份公司 12-跨国公司集团公司 13-跨国公司上市股份公司集团公司 20-上市股份公司 21-上市股份公司集团公司 30-集团公司 00-不属所列的公司。

数据来自：纳税人基本信息相关表中', "SKYYZHID" IS '待解户的账号的ID，根据这个ID可以找到唯一的一个待解户的账号，和收款银行的名称，数据来自：T_ZS_DJJKMX',
"KGLX_DM" IS '控股类型代码：帐目发生时期的税务登记信息之一，数据来自：纳税人基本信息相关表中',
"YKP_BJ" IS '表示这条记录已经清缴,不再是欠税 1-已开票 0-没有开票，来自
T_ZS_YZMX',
"ZF_BJ" IS '作废标志：1-作废 0-未作废,来自YZMX !',
"YHHB_DM" IS '收款银行行别代码，来自T_QS_SKZH',
"SF_BZ" IS '税费标志:1-税收收入 9-其它收入,依据征收项目',
"YSKM_DM" IS '预算科目代码，来自T_ZS_YZMX',
"JS_YJ" IS '计税依据，来自T_ZS_YZMX',
"LR_SJ" IS '录入时间',
"XG_SJ" IS '修改时间',
"DJFS_RQ" IS '待解发生日期：统一用待解库的JK_RQ',
"ZTFS_RQ" IS '在途发生日期：待解的汇总日期（注意不取现金汇总日期）',
"JM_RQ" IS '减免日期：征前减免的日期',
"DJ_RQ" IS '抵缴日期，来自T_ZS_DJXX',
"SJJYDZSZXZQY_DM" IS '实际经营地址所在行政区域代码：省内行政区域维的代码。

', "DJ_ZT" IS '纳税人状态维的代码：01-快速发证未录齐资料 02-登记资料未审核 20-正常30-停业 40-非正常 50-注销 60-失踪户 (取值见t_dm_gy_nsrzt代码表)',
"GLJGSJ_DM" IS '管理机关市级代码',
"GLJGQXJ_DM" IS '管理机关区县级代码',
"GLJGXZJ_DM" IS '管理机关乡镇级代码',
"SZSJGH_BZ" IS '省直属局管户标志：1-是 0-否,数据由分析系统后期维护，刷新时保持。

',
"YCXSY_BZ" IS '一次性税源标志：1-是 0-不是',
"HZSK_BZ" IS '缓征税款标志：1-是 0-不是',
"DZZR_BZ" IS '呆帐转入标志：1-是 0-不是',
"QQLX_DM" IS '清欠类型代码：0-未到期 1-本年欠税 2-往年欠税 3-逾期未缴款且未到当月底初始化时用来判断清欠类型： 1：已缴款：缴款日期与原缴款期限比较； 2：未缴款：物理日期与缴款期限比较；',
"YQNSJK_BZ" IS '逾期纳税缴款标志：1-是 0-不是',
"JMDJWSH" IS '减免抵缴文书号',
"JMDJWSMX_XH" IS '减免抵缴文书明细序号',
"KQ_BZ" IS '跨区申报标志：1-是 0-不是数据来自：管理机关和征收机关不一直(待咨询)',
"TKXZ_DM" IS '退库性质代码：用什么类型的退税用来抵缴',
"XGLJG_DM" IS '现管理机关代码：来自T_DJ_JGNSR；T_SB_LSNSRXX；T_DJ_ZRR（暂未用）',
"YSGY_DM" IS '现税管员代码:纳税人最新登记中的现税管员代码。

来自T_DJ_JGNSRFB（暂未用）',
"XZDSY_BZ" IS '现重点税源标志：纳税人最新登记中的重点税源标志，来自T_DJ_JGNSRFB （暂未用）',
"KCSE_JE" IS '? 扣除税额，来自T_ZS_YZMX',
"ZNTS" IS '? 滞纳天数，来自T_ZS_YZMX',
"SL" IS '? 税率，来自T_ZS_YZMX',
"PZSL" IS '? 票证数量，来自T_ZS_PZSYQK',
"KPRY_DM" IS '? 开票人员代码，来自T_ZS_PZSYQK',
"TFZSJG_DM" IS '? 开票机关代码,来自T_ZS_PZSYQK的ZSJG_DM',
"YH_DM" IS '? 扣款银行代码,来自T_DJ_NSRYHZH（通过T_ZS_ZJJKMX中的ZH_DM关联）', "YH_ZH" IS '? 扣款银行账号，来自T_DJ_NSRYHZH（通过T_ZS_ZJJKMX中的ZH_DM关联）', "SKYH_DM" IS '? 收款银行代码,来自T_QS_SKZH',
"SKYH_ZH" IS '? 收款银行账号,来自T_QS_SKZH',
"KJ_RQ" IS '扣款日期，来自现金、待解和直解表。

(delete)',
"QS_RQ" IS '清算日期,来自直解表(delete)',
"YJK_QX" IS '原缴款期限（delete）',
"DWSBH" IS '单位社保号',
"SBGLJG_DM" IS '社保管理机构',
"SFSX_DM" IS '社保属性' );
GRANT CONTROL ON TABLE "DB_SSFX"."T_FX_SBZSMX_NEW" TO USER "DB2SJCK";
GRANT CONTROL ON TABLE "DB_SSFX"."T_FX_SBZSMX_NEW" TO USER "DB_USER";
GRANT SELECT, INSERT, UPDATE, ALTER, DELETE,
INDEX, REFERENCES ON TABLE "DB_SSFX"."T_FX_SBZSMX_NEW" TO USER "DB2SJCK" WITH GRANT OPTION;
GRANT SELECT, INSERT, UPDATE, ALTER, DELETE,
INDEX, REFERENCES ON TABLE "DB_SSFX"."T_FX_SBZSMX_NEW" TO USER "DB2_USER" WITH GRANT OPTION;
DB20000I The SQL command completed successfully.
3.2.2拷贝表的数据
拷贝时间： 12:55—13：51
系统负荷：系统CPU 使用率 30% , 5个硬盘(hdiskpower28—47)读写总和速度约10MB/s ，Disk busy 2%-10%
表的行数： 19580367
执行脚本文件：copy_table.cmd
date >> /tmp/sinobest/copy_table.log
db2 connect to ssfx
db2 -tvf /tmp/sinobest/copy_table.sql >> /tmp/sinobest/copy_table.log
date >> /tmp/sinobest/copy_table.log
copy_table.sql 内容：
DECLARE DB_SSFX.T_FX_SBZSMX CURSOR FOR SELECT * FROM DB_SSFX.T_FX_SBZSMX; LOAD FROM DB_SSFX.T_FX_SBZSMX of CURSOR INSERT INTO DB_SSFX.T_FX_SBZSMX_NEW;
拷贝结果：
Tue Nov 16 12:55:04 BEIST 2010
DECLARE DB_SSFX.T_FX_SBZSMX CURSOR FOR SELECT * FROM DB_SSFX.T_FX_SBZSMX
DB20000I The SQL command completed successfully.
LOAD FROM DB_SSFX.T_FX_SBZSMX of CURSOR INSERT INTO DB_SSFX.T_FX_SBZSMX_NEW
Agent Type Node SQL Code Result
______________________________________________________________________________
LOAD 000 +00000000 Success.
______________________________________________________________________________
LOAD 001 +00000000 Success.
______________________________________________________________________________
PARTITION 001 +00000000 Success.
______________________________________________________________________________
RESULTS: 2 of 2 LOADs completed successfully.
______________________________________________________________________________
Summary of Partitioning Agents:
Rows Read = 19580367
Rows Rejected = 0
Rows Partitioned = 19580367
Summary of LOAD Agents:
Number of rows read = 19580367
Number of rows skipped = 0
Number of rows loaded = 19580367
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 19580367
Tue Nov 16 13:51:09 BEIST 2010
新建的表空间使用情况：
使用表分区和压缩技术，迁移到新表空间使用情况: （两个节点，每个节点４ＧＢ数据．因此该表有８ＧＢ数据，19580367行记录）
Tablespace ID = 11
Name = TS_LARGE_DATA03
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0000
Detailed explanation:
Normal
Total pages = 1305600
Useable pages = 1305472
Used pages = 129280
Free pages = 1176192
High water mark (pages) = 129280
Page size (bytes) = 32768
Extent size (pages) = 32
Prefetch size (pages) = 768
Number of containers = 4
DB21011I In a partitioned database server environment, only the table spaces
on the current node are listed.
3.2.3修改表名
修改表名:
db2 => rename table DB_SSFX.T_FX_SBZSMX to T_FX_SBZSMX_OLD
DB20000I The SQL command completed successfully.
db2 => rename table DB_SSFX.T_FX_SBZSMX_NEW to T_FX_SBZSMX
DB20000I The SQL command completed successfully.
3.2.4修改索引名
修改旧的索引改名：
RENAME INDEX "DB_SJCK"."I_SBZSMX_DJFSRQ" to "I_SBZSMX_DJFSRQ_OLD"
RENAME INDEX "DB_SJCK"."I_SBZSMX_DJRQ" to "I_SBZSMX_DJRQ_OLD"
RENAME INDEX "DB_SJCK"."I_SBZSMX_GLJGSJ" to "I_SBZSMX_GLJGSJ_OLD"
RENAME INDEX "DB_SJCK"."I_SBZSMX_JGRQ" to I_SBZSMX_JGRQ_OLD
RENAME INDEX "DB_SJCK"."I_SBZSMX_JKRQ" to I_SBZSMX_JKRQ_OLD
RENAME INDEX "DB_SJCK"."I_SBZSMX_JKXH" to I_SBZSMX_JKXH_OLD
RENAME INDEX "DB_SJCK"."I_SBZSMX_JMRQ" to I_SBZSMX_JMRQ_OLD
RENAME INDEX "DB_SJCK"."I_SBZSMX_NSRBM" to I_SBZSMX_NSRBM_OLD
RENAME INDEX "DB_SJCK"."I_SBZSMX_NSRNBM" to I_SBZSMX_NSRNBM_OLD
RENAME INDEX "DB_SJCK"."I_SBZSMX_PZXH" to I_SBZSMX_PZXH_OLD
RENAME INDEX "DB_SJCK"."I_SBZSMX_RKRQ" to I_SBZSMX_RKRQ_OLD
RENAME INDEX "DB_SJCK"."I_SBZSMX_SBRQ" to I_SBZSMX_SBRQ_OLD
RENAME INDEX "DB_SJCK"."I_SBZSMX_SFSSQ" to I_SBZSMX_SFSSQ_OLD
RENAME INDEX "DB_SJCK"."I_SBZSMX_SGY" to I_SBZSMX_SGY_OLD
RENAME INDEX "DB_SJCK"."I_SBZSMX_TZLX" to I_SBZSMX_TZLX_OLD
RENAME INDEX "DB_SJCK"."I_SBZSMX_YSFPBL" to I_SBZSMX_YSFPBL_OLD
RENAME INDEX "DB_SJCK"."I_SBZSMX_YZFSRQ" to I_SBZSMX_YZFSRQ_OLD
RENAME INDEX "DB_SJCK"."I_SBZSMX_ZCLX" to I_SBZSMX_ZCLX_OLD
RENAME INDEX "DB_SJCK"."I_SBZSMX_ZSXM" to I_SBZSMX_ZSXM_OLD
RENAME INDEX "DB_SJCK"."I_SBZSMX_ZTFSRQ" to I_SBZSMX_ZTFSRQ_OLD
RENAME INDEX "DB_SSFX"."I_SBZSMX_GLJGXZJ" to I_SBZSMX_GLJGXZJ_OLD
RENAME INDEX "DB_SSFX"."SBZSMX_GLJGQXJ" to SBZSMX_GLJGQXJ_OLD
RENAME INDEX "DB_SSFX"."SBZSMX_JK_QX" to SBZSMX_JK_QX_OLD
RENAME INDEX "DB_SSFX"."SBZSMX_QQLX" to SBZSMX_QQLX_OLD
3.2.5创建索引
15:30 -15:35 (创建索引时IO读写速度75MB/s CPU 8%)
CREATE INDEX "DB_SJCK"."I_SBZSMX_DJFSRQ" ON "DB_SSFX"."T_FX_SBZSMX" ( "DJFS_RQ" ASC ) DISALLOW REVERSE SCANS
其他索引(23个)创建：15:53:07---16:31 (创建索引时IO读写速度100MB/s CPU 50%
DISK busy 50%-100%)
创建索引脚本：crt_sbzsmx_index.cmd
ate >> /tmp/sinobest/crt_sbzsmx_index.log
db2 connect to ssfx
db2 -tvf /tmp/sinobest/crt_sbzsmx_index.sql >>/tmp/sinobest/crt_sbzsmx_index.log
date>> /tmp/sinobest/crt_sbzsmx_index.log
crt_sbzsmx_index.sql内容：
CREA TE INDEX "DB_SJCK"."I_SBZSMX_DJRQ"
ON "DB_SSFX"."T_FX_SBZSMX"
( "DJ_RQ" ASC )
DISALLOW REVERSE SCANS;
GRANT CONTROL ON INDEX "DB_SJCK"."I_SBZSMX_DJRQ" TO USER "DB_USER";
CREA TE INDEX "DB_SJCK"."I_SBZSMX_GLJGSJ"
ON "DB_SSFX"."T_FX_SBZSMX"
( "GLJGSJ_DM" ASC )
DISALLOW REVERSE SCANS;
GRANT CONTROL ON INDEX "DB_SJCK"."I_SBZSMX_GLJGSJ" TO USER "DB_USER";
CREA TE INDEX "DB_SJCK"."I_SBZSMX_JGRQ"
ON "DB_SSFX"."T_FX_SBZSMX"
( "RJ_XH" ASC )
DISALLOW REVERSE SCANS;
GRANT CONTROL ON INDEX "DB_SJCK"."I_SBZSMX_JGRQ" TO USER "DB_USER";
CREA TE INDEX "DB_SJCK"."I_SBZSMX_JKRQ"
ON "DB_SSFX"."T_FX_SBZSMX"
( "JK_RQ" ASC )
DISALLOW REVERSE SCANS;
GRANT CONTROL ON INDEX "DB_SJCK"."I_SBZSMX_JKRQ" TO USER "DB_USER";
CREA TE INDEX "DB_SJCK"."I_SBZSMX_JKXH"
ON "DB_SSFX"."T_FX_SBZSMX"
( "JK_XH" ASC, "JKLSMXH_XH" ASC )
DISALLOW REVERSE SCANS;
GRANT CONTROL ON INDEX "DB_SJCK"."I_SBZSMX_JKXH" TO USER "DB_USER";
CREA TE INDEX "DB_SJCK"."I_SBZSMX_JMRQ"
ON "DB_SSFX"."T_FX_SBZSMX"
( "JM_RQ" ASC )
DISALLOW REVERSE SCANS;
GRANT CONTROL ON INDEX "DB_SJCK"."I_SBZSMX_JMRQ" TO USER "DB_USER";
CREA TE INDEX "DB_SJCK"."I_SBZSMX_NSRBM"
ON "DB_SSFX"."T_FX_SBZSMX"
( "NSRBM" ASC )
DISALLOW REVERSE SCANS;
GRANT CONTROL ON INDEX "DB_SJCK"."I_SBZSMX_NSRBM" TO USER "DB_USER";
CREA TE INDEX "DB_SJCK"."I_SBZSMX_NSRNBM"
ON "DB_SSFX"."T_FX_SBZSMX"
( "NSRNBM" ASC )
DISALLOW REVERSE SCANS;
GRANT CONTROL ON INDEX "DB_SJCK"."I_SBZSMX_NSRNBM" TO USER "DB_USER";
CREA TE UNIQUE INDEX "DB_SJCK"."I_SBZSMX_PZXH"
ON "DB_SSFX"."T_FX_SBZSMX"
( "PZ_XH" ASC, "YZMX_XH" ASC )
DISALLOW REVERSE SCANS;
GRANT CONTROL ON INDEX "DB_SJCK"."I_SBZSMX_PZXH" TO USER "DB_USER";
CREA TE INDEX "DB_SJCK"."I_SBZSMX_RKRQ"
ON "DB_SSFX"."T_FX_SBZSMX"
( "RK_RQ" ASC )
DISALLOW REVERSE SCANS;
GRANT CONTROL ON INDEX "DB_SJCK"."I_SBZSMX_RKRQ" TO USER "DB_USER";。