利用 Shell 脚本实现 DB2 数据库的敏捷开发

核心提示：本文是“使用脚本加速 DB2 存储过程的开发”的升级篇，在前一篇文章中我们通过使用 Windows 下的 bat 批处理 (.bat) 脚本来帮助我们进行数据库开发，利用 Shell 脚本实现 DB2 数据库的敏捷开发，本文中介绍的 mydbtool 脚本是对它们的重写，mydbtool 是用

本文是“使用脚本加速 DB2 存储过程的开发”的升级篇。在前一篇文章中我们通过使用 Windows 下的 bat 批处理 (.bat) 脚本来帮助我们进行数据库开发。本文中介绍的 mydbtool 脚本是对它们的重写。mydbtool 是用 shell 脚本来编写的。之所以使用 shell 而不是 bat，是因为 shell 提供了更多的功能，而且它可以在 Windows(Cygwin) 和 Linux/Unix 等系统下使用。所以 mydbtool 是一个功能更强大，而且跨平台的工具。本文不会重复介绍前一篇已经介绍过的命令，它们在 mydbtool 脚本中仍然适用。下面我们将主要讨论 mydbtool 脚本提供的一些高级功能。

本文主要讨论使用 Shell 脚本与 DB2 交互的一些技巧和方法。介绍了利用 Shell 脚本帮助我们进行存储过程调优和部署。同时通过一个演示项目的开发过程的介绍来展示如何使用文中的脚本进行开发。

背景

我们遇到的问题

在项目中我们大量使用了存储过程，并且每三个月发布一个新版本。在开发每个版本中，我们要经过：Unit Test，Function Verification Test (FVT) 和 User Acceptance Test (UAT) 阶段，然后我们才会把代码发布到 Production 环境。每个阶段对应一个数据库（fvtdb，uatdb 和 proddb 等 )。大家知道，存储过程的部署不像 J2EE 应用程序的部署。J2EE 应用程序只需要把所有的 Java class， XML，JSP 等文件打成 ear 包，部署到 J2EE 服务器上就可以了。但是存储过程的部署需要我们只更新修改过的存储过程。因此，如何正确快速的把存储过程部署到为数众多的数据库中，就是一个很大的问题。

我们的解决方案

敏捷开发要求我们能够迅速应对变化，快速应对变化就要求我们尽量把一些耗时的工作自动化。出于这个目的，我们开发了 mydbtool 脚本来解决我们遇到的问题。它提供很多数据库开发和部署的工具，可以大大提高开发工作的效率和准确性。其部署工具可以比较数据库 (fvtdb， uatdb 和 proddb 等 ) 之间的不同，生成一个报告。通过该报告，我们可以很容易的把更新的存储过程部署到正确的数据库环境中。

在介绍 mydbtool 之前，让我们先了解一下 Cygwin 和 Shell。

Cygwin 简介

Cygwin 是一个用于在 Windows 上模拟 Linux/Unix 操作系统环境的软件。运行 Cygwin 后，你会得到一个类似 Linux/Unix 的 Shell 环境，在其中你可以使用绝大部分 Linux/Unix 软件和功能。如 sed， awk， 运行 Shell 脚本等等，总之如果你想使用某个 Linux/Unix 下 的功能，而 Windows 上又找不到好的软件的话，你就可以采用 Cygwin。这样就解决了我们的问题，我们开发好一套 Shell 脚本后，如果你用的工作站是 Linux/Unix，那么可以直接运行脚本；如果你用的是 Windows 操作系统，那么可以把脚本放到 Cygwin 环境下运行，效果是一样的。

Cygwin 的下载及安装：请参见 参考资料 中的 "Cygwin 中国镜像"。你只需要安装默认的软件包就可以开始使用 mydbtool 脚本了。

Shell 编程简介

Shell 也叫做命令行界面，它为我们和 Linux/Unix 系统之间提供了一个交互的接口。用户既可以输入命令来执行各种各样的任务，也可以通过编写 Shell 脚本完成更加自动化和复杂的操作。它的作用就是按照一定的语法规范将用户指令加以转化并传给系统进行处理。

Linux/Unix 操作系统提供了几种不同的 Shell， 如 Bourne Shell(/bin/sh)、Korn Shell(/bin/ksh)、Bourne Again Shell(/bin/bash)、 Tenex C Shell(tcsh)、C Shell(/bin/csh) 等，其中 Bourne Again Shell( 即 bash) 是自由软件基金会 (GNU) 支持开发的一个 Shell 版本，它是很多 Linux 系统中一个默认的 Shell。Cygwin 中默认的 Shell 也是 Bash。

本文的 mydbtool 脚本就是采用 Bash 来编写的，它充分利用了 DB2 的常用命令和 sed， awk 等 Linux/Cygwin 下的文本处理工具。

mydbtool 用法简介

要使用 mydbtool 脚本，你需要把本文中的附件 mydbtool.zip 解压到某个目录，然后把该目录放到 PATH 里面。

清单 1. 使用 export 设置 PATH 变量

　export　PATH=mydbtool_dir;$PATH　

mydbtool 有两种使用模式，一种是交互式，一种是命令式。交互式方式方便用户执行 DB2 命令。命令式则可以在其他脚本中调用 mydbtool。在控制台输入 mydbtool 然后回车就会进入交互模式。在交互模式中你可以使用 mydbtool 的很多命令。 mydbtool 的命令模式需要在调用的时候加一个参数 -d [database name]。下面，我们会依次介绍 mydbtool 中各个命令的使用方法。

在命令行中输入： mydbtool help。我们就可以看到一个简单的帮助。

清单 2. mydbtool 的帮助

　will@will-laptop:~$　mydbtool　help　
　
　mydbtool　---　An　easy　way　to　use　DB2　
　
　list　of　commands:　
　
　conn　[dbname]　　　　　　　　　　　　　--　Connect　to　a　database　
　listconn　　　　　　　　　　　　　　　　--　List　all　connections　
　view　[spname]　　　　　　　　　　　　　--　View　the　source　code　of　Stord　Procedure　or　UDF　
　table　[table　name]　　　　　　　　　　　--　View　the　definition　of　table　
　dep　[table　name]　　　　　　　　　　　　--　View　the　dependence　
　column　[schemaname.tablename.columnname]　
　　　　　　　　　　　　　　　　　　　　--　Determine　dependencies　for　a　column　name　
　check　[spname]　　　　　　　　　　　　　--　Call　the　sample　call　in　the　source　code　
　e　[SQL　file　or　SQL　statment]　　　　　　--　Execute　the　SQL　file.　
　rebindall　　　　　　　　　　　　　　　--　Rebind　all　invalid　Stored　Procedure　
　rebindsp　[spname]　　　　　　　　　　　--　Rebind　a　Stored　Procedure　
　exp　[file　path]　　　　　　　　　　　　--　Get　access　plan　for　a　given　SQL　in　the　file　
　expsp　[spname]　　　　　　　　　　　　　--　Get　access　plan　for　Stored　Procedure　
　diffsp　[db_A]　[db_B]　[file　path]　　　　--　Compare　Stord　Procedures　between　two　databases　
　diffgrant　[db_A]　[db_B]　[file　path]　　--　Compare　grant　statment　between　two　databases　
　difftab　[db_A]　[db_B]　[file　path]　　　--　Compare　tables/views　between　two　databases　
　diffdata　[db_A]　[db_B　or　file]　[file　path]　
　　　　　　　　　　　　　　　　　　　　--　Compare　data　for　a　given　sql　file　
　diffdb　[db_A　]　[db_B]　[project　path]　(date)　
　　　　　　　　　　　　　　　　　　　　--　Compare　database　objects　between　two　databases　
　editor　　　　　　　　　　　　　　　　　--　Set　text　editor　to　open　file　generated　by　mydbtool　
　quit　　　　　　　　　　　　　　　　　　--　Exit　
　will@will-laptop:~$　

建立连接

在我们项目组有很多数据库，而且如果在连接数据库时密码输错三次，用户就会被锁住，需要让经理去解锁。所以为了减少录入密码错误， mydbtool 会记录下数据库的用户名和密码。下次你连接相同数据库时就不需要再次输入密码。

在命令行中输入： conn [ 数据库名称 ]。如果你曾经连接过该数据库， mydbtool 会使用你上次的用户名和密码连接数据库。如果你以前没有连接该数据库，或者数据库的密码被修改了。mydbtool 会提示你重新输入相关连接信息。请看下面的程序流程图。

图 1. 连接数据库函数的流程图

查看原图（大图）

如果您想知道 mydbtool 记录了哪些连接信息， 输入 listconn 命令 :

清单 3. 显示连接信息

　will@will-laptop:~$　mydbtool　listconn　
　duke　webduke　db2user　password　
　rod　　webrod　　db2user　password　
　roll　webroll　db2user　password　
　rail　webrail　db2user　password　
　plug　webplug　db2user　password　
　EDITOR:emacsclient　
　will@will-laptop:~$　

所有连接信息都记录在 ${HOME}/mydbtool.profile 文件中，其中 EDITOR 属性是用来设置你希望用什么工具打开文本文件。你可以改成你喜欢的文本编辑器，也可以删除该属性，让 mydbtool 使用系统默认的文本编辑器。

获得数据库对象信息

mydbtool 提供了一些命令用于方便获取数据库对象的信息。运行这些命令前，你需要先使用 conn 命令连接上一个数据库。

view 命令用于获得数据库中用 SQL 编写的存储过程或者 UDF 的源代码。

清单 4. view 命令示例

　#　交互模式　
　db2==>conn　duke　
　webduke==>view　ebiz1.i_qt_quote　
　
　#　命令模式　
　mydbtool　-d　duke　view　ebiz1.i_qt_quote　

table 命令用于获得表的相关信息，包括列的信息，外键，主键和索引。

清单 5. table 命令示例

　#　交互模式　
　db2==>conn　duke　
　webduke==>table　ebiz1.web_quote　
　
　#　命令模式　
　mydbtool　-d　duke　table　ebiz1.web_quote　

dep 命令用于获得依赖于某个表的所有数据库对象。

清单 6. dep 命令示例

　#　交互模式　
　db2==>conn　duke　
　webduke==>dep　ebiz1.web_quote　
　
　#　命令模式　
　mydbtool　-d　duke　dep　ebiz1.web_quote　

column 命令是用于获得依赖与表中某一列的数据库对象。在数据库开发中，有时候我们确实需要知道，哪些存储过程用到了表中的某一列。例如，我们需要把某列删除或者改名时，可以使用 column 命令分析列修改后的影响。 column 命令的参数格式是： schema.tablename.columnname。

清单 7. column 命令示例

　#　交互模式　
　db2==>conn　duke　
　webduke==>column　ebiz1.web_quote.web_quote_num　
　
　#　命令模式　
　mydbtool　-d　duke　column　ebiz1.web_quote.web_quote_num　

check 命令用于快速测试某个存储过程。我们在编写存储过程时，会把一些存储过程调用的例子放在注释里面。 check 命令会提取这些例子，并调用他们，这样我们就很容易的看出，该存储过程在数据库中工作是否正常。

清单 8. check 命令示例

　#　SQL　文件里的　Sample　Call　
　--　Sample　Calls:　
　--　call　EBIZ1.I_QT_QUOTE　(?,　?,　'changwei@cn.ibm.com'　,　'USA',　'USD',　'',　'',　'',　NULL)　
　
　#　交互模式　
　db2==>conn　duke　
　webduke==>check　ebiz1.i_qt_quote　
　
　#　命令模式　
　mydbtool　-d　duke　check　ebiz1.i_qt_quote　

监控数据库

本节我们将介绍一些用于执行，监控和调优的命令。运行这些命令前，你需要先使用 conn 命令连接上一个数据库。

e 命令用于执行一个 SQL 文件或者一个 SQL 语句。e 命令会检查传入的参数是否是一个文件，如果是就会执行该文件 ; 如果不是， e 命令会把参数当做 SQL 语句执行。

清单 9. e 命令示例

　#　交互模式　
　db2==>conn　duke　
　
　#　执行一条　SQL　语句　
　webduke==>e　select　count(1)　from　ebiz1.web_quote　
　loading　on　[webduke]　and　logged　in　[logs/122809/webduke/run_webduke.log]　
　
　1　
　-----------　
　　　56764　
　
　1　record(s)　selected.　
　
　#　执行一个文件　
　webduke==>e　demo.sql　
　[demo.sql]　is　loading　on　[webduke]　and　logged　in　[logs/122809/webduke/run_webduke.log]　
　
　1　
　-----------　
　　　56764　
　
　1　record(s)　selected.　
　webduke==>　
　
　#　命令模式　
　mydbtool　-d　duke　e　demo.sql　
　[demo.sql]　is　loading　on　[webduke]　and　logged　in　[logs/122809/webduke/run_webduke.log]　
　
　1　
　-----------　
　　　56764　
　
　1　record(s)　selected.　

rebindall 命令用于把非法的存储过程重新编译一遍。存储过程经常会因为其依赖的表的修改而变成非法的。如果把那些非法的存储过程重新再装载一遍，那就太麻烦了。rebindall 命令帮我们解决了这个问题，它会把所有的非法的存储过程编译一遍，使得它们再次可用。rebindall 命令没有参数。

清单 10. rebindall 命令示例

　#　交互模式　
　db2==>conn　duke　
　webduke==>rebindall　
　
　#　命令模式　
　mydbtool　-d　duke　rebindall　

rebindsp 命令用于重新编译某一个存储过程，当我们建立一个索引以后，通常需要编译其相关的存储过程使得执行计划使用新建的索引。这个命令就是帮助我们做这件事情的。

清单 11. rebindsp 命令示例

　#　交互模式　
　db2==>conn　duke　
　webduke==>rebindsp　ebiz1.i_qt_quote　
　
　#　命令模式　
　mydbtool　-d　duke　rebindsp　ebiz1.i_qt_quote　

exp 和 expsp 命令用于生成文本格式的数据库执行计划。在大型的应用系统中，性能是一个不可回避的问题。一般我们可以通过创建索引来提高数据库的性能。为了确定需要创建哪些索引，我们一般会查看数据库的执行计划，看其中有没有进行表扫描，如果扫描的表是一个数据量很大的表，我们就需要建立相应的索引。 exp 命令的参数是需要解析的 SQL 文件，expsp 命令的参数是需要解析的存储过程名称。

清单 12. exp 和 expsp 命令示例

　#　交互模式　
　db2==>conn　duke　
　webduke==>exp　demo.sql　
　webduke==>expsp　ebiz1.i_qt_quote　
　
　#　命令模式　
　mydbtool　-d　duke　exp　demo.sql　
　mydbtool　-d　duke　expsp　ebiz1.i_qt_quote　

比较数据库对象

本节我们将介绍 mydbtool 中最重要的一些命令。这些命令用于比较数据库。

diffsp 命令是用于比较存储过程或者 UDF 在两个数据库之间的不同。此命令有三个参数： 源数据库，目标数据库和一个文件路径，该文件里面列出了需要比较的存储过程的名称。

清单 13. diffsp 命令示例

　#　交互模式　
　webduke==>diffsp　duke　rod　sp_change_list.txt　
　
　#　命令模式　
　mydbtool　diffsp　duke　rod　sp_change_list.txt　

difftab 命令是用于比较表在两个数据库之间的不同，包括表相关的索引和访问权限。此命令有三个参数： 源数据库，目标数据库和一个文件路径，该文件里面列出了需要比较的表的名称。

清单 14. difftab 命令示例

　#　交互模式　
　webduke==>difftab　duke　rod　table_change_list.txt　
　
　#　命令模式　
　mydbtool　difftab　duke　rod　table_change_list.txt　

diffdb 命令是前面两个命令的组合，它会比较存储过程，UDF，表，索引和访问权限。此命令有四个参数： 源数据库，目标数据库，存储存储过程源程序的项目目录和日期。这里，我们解释一下后两个参数。diffsp 和 difftab 命令中需要一个包含要比较对象的列表 (Change List)，有时候准备这个列表也是比较麻烦的，所以在 diffdb 中， mydbtool 帮助我们准备这个列表文件。我们告诉存储过程和表的源文件的存放目录和一个时间，mydbtool 会找到所有在该时间后修改过的文件，然后从这些文件中取出表或者存储过程的名称，这样一个列表文件就有了。然后 diffdb 再使用这个列表文件调用 diffsp 和 difftab 命令来得到比较结果。

清单 15. diffdb 命令示例

　#　交互模式　
　webduke==>diffdb　duke　rod　~/v1_0/quoteSQL　200905010000　
　
　#　命令模式　
　mydbtool　diffdb　duke　rod　~/v1_0/quoteSQL　200905010000　

项目开发实例

我们已经发布了该系统的 v1.0 版。现在在 v2.0 版开发中，我们项目中有如下数据库，

webduke: 开发数据库，用于开发调试存储过程和进行 Unit Test。

webrod: FVT 数据库，用于集成测试和功能验证。

webroll: UAT 数据库，用于全面的测试，为发布作准备。

webplug: 生产数据库，我们的项目会发布到该数据库供最终用户使用。

webrail: 该数据库环境和生产环境是一致的。用于重现和修复最终用户发现的系统 bug。

表 1. 开发环境信息

版本	SQL 文件存储路径	开发起始时间
v1.0	~/v1_0/orderSQL	2009 年 7 月 1 日
v2.0	~/v2_0/orderSQL	2009 年 10 月 8 日

随着需求的不断增长， 我们的《订单管理系统》的功能越来越丰富，同时为了支持数据挖掘，我们系统里的表还被其他系统的存储过程访问。我们有一个新的需求： 把字段 MIS.ORDER.ORDER_TYPE 的类型从 Int 改为 String。

首先，让我们来分析一下这个改动对系统的影响：需要修改哪些存储过程或者 UDF ; 对其他应用系统有没有影响等等。如何确定改动所引起的影响呢？可能有人会说，使用 dep 命令就可以得到依赖表 MIS.ORDER 的存储过程和 UDF。对，我们可以使用 dep 命令。但是，现在我们系统已有上百个存储过程，引用 MIS.ORDER 表的存储过程也有几十个，而且其他应用系统的存储过程也依赖 MIS.ORDER 表。我们去检查这几十个存储过程，不但费时，而且也容易出错。这里，我们使用 column 命令。

清单 16. 获得 ORDER_TYPE 的引用

　mydbtool　-d　duke　column　mis.order.order_type

mydbtool 列出了十个引用该列的存储过程。现在我们只需要研究一下这十个存储过程如何修改就可以了。

确定了需要修改的存储过程，我们就可以使用 mydbtool 提供的命令来开发存储过程了。这里不再多说。感兴趣的读者可以参考“使用脚本加速 DB2 存储过程的开发”。这里我们主要介绍一下如何使用 mydbtool 进行存储过程性能调试和部署。

我们发现，MIS.S_QT_ACCESS_USER 这个存储过程的性能特别差，我们需要对它进行性能调优。我们使用 expsp 获得该存储过程的执行计划。

清单 17. 获得存储过程 MIS.S_QT_ACCESS_USER 的执行计划

　mydbtool　-d　duke　expsp　mis.s_qt_access_user　

根据 mydbtool 生成的执行计划，我们发现存储过程中的第二个游标的 cost 很大，而且有表扫描（ table scan ）。于是我们建立了一些索引来避免表扫描。然后，我们需要重新编译绑定这个存储过程和获得新的执行计划。

清单 18. 编译和获得存储过程 MIS.S_QT_ACCESS_USER 的执行计划

　mydbtool　-d　duke　rebindsp　mis.s_qt_access_user　
　mydbtool　-d　duke　expsp　mis.s_qt_access_user　

根据新的执行计划，我们看到这个存储过程的 cost 降低了很多。通过 check 命令，我们测试可以看到执行速度从 10 秒降到了 2 秒。我们在开发数据库 webduke 修改完了所有的存储过程，现在我们需要把它们部署到 FVT 数据库 webrod 上。如何确定哪些存储过程需要部署到 webrod 上呢？ 解决这个问题，有两种方法。方法一，我们记录下所有修改的存储过程名称，然后使用 mydbtool 的 e 命令把它们部署到 webrod 上。这种方法对于小项目或者说小的修改是可行的。但是对于像我们有几十个人的跨国开发团队，把所有修改过得存储过程记录在文档中，显然有些困难，而且也不高效。我们使用方法二， webduke 是我们的开发数据库，所有修改的存储过程都部署在那里，并且被开发人员测试过了。 webrod 上的存储过程是上个版本 v1.0 的。我们只需要比较 webduke 和 webrod 上的存储过程的异同，就可以知道在这个版本 v2.0 中我们修改了哪些存储过程，这些修改了的存储过程需要部署到 webrod 上去。

清单 19. 获得 v2.0 中修改的存储过程

　mydbtool　diffdb　duke　rod　~/v1.0/orderSQL　200907010000　

mydbtool 的 diffdb 命令给我们生成了一个报告，报告中有需要部署到 webrod 上的存储过程名称列表 cr_list.txt 和一个部署它们的 Shell 脚本 cr_report.txt 。我们把 cr_report.txt 文件重命名为 cr_rod.sh，并且在文件开头加上连接数据库的命令。然后我们就可以使用 cr_rod.sh 部署存储过程了。

清单 20. 部署修改的存储过程

　bash　cr_rod.sh　

现在让我们简单的测试一下 webrod 上的存储过程。

清单 21. 简单验证部署情况

　mydbtool　-d　rod　check　ebiz1.i_qt_quote

我们在控制台上可以看到 mydbtool 调用存储过程中的 Sample Call 的情况。

当然，在我们从 FVT 到 UAT 再到 Production 的过程中，都是使用 diffdb 来进行部署的。可以看到， diffdb 为我们减少了很多工作量，同时避免了错误。

结束语

脚本文件是我们开发 DB2 数据库应用的一大利器。我们充分利用脚本文件，可以提高开发效率。毕竟，每次在需要的时候，都去重新编写同一个命令是一件很麻烦的工作。而脚本文件的最大好处，就是可以提高语句的重用性，节省我们编写语句、调试测试的时间。

本文示例源代码或素材下载