另一篇有关高级脚本编制的文章

文档选项 未显示需要 JavaScript 的文档选项

级别： 初级

Paul YipIBM 多伦多实验室

2003 年 7 月 01 日

为了回应读者的问题，作家 Paul Yip 对于脚本编制提出了另外几个技巧和技术，还概述了 DB2 V8 中的新增功能。

重要：在阅读本文之前请先看一下 免责声明。

简介

本文是以前的文章 DB2 v7.2 中的高级 SQL 过程脚本编制的续篇。本文向您提供了新的理解、技巧和诀窍。因为本文是为回应那些阅读了第一篇文章的读者反馈而编写的，而且它作为附加问题的回答，所以结构不是很严谨。事实上，根本没有结构可言 — 确实如此。但是，根据每一节的标题，您应该能快速找到需要的内容。

编写的所有示例都适用于 UNIX®。只要做少量更改（如果您愿意），那么它们也可适用于 Windows®。

回页首

对于 SQL 过程化语言（SQL PL）脚本编制，DB2® V8.1 新增了什么功能

DB2 Universal Database™ V8 中的两个主要增强功能是：

• 支持分区数据库
• 支持 FETCH FIRST n ROWS

支持分区数据库

我编写过有关用于触发器和脚本编制的 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);
    }
}

1. 将上述代码复制到文本编辑器，并将该文件保存为 os_cmdUDF.java 。在对该文件命名时要小心，因为 Java 源文件区分大小写，而且必须与类名相匹配。上述代码很简单 — 该类称为 os_cmdUDF，而方法名是 os_cmd()，它使用一个 String 参数。即使不熟悉 Java，也不必担心。重要的是理解 os_cmd() 接受的参数作为操作系统调用被执行。如果操作系统调用成功，则该函数返回 0，如果失败，则返回 -1。

2. 要将这个外部 Java 程序构建为 DB2 函数，我们必须编译代码，将它复制到 sqllib/function 目录，并发出 CREATE FUNCTION 语句。
   1. 要编译这个 Java 程序，执行：

      javac os_cmdUDF.java

      
      
   2. 产生的结果是一个称为 os_cmdUDF.class 的文件，它是 Java 程序的已编译形式。随后需要将该 .class 文件复制到 sqllib/function 目录：

      cp os_cmdUDF.class sqllib/function/

      
      
   3. 最后一步是使用 CREATE FUNCTION 语句注册 Java UDF。连接到数据库后，如清单 5 所示发出该语句：
      

      清单 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

      
      
3. 现在，通过将命令作为参数传递给 os_cmd() UDF 来进行外部操作系统调用。

   清单 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 编号从任何在线书籍零售商那里进行预订。

通过任意使用这一执行外部操作的新方法，设想以下各种可能性：

• 每当标量函数有效时就可以使用 UDF — 作为 SQL 语句、其它函数、触发器和存储过程的一部分。
• 通过调用操作系统脚本，可以执行几乎任何 OS 操作。

如果您用这种方法确实进行了创造性的工作，那么我愿意听一听。

好几次有人问我，是否有一种方法可以通过使用表空间子句来编写创建表的参数化脚本。在特定表空间（而不是缺省表空间（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 会使用以下规则为表选择表空间：

1. 如果存在的话，则可以在分区组 IBMDEFAULTGROUP 中选择一个具有足够页面大小的表空间，用户对该表空间有 USE 特权。
2. 如果存在的话，则选择一个具有足够页面大小的表空间，用户对该表空间有 USE 特权。
3. 发出错误（SQLSTATE 42727）。

当有多个表空间满足上述规则时，依照以下被授予 USE 特权的优先次序进行选择：

1. 授权标识
2. 授权标识所在的组
3. PUBLIC

如果还是有多个表空间可供选择，那么由数据库管理员决定最终选择。

记住了这些规则，该解决方案就依赖于操作表空间的权限。要使这个解决方案有效，用于创建表的用户标识不能拥有任何特殊特权（如 DBADM）而且不能是任何表空间的所有者。这通常不是问题，因为我经常看到客户使用 SYSADM 用户创建表空间，随后使用没有特权的用户创建表和其它对象。

使用脚本可以使该解决方案更简单

以下是该解决方案的各个具体步骤：

1. 取消对所有用户表空间（包括用户将创建表的表空间 USERSPACE1）的 USE 特权
2. 对想要创建的表所在表空间授予 USE 特权
3. 作为没有特权的用户连接到数据库，并创建表

假设我们有如下需求：

• application1 的表应置于表空间 APP1_TS。
• application2 的表应置于表空间 APP2_TS。
• 应该对表空间名称进行完全参数化，以便于在表空间名称更改时容易部署。

要满足这些需求，我们需要将操作系统脚本和 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：用 % 替换 $ 字符。

让我们仔细研究一下主脚本。

• 第 1 行，使用当前的用户标识（SYSADM）建立到数据库的连接。
• 第 2 行，取消 PUBLIC 在 USERSPACE1 的 USE 特权（缺省情况下每个数据库中都有这一特权）。此时，没有特权的用户不能使用任何表空间。
• 第 3 行，对于由第二个参数确定的表空间，我们授予 USE。根据 DB2 用于选择表空间的规则，在第 4 行上执行 application1 的 DDL 脚本（app1_tables.ddl）时，所有表都被引导至所希望的表空间。
• 在该脚本执行完后，第 6 行取消对 application1 的表空间的特权。
• 第 7 行到第 10 行中对 application2 执行同样的步骤。

为了演示这个示例，我们将使用 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 脚本。

回页首

致谢

作者要感谢 Serge Rielau、Tyronne Mayadunne 和 Ted Wasserman 所做的评审及支持。

回页首

免责声明

本文包含样本代码。IBM 授予您（“被许可方”）使用这个样本代码的非专有的、版权免费的许可证。然而，样本代码是以“按现状”的基础提供的，不附有任何形式的（不论是明示的，还是默示的）保证，包括对适销性、适用于某特定用途或非侵权性的默示保证。IBM 及其许可方不对被许可方使用该软件所导致的任何损失负责。任何情况下，无论损失是如何发生的，也不管责任条款怎样，IBM 或其许可方都不对由使用该软件或不能使用该软件所引起的收入的减少、利润的损失或数据的丢失，或者直接的、间接的、特殊的、由此产生的、附带的损失或惩罚性的损失赔偿负责，即使 IBM 已经被明确告知此类损害的可能性，也是如此。

关于作者

		Paul Yip是 IBM 多伦多实验室的数据库顾问，用于各种分布式平台的 DB2 就是该实验室开发的。他的工作主要是帮助公司将应用程序从其它数据库迁移到 DB2，并经常充当急救员的角色 — 忙于飞来飞去处理紧急情况。他编写了多篇 DB2 文章和白皮书，还是 DB2 SQL Procedural Language for Linux, UNIX and Windows（Prentice Hall 2003，ISBN:0131007726）一书的合著者。您愿意阅读其它文章中有关过程脚本编制的讨论吗？可以通过 ypaul@ca.ibm.com与 Paul 联系。

对本文的评价

太差！ (1) 需提高 (2) 一般；尚可 (3) 好文章 (4) 真棒！(5) 建议？

回页首