利用mydb2脚本实现数据库的敏捷开发
本文是《使用脚本加速 DB2 存储过程的开发》的升级篇。 在《使用脚本加速 DB2 存储过程的开发》中我们通过使用 Windows 下的 bat批处理(.bat) 脚本来帮助我们进行数据库开发。 本文中介绍的 mydb2 脚本是对它们的重写。mydb2 是用 shell 脚本来编写的。之所以使用 shell 而不是 bat， 是因为 shell 提供了更多的功能，而且它可以在 Windows(Cygwin) 和 Linux/Unix 等系统下使用。所以 mydb2 是一个功能更强大， 而且跨平台的工具。本文不会重复介绍《使用脚本加速 DB2 存储过程的开发》已经介绍过的命令，它们在 mydb2 脚本中仍然适用。 下面我们将主要讨论 mydb2 脚本提供的一些高级功能。
本文主要讨论使用 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 服务器上就可以了。 但是存储过程的部署需要我们只更新修改过的存储过程。因此，如何正确快速的把存储过程部署到为数众多的数据库中，就是一个很大的问题。
我们的解决方案
敏捷开发要求我们能够迅速应对变化，快速应对变化就要求我们尽量把一些耗时的工作自动化。 出于这个目的，我们开发了 mydb2 脚本来解决我们遇到的问题。它提供很多数据库开发和部署的工具，可以大大提高开发工作的效率和准确性。 其部署工具可以比较数据库(fvtdb， uatdb 和 proddb 等)之间的不同，生成一个报告。通过该报告，我们可以很容易的把更新的存储过程部署到正确的数据库环境中。
在介绍 mydb2 之前，让我们先了解一下 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 中国镜像"。你只需要安装默认的软件包就可以开始使用 mydb2 脚本了。
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。
本文的 mydb2 脚本就是采用 Bash 来编写的，它充分利用了 DB2 的常用命令和 sed， awk 等 Linux/Cygwin 下的文本处理工具。
mydb2 用法简介
要使用 mydb2 脚本，你需要把本文中的附件 mydb2.zip 解压到某个目录，然后把该目录放到 PATH 里面。
<#+BEGIN_SRC >
mydb2 有两种使用模式，一种是交互式，一种是命令式。交互式方式方便用户执行 DB2 命令。命令式则可以在其他脚本中调用 mydb2。 在控制台输入 mydb2 然后回车就会进入交互模式。在交互模式中你可以使用 mydb2 的很多命令。 mydb2 的命令模式需要在调用的时候加一个参数 -d [database name]。下面，我们会依次介绍 mydb2 中各个命令的使用方法。
在命令行中输入： mydb2 help。我们就可以看到一个简单的帮助。
will@will-laptop:~$ mydb2 help
mydb2 --- 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 mydb2
quit -- Exit
will@will-laptop:~$
建立连接
在我们项目组有很多数据库，而且如果在连接数据库时密码输错三次，用户就会被锁住，需要让经理去解锁。所以为了减少录入密码错误， mydb2 会记录下数据库的用户名和密码。下次你连接相同数据库时就不需要再次输入密码。
在命令行中输入： conn [数据库名称]。如果你曾经连接过该数据库， mydb2 会使用你上次的用户名和密码连接数据库。 如果你以前没有连接该数据库，或者数据库的密码被修改了。mydb2 会提示你重新输入相关连接信息。请看下面的程序流程图。
图 1. 连接数据库函数的流程图
如果您想知道 mydb2 记录了哪些连接信息， 输入 listconn 命令:
will@will-laptop:~$ mydb2 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}/mydb2.profile 文件中，其中 EDITOR 属性是用来设置你希望用什么工具打开文本文件。你可以改成你喜欢的文本编辑器， 也可以删除该属性，让 mydb2 使用系统默认的文本编辑器。
获得数据库对象信息
mydb2 提供了一些命令用于方便获取数据库对象的信息。运行这些命令前，你需要先使用 conn 命令连接上一个数据库。
view 命令用于获得数据库中用 SQL 编写的存储过程或者 UDF 的源代码。
<#+BEGIN_SRC ">
db2==>conn duke webduke==>view ebiz1.i_qt_quote
mydb2 -d duke view ebiz1.i_qt_quote #+END_SRC
table 命令用于获得表的相关信息，包括列的信息，外键，主键和索引。
<#+BEGIN_SRC ">
db2==>conn duke webduke==>table ebiz1.web_quote
mydb2 -d duke table ebiz1.web_quote #+END_SRC
dep 命令用于获得依赖于某个表的所有数据库对象。
<#+BEGIN_SRC ">
db2==>conn duke webduke==>dep ebiz1.web_quote
mydb2 -d duke dep ebiz1.web_quote #+END_SRC
column 命令是用于获得依赖与表中某一列的数据库对象。在数据库开发中，有时候我们确实需要知道，哪些存储过程用到了表中的某一列。 例如，我们需要把某列删除或者改名时，可以使用 column 命令分析列修改后的影响。 column 命令的参数格式是： schema.tablename.columnname。
<#+BEGIN_SRC ">
db2==>conn duke webduke==>column ebiz1.web_quote.web_quote_num
mydb2 -d duke column ebiz1.web_quote.web_quote_num #+END_SRC
check 命令用于快速测试某个存储过程。我们在编写存储过程时，会把一些存储过程调用的例子放在注释里面。 check 命令会提取这些例子，并调用他们，这样我们就很容易的看出，该存储过程在数据库中工作是否正常。
<#+BEGIN_SRC ">
-- Sample Calls: -- call EBIZ1.I_QT_QUOTE (?, ?, 'changwei@cn.ibm.com' , 'USA', 'USD', '', '', '', NULL)
db2==>conn duke webduke==>check ebiz1.i_qt_quote
mydb2 -d duke check ebiz1.i_qt_quote #+END_SRC
监控数据库
本节我们将介绍一些用于执行，监控和调优的命令。运行这些命令前，你需要先使用 conn 命令连接上一个数据库。
e 命令用于执行一个 SQL 文件或者一个 SQL 语句。e 命令会检查传入的参数是否是一个文件，如果是就会执行该文件; 如果不是， e 命令会把参数当做 SQL 语句执行。
# 交互模式
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==>
# 命令模式
mydb2 -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 命令没有参数。
# 交互模式 db2==>conn duke webduke==>rebindall # 命令模式 mydb2 -d duke rebindall
rebindsp 命令用于重新编译某一个存储过程，当我们建立一个索引以后，通常需要编译其相关的存储过程使得执行计划使用新建的索引。 这个命令就是帮助我们做这件事情的。
# 交互模式 db2==>conn duke webduke==>rebindsp ebiz1.i_qt_quote # 命令模式 mydb2 -d duke rebindsp ebiz1.i_qt_quote
exp 和 expsp 命令用于生成文本格式的数据库执行计划。在大型的应用系统中，性能是一个不可回避的问题。 一般我们可以通过创建索引来提高数据库的性能。为了确定需要创建哪些索引，我们一般会查看数据库的执行计划， 看其中有没有进行表扫描，如果扫描的表是一个数据量很大的表，我们就需要建立相应的索引。 exp 命令的参数是需要解析的 SQL 文件，expsp 命令的参数是需要解析的存储过程名称。
# 交互模式 db2==>conn duke webduke==>exp demo.sql webduke==>expsp ebiz1.i_qt_quote # 命令模式 mydb2 -d duke exp demo.sql mydb2 -d duke expsp ebiz1.i_qt_quote
比较数据库对象
本节我们将介绍 mydb2 中最重要的一些命令。这些命令用于比较数据库。
diffsp 命令是用于比较存储过程或者 UDF 在两个数据库之间的不同。此命令有三个参数： 源数据库，目标数据库和一个文件路径， 该文件里面列出了需要比较的存储过程的名称。
# 交互模式 webduke==>diffsp duke rod sp_change_list.txt # 命令模式 mydb2 diffsp duke rod sp_change_list.txt
difftab 命令是用于比较表在两个数据库之间的不同，包括表相关的索引和访问权限。此命令有三个参数： 源数据库，目标数据库和一个文件路径， 该文件里面列出了需要比较的表的名称。
# 交互模式 webduke==>difftab duke rod table_change_list.txt # 命令模式 mydb2 difftab duke rod table_change_list.txt
diffdb 命令是前面两个命令的组合，它会比较存储过程，UDF，表，索引和访问权限。 此命令有四个参数： 源数据库，目标数据库，存储存储过程源程序的项目目录和日期。 这里，我们解释一下后两个参数。diffsp 和 difftab 命令中需要一个包含要比较对象的列表(Change List)， 有时候准备这个列表也是比较麻烦的，所以在 diffdb 中， mydb2 帮助我们准备这个列表文件。 我们告诉存储过程和表的源文件的存放目录和一个时间，mydb2 会找到所有在该时间后修改过的文件，然后从这些文件中取出表或者存储过程的名称， 这样一个列表文件就有了。然后 diffdb 再使用这个列表文件调用 diffsp 和 difftab 命令来得到比较结果。
# 交互模式 webduke==>diffdb duke rod ~/v1_0/quoteSQL 200905010000 # 命令模式 mydb2 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 命令。
<#+BEGIN_SRC >
mydb2 列出了十个引用该列的存储过程。现在我们只需要研究一下这十个存储过程如何修改就可以了。
确定了需要修改的存储过程，我们就可以使用 mydb2 提供的命令来开发存储过程了。这里不再多说。 感兴趣的读者可以参考《使用脚本加速 DB2 存储过程的开发》。这里我们主要介绍一下如何使用 mydb2 进行存储过程性能调试和部署。
我们发现，MIS.S_QT_ACCESS_USER 这个存储过程的性能特别差，我们需要对它进行性能调优。我们使用 expsp 获得该存储过程的执行计划。
<#+BEGIN_SRC >
根据 mydb2 生成的执行计划，我们发现存储过程中的第二个游标的 cost 很大，而且有表扫描（ table scan ）。 于是我们建立了一些索引来避免表扫描。然后，我们需要重新编译绑定这个存储过程和获得新的执行计划。
mydb2 -d duke rebindsp mis.s_qt_access_user mydb2 -d duke expsp mis.s_qt_access_user
根据新的执行计划，我们看到这个存储过程的 cost 降低了很多。通过 check 命令，我们测试可以看到执行速度从10秒降到了2秒。 我们在开发数据库 webduke 修改完了所有的存储过程，现在我们需要把它们部署到 FVT 数据库 webrod 上。 如何确定哪些存储过程需要部署到 webrod 上呢？ 解决这个问题，有两种方法。 方法一，我们记录下所有修改的存储过程名称，然后使用 mydb2 的 e 命令把它们部署到 webrod 上。 这种方法对于小项目或者说小的修改是可行的。但是对于像我们有几十个人的跨国开发团队，把所有修改过得存储过程记录在文档中， 显然有些困难，而且也不高效。我们使用方法二， webduke 是我们的开发数据库，所有修改的存储过程都部署在那里，并且被开发人员测试过了。 webrod 上的存储过程是上个版本 v1.0 的。我们只需要比较 webduke 和 webrod 上的存储过程的异同， 就可以知道在这个版本 v2.0 中我们修改了哪些存储过程，这些修改了的存储过程需要部署到 webrod 上去。
<#+BEGIN_SRC >
mydb2 的 diffdb 命令给我们生成了一个报告，报告中有需要部署到 webrod 上的存储过程名称列表 cr_list.txt 和一个部署它们的 Shell 脚本 cr_report.txt 。我们把 cr_report.txt 文件重命名为 cr_rod.sh， 并且在文件开头加上连接数据库的命令。然后我们就可以使用 cr_rod.sh 部署存储过程了。
bash cr_rod.sh
现在让我们简单的测试一下 webrod 上的存储过程。
<#+BEGIN_SRC >
我们在控制台上可以看到 mydb2 调用存储过程中的 Sample Call 的情况。
当然，在我们从 FVT 到 UAT 再到 Production 的过程中，都是使用 diffdb 来进行部署的。 可以看到， diffdb 为我们减少了很多工作量，同时避免了错误。
结束语
脚本文件是我们开发DB2数据库应用的一大利器。我们充分利用脚本文件，可以提高开发效率。 毕竟，每次在需要的时候，都去重新编写同一个命令是一件很麻烦的工作。 而脚本文件的最大好处，就是可以提高语句的重用性，节省我们编写语句、调试测试的时间。