创建表空间、分区表、索引；初始分区表数据
db2 connect to saldev
db2 "create tablespace tbs1 managed by database using ( file'/dbfile/database/saldev/db2inst1/NODE0000/SALDEV/tbs1.bat' 200M)"
db2 "create tablespace tbs2 managed by database using ( file '/dbfile/database/saldev/db2inst1/NODE0000/SALDEV/tbs2.bat' 200M)"
db2 "create tablespace tbs3 managed by database using ( file '/dbfile/database/saldev/db2inst1/NODE0000/SALDEV/tbs3.bat' 200M)"
db2 "create tablespace tbs4 managed by database using ( file '/dbfile/database/saldev/db2inst1/NODE0000/SALDEV/tbs4.bat' 200M)"
db2 "create tablespace tbs5 managed by database using ( file '/dbfile/database/saldev/db2inst1/NODE0000/SALDEV/tbs5.bat' 200M)"
db2 "create tablespace tbs6 managed by database using ( file '/dbfile/database/saldev/db2inst1/NODE0000/SALDEV/tbs6.bat' 200M)"
db2 "create tablespace tbs7 managed by database using ( file '/dbfile/database/saldev/db2inst1/NODE0000/SALDEV/tbs7.bat' 200M)"
db2 "create tablespace tbs8 managed by database using ( file '/dbfile/database/saldev/db2inst1/NODE0000/SALDEV/tbs8.bat' 200M)"
db2 "create tablespace tbs9 managed by database using ( file '/dbfile/database/saldev/db2inst1/NODE0000/SALDEV/tbs9.bat' 200M)"
CREATE TABLE "DB
)
PARTITION BY RANGE(COMPID) (STARTING 'HP021105941720969' ENDING 'HP021105941720969' in tbs1,
COMMENT ON COLUMN "DB"."TBAR01PARTITION"."CREATETIME" IS '';
ALTER TABLE "DB"."TBAR01PARTITION" ADD PRIMARY KEY ("AR01ID");
db2 insert into db.tbar01partition select * from db.tbar01;
数据规模：110万行
CREATE INDEX "DB"."TBAR01_compid" ON "DB"."TBAR01"
CREATE INDEX "DB"."TBAR01_compid" ON "DB"."TBAR01PARTITION"
查看执行效率：db2batch -d saldev -a db2inst1/db2admin -f '/home/db2inst1/123.txt' -i short -o p 3 f -1 r 0
执行效率对比：
/home/db2inst1/123.txt: select compid,INDEXNO,BALANCEDATE,CASTSTATE from db.tbar01 where compid='tuid';
Total Time:
/home/db2inst1/123.txt: select compid,INDEXNO,BALANCEDATE,CASTSTATE from db.tbar01 where compid='HP021107313616236';
Total Time:
/home/db2inst1/123.txt: select compid,INDEXNO,BALANCEDATE,CASTSTATE from db.tbar01partition where compid='tuid';
Total Time:
/home/db2inst1/123.txt: select compid,INDEXNO,BALANCEDATE,CASTSTATE from db.tbar01partition where compid='HP021107313616236';
Total Time:
/home/db2inst1/123.txt: select compid,count(CASTSTATE) from db.tbar01 where compid='tuid' or compid='HP021107313616236' group by compid;
Total Time:
/home/db2inst1/123.txt: select compid,count(CASTSTATE) from db.tbar01partition where compid='tuid' or compid='HP021107313616236' group by compid;
/home/db2inst1/123.txt: select compid,count(CASTSTATE) from db.tbar01 where compid='tuid' or compid='HP021107313616236' or compid='HP021107313616235' group by compid;
Total Time:
/home/db2inst1/123.txt: select compid,count(CASTSTATE) from db.tbar01partition where compid='tuid' or compid='HP021107313616236' or compid='HP021107313616235' group by compid;
Total Time:
执行计划对比：
select compid,INDEXNO,BALANCEDATE,CASTSTATE from db.tbar01 where compid='HP021107313616236';
select compid,INDEXNO,BALANCEDATE,CASTSTATE from db.tbar01partition where compid='HP021107313616236';
select compid from db.tbar01partition where compid='HP021107313616236';
select compid,count(CASTSTATE) from db.tbar01 where compid='tuid' or compid='HP021107313616236' or compid='HP021107313616235' group by compid;
select compid,count(CASTSTATE) from db.tbar01partition where compid='tuid' or compid='HP021107313616236' or compid='HP021107313616235' group by compid;
结果分析：
不支持的数据类型
- 用户定义的类型（结构化）
- LONG VARCHAR
- LONG VARCHAR FOR BIT DATA
- BLOB
- BINARY LARGE OBJECT
- CLOB
- CHARACTER LARGE OBJECT
- DBCLOB
- LONG VARGRAPHIC
- REF
- C 变长字符串
- Pascal 变长字符串
在分区表中，不支持 XML 数据类型。
<span class="ph b" style="font-weight: bold;">CREATE TABLE</span> sales (year INT, month INT) <span class="ph b" style="font-weight: bold;">PARTITION BY RANGE</span>(year, month) <span class="ph b" style="font-weight: bold;">(STARTING FROM</span> (2001, 1) <span class="ph b" style="font-weight: bold;">ENDING</span> (2001,3) <span class="ph b" style="font-weight: bold;">IN</span> tbsp1, <span class="ph b" style="font-weight: bold;">ENDING</span> (2001,6) <span class="ph b" style="font-weight: bold;">IN</span> tbsp2, <span class="ph b" style="font-weight: bold;">ENDING</span> (2001,9) <span class="ph b" style="font-weight: bold;">IN</span> tbsp3, <span class="ph b" style="font-weight: bold;">ENDING</span> (2001,12) <span class="ph b" style="font-weight: bold;">IN</span> tbsp4, <span class="ph b" style="font-weight: bold;">ENDING</span> (2002,3) <span class="ph b" style="font-weight: bold;">IN</span> tbsp5, <span class="ph b" style="font-weight: bold;">ENDING</span> (2002,6) <span class="ph b" style="font-weight: bold;">IN</span> tbsp6, <span class="ph b" style="font-weight: bold;">ENDING</span> (2002,9) <span class="ph b" style="font-weight: bold;">IN</span> tbsp7, <span class="ph b" style="font-weight: bold;">ENDING</span> (2002,12) <span class="ph b" style="font-weight: bold;">IN</span> tbsp8)这将生成 8 个数据 分区，即 2001 年和 2002 年的每个季度有一个数据 分区。
- 当将多个列用作表分区键时，将把这些列视为组合键（类似于索引中的组合键），其中，后面的列依赖于前面的列。指定的每个起始值或结束值（所有列一起）不能超出 512 个字符。此限制与 SYSCAT.DATAPARTITIONS 目录视图中的 LOWVALUE 和 HIGHVALUE 列大小对应。如果指定超出 512 个字符的起始值或结束值，就会导致错误 SQL0636N，原因码为 9。
- 表分区是多列的，而不是多维的。在表分区中，使用的所有列都包含在单个维中。
生成列
可以将生成列用作表分区键。此示例创建包含 12 个数据分区的表，即每个月一个数据分区。对于任何年份，一月份的所有行都将被放到第一个数据分区中，二月份的行将被放到第二个数据分区中，依此类推。
示例 1
<span class="ph b" style="font-weight: bold;">CREATE TABLE</span> monthly_sales (sales_date date,
sales_month int <span class="ph b" style="font-weight: bold;">GENERATED ALWAYS AS</span> (month(sales_date)))
<span class="ph b" style="font-weight: bold;">PARTITION BY RANGE</span> (sales_month)
<span class="ph b" style="font-weight: bold;">(STARTING FROM</span> 1 <span class="ph b" style="font-weight: bold;">ENDING AT</span> 12 <span class="ph b" style="font-weight: bold;">EVERY</span> 1);
- 对于表分区键中使用的生成列，不能改变或删除其表达式。不允许对表分区键中使用的列添加生成列表达式。对于表分区键中使用的列，如果尝试添加、删除或改变该列的生成列表达式，就会导致错误（SQL0270N，原因码为 52）。
- 如果生成列不是单调的，或者优化器无法检测出该列是否是单调的，就不会对范围谓词使用数据分区消除功能。如果存在非单调表达式，那么只能对等价或 IN 谓词执行数据分区消除功能。有关单调性的详细讨论和示例
由于在项目中在delete许多大表的数据，起初采用不写日志的方式，后在集成测试时发现，如果有在delete过程中出现中断，这时这个表就不能再用了，必须drop后重建，风险性比较大，后来经过查找资料，请教dba后采用另一种方法，修改大表的建表语句，使之变成分区表，然后进行detach把分区数据到临时表中，删除临时表，这个就达到删除数据目的了，下面我列出具体的操作步骤及相应的shell脚本。
1.创建分区表，INCLUSIVE（包含),exclusive（不包含)
CREATE TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" (
"REPORT_DATE" DATE,
"MA_ACCT_NO" VARCHAR(100),
"TRANS_DATE" DATE,
"ORG_UNIT_ID" VARCHAR(15),
"ORG_PROD_ID" VARCHAR(15),
"CURR_CD" VARCHAR(15),
"ACCT_NO" VARCHAR(15),
"TRANS_NUM" VARCHAR(15),
"TRANS_NO" VARCHAR(15),
"TRANS_DIF" VARCHAR(15),
"DEPOSIT_CHAR" VARCHAR(15),
"DEPOSIT_BAL" DECIMAL(18,2),
"TRANS_AMT" DECIMAL(18,2),
"TRANS_TYPE" CHARACTER(1),
"FLG" CHARACTER(1),
"RATE" DECIMAL(18,6),
"TRXMEM" DECIMAL(4,0)
)
IN "MA_DATA"
INDEX IN "MA_INDEX"
PARTITION BY RANGE ( "REPORT_DATE" NULLS LAST ) ( PARTITION PART0 STARTING '2010-12-10' INCLUSIVE ENDING '2010-12-20' INCLUSIVE )
;
ALTER TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK"
DATA CAPTURE NONE
LOCKSIZE ROW
APPEND OFF
NOT VOLATILE;
COMMENT ON TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" IS '活期交易明细表';
COMMENT ON "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" (
"REPORT_DATE" IS '数据日期',
"MA_ACCT_NO" IS '管会账号',
"TRANS_DATE" IS '营业日期',
"ORG_UNIT_ID" IS '行所号',
"ORG_PROD_ID" IS '业务品种 ',
"CURR_CD" IS '币别',
"ACCT_NO" IS '帐号',
"TRANS_NUM" IS '交易序号',
"TRANS_NO" IS '交易代号',
"TRANS_DIF" IS '交易区别',
"DEPOSIT_CHAR" IS '存款性质 ',
"DEPOSIT_BAL" IS '存款余额',
"TRANS_AMT" IS '交易金额',
"TRANS_TYPE" IS '交易别',
"FLG" IS '连动标志',
"RATE" IS '汇率' );
GRANT CONTROL ON TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" TO USER "DB2INST2";
GRANT SELECT, INSERT, UPDATE, ALTER, DELETE,
INDEX, REFERENCES ON TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" TO USER "DB2INST2" WITH GRANT OPTION;
GRANT SELECT, INSERT, UPDATE, ALTER, DELETE,
INDEX, REFERENCES ON TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" TO USER "DB2INST2" WITH GRANT OPTION;
GRANT SELECT, INSERT, UPDATE, ALTER, DELETE,
INDEX, REFERENCES ON TABLE "MABAS"."BAS_MID_TRANS_LIST_CK_BAK" TO USER "MAPUB" WITH GRANT OPTION;
2.增加分区,注意这里的INCLUSIVE，exclusive，这时只有2011-01-31的数据可以进行insert
alter table MABAS.BAS_MID_TRANS_LIST_CK_BAK add partition LIST_CK_bak0131 STARTING '2011-01-31' INCLUSIVE ENDING '2011-02-01' exclusive
3.insert 数据
insert into MABAS.BAS_MID_TRANS_LIST_CK_BAK select * from MABAS.BAS_MID_TRANS_LIST_CK where report_date=date('2011-01-31');
4.转移分区到临里表
alter table MABAS.BAS_MID_TRANS_LIST_CK_BAK detach partition LIST_CK_bak0131 into MABAS.BAS_MID_TRANS_LIST_CK_BAK1
5.删除生成的分区迁移表,注意这时分区表的分区（LIST_CK_bak0131）己经不存在了，如果要insert必须新增该分区
drop table MABAS.BAS_MID_TRANS_LIST_CK_BAK1
以下为应用写的shell脚本，并参照syscat.datapartitions 进行判断，如果有则删除分区，否则进行新建，以下为具体的脚本。
# 创建人员: 姜春涛
# 创建日期: 2011-05-21
# 脚本描述: 删除表数据通用程序
# 修改人员:
# 修改日期:
# 修改原因:
# 版本说明: v1.0
# 公司名称: 宇信易诚
. /home/odsuser1/.profile
#配置文件
SYSNAME=GDBMA
MADS_HOME=/home/odsuser1/gdbma/etl
#DS Config
DSConfigFile=$MADS_HOME/dsconfig_gdbma
#MARPT ETL2数据库
#DB信息
DBNAME=`awk 'FS="=" {if ($0~/^MABASDBName/) print $2}' $DSConfigFile`
DBUSR=`awk 'FS="=" {if ($0~/^MABASDBUser/) print $2}' $DSConfigFile`
DBPWD=`awk 'FS="=" {if ($0~/^MABASDBPassword/) print $2}' $DSConfigFile`
DBSCHEMA=`awk 'FS="=" {if ($0~/^MABASDBSchema/) print $2}' $DSConfigFile`
DBPWD=`$MADS_HOME/Encrypt/discrypt.sh $DBPWD`
dbname=$DBNAME
user=$DBUSR
passwd=$DBPWD
#连接数据库
db2 connect to $DBNAME user $DBUSR using $DBPWD >/dev/null
db2 set schema=$DBSCHEMA;
#传递参数
JOB_NAME=$1
DELETE_DATE=$2
#DELETE_DBSCHEMA=$1
#DELETE_TAB=$2
#DELETE_COL=$3
#DELETE_TYPE=$4
#DELETE_DATE=$5
#DELETE_VALUE=$6
delete_tab="select SCH_NAME,TAB_NAME,IF_PARTITION,TAB_DATE,DEL_VALUE from mabas.s_job_info_m t where JOB_NAME = '"$JOB_NAME"' "
DEL_DATA=`db2 -t "$delete_tab"`
if [ $? -ne 0 ]
then
echo "$SDATA"
fi
echo "$DEL_DATA" | sed -e '4,/^$/!d;/^$/d'|
#循环读取job，然后调度
while read SCH_NAME TAB_NAME IF_PARTITION TAB_DATE DEL_VALUE
do
#判断删除方式
#分区字段拼写
if [ "$IF_PARTITION" = 'Y' ]
then
#分区进行拼写
partiton_name=`db2 -tx "select upper('p'||replace(varchar(date('"$DELETE_DATE"')),'-','')) from sysibm.sysdummy1 "`
tmp_tab=`db2 -tx "select upper('tmp'||substr(replace(replace(varchar(current timestamp),'-',''),'.',''),5,10)) from sysibm.sysdummy1 "`
#判断分区是否存在
vi_result=`db2 -tx "select count(*) from syscat.datapartitions t where tabschema = upper('"$SCH_NAME"')
and tabname = upper('"$TAB_NAME"')
and datapartitionname=upper('"$partiton_name"') "`
#对分区进行操作
if [ "$DEL_VALUE" = '-' ]
then
#判断分区是否存在
if [ $vi_result -ne 0 ]
then
#进行分区数据到临时表
alter_parition=`db2 -tx "alter table $SCH_NAME.$TAB_NAME detach partition $partiton_name into $SCH_NAME.$tmp_tab"`
#删除临时表
drop_tmp=`db2 -tx "drop table $SCH_NAME.$tmp_tab"`
#分区结束
partition_end=`db2 -tx "select varchar(date('"$DELETE_DATE"') +1 days) from sysibm.sysdummy1"`
#增加分区
add_partition=`db2 -tx "alter table $SCH_NAME.$TAB_NAME add partition $partiton_name STARTING '"$DELETE_DATE"' INCLUSIVE ENDING '"$partition_end"' exclusive "`
else
#分区结束
partition_end=`db2 -tx "select varchar(date('"$DELETE_DATE"') +1 days) from sysibm.sysdummy1"`
#增加分区
add_partition=`db2 -tx "alter table $SCH_NAME.$TAB_NAME add partition $partiton_name STARTING '"$DELETE_DATE"' INCLUSIVE ENDING '"$partition_end"' exclusive "`
fi
else
if [ $vi_result -ne 0 ]
then
#进行分区数据到临时表
drop_parition=`db2 -tx "alter table $SCH_NAME.$TAB_NAME detach partition $partiton_name into $SCH_NAME.$tmp_tab "`
#备份临时表中不属于该条件的数据
delete_tab_date=`db2 -tx "delete from $SCH_NAME.$tmp_tab where "$DEL_VALUE" "`
#分区结束
partition_end=`db2 -tx "select varchar(date('"$DELETE_DATE"') +1 days) from sysibm.sysdummy1"`
#增加分区
add_partition=`db2 -tx "alter table $SCH_NAME.$TAB_NAME add partition $partiton_name STARTING '"$DELETE_DATE"' INCLUSIVE ENDING '"$partition_end"' exclusive "`
#insert 不符合删除条件的数据
insert_date=`db2 -tx "insert into $SCH_NAME.$TAB_NAME select * from $SCH_NAME.$tmp_tab "`
else
#分区结束
partition_end=`db2 -tx "select varchar(date('"$DELETE_DATE"') +1 days) from sysibm.sysdummy1"`
#增加分区
add_partition=`db2 -tx "alter table $SCH_NAME.$TAB_NAME add partition $partiton_name STARTING '"$DELETE_DATE"' INCLUSIVE ENDING '"$partition_end"' exclusive "`
fi
fi
else
#直接删除表
if [ "$DEL_VALUE" = "-" ]
then
delete_tab_data=`db2 -tx "delete from $SCH_NAME.$TAB_NAME where date("$TAB_DATE") = DATE('"$DELETE_DATE"') "`
else
delete_tab_data=`db2 -tx "delete from $SCH_NAME.$TAB_NAME where date("$TAB_DATE") = DATE('"$DELETE_DATE"') and $DEL_VALUE "`
fi
fi
done
http://blog.chinaunix.net/uid-196042-id-2103401.html
http://blog.chinaunix.net/uid/196042/cid-38159-list-1.html
http://www.cppblog.com/prayer/archive/2010/04/15/112725.html
1821
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
