‘/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…