使用脚本加速 DB2 存储过程的开发

在针对 DB2 存储过程的开发中，您是否觉得 DB2 提供的开发工具虽然功能强大，但是执行速度较慢，稍显笨重，而我们的日常开发中仅仅使用了一些比较常用的功能。你是否觉得虽然其工具操作起来比较直观，但是在处理大量创建、修改操作或者重复执行一些操作时就会比较费时？是否感觉使用其工具，无法方便的记录跟踪我们执行过的操作？如果您有上述的问题，本文会给您提供一个解决办法——使用 Windows 脚本进行 DB2 存储过程开发。脚本通常执行速度快捷，灵活；善于处理批量操作；以文本格式编写的脚本也方便我们查看具体我们执行了哪些操作。脚本的特点恰恰帮助我们解决了刚刚提到的问题。

本文主要讨论使用 Windows 脚本与 DB2 交互的一些常用技巧和方法。介绍了利用 Windows 的脚本（批处理程序）连接数据库，查询表结构，UDF之间的引用关系，存储过程中使用了哪些表等等。同时通过一个演示项目的开发过程的介绍来展示如何使用文中的脚本进行开发。

背景知识简介

Windows 脚本常用命令

Windows 脚本也称之为批处理程序，是一个包含了一些需要顺序执行的 Windows 命令的文本文件，其扩展名为 bat 或 cmd。由于本文的脚本需要传入参数，读者需要在控制台中执行本文提供的 Windows 脚本。

下面是 Windows 中的一些常用命令和符号。这里仅仅列出一些说明，如果想了解 Window 命令的详细信息，请参考本文提供的参考资料。

1. REM：行注释命令，它不会被执行，只是起一个注释的作用，便于代码的阅读和维护；
2. ECHO：命令主要参数有 OFF 和 ON,用于控制是否在控制台上显示正在执行的命令。也可以使用 ECHO message 来显示一个特定的消息；
3. GOTO： 即为跳转的意思。在脚本中以“：XXX”构建一个标号然后用GOTO XXX 来执行标号后的命令；
4. CALL： 命令用于在主脚本执行过程中调用子脚本，当子脚本执行完毕后再继续执行主脚本。如果我们不使用 CALL 来调用子脚本，而是在主脚本中直接调用子脚本，那么被调用的子脚本执行完毕，也就表示主脚本执行的完毕，主脚本后续的命令将不会被执行；
5. PAUSE： 暂时停止脚本的执行；
6. PROMPT： 修改命令提示符。PROMPT [text]。text 表示新的提示符。 $P 代表当前路径，$G 代表 >；
7. START： 打开一个新的窗口执行某个命令。如果 START 后面跟的是一个文件名，那么 Windows 会调用打开该种文件的程序将其打开。例如，如果你设定 notepad（记事本）为 *.log 文件的打开程序。那么执行 START my.log 命令后，Windows 会启动 notepad（记事本）程序，并且打开 my.log 文件；
8. 符号（@）：@ 符号可以关闭当前命令行的回显。我们从上面知道用命令 echo off 可以关掉整个批处理的命令回显但却不能不显示 echo off 这个命令。现在我们在这个命令前加上 @，这样 echo off 这一命令就被 @ 关闭了回显，从而达到所有命令均不回显的要求；
9. 管道（输出重定向）命令 > 和 >>：将命令输出的结果重定向到特定文件中, > 与 >> 的区别在于，> 会清除文件中原来的内容，然后把输出结果写入文件，而 >> 只会追加内容到文件中，而不会改动其中原有的内容；
10. Windows 脚本参数：我们可以向脚本传递参数，并且可以在脚本中通过 %n 来引用。脚本每次能处理的变量从 %0~%9 共 10 个。其中 %0 默认为批处理的文件名。

DB2 CLP 简介

DB2 Command Line Processor（DB2 CLP）是所有 DB2 产品中都有的，可以使用这个应用程序运行 DB2 命令、操作系统命令或 SQL 语句。DB2 CLP 可以成为强大的工具，因为它能够将经常使用的命令或语句序列存储在批处理文件中，可以在必要的时候运行这些批处理文件。在 Windows 系统中，必须先（从普通命令窗口）运行 db2cmd 命令来启动 DB2 命令行环境。

在什么时候使用本文介绍的脚本？

脚本的优势在于可以重复执行。如果一个任务会被执行多次，或者被多人执行。那么将该任务编写成脚本会提高任务的执行效率和准确性。本文介绍的脚本都是一些在我们进行存储过程开发和调试中经常用到的。读者在后面会渐渐体会到脚本给我们带来的便利。

DB2 系统表的功能

编写针对 DB2 的脚本，我们需要首先了解 DB2 为我们提供了哪些命令和信息。DB2 为我们提供了大量的命令例如连接数据库，执行一个 SQL 文件，获得表结构的信息等等。我们会在下面解释具体的脚本的同时对一些简单的 DB2 命令进行解释说明。同时，DB2 把数据库对象的很多信息都存储到了系统表中。熟悉这些系统表就能够通过 SQL 语句获得我们需要的信息。下面我们先来学习一下 DB2 系统表。

在 DB2 数据库被创建的时候，DB2 会创建一些系统表。这些系统表中记录了所有数据库对象的信息，表或视图的列的数据类型，约束的定义，对象的权限和对象之间的依赖关系等。这些系统表的模式为 SYSIBM，其表名以 SYS 作为前缀。例如: SYSTABLES、SYSVIEWS 等等。DB2 为这些系统表建立了相对应的只读视图。这些视图的模式是 SYSCAT，它们的内容是其相对应的系统表的全部或者部分内容。这些视图的名字没有 SYS 的前缀。例如：SYSCAT.TABLES 是 SYSIBM.SYSTABLES 的视图。

我们可以通过 LIST TABLES FOR SYSTEM 或 LIST TABLES FOR SCHEMA schemaname 命令查看所有的系统表和相关的视图信息。下面我们会介绍一些本文用到的系统表和视图。

SYSCAT.TABLES：数据库中对象的信息，包括 table，view，nickname 和 alias 的一些定义。详细说明见表 1。

SYSCAT.VIEWS：视图的定义信息。详细说明见表 2。

SYSCAT.ROUTINES：DB2 UDF，系统方法（system-generated method），用户定义方法（user-defined method）和存储过程（SP）的定义。我们可以认为该视图包含了数据库中程序的定义。见表 3。

表 4 所示的 SYSCAT.ROUTINEDEP 说明了 DB2 UDF与其他对象的依赖关系。

表 5 所示的 SYSCAT.COLUMNS 说明了表或视图的每一个列的信息。

表 6 所示的 SYSCAT.PACKAGEDEP 说明了 Pachage 与其他数据库对象的依赖关系。

表 7 所示的 SYSCAT.TABDEP 说明了视图或者固化视图和 DB2 对象之间的依赖关系。

使用脚本进行存储过程开发

在 DB2 开发过程中我们经常使用的操作是：

1. 连接数据库；
2. 在不同的数据库中执行编写好的 SQL 文件，对创建的数据库对象进行简单测试；
3. 当我们编写存储过程或者 UDF 的时候，我们需要查看其依赖的数据库表或视图的定义；
4. 修改表结构的时候，我们需要获得所有依赖这个表的存储过程和 UDF，防止因为表结构变化，使得相关的存储过程和 UDF 不可用。

以上操作我们可以在 CLP 下通过调用 DB2 提供的命令或者通过 SELECT 语句从 DB2 系统表中查出相关信息来完成。我们把这些操作编写为如清单 1 所示的脚本。

                1.	@echo off
2.	if {%1}=={} goto noparms 
3.	if {%1}=={dev} goto dev
4.	if {%1}=={test} goto test
5.	if {%1}=={prod} goto prod
6.	if {%1}=={off} goto terminate
7.	goto dev

8.	:noparms 
9.	echo Usage: conn db2type 
10.	echo example: conn dev 
11.	echo db2type -- dev, test, prod, off
12.	goto end 

13.	:dev
14.	db2 connect to db2dev user username using password
15.	PROMPT $P(%1)$G
16.	goto end

17.	:test
18.	db2 connect to db2test user username using password
19.	PROMPT $P(%1)$G
20.	goto end

21.	:prod
22.	db2 connect to db2prod user username using password
23.	PROMPT $P(%1)$G
24.	goto end

25.	:terminate
26.	db2 terminate
27.	PROMPT $P$G
28.	goto end 

29.	:end

每次连接数据库我们只需要键入 conn dev/test 就可以完成数据库的连接，简化了命令输入，避免输入错误的用户和密码。同时这个脚本也可被其他的脚本调用。

第 2 个批处理脚本是如清单 2 所示的执行 SQL 文件的脚本。使用方法是 esql test.sql [logs\out.log]。

                1.	@echo off
2.	if {%2} =={} goto setlog
3.	set LogFile=%2
4.	goto exe
5.	rem 如果没有指定输出文件，设置默认输出文件
6.	:setlog
7.	set LogFile=logs\run.log
8.	
9.	:exe
10.	echo %LogFile%
11.	echo --------------------- execute file:%1-------------------- >> %LogFile%
12.	db2 values(current time) >> %LogFile%
13.	db2 -td@ -f %1 -z %LogFile%
14.	db2 values(current time) >> %LogFile%
15.	pause

其中 –td@ 选项表示 SQL 文件中各 SQL 语句间的分割符为 @。–z 选项可以使 sql 的执行结果输出到指定的 Log 文件中。这里我们使用 -z 而不使用脚本中的管道符号（>）是因为 -z 不仅可以把执行结果记录到指定的文件中，同时还会在控制台上显示，这样即方便我们在执行 sql 的时候实时掌握 sql 运行情况，又可以在 log 中查找分析 sql 执行情况。

该脚本需要两个参数，第一个参数指明要执行的 sql 文件，第二个参数指明 log 文件名称,该参数可选。在脚本的第 12 行，第 14 行，我们使用 values(current time) 来记录脚本执行的开始时间和结束时间。这样可以帮助我们计算出脚本执行完毕需要多长时间。

第 3 个批处理脚本的作用是获得表定义,包括字段属性、主键、前 20 条记录，如果是视图，显示其创建语句，并显示视图中包含的表。如清单 3 所示。它的使用方法是 viewtable schema tablename。

                1.	@echo off
2.	mkdir logs
3.	set logfile=logs\view_%2.txt
4.	db2 describe table %1.%2 show detail >%logfile%
5.	echo  -----------------------below is (are) pk-------------------------  >>%logfile%
6.	db2 "select substr(colname,1,30) as pk from syscat.columns 
where keyseq is not null and tabschema=upper('%1')
and tabname = upper('%2')" >>%logfile%
7.	echo  ---------------below are the fisrt 20 rows in the %1.%2 table----  >>%logfile%
8.	db2 select * from %1.%2 fetch first 20 rows only >>%logfile%
9.	echo  -----------------------below is the sql of the %1.%2 view ---------  >>%logfile%
10.	db2 "select varchar(text,32670) from syscat.views 
where viewschema=upper('%1')and viewname = upper('%2')" >>%logfile%
11.	echo  ---------------below are tables  of the %1.%2 view ---------  >>%logfile%
12.	db2 "select distinct bname from syscat.viewdep 
where viewname= upper('%1')  and btype='T'" >>%logfile%
13.	start %logfile%

脚本中第 4 行的 describe 命令用于显示数据库表或视图的结构。包括字段名称，数据类型等。第 6 行用于查出表中的主键。视图 syscat.columns 中的 keyseq 字段表明了该字段在表的主键的顺序，为空表示该字段不是表的主键。第 8 行是获得表或视图中的前 20 条记录，便于我们对其分析。第 10 行是获得视图的 SQL 定义。第 12 行获得视图中依赖的表。

第 4 个批处理脚本的作用是查看存储过程或 UDF 的定义。如清单 4 所示。它的使用方法是 view spname。

                1.	@echo off
2.	set logfile=logs\view_%1.sql
3.	db2 select varchar(text,32670)  from syscat.routines 
where routinename=upper('%1') > %logfile%
4.	echo  -----------------------below are tables used by %1 ---------  >>%logfile%
5.	db2 select bname from syscat.packagedep 
where (btype='T' or btype='V') and pkgname in
(select bname from sysibm.sysdependencies where dname =upper('%1')) >> %logfile%
6.	
7.	start %logfile%

在脚本中我们之所以使用 VARCHAR 函数来格式 TEXT 字段中的内容，是因为 TEXT 字段是 CLOB 类型，其存储的存储过程或 UDF 的创建语句的字符比较多。如果我们不使用 VARCHAR 函数进行格式化，DB2 就会使用默认的 CHAR 函数（CHAR 的最大长度是 254）对其格式化，这样返回的 SQL 创建语句就可能被截去后面的部分。

第 5 行中脚本首先获得依赖某存储过程的所有的包（package）名，然后查找依赖这些包的所有的表和视图。这样就可以获得存储过程中使用的表和视图。

第 4 个批处理脚本的作用是使用脚本获得依赖某个表的所有存储过程，UDF 和视图。如清单 5 所示。它的使用方法是 dep schema spname。

                1.	@echo off
2.	mkdir logs
3.	set logfile=logs\%2.dep.txt
4.	echo --- dependent SPs --- >> %logfile%
5.	db2 "select r.routineschema, r.routinename  from syscat.routines r, 
syscat.packagedep pdep where pdep.bname=upper('%2') and pdep.bschema=upper('%1') 
and pdep.pkgname = 'p'||substr(char(r.lib_id+10000000),2) " >> %logfile%
6.	echo --- dependent UDF --- >> %logfile%
7.	db2 select routineschema, routinename from syscat.routinedep 
where bschema = upper('%1')  and bname = upper('%2') and btype ='T' 
order by bname >> %logfile%
8.	echo --- dependent view --- >> %logfile%
9.	db2 select viewschema, viewname from syscat.viewdep 
where bschema = upper('%1')  and bname = upper('%2') and btype ='T' 
order by bname >> %logfile%
10.	start %logfile%

脚本第 5 行是获得依赖某表的存储过程。因为存储过程会被编译成包（package）。所以，存储过程和表的依赖关系实际上是存储过程对应的包（package）和表之间的依赖关系。这种关系被记录在 syscat.packagedep 视图中。同样的，我们可以通过查询 syscat.routinedep 和 syscat.viewdep 视图，获得 UDF 和表，视图和表之间的依赖关系。

开发实例

使用上面的脚本，进行数据库开发就会很方便。下面我们看一个开发的例子。

需求描述

由于业务变更，我们的《订单管理系统》中的表 MIS.ORDER 需要加一个字段 GOV_FLAG SMALLINT 来标识是否是政府订单，如果是则需要执行特殊的折扣政策。同时需要去除冗余字段 ORDER_PRICE_TOT DECIMAL(19,4)。

需求描述

我们需要修改相应的表，存储过程和 UDF 的 SQL 文件，并把它们重新装载到开发数据库和集成测试数据库中。并需要保留表中原有数据。

假设我们有两个数据库，一个是名为 dev 的开发数据库，一个是名为 test 的集成测试数据库。只有项目组长拥有在 test 数据库上执行的权限，开发人员只可以在 dev 数据库上执行操作。

首先项目组长会分析需求，制定任务分配；然后开发人员根据任务分配编写相应的 SQL 和脚本文件，并且在 dev 数据库上进行装载和测试；最后项目组长把开发人员提交的脚本文件汇总起来，在 test 数据库上进行装载和测试。

1. 项目组长

项目组长需要把这个任务分配给组员去完成。首先组长使用脚本 viewtable MIS ORDER 查看一下当前在数据库中表的结构，检查添加的字段是否会和其他字段有冲突。然后项目组长使用脚本 dep MIS ORDER 获得依赖表 MIS.ORDER 的所有 DB2 对象。经过分析这些依赖对象，项目组长发现，表结构的修改会导致存储过程 MIS.SP_ADD_ORDER、UDF MIS.F_CHECK_ORDER 和视图 MIS.V_ORDER 的修改，并会导致两个存储过程需要 rebind。分析结果汇总如下表 8 所示：

于是项目组长进行如表 9 的任务分配。

2. 开发人员 A

A 接到任务后，从 CVS 中获得表创建 SQL 文件 ORDER.SQL，根据任务要求修改了字段 GOV_FLAG 和 ORDER_PRICE_TOT。下面 A 需要把它装载到数据库中。在重新创建表 MIS.ORDER 前，我们需要先做一些准备工作：

• 备份表 MIS.ORDER 中的数据；
• 获得依赖表 MIS.ORDER 所有 UDF，备份这些UDF；
• 获得依赖表 MIS.ORDER 的所有存储过程，以便在重新创建表后，对它们进行 rebind。

A 首先编写了备份表的 SQL 文件 BACKUP_MIS_ORDER.SQL；然后编写恢复数据的 SQL 文件 RESTORE_MIS_ORDER.SQL；执行 dep MIS ORDER 获得依赖该表的 UDF 和存储过程。编写 DROP_UDF_4_ORDER.SQL，RELOAD_UDF_4_ORDER.SQL 和 REBIND_SP_4_ORDER.SQL。如果读者不熟悉如何获得和 rebind 非法的存储过程，可以参考 DB2 存储过程开发最佳实践。

当所有的 SQL 文件编写完后，A 开始编写执行脚本，如清单 6 所示：

                rem 连接开发数据库 dev
conn dev
rem 备份表
esql BACKUP_MIS_ORDER.SQL
rem 删除依赖表的 UDF
esql DROP_UDF_4_ORDER.SQL
rem 重建表
esql ORDER.SQL
rem 恢复表数据
esql RESTORE_MIS_ORDER.SQL
rem 恢复 UDF
esql RELOAD_UDF_4_ORDER.SQL
rem rebind 相关存储过程
esql REBIND_SP_4_ORDER.SQL
rem 断开数据库连接
conn off

通过上面的脚本，A 成功的完成了他的任务。他把 SQL 和 Windows 脚本提交给项目组长。

3. 开发人员 B

B 接到任务后，从 CVS 中得到 SP_ADD_ORDER.SQL。 为了避免其他人也在修改这个存储过程，B 使用脚本 view SP_ADD_ORDER 命令获得了当前数据库中的存储过程脚本，然后和 CVS 上的进行比较。当确认获得了最新的 SP_ADD_ORDER.SQL 后，B 开始修改 SP_ADD_ORDER.SQL。修改完毕以后，B 又修改了针对该存储过程的测试脚本 TEST_SP_ADD_ORDER.SQL。所有的 SQL 修改完，并且被检查通过后，B 开始编写脚本把存储过程装载到数据库中。脚本内容大致如清单 7 所示。

                rem 连接开发数据库
conn dev
rem 重建修改过的存储过程
esql SP_ADD_ORDER.SQL
rem 对重建的存储过程进行测试
esql TEST_SP_ADD_ORDER.SQL
conn off

通过上面的脚本，B 成功的完成了他的任务。他把 SQL 和 Windows 脚本提交给项目组长。

4. 开发人员 C

C 同样使用 dep MIS F_CHECK_ORDER 和 dep MIS V_ORDER 获得依赖它们的对象，发现只是 F_CHECK_ORDER 有一些依赖对象需要重建。所以 C 编写了下面的 SQL 文件：DROP_UDF_4_MIS_F_CHECK_ORDER.SQL 和 RESTORE_UDF_4_MIS_F_CHECK_ORDER.SQL。下面是 Windows 脚本，如清单 8 所示：

                rem 连接开发数据库
conn dev
rem 删除 F_CHECK_ORDER 的依赖对象
esql DROP_UDF_4_MIS_F_CHECK_ORDER.SQL
rem 重建修改的 UDF 和视图
esql V_ORDER.SQL
esql F_CHECK_ORDER.SQL
rem 恢复依赖对象
esql RESTORE_UDF_4_MIS_F_CHECK_ORDER.SQL
rem 测试重建的 UDF 和视图
esql TEST_V_ORDER.SQL
esql TEST_F_CHECK_ORDER.SQL
conn off

通过上面的脚本，C 成功的完成了他的任务。他把 SQL 和 Windows 脚本提交给项目组长。

5. 项目组长

项目组长把 A、B、C 提交上的 Windows 脚本合并成一个脚本，并改为连接集成测试数据库，把这些存储过程，UDF 等的改变装载到了集成开发测试数据库中。脚本如清单 9 所示：

                conn test
esql BACKUP_MIS_ORDER.SQL
…
esql TEST_V_ORDER.SQL
…
conn off

这样，一个数据库对象的修改就完成了。在实际开发中的情况可能要更复杂一些。但是通过这个例子我们可以看到，通过使用 Windows 脚本，我们可以节省一些重复性的工作并且提高了工作的准确性。当整个团队熟悉了这些脚本，工作效率的提高是相当可观的。

总结

对脚本的灵活快捷特性以及 DB2 系统表含义的理解，是编写出文中脚本的根本。利用脚本和 DB2 系统表的方式和方法远比本文描述的要丰富得多。有兴趣的读者可以加深对它们的研究。相信会对大家的日常工作有所帮助。

查看本文来源