程序员: 减少与 DB2 for z/OS 的会话:第 3 部分

以不同的思路思考普通事务

在您的编程方法中是否遵循最佳实践? Bonnie Baker 提出多个不良的 SQL 使用习惯,只有打破这些习惯,才能消除或减少与 DB2 for z/OS 的对话。

来自 IBM Data Management Magazine中文版。

Bonnie Baker, 负责人, Bonnie Baker Corporation

Bonnie Baker 是专门为公司、机构和 DB2 用户组现场讲授课程的培训专家。她是 IBM DB2 金牌顾问、是 IBM Information Champion、五次 IDUG Overall Best Speaker 奖得主以及 IDUG Speakers Hall of Fame 成员。她擅长于通过类比和战争故事来讲授复杂的概念



2010 年 9 月 09 日

阅读本文的互动数字版!

在前面两个专栏中,谈到不必要的 SQL,并给出了执行次数过多的SQL 例子,以及应当由更新的、性能更优的 SQL 来替代的例子。在本专栏中,将讲述更多 SQL 应当被推迟、结合、或者完全避免的情况,最终目的都是减少与DB2 的对话。

通过三步移除不必要的重复读取(re-reads)

Screen-display 应用因其大量不必要的重复读取而出名。我们读取一个图像并显示;用户变更屏幕上的值并点击功能键来更新;程序获得控制权并重新读取该行(通常此时利用使用 FOR UPDATE OFCURSOR )。FETCH 之后,程序进行 “前后” 检查,来查看在进行屏幕显示时,是否有不同用户对该行做了改动。

如果该行与最开始时一致,则用户的 UPDATE 操作完成。如果行中的任何列改变了,相应的信息/错误将返回给用户。

解决这一不必要 SQL 问题的方法是,采用 “乐观锁定(optimistic locking)” 技术。我们确信大多数情况下,行不会在屏幕显示时改变。我们的 UPDATE 杯子是半满,而不是半空。因此,将不必重新读取该行而直接进行 UPDATE 操作。但是,我们并不笨;我们将使用试错(tried-and-true)技术来确保针对未变更行的 UPDATE 操作已经完成:我们在子句 WHERE 中增加了一个或多个谓词,来测试变更。有很多选项可用于测试变更,其中一些允许比其他有更多的流量。例如,我们可以:

  1. 设计表来包含 DB2 维护的 ROW UPDATE TIMESTAMP(DB2 9 中的新特性),并在 WHERE 子句中包含一个谓词,来检查该列,从而确定该值是否与从列中读取的初始值相同。
  2. 将用户维护的、最后更新的时间戳与其初始值对比(并在 SET 子句中重置该时间戳),来确定从该行取得图像后,该行没有任何变化。
  3. 在行的每个列中包含 WHERE 子句谓词,来确定每列的值是否与其初始值相同。
  4. 在重要的、与我们相关的列中包含 WHERE 子句谓词。我们肯定想确保行仍然合格,并且我们想验证我们所选的、未变化的列。

为提高性能而进行的主要变化

具有 TRANDATEPROCESS-DATE、以及 CUSTNO 的索引,希望 FETCH 行,并提交报告,如下情况的行将被删除:时间超过 3 个月、无效、并在程序工作存储中的清单中发现 CUSTNO

您可以:

Declare Cursor csr-delete-old-rows for
		Select ponbr, custno, trandate, process-date
	From bigtable
	Where trandate < current date - 3 months
		And status = :hv-inactive
        
Open csr-delete-old-rows

Fetch csr-delete-old-rows into
		:hvponbr, :hvcustno, :hvtrandate, :hvprocess-date

Write to a report

Delete from bigtable                
	Where current of csr-delete-old-rows

如果有 3,000 个索引行指向具有 TRANDATE 超过 3 个月的表行,但是,3,000 个表行中只有 500 个其 STATUS为 “inactive”,那么最坏情况是您需要:

  1. OPEN 进行 CURSOR 设置,CONNECT 到 DB2
  2. 为每个 FETCHCONNECT 到 DB2。然后,对于 3,000 连接中的每个:
    a. 对索引树 multi-level 中的页,发出多个 GET PAGE 请求
    b. 读取 6 个索引项的平均值
    c. 对于 6 个 RIDs 索引中的每个,向表发出 GET PAGE 请求,然后可能需要等待 I/O 同步
    d. 完成对表的 GET PAGE 请求(并读取 I/Os)后,向这 6 个表行应用 STATUS 谓词,拒绝 5 个并接受 1 个
  3. 向程序返回一个行
  4. 在 500 个合格的行中,只有 25 个 CUSTNOs 在工作存储列表中(在从 DB2 返回后,在程序中拒绝行的条件称为 “Stage 3 谓词” —— 并且 Stage 3 谓词比 Stage 2 谓词更糟糕)
  5. 对每个完全合格行,再次对 DB2 执行 CONNECT,此次进行 DELETE 操作(25 个连接)
  6. 为多达 528 CONNECTs 重复 24 次,并且对每个索引和表进行太多的 GET PAGEs。

那么如何在这种条件下减少对话的数量?我建议做 4 方面改善(您可能还记得在 上一专栏的第 2 步 中的建议):

  1. 如果可行,在索引中增加 STATUS,来使得有效性只适用索引
  2. 改变 CURSOR 来使 ROWSET POSITIONING 每次读取多行
  3. 利用 SELECT FROM DELETE 来查看行
  4. 通过将数字 INSERTCREATED GLOBAL TEMPORARY TABLE(例如,CTT_CUST)并通过在 SQL 中使用临时表,去掉 STAGE 3 取消资格(程序检查 CUSTNO
Declare Cursor csr-select-and-delete-old-rows
	With rowset positioning
	For
	Select ponbr, custno, trandate, process-date
	From final_table
		(Delete from bigtable
		Where trandate < current date - 3 months
			And status = :hv-inactive
			And custno in (select custno from ctt_cust)
Open csr-select-and-delete-old-rows

(一个 CONNECT

Fetch rowset from final_table
For 100 rows into
:hvponbr-array, :hvcustno-array, :hvtrandate-array, :hvprocess-date-array

(一个 CONNECT ,以及 100 个以上针对第 26 行返回的 SQLCODE

Close csr-select-and-delete-old-rows

(一个 CONNECT

新的最佳情况是,您将:

  1. OPEN 进行 CURSOR 设置,CONNECT 到 DB2
  2. FETCHCONNECT 到 DB2,并且:
    a. 对索引树中的页发出 GET PAGE 请求
    b. 读取 25 索引项,在读取表之前,完全限定该行
    c. 对 25 个 RID 中的每一项,向表发起 GET PAGE 请求(如果我们很幸运,并且有些行共享一些页,可能会少做点 GET PAGEs 操作)
    d. 对于每个 GET PAGE,需要做 READ I/O 操作
    e. 当发现每行时,DELETE
    f. 将每行中已 SELECT 的列插入 final_table
    g. 向程序返回 25 行
  3. 写入报告:
Close csr-select-and-delete-old-rows

(一个 CONNECT

利用前面的 4 项技术,将对话从 528 次减少到 3 次,同时,将表 GET PAGE 请求的数量从 3,000 次降低到至多 25 次。


消除 Stage 3

我想详细阐述上述第 4 个建议改进(去除 STAGE 3 取消资格)。作为咨询人员,我发现主要的公共编码失误之一是在程序中,而不是 DB2 中验证并拒绝行。

这些 STAGE 3 谓词,特别是拒绝大部分行的那些,通常是不使用 WHERE 子句的旧有 VSAM 逻辑的结果。我们采用键控读取,然后向记录应用程序编码的 IF/THEN/ELSE 逻辑。IF/THEN/ELSE 逻辑是搞清楚如何使用 WHERE 子句谓词的良好基础,因此在 DB2 地址空间中行能够被快速拒绝。


结束语

我编写这个三部分系列文章,来强调完全消除或者延迟 —— 直到最后一刻 —— 与 DB2 对话的重要性。今后,在为每个 SQL 语句编码时,我希望会有一个小 Bonnie 坐在您身边,问道:“这有必要么?能否一次对多个行进行读/维护?能否推迟这一 SQL,直到确保需要这样做?”

参考资料

学习

获得产品和技术

  • 现在可以免费使用 DB2 。下载 DB2 Express-C,这是为社区提供的 DB2 Express Edition 的免费版本,它提供了与 DB2 Express Edition 相同的核心数据特性,为构建和部署应用程序奠定了坚实的基础。
  • 下载 IM 软件试用版,体验信息管理软件强大的功能。

讨论

条评论

developerWorks: 登录

标有星(*)号的字段是必填字段。


需要一个 IBM ID?
忘记 IBM ID?


忘记密码?
更改您的密码

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件

 


在您首次登录 developerWorks 时,会为您创建一份个人概要。您的个人概要中的信息(您的姓名、国家/地区,以及公司名称)是公开显示的,而且会随着您发布的任何内容一起显示,除非您选择隐藏您的公司名称。您可以随时更新您的 IBM 帐户。

所有提交的信息确保安全。

选择您的昵称



当您初次登录到 developerWorks 时,将会为您创建一份概要信息,您需要指定一个昵称。您的昵称将和您在 developerWorks 发布的内容显示在一起。

昵称长度在 3 至 31 个字符之间。 您的昵称在 developerWorks 社区中必须是唯一的,并且出于隐私保护的原因,不能是您的电子邮件地址。

标有星(*)号的字段是必填字段。

(昵称长度在 3 至 31 个字符之间)

单击提交则表示您同意developerWorks 的条款和条件。 查看条款和条件.

 


所有提交的信息确保安全。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=10
Zone=Information Management
ArticleID=517852
ArticleTitle=程序员: 减少与 DB2 for z/OS 的会话:第 3 部分
publish-date=09092010