‘/database/db2inst1/db2log_node22′”
$ db2_all “<<+23<db2 update db cfg for zmccdw using newlogpath ‘/database/db2inst1/db2log_node23′”
$ db2_all “<<+24<db2 update db cfg for zmccdw using newlogpath ‘/database/db2inst1/db2log_node24′”
$ db2_all “<<+25<db2 update db cfg for zmccdw using newlogpath ‘/database/db2inst1/db2log_node25′”
$ db2_all “<<+26<db2 update db cfg for zmccdw using newlogpath ‘/database/db2inst1/db2log_node26′”
$ db2_all “<<+27<db2 update db cfg for zmccdw using newlogpath ‘/database/db2inst1/db2log_node27′”
$ db2_all “<<+28<db2 update db cfg for zmccdw using newlogpath ‘/database/db2inst1/db2log_node28′”
$ db2_all “<<+29<db2 update db cfg for zmccdw using newlogpath ‘/database/db2inst1/db2log_node29′”
$ db2_all “<<+30<db2 update db cfg for zmccdw using newlogpath ‘/database/db2inst1/db2log_node30′”
$ db2_all “<<+31<db2 update db cfg for zmccdw using newlogpath ‘/database/db2inst1/db2log_node31′”
$ db2_all “<<+32<db2 update db cfg for zmccdw using newlogpath ‘/database/db2inst1/db2log_node32′”
13.3 Filter Recovery
Filter Recovery过程
1． Db2 force applications all结束其他占用日志较少的事务。
2． Db2_kill结束所有db2的进程。清理IPC资源。
3． Db2start重新启动数据库
4． Filter recovery
5． db2_all ‘;db2 “restart db zmccdw filtering key KOJWLKCG (TABLE 10 41,table 10 1327)”‘
括号内的部分是通过表空间ID和表ID指定需要过滤的表。
6． Db2_all “;db2 connect to zmccdw” 连接数据库确认数据库可用
7． Db2 drop table all_user_imei_200605; db2 drop table dwd_user_imei_200605
Filter Recovery过程总共用时50分钟。
13.4 均衡分区存储
|
找出数据节点不均匀的表
db2 “select dbpartitionnum(SP_BUSN_ID ), count(*) from dw. ST_MNET_PRODUCT_MID_200709 group by dbpartitionnum(SP_BUSN_ID)” 1 1117715 2 1696424 3 7554797 (查出该系列对象数据大量存储在3号节点上) 4 1124929 5 1419371 6 1176204 7 1081522 8 1331763 9 1003365 10 1200083 11 1151844 12 1356046 13 1172018 14 1268914 15 1058454 16 1312104 17 982018 18 1427433 19 896497 20 4492476 21 1038896 22 1336361 23 1306847 24 1076422 25 933977 26 1066838 27 860485 28 1159581 29 1294018 30 1545965 31 1057874 32 1235952
|
|
解决措施 |
|
#db2look -d zmccdw -e -t ST_MNET_PRODUCT_MID_200709 -nofed > ST_MNET_PRODUCT_MID_200709.DDL 更换*.DDL partitioning key PARTITIONING KEY (“SP_BUSN_ID”)–>PARTITIONING KEY (“CITY_ID”, “COUNTY_ID”, “SP_BUSN_ID”)USING HASHING #db2 rename table ST_MNET_PRODUCT_MID_200709 to ST_MNET_PRODUCT_MID_200709_BAK db2 “insert into ST_MNET_PRODUCT_MID_200709 select * from ST_MNET_PRODUCT_MID_200709_BAK” #db2 “alter table ST_MNET_PRODUCT_MID_200709 activate not logged initially with empty table” #db2 drop table ST_MNET_PRODUCT_MID_200709 #db2 rename table ST_MNET_PRODUCT_MID_200709_BAK to ST_MNET_PRODUCT_MID_200709 以同样的方法调整: ST_MNET_PRODUCT_MID_200704-> ST_MNET_PRODUCT_MID_200708 |
13.5 SEQUENCE
“ROW_ID” BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (
START WITH +0
INCREMENT BY +1
MINVALUE +0
MAXVALUE +9223372036854775807
NO CYCLE
CACHE 20
NO ORDER ) )
PARTITIONING KEY (“ROW_ID”) USING HASHING
Cache 20 是个性能参数,提高cache值至1000,
既通过高速缓存值来调优该表的标识顺列的性能
执行：
db2 “alter table st_chnl_user_owe_mm alter row_id set cache 1000
13.6 问题sql
insert into ST_CHNL_RENEW_MM (
op_time,
channel_id,
city_id,
county_id,
parent_channel_id,
selfsite_id,
brand_id,
plan_id,
region_type,
chnl_type1,
chnl_type2,
chnl_type3,
exclusive,
sale_type,
online_flag,
renew_user_cnt,
new_user_cnt
)
select
‘2007-09-01’ as op_time,
e.channel_id,
e.city_id,
e.county_id,
e.parent_channel_id,
e.selfsite_id,
e.brand_id,
e.plan_id,
e.region_type,
e.chnl_type1,
e.chnl_type2,
e.chnl_type3,
e.exclusive,
e.sale_type,
e.online_flag,
sum(d.renew_user_cnt), –renew_mark–
sum(e.new_user_cnt)
from
(select
channel_id,
city_id,
county_id,
parent_channel_id,
selfsite_id,
brand_id,
plan_id,
region_type,
chnl_type1,
chnl_type2,
chnl_type3,
exclusive,
sale_type,
online_flag,
sum(case when month_new_mark >0 then 1 else 0 end) as
以下文章点击率最高
Loading…