一例数据仓库执行update引起CPU占用超过90%问题-CSDN博客

本文介绍了一个在DB2数据库中执行更新操作导致CPU占用率过高的问题，并提供了两种有效的解决方案：一是通过export和load命令导出再导入数据；二是使用session表作为中间表进行数据更新。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

[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