DB2 脚本编制用于自动执行任务、测试和使简单方案快速原型化。本文描述了如何编写包含了 SQL 过程元素的脚本、如何将脚本的输出假脱机存储到一个日志文件以及如何处理参数。
本文使用了几个示例教您如何在 DB2® Universal Database? 中编写高级 SQL 脚本，这些脚本可以用于（但并不仅限于）运行简单批处理进程、开发测试方案以及自动测试应用程序组件。本文假定您熟悉
DB2 SQL 过程语言的基本知识。
如果您已经阅读了我的上一篇有关“DB2 开发人员专区”的文章 —
在 DB2 UDB 中将 SQL 过程语言用于触发器，那么您就会熟悉将在这里看到的一些语法。
先决条件：要运行这些示例，需要 DB2 7.2 或者带 FixPak 3 的 DB2 v7.1。
遵循学习与计算机相关的新知识的常见惯例，我们将以一个简单脚本开始，创建一个名为 HELLO 的表，并在其中插入单词“hello
world”。
1. 使用文本编辑器，输入如下行，然后将此文件保存为 helloworld.db2。
!echo Beginning Script1@
!echo creating table....@
CREATE TABLE HELLO (mycol VARCHAR(20))@
!echo inserting values....@
INSERT INTO HELLO VALUES ('hello world')@
!echo displaying result@
SELECT * FROM HELLO@
!echo cleaning up@
DROP TABLE HELLO@
!echo done.@ |
2. 使用与以下类似的命令，连接至一个数据库：
db2 connect to <
dbname> user <
userid> using <
password>
|
3.当完成连接后，输入以下命令：
db2 -td@ -f helloworld.db2
|
这个命令执行时有点象带有一些特殊标志的标准 DB2 命令行处理器（Command Line Processor(CLP)）命令：
- -t 标志单独指定了脚本使用标准分号（;）来作为命令的结束。
- 然而，当与 -d 标志和 @ 结合使用时，CLP 把 @ 解释为一个语句的结束。稍后有一个示例将强调这一点的重要性。还请注意：在
-d 和 @ 之间不可以有空格，因为那样会表示您要使用一个空格作为终止字符。
- 最后，-f 标志和文件名参数指定了用来作为 CLP 的输入的文件名。
4. 在 步骤 3输入命令后，脚本的屏幕输出看起来如下：
Beginning Script1
creating table....
DB20000I The SQL command completed successfully.
inserting values....
DB20000I The SQL command completed successfully.
displaying result
MYCOL
--------------------
hello world
1 record(s) selected.
cleaning up...
DB20000I The SQL command completed successfully.
Done.
|
从上面的示例中，您会看到 DB2 中的基本脚本编制相当容易。这个示例强调了以下几点：
- 这种脚本编制不同于用‘db2’作为每个 DB2 语句前缀的 OS shell 脚本。有关这一点的示例，请参阅 从
OS shell 脚本传递参数。
- 通过用感叹号（!）作为 OS 命令的前缀，可以从这些 DB2 脚本执行 OS 命令。DB2 CLP 还支持 echo
语句，但我用“!echo”说明 OS 命令的使用。
- 脚本内的所有命令（包括 OS 命令）的结尾都要使用命令终止字符（@）。
技巧：您可能已注意到执行的 DB2 命令没有在屏幕上显示，而只显示了命令成功与否。如果要查看显示在屏幕上的命令，可以使用
-v 标志。例如：
db2 -v -td@ -f helloworld.db2
|
现在让我们尝试一个更为复杂的脚本。例如，假定您希望使用脚本编制整夜执行一些无人照管的任务，但又不打算呆在办公室里来确保它成功执行。您可以做的就是将脚本输出写到一个日志文件，然后在第二天早上查看结果。
为了说明 DB2 支持的某些高级逻辑，我们通过添加以下限定条件使示例更为复杂：必须只在星期一和星期五执行脚本。为了强制执行这个规定，我们将在脚本中添加几个约束。
1.使用文本编辑器，输入下列代码，然后将文件保存为 complex.db2：
!echo beginning complex.db2@
!echo creating table....@
CREATE TABLE HELLO (mycol VARCHAR(20))@
begin atomic
if (DAYOFWEEK (Current Timestamp)=2) then
insert into HELLO values ('Hello Monday');
elseif (DAYOFWEEK (Current Timestamp)=6) then
insert into HELLO values ('Hello Friday');
else
SIGNAL SQLSTATE '80000'
SET MESSAGE_TEXT='Script is for MON & FRI only!';
end if;
end@
!echo Retrieving from HELLO table..@
SELECT * FROM HELLO@
!echo Cleaning up...@
DROP TABLE HELLO@
!echo This message will get written to screen, but not to the log file@
VALUES ' This message will get written to the screen AND log file'@
|
提示：DAYOFWEEK() 是一个函数，在给定一个时间戳记参数的情况下，它会返回一个表示一周中某一天的值（在
1（星期日）和 7（星期六）之间）。
在运行上面的脚本之前，让我们先对这里说明的一些 DB2 特性加以强调：
- 可以使用诸如 DAYOFWEEK() 那样的 DB2 内置函数。可以混合使用 DB2 提供的任何函数或者任何您自己的用户定义函数。
- 可以使用诸如 CURRENT TIMESTAMP、CURRENT SCHEMA、CURRENT DATE、CURRENT
TIME 那样的 DB2 特殊寄存器。
- 可以使用 SIGNAL 抛出一个用户定义的 SQL 错误，错误将返回到启动这个脚本的控制台或应用程序。
- 可以在脚本中使用 DB2 的 VALUES 语句。然而，如果在原子复合语句中使用了 VALUES，那么将不显示函数的任何结果。
- 可以在单个脚本中混合使用复合和常规 SQL 语句。
- 可以在复合 SQL 语句中使用高级过程逻辑，而且命令可以跨越多行。
3. 要运行这个脚本，从命令行输入以下命令。象前面一样，首先需要与数据库连接。
db2 -td@ -f complex.db2 -z output.log
|
该命令中额外的 -z 标志和文件名参数 output.log 使 DB2 将所有 DB2 生成的输出写到指定文件。使用这个标志，可以让脚本运行，并稍后返回到您的工作站以分析脚本的结果。
表 1显示了当脚本在星期五运行时，屏幕输出和日志文件输出的横向比较：
| 屏幕输出 |
日志文件输出 |
beginning complex.db2
DB20000I The SQL command completed successfully.
Retrieving from HELLO table..
MYCOL
--------------------
Hello Friday
1 record(s) selected.
Cleaning up...
DB20000I The SQL command completed successfully.
This will get written to screen, but not to log file
1
----------------------------------
This will get written to the screen AND log file
1 record(s) selected.
|
|
DB20000I The SQL command completed successfully.
MYCOL
--------------------
Hello Friday
1 record(s) selected.
DB20000I The SQL command completed successfully.
1
-----------------------------------
This will get written to the screen AND log file
1 record(s) selected.
|
|
在上面的输出中，成功执行了脚本，但我们注意到屏幕输出不同于日志文件（output.log）的输出。用粗体突出显示了这些差别，并且它们都是用感叹号（!）作为前缀的
OS“echo”命令。因为那些命令都在 OS 级别上执行，而不是由 DB2 执行，所以没有通过 -z 标志记录它们的输出。
请注意最后一句由下面 DB2 的 VALUES 语句发出的注释：
This will get written to the screen AND log file
|
被同时写到屏幕和日志文件，因为这个命令是由 DB2，而不是 OS 执行的。
下面是当脚本在星期三运行时相对应的屏幕和日志文件的结果：
| 屏幕输出 |
日志文件输出 |
beginning complex.db2
DB21034E The command was processed as an SQL statement
because it was not a valid Command Line Processor command.
During SQL processing it returned: SQL0438N Application
raised error with diagnostic text: "Script is for MON
& FRI only!" SQLSTATE=80000
Retrieving from HELLO table..
MYCOL
--------------------
0 record(s) selected.
Cleaning up...
SQL0100W No row was found for FETCH, UPDATE or
DELETE; or the result of a query is an empty table. SQLSTATE=02000
This will get written to screen, but not to log file
1
-----------------------------------
This will get written to the screen AND log file
1 record(s) selected.
|
DB21034E The command was processed
as an SQL statement because it was not a valid Command Line
Processor command. During SQL processing it returned:
SQL0438N Application raised error with diagnostic
text: "Script is for MON & FRI only!" SQLSTATE=80000
MYCOL
--------------------
0 record(s) selected.
SQL0100W No row was found for FETCH, UPDATE or
DELETE; or the result of a query is an empty table. SQLSTATE=02000
1
-----------------------------------
This will get written to the screen AND log file
1 record(s) selected. |
这次，粗体的突出显示指出了脚本的成功执行和未成功执行之间的区别。象预期的那样，查看脚本是如何失败的，并以我们定制的错误消息“Script
is for MON & FRI only!”来显示“应用程序引起的错误 SQL0438N”。因为存在这个错误，所以
HELLO 表中没有插入任何行，并且随后的 DELETE 语句没有找到任何可删除的内容，从而产生一个对我们的需求无害的 SQL
警告。
研究复合 SQL
再次查看 步骤 1中的代码。BEGIN ATOMIC 和 END@ 表示一个 DB2
复合原子 SQL 语句。通过使用复合 SQL 语句，可以将几个 SQL 语句合并为一个语句，并使 DB2 把整个脚本主体当作一个非全有即全无（all-or-nothing）的语句（象一个事务）。
正如上面的示例阐述的那样，您可以在一个脚本中混合使用复合 原子和常规 SQL 语句来实现所期望的最终结果。使用复合原子
SQL 语句还让您使用 SQL 过程语言元素。
在脚本编制中不支持使用非原子复合 SQL 语句。
终止字符
请注意：在 BEGIN ATOMIC 和 END@ 之间的语句中，@ 符号不作为终止字符是如何使用的。在一个复合语句体中，必须使用分号来表示任何
SQL 语句的结束。基于这个原因，我们必须使用 -td@ 标志，从而使命令解析器能够区分脚本中复合 SQL 语句和其它语句的语句结尾。
复合 SQL 中的 SELECT 语句
如果在复合 SQL 语句中包含 SELECT 语句，那么 SELECT 语句的结果不会显示在屏幕上。这是因为对于 shell
来说，SELECT 语句“从未发生”（总体上，DB2 只返回复合原子语句的执行是成功还是失败）。例如，比较以下结果：
| 脚本代码 |
结果 |
SELECT 'hello'
FROM sysibm.sysdummy1@
|
|
DB20000I The SQL command completed successfully.
1
-------
hello
1 record(s) selected.
|
|
BEGIN ATOMIC
SELECT 'hello'
FROM sysibm.sysdummy1;
END@
|
|
DB20000I The SQL command completed successfully.
|
|
提示：为了去除 SELECT 语句结果的列标题，请在发出 db2 命令时使用 -x 标志。如果脚本的输出要用来作为另一个进程的输入，那么去除标题是有用的。
抛出 SQL 异常
SIGNAL SQLSTATE...SET MESSAGE_TEXT 语句抛出一个用户定义的 SQL 异常。在上面的例子中，抛出
SQLSTATE 80000，其错误文本为：“Script is for MON & FRI only!”。 如果在同一个
BEGIN ATOMIC 节中存在其它修改数据的 SQL 语句，那么这个错误会使它们回滚。复合语句后面的语句将继续执行。
SIGNAL 语句的错误消息长度限制在 70 个字符。如果您指定了一个超出这一限制的消息，那么会在毫无警告的情况下截断它。
为了使脚本更灵活，您可能希望创建在执行期间可以从命令行上获取参数的脚本。遗憾的是，到目前为止，在我们所进行的这种脚本编制中，还无法将参数从
OS shell 上传递到脚本。然而，您可以通过如下方法解决这一限制：
- 临时创建一个获取参数的 SQL 用户定义函数（UDF）或 SQL 存储过程。
- 在脚本中调用这个函数或过程。
- 在脚本结束时删除这个 UDF 或存储过程。
选择使用 UDF 还是存储过程
选择存储过程而不是选择 UDF 有两个主要原因：
- 如果使用复杂查询和大型数据集，那么使用 SQL 存储过程可以达到最佳性能。
- 如果代码修改数据（INSERT、UPDATE 或 DELETE），那么必须使用 SQL 存储过程，因为 SQL UDF
目前还不支持数据修改。
从 OS shell
脚本传递参数
对于用“db2”作为数据库命令前缀的 OS shell 脚本，您可以按照以下方式传递参数（适用于 UNIX®）：
db2 select * from employee where empno='$1'
db2 select * from employee where empno='$2'
|
上面的脚本从 EMPLOYEE 表中选择雇员号（empno）等于从 OS 传递到脚本的第一个参数的记录，然后再选择雇员号与从
OS 传递到脚本的第二个参数相等的记录。（在 Windows® 平台上，使用 %1 和 %2 分别替代
$1 和 $2。）
然而，使用如上所示那样命令的 Shell 脚本，在脚本内部不能轻松支持如 IF/THEN/ELSE、局部变量、FOR
LOOPS 等 SQL 过程元素。这主要是由于代码格式有较多的限制，从而使这种方法只适合于简单的脚本编制。 |
使用 UDF 的示例
1. 为了准备运行这个示例，我们需要创建一个表，并在其中插入一些值。连接至数据库，然后执行下列 SQL 语句：
CREATE TABLE tab1 (id INT NOT NULL PRIMARY KEY, text VARCHAR(10))
INSERT INTO tab1 VALUES (1, 'one')
INSERT INTO tab1 VALUES (2, 'two')
|
2. 现在，将下列脚本输入到文本文件中，然后把它保存为 funcparam.ddl：
CREATE FUNCTION getText(key INT)
LANGUAGE SQL
RETURNS VARCHAR(20)
BEGIN ATOMIC
RETURN SELECT text FROM tab1 t WHERE t.id=key;
END@
|
3. 创建名为 funcparam.cmd 的第二个文件，它是一个获取参数的 shell 脚本，含有以下内容（使用适用于您平台的版本）
| UNIX |
Windows |
db2 connect to <dbname>
db2 -td@ -f funcparam.ddl
db2 values getText($1)
db2 drop function getText (INT)
|
|
db2 connect to <dbname>
db2 -td@ -f funcparam.ddl
db2 values getText(%1)
db2 drop function getText(INT)
|
|
4. 按如下执行脚本：
chmod +x funcparam.cmd （只有在 UNIX 中才需要用它来使文件可执行）
funcparam.cmd 1
|
在上面的示例中，值 1 作为唯一参数传递给 funcparameter.cmd shell 脚本。然后，这个 shell
脚本创建函数，并用提供的参数调用这个函数。接着，在完成之前，它通过删除（drop）这个函数来清除其自身。请注意：在 UNIX
脚本中，需要使用 connect 语句，因为 UNIX 中的 shell 脚本被派生（fork）到了它们自己的进程中。在 Windows
中不需要 connect 语句。
这里是上面示例的输出：
db2 -td@ -f funcparam.ddl
DB20000I The SQL command completed successfully.
db2 values getText(1)
1
--------------------
one
1 record(s) selected.
db2 drop function getText(INT)
DB20000I The SQL command completed successfully.
|
|
使用 SQL 存储过程的示例
这里是与上面脚本等效的使用 SQL 存储过程的版本。（在机器上需要一个受支持的 C 编译器。更多信息，请参阅
DB2 Application Building Guide。）
1. 在文本文件中输入以下脚本，然后把这个文件保存为 procparam.ddl：
CREATE PROCEDURE getText (IN key INT)
LANGUAGE SQL
RESULT SETS 1
BEGIN
DECLARE C1 CURSOR WITH RETURN FOR
SELECT text FROM tab1 t WHERE t.id=key;
-- leave cursor open so that result set is returned.
OPEN C1;
END@
|
2. 创建名为 procparam.cmd 的第二个文件，它是一个获取参数的 shell 脚本，含有以下内容（使用适用于您平台的版本）：
| UNIX |
Windows |
db2 connect to <dbname>
db2 -td@ -f procparam.ddl
db2 call getText($1)
db2 drop procedure getText (INT)
|
|
db2 connect to <dbname>
db2 -td@ -f procparam.ddl
db2 call getText(%1)
db2 drop procedure getText (INT)
|
|
然后，为了执行这个脚本，请完成：
chmod +x procparam.cm （仅 UNIX 需要）
procparam.cmd 1
|
本节包含一些我觉得非常有用的技巧：
时间戳记和其它特殊寄存器
查看下面的脚本。您希望发生什么？
BEGIN ATOMIC
Insert into HELLO values (char (current timestamp));
Insert into HELLO values (char (current timestamp));
END
|
乍一看，您可能希望在 Hello 表中插入两个稍有不同的时间戳记值。然而，如果执行它，那么您会发现同一个时间戳记值插入了两次。发生这种情况是因为实际上
ATOMIC 复合 SQL 语句是作为一个 SQL 语句执行的一组 SQL 语句。因此，要小心使用日期和时间寄存器。
当 BEGIN ATOMIC 是这个行为定义的一部分时，该行为也适用于 SQL UDF。这种行为不适用于 SQL 存储过程，除非在同一个
BEGIN ATOMIC 节中包含了 SQL 语句。
如果您需要生成唯一的时间戳记，将 GENERATE_UNIQUE() 函数与时间戳记一起使用。例如：
BEGIN ATOMIC
insert into HELLO
values (char(timestamp(generate_unique()) + current timezone));
insert into HELLO
values (char(timestamp(generate_unique()) + current timezone));
END
|
UDF 及脚本中的游标
目前还不支持可更新的游标。如果您需要在脚本中使用游标，那么可以使用 FOR LOOP 构造（它的功能与只读游标类似），并在循环内使用
UPDATE 语句。例如：
BEGIN ATOMIC
FOR cur1 AS SELECT c1, c2, c3 from mytable
IF cur1.c1 = 1 THEN
INSERT INTO sometable1 values (cur1.c2, cur1.c3);
ELSE
UPDATE sometable1 SET somecol=cur1.c2;
END IF;
END@
|
我们已经了解了如何利用 DB2 的增强脚本编制功能。脚本编制有助于自动执行任务、测试和使简单方案快速原型化。我们知道了如何编写包含
SQL 过程语言元素的脚本、如何将脚本的输出假脱机存储到一个日志文件以及如何通过使用 UDF 和 SQL 存储过程在脚本中处理参数。
