一例資料倉儲執行update引起CPU佔用超過90%問題

[Icefeish@#][24 /etl1_home/kft/Icefeish]$db2 "select count(*) from DM.TM_STU_INFO with ur "
1
-----------
1170280
1 record(s) selected.

[Icefeish@#][25 /etl1_home/kft/Icefeish]$db2 "select count(*) from ref.tr_nbrseg with ur "
1
-----------
271251
1 record(s) selected.

CMD="update DM.TM_STU_INFO a SET BRND_CD=(select BRND_CD from ref.tr_nbrseg b where a.STUDENT_NBR between b.BEGIN_NBR and b.END_NBR fetch first 1 rows only )"
EXEDB2_CMD

--上面的語句導致CPU佔用基本達到90%,此時執行select一個5條記錄的表都差不多1分鐘.

解決方法:
方法一.export/load
1.
export to TM_STU_INFO.txt of del select
A.SCHOOL_CD,
A.SCHOOL_NAM,
A.STUDENT_NBR,
A.STUDENT_NAM,
A.SEX,
B.BRND_CD,
A.ACADEMY,
A.CLASS,
A.ADDRESS,
A.ID,
A.PROFILE,
A.ROLLIN,
A.DUTY,
A.MAIL_ADDR
from DM.TM_STU_INFO A left outer join ods.to_usr B
on A.STUDENT_NBR =B.USR_NBR
with ur ;
2.
db2 "load client from /etl1_home/kft/Icefeish/TM_STU_INFO.txt of del insert into dm.TM_STU_INFO nonrecoverable"

方法二.利用session表中轉
1.
db2 -tv +p<insert into session.TM_STU_INFO
select
A.SCHOOL_CD,
A.SCHOOL_NAM,
A.STUDENT_NBR,
A.STUDENT_NAM,
A.SEX,
B.BRND_CD,
A.ACADEMY,
A.CLASS,
A.ADDRESS,
A.ID,
A.PROFILE,
A.ROLLIN,
A.DUTY,
A.MAIL_ADDR
from DM.TM_STU_INFO A left outer join ods.to_usr B
on A.STUDENT_NBR =B.USR_NBR
with ur ;
insert into DM.TM_STU_INFO select * from session.TM_STU_INFO with ur ;
EOF

[@more@]