Paul Yip
IBM 多伦多实验室
2003 年 7 月
为了回应读者的问题，作家 Paul Yip 对于脚本编制提出了另外几个技巧和技术，还概述了 DB2 V8 中的新增功能。
重要：在阅读本文之前请先看一下 免责声明。
简介
本文是以前的文章 DB2 v7.2 中的高级 SQL 过程脚本编制的续篇。本文向您提供了新的理解、技巧和诀窍。因为本文是为回应那些阅读了第一篇文章的读者反馈而编写的，而且它作为附加问题的回答，所以结构不是很严谨。事实上，根本没有结构可言 — 确实如此。但是，根据每一节的标题，您应该能快速找到需要的内容。
编写的所有示例都适用于 UNIX®。只要做少量更改（如果您愿意），那么它们也可适用于 Windows®。
对于 SQL 过程化语言（SQL PL）脚本编制，DB2® V8.1 新增了什么功能
DB2 Universal Database™ V8 中的两个主要增强功能是：
支持分区数据库
我编写过有关用于触发器和脚本编制的 SQL PL 的其它文章，如果您已经阅读了这些文章，那么可能已经注意到这些文章顶部的说明：
这里所开发和测试的示例适用于 DB2 个人版（Personal Edition）、工作组版（Workgroup Edition）和企业版（Enterprise Edition）。如果您对这些示例有疑问，请与作者联系。
这是一种暗示：企业扩展版（Enterprise-Extended Edition，也称为 EEE）不支持将 SQL PL 用于动态复合语句。DB2 V8 开始取消了这一限制。另外，需要指出，在 V8 中，企业版和企业扩展版已经合并成企业服务器版（Enterprise Server Edition，ESE）。
支持 FETCH FIRST n ROWS
支持 FETCH FIRST n ROWS 允许您在产生多行时，对变量赋值限制结果集。清单 1 显示了这一限制的实际使用：
清单 1. 在子 select 中使用 FETCH FIRST n ROWS 的示例
BEGIN ATOMIC DECLARE v_name VARCHAR(30); SET v_name = (SELECT firstnme || || lastname FROM employee FETCH FIRST 1 ROWS ONLY); SIGNAL SQLSTATE 80000 SET MESSAGE_TEXT=v_name; END@ |
如果不使用 FETCH FIRST 1 ROWS ONLY 子句，那么 SELECT 返回多行时，就会产生错误，因为我们只能对变量 v_name 赋一个值。再回忆一下，您可能会发现，DB2 V7.2 中的变通方法是使用一个已标号的带 LEAVE 语句的 FOR 循环来模拟一个游标，从而只访问第一行。
FETCH FIRST 1 ROWS ONLY 子句保证只返回第一行，因此 SET 语句将决不会失败（尽管如果 SELECT 不返回任何行，该语句可能返回空值）。为了显示该子句的作用，上述示例使用了带有定制消息的 SIGNAL SQLSTATE。该消息是雇员表中第一个雇员的名字。
$ db2 -td@ -f fig1.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: "CHRISTINE HAAS". SQLSTATE=80000 |
检查 SQL 语句是否执行成功
假设有这样的方案，您有一组应用程序安装脚本。例如其中一个脚本是将基本数据插入到数据库。另一个脚本是安装第二阶段的一部分，如果第一个脚本中有任何操作没有顺利完成，那么决不应该执行第二个脚本。处理这种情况的一种方法是让第一个脚本记录重要的 SQL 操作的成败，以便第二个脚本可以确定它是否应该执行。
以下是最近引起我注意的一个诀窍。要设置这个示例，如下所示创建两个表：
db2 "create table SQL_LOG (description VARCHAR(20), status SMALLINT)" db2 "create table T1 (c1 INT NOT NULL UNIQUE)" |
表 SQL_LOG 用于跟踪重要的 SQL 执行的成败。表 T1 用于简化示例。表 T1 的重要功能在于它对列 C1 定义了唯一约束。
现在，请考虑清单 2 中的语句。
清单 2. Shell 脚本编制和 DB2 返回码
db2 "insert into T1 values (1)" db2 "insert into SQL_LOG values (insert first value, $?)" db2 "insert into T1 values (1)" db2 "insert into SQL_LOG values (insert second value, $?)" |
Windows：用 %errorlevel% 来替代 $?
这个脚本相当简单。T1 中两次插入了值 “1”。每次插入到 T1 后，我们还要对表 SQL_LOG 执行 INSERT 操作，插入的内容是操作的描述和 $? 的值。对 T1 的第二次插入会失败，因为其中定义了唯一约束。
变量 $? 保留前一次 SQL 语句执行的返回码。如果 $? 为 “0”，则该语句成功。如果 $? 为 “1”，则该语句成功，但有警告。任何其它值都意味着失败。
以下是选择表 SQL_LOG 中所有行时的输出。正如我们期望的，第二项报告失败：
SELECT * FROM SQL_LOG DESCRIPTION STATUS -------------------- ------ insert first value 0 insert second value 4 2 record(s) selected. |
限制：这个特性仅对于 shell 脚本编制是受支持的。它对 DB2 脚本无效。换句话说，正如示例中演示的那样，如果脚本中用 db2 作为每个语句的前缀，那么 $? 和 %errorlevel% 只返回一个值。
调用外部程序的诀窍
在有关脚本编制的文章中，我向您显示了如何通过使用动态复合 SQL 语句的 SQL PL 来进行脚本编制。我还向您显示了如何通过用感叹号（!）作为命令的前缀来调用操作系统命令。如果您使用过这两个特性，那么您可能会发现：复合 SQL 语句内不支持调用操作系统命令。换句话说，要么使用复合 SQL 语句，要么调用操作系统命令，但二者不能同时进行。
例如，清单 3 显示不受支持的的语法：
清单 3. 动态复合语句中不支持（!）
BEGIN ATOMIC IF (CURRENT DATE < DATE(2002-12-25)) THEN ! echo "SQL PL Book available soon!" >> messages.log ELSE ! echo "SQL PL Book now available!" >> messages.log END IF; END@ |
因为动态复合语句用于支持 SQL PL 在脚本编制、触发器和用户定义的函数（UDF）中的使用，因此它们将有相同的限制。那么怎样解决这个限制呢？
幸运的是，我们可以使用一点诀窍：利用 UDF 来执行纯 SQL 中不可用的操作。UDF 可以用外部语言（即，SQL 以外的其它语言）编写，并可以通过使用 CREATE FUNCTION 语句向数据库注册。在这个示例中，外部语言将是 Java™。
Java 代码（几乎）可以完成常规 Java 应用程序可以做的任何事情，包括进行操作系统调用。注册了外部函数之后，在可以使用常规数据库函数的任何地方都可以使用该函数。清单 4 显示了使调用外部程序简化的 Java 代码：
清单 4. os_cmdUDF.java 的外部 UDF 代码
import java.io.*; public class os_cmdUDF { public static int os_cmd(String cmd) { Runtime rt = Runtime.getRuntime(); Process p=null; int success = 0; try { p = rt.exec(cmd); } catch (IOException e) { success = -1; } return (success); } } |
将上述代码复制到文本编辑器，并将该文件保存为 os_cmdUDF.java 。在对该文件命名时要小心，因为 Java 源文件区分大小写，而且必须与类名相匹配。上述代码很简单 — 该类称为 os_cmdUDF，而方法名是 os_cmd()，它使用一个 String 参数。即使不熟悉 Java，也不必担心。重要的是理解 os_cmd() 接受的参数作为操作系统调用被执行。如果操作系统调用成功，则该函数返回 0，如果失败，则返回 -1。
sqllib/function 目录，并发出 CREATE FUNCTION 语句。
javac os_cmdUDF.java |
os_cmdUDF.class 的文件，它是 Java 程序的已编译形式。随后需要将该 .class 文件复制到 sqllib/function 目录：
cp os_cmdUDF.class sqllib/function/ |
清单 5：通过使用 CREATE FUNCTION 注册外部 UDF
CREATE FUNCTION OS_CMD ( IN VARCHAR(1000)) RETURNS INTEGER EXTERNAL NAME os_cmdUDF!os_cmd LANGUAGE JAVA PARAMETER STYLE JAVA NOT DETERMINISTIC NO SQL EXTERNAL ACTION |
清单 6 显示了我们如何根据上述语句修改 DB2 脚本以使用新函数：
清单 6：使用 os_cmd() 的示例
BEGIN ATOMIC DECLARE status INT DEFAULT 0; DECLARE script VARCHAR(20) DEFAULT /home/db2inst1/sqllib/write_msg.sh ; IF (CURRENT DATE < DATE(2002-11-30)) THEN SET status = os_cmd(script || DB2 SQL PL Book Available Soon!); ELSE SET status = os_cmd(script || DB2 SQL PL Book Now Available!); END IF; IF (status <> 0) THEN SIGNAL SQLSTATE 80000 SET MESSAGE_TEXT=OS command failed; END IF; END@ |
该示例尝试执行一个称为 write_msg.sh 的 UNIX 脚本，它将作为参数传递的文本追加到日志文件中。与清单 3 不同的代码以粗体突出显示。其中添加了变量“script”来保存带有绝对路径的脚本名，这样，当路径变化时便于修改。
建议：为了消除模糊性，请指出被调用脚本的绝对路径。
write_msg.sh 中的代码很平常，如清单 7 所示。
清单 7. write_msg.sh 的内容
# Simple message writer script echo $@ >> /home/db2inst1/application/messages.log |
UNIX：确保对脚本 write_msg.sh 的执行位作了设置，并确保可由 DB2 受防护的用户标识（即 db2fenc1）编写 message.log 文件。
Windows：使用write_msg.bat作为该脚本名称，将 $@ 替换为 %*，并使用正确的 Windows 路径语法。
要完全利用 UDF，就要添加额外代码，以检查 UDF 的返回码。无论什么原因导致操作系统命令失败，都会返回 SQLSTATE 80000。
另外，如果从示例上看不是很明显，那么可以使用有关 SQL PL 的名为 DB2 SQL Procedural Language for Linux, UNIX and Windows的新书，它很快会由 IBM Press/Prentice Hall（ISBN 0-13-100772-6）出版。在 http://www7b.boulder.ibm.com/dmdd/library/techarticle/0209yip/splbook.html上可以获得该书（草稿）的摘录，并且可以使用 ISBN 编号从任何在线书籍零售商那里进行预订。
通过任意使用这一执行外部操作的新方法，设想以下各种可能性：
如果您用这种方法确实进行了创造性的工作，那么我愿意听一听。
不使用表空间子句来编写灵活的表 DDL 脚本好几次有人问我，是否有一种方法可以通过使用表空间子句来编写创建表的参数化脚本。在特定表空间（而不是缺省表空间（USERSPACE1））中创建表的标准实践是将表空间名称作为 CREATE TABLE 命令的一部分提供。例如，要在表空间 TS1 中创建表 T1，可以使用以下 DDL 语句：
CREATE TABLE T1 (c1 INT) IN TS1; |
请考虑这样的方案：您有一组常用的 DDL 脚本，但在不同环境中执行这些脚本时，表空间名称会发生更改（您可能有许多客户，每个客户都拥有自己的表空间命名策略），通过使用类似如下的语句，您能使 DDL 脚本尽可能灵活：
CREATE TABLE T1 (c1 INT) IN $v_tablespace |
DB2 脚本编制当前不支持这一语句，因此以下是处理这一问题的一种方法。该解决方案利用了当 CREATE TABLE 语句未指定 IN 子句时 DB2 决定要使用哪个表空间的方法。
DB2 如何选择表空间
DB2 会使用以下规则为表选择表空间：
当有多个表空间满足上述规则时，依照以下被授予 USE 特权的优先次序进行选择：
如果还是有多个表空间可供选择，那么由数据库管理员决定最终选择。
记住了这些规则，该解决方案就依赖于操作表空间的权限。要使这个解决方案有效，用于创建表的用户标识不能拥有任何特殊特权（如 DBADM）而且不能是任何表空间的所有者。这通常不是问题，因为我经常看到客户使用 SYSADM 用户创建表空间，随后使用没有特权的用户创建表和其它对象。
使用脚本可以使该解决方案更简单
以下是该解决方案的各个具体步骤：
假设我们有如下需求：
要满足这些需求，我们需要将操作系统脚本和 DB2 脚本混合，以满足参数化需求。我们将利用一个操作系统脚本来充当主脚本（ createtables.bat 或 createtables.sh ），它调用所有其它脚本： app1_tables.ddl 和 app2_tables.ddl 。用户 user1 是没有特权的用户。
以下是 application1 的 DDL 脚本（app1_tables.ddl）：
CONNECT TO sample USER user1@ CREATE TABLE tab1 (c1 INT)@ |
以下是 application2 的 DDL 脚本（app2_tables.ddl）：
CONNECT TO sample USER user1@ CREATE TABLE tab2 (c1 INT)@ |
SYSADM 用户调用以下主脚本，并使用两个参数：application1 的表空间名称和 application2 的表空间名称。显示了行号便于讨论，它们不属于脚本。
清单 8：主脚本（createtab.sh）
1: db2 CONNECT TO sample 2: db2 REVOKE USE OF TABLESPACE USERSPACE1 FROM PUBLIC 3: db2 GRANT USE OF TABLESPACE $1 TO user1 4: db2 -td@ -f app1_tables.ddl 5: db2 CONNECT TO sample 6: db2 REVOKE USE OF TABLESPACE $1 FROM user1 7: db2 GRANT USE OF TABLESPACE $2 TO user1 8: db2 -td@ -f app2_tables.ddl 9: db2 CONNECT TO sample 10: db2 REVOKE USE OF TABLESPACE $2 FROM user1 |
Windows：用 % 替换 $ 字符。
让我们仔细研究一下主脚本。
为了演示这个示例，我们将使用 SYSADM 用户创建称为 APP1_TS 和 APP2_TS 的两个表空间：
CREATE TABLESPACE APP1_TS MANAGED BY SYSTEM USING (app1_ts) CREATE TABLESPACE APP2_TS MANAGED BY SYSTEM USING (app2_ts) |
接着，作为 SYSADM，发出：
createtab.sh app1_Ts app2_ts |
在调用 application1 和 application2 的子脚本时，会提示您输入 user1 的密码。在该脚本执行完时，会看到在 APP1_TS 中创建了 TAB1，而在 APP2_TS 中创建了 TAB2。
select substr(t.tabname,1,10), substr(ts.tbspace,1,10) from syscat.tables t, syscat.tablespaces ts where t.tbspaceid=ts.tbspaceid and tabname like TAB% and t.tabschema not like SYS% 1 2 ---------- ---------- TAB1 APP1_TS TAB2 APP2_TS 2 record(s) selected. |
运行该示例后，您可能想恢复 PUBLIC 在 USERSPACE1 上的 USE 特权：
db2 GRANT USE OF TABLESPACE USERSPACE1 TO PUBLIC |
结束语
总之，本文提供了一组互不相关的新技巧和诀窍，作为对最初的有关高级 SQL 过程脚本编制文章的反馈以及客户新的 FAQ 的回答。本文在重点介绍了用于动态复合 SQL 的新 SQL PL 特性之后，还介绍了一些有用的技术，可以用它们来增强脚本的功能。本文还包括以下一些技巧：获得 SQL 语句返回码、使用 UDF 调用外部程序及如何创建与表空间无关的 DDL 脚本。
