提高db2存储过程运行效率

如何提高db2存储过程的执行效率技能创新挑战活动课题报告人：崔旭报告日期：2012.8.28第1部分课题研究背景第2部分课题研究的目的和意义第3部分课题进展情况第4部分下一步计划目录课题研究背景本课题选定研究对象主要是行业应用文件版本监控平台项目中的数据监控预警系统涉及到的db2数据库存储过程，以便在实践中检验相关技术的有效性并解决实际工作过程中遇到的困难。上述系统中存储过程主要工作为：根据每日采集的全辖所有工业企业的应用程序文件的md5值（MD5为一种加密算法，根据文件生成唯一的代表该文件的32位的16进制数），与历史记录比对，记录程序文件变化情况。课题研究的目的和意义本课题旨在解决存储过程执行效率问题，通过对sql存储过程的编程方法以及用到的标准技术分析，主要从sql编写，游标使用，业务逻辑，事务控制，参数传递，存储过程异常判断，数据的逻辑、物理存储等方面找出可能影响存储过程执行效率的因素，分析并测试，找出可优化点，进行优化。总结出一套适用本课题的存储过程执行效率问题的定位解决方法。课题进展情况对db2存储过程的sql编写，游标使用，业务逻辑，参数传递，存储过程异常判断以及db2数据库数据的逻辑、物理存储等方面进行了分析和测试，确认可优化点。课题进展情况存储过程表MD5FDI主要存放每日抽取的企业代码、应用包名、程序文件名、md5值、文件路径、时间等关键信息，数据量大，考虑分区提高查询效率。缺少应用包级的游标循环。导致比较范围不准确，从而影响到比较速度以及结果中存在重复数据。将表MD5FDI以时间字段（FDATE）进行分区，提高数据查询速度。分析并确认可优化点增加表EARNAMEBASE、FPATHDI、MD5FI。修改表MD5FDI数据项：去掉EARNAME、FPATH，添加EARNAMEMD5、FPATHMD5。重新编写存储过程，嵌套使用游标，共三层，用来确定比较范围，再进行比较输出。表结构数据存储表MD5FDI中数据项：应用包名、程序文件路径为不定长数据。考虑取其md5值变为定长数据，提高比较效率。

比较数据均在表MD5FDI中，考虑增加辅助表，已便于比较条件的关联与确定。课题进展情况执行情况比较结果：用时：约1小时。数据：不存在重复数据。测试环境及方法同优化前。优化前：DIFFDATA优化后：DIFFNEW测试环境：192.168.1.58，aix6.1，db29.7。

编译通过后使用crontab定时调用。

通过查看crontab输出文件的修改时间确认执行时间。优化前后存储过程执行情况对比结果：用时：约4小时。数据：存在重复数据。下一步计划现已完成第一、二阶段工作，正在整理相关资料编写文档。第一阶段

db2资料查阅，主要侧重于存储过程相关。

熟悉课题研究用测试环境及相关库表结构。

思考存储过程实现的业务逻辑。第二阶段

分析、讨论优化方法。

确定优化点。

进行优化、测试。第三阶段整理相关资料。

编写提交文档。Thanks！谢谢!ClicktoaddtitleAtitleaboutcontentTitleinhere

PowerPointDiagramsdesignedbyThemeGallery.

PowerPointDiagramsdesignedbyThemeGallery.

PowerPointDiagramsdesignedbyThemeGallery.AddtitleinhereAddtitleinhereAddtitleinhereClicktoaddtitleAddtitleinhere

DescriptionofthesubcontentsDescriptionofthesubcontentsDescriptionofthesubcontentsDescriptionofthesubcontentsDescriptionofthesubcontents“ThemeGalleryisaDesignDigitalContent&ContentsmalldevelopedbyGuildDesignInc.”ThemeGalleryisaDesignDigitalContent&ContentsmalldevelopedbyGuildDesignInc.TextTextTextTextTextClicktoaddtitleLevel3TextinhereThemeGalleryisaDesignDigitalContent&ContentsmalldevelopedbyGuildDesignInc.

ClicktoaddTextClicktoaddTextClicktoaddText

ClicktoaddTextClicktoaddTextClicktoaddTextDescriptionofthecontentsCompanyLOGODescriptionofthecontentsDescriptionofthecontentsLevel3TextinhereLevel3TextinhereClicktoaddtitleDescriptionofthecontents

DescriptionofthesubcontentsDescriptionofthesubcontentsDescriptionofthesubcontents

ThemeGalleryisaDesignDigitalContent&ContentsmalldevelopedbyGuildDesignInc.

DescriptionofthesubcontentsDescriptionofthesubcontentsDescriptionofthesubcontentsDescriptionofthesubcontentsThemeGalleryisaDesignDigitalContent&ContentsmalldevelopedbyGuildDesignInc.DescriptionofthecontentsClicktoedittitle

DescriptionofthesubcontentsDescriptionofthesubcontentsDescriptionofthesubcontents“ThemeGalleryisaDesignDigitalContent&ContentsmalldevelopedbyGuildDesignInc.”Addtextinhere

ClicktoaddText

ClicktoaddText

ClicktoaddText

ClicktoaddTextAddtextinhereAddtextinhere

ClicktoaddText

ClicktoaddText

ClicktoaddText

ClicktoaddText

ClicktoaddText

ClicktoaddText

ClicktoaddText

ClicktoaddTextClicktoedittitleThemeGalleryisaDesignDigitalContent&ContentsmalldevelopedbyGuildDesignInc.TextinhereTextinhereTextinhere

ClicktoaddTextClicktoaddTextClicktoaddTextClicktoaddTextDescriptionofthesubcontentsThemeGalleryisaDesignDigitalContent&ContentsmalldevelopedbyGuildDesignInc.

ClicktoaddTextClicktoaddTextClicktoaddTextClicktoaddText

ClicktoaddTextClicktoaddTextClicktoaddTextClicktoaddTextClicktoaddtitleText4.Descriptionofthebusiness1.Descriptionofthebusiness2.Descriptionofthebusiness3.DescriptionofthebusinessThemeGalleryisaDesignDigitalContent&ContentsmalldevelopedbyGuildDesignInc.TextTextText5.DescriptionofthebusinessTextClicktoedittitleThemeGalleryisaDesignDigitalContent&ContentsmalldevelopedbyGuildDesignInc.Start2000~2003

YourtextinhereYourtextinhereYourtextinhereYourtextinhereGrowth2004~2007

YourtextinhereYourtextinhereYourtextinhereYourtextinhereJump2008~2011

YourtextinhereYourtextinhereYourtextinhereYourtextinhereClicktoaddtitleTextinhereTextinhereTextinhereTextinhere

DescriptionofthecontentsDescriptionofthecontentsDescriptionofthecontentsDescriptionofthecontents

ContentsTitleinhere

DescriptionofthecontentsDescriptionofthecontentsDescriptionofthecontentsDescriptionofthecontents

DescriptionofthecontentsDescriptionofthecontentsDescriptionofthecontentsThemeGalleryisaDesignDigitalContent&ContentsmalldevelopedbyGuildDesignInc.ClicktoaddtitleTitleinhere

Descriptionofthecontents

DescriptionofthecontentsTitleinhere

Descriptionofthecontents

DescriptionofthecontentsTitleinhere

Descriptionofthecontents

DescriptionofthecontentsTitleinhere

Descriptionofthecontents

15%

45%50%27%

53%

45%25%13%

Descriptionofthecontents

PowerPointDiagramsdesignedbyThemeGalleryTitleinhere

PowerPointDiagramsdesignedbyThemeGalleryTitleinhereTitleinhereClicktoaddtitleAtitleaboutcontentThemeGalleryisaDesignDigitalContent&ContentsmalldevelopedbyGuildDesignInc.

PowerPointDiagramsdesignedbyThemeGalleryTitleinhere

PowerPointDiagramsdesignedbyThemeGallery

PowerPointDiagramsdesignedbyThemeGallery12345TitleinhereClicktoaddtitleCompanyLOGOThemeGalleryisaDesignDigitalContent&ContentsmalldevelopedbyGuildDesignInc.ThemeGalleryisaDesignDigitalContent&ContentsmalldevelopedbyGuildDesignInc.

DescriptionofthesubcontentsDescriptionofthesubcontentsDescriptionofthesubcontentsTextinhereTextinhere

DescriptionofthesubcontentsDescriptionofthesubcontentsDescriptionofthesubcontentsThemeGalleryisaDesignDigitalContent&ContentsmalldevelopedbyGuildDesignInc.ClicktoaddtitleAtitleaboutcontentLOGOAddtitleinhereAddtitleinhereAddtitleinhereAddtitleinhere

PowerPointDiagramsdesignedbyThemeGallery.

PowerPointDiagramsdesignedbyThemeGallery.

PowerPointDiagramsdesignedbyThemeGallery.

PowerPointDiagramsdesignedbyThemeGallery.ClicktoaddtitleTextinhereThemeGalleryisaDesignDigitalContent&ContentsmalldevelopedbyGuildDesignInc.TextinhereThemeGalleryisaDesignDigitalContent&ContentsmalldevelopedbyGuildDesignInc.TextinhereThemeGalleryisaDesignDigitalContent&ContentsmalldevelopedbyGuildDesignInc.TextinhereThemeGalleryisaDesignDigitalContent&ContentsmalldevelopedbyGuildDesignInc.1234ClicktoaddtitleTitleinhereThemeGa