【转】DB2 的命令行处理器和脚本编写

时间：2022-03-16 10:03

命令行处理器是 DB2 的接口，它最佳地体现了 DB2 的威力，以及 DB2 的简单性和通用性。作者通过例子对其进行了详细的论述。

, DB2 Technology Development, IBM

Blair Adamache 是 IBM 多伦多实验室里的一名有 17 年工作经验的老员工。他拥有修辞学（Rhetoric）的硕士学位，但是承诺只将自己的能力应用于好的服务。Blair 当前的职责是管理 DB2 服务小组，这个小组负责确保客户能够继续享用数据库业内最好的软件支持。Blair 有多年的和用户打交道的经验，涉及营销、服务、开发和管理。他是"DB2 Fundamentals Certification for Dummies (Hungry Minds)"的技术编辑，"The Complete DB2 Reference (McGraw Hill)"的合著者之一，并且为 IBM DB2 开发者园地撰写过许多文章。因为技术问题而找 Blair 的最好方式就是发邮件至 comp.databases.ibm-db2。

2003 年 6 月 01 日

内容

注意：对于那些对 DB2 和操作系统都有意义的符号，比如 Windows 上的大于号（>），操作系统会最先给予解释。SELECT * FROM EMPLOYEE WHERE SALARY >9999 语句会将错误消息输送到一个名为 9999 的文件。要划清这些特殊符号的界限；您应该输入： SELECT * FROM EMPLOYEE WHERE SALARY ">" 9999

虽然您的用户可能永远不会使用 CLP 来访问他们的数据，对于 DBA 或者应用程序的编程人员来说， CLP 在工具箱中是最基本的工具：它就像是在所有场合都适用的燕尾服。对于关键应用来说，往往数据库是关键之处，如果某个地方出错的话，问题的判定会比较困难。在大型主机/微型计算机领域中，问题可能出在应用程序中，也可能出在软件系统中，但是问题总可以界定在一台计算机以内。而在客户机-服务器领域中，问题可能隐藏在客户机系统软件中（操作系统，数据库客户机，或者通信协议），可能隐藏在客户机应用程序代码中，可能隐藏在服务器应用程序代码中（如果您正在使用触发器，用户定义的函数，或者存储过程），还可能隐藏在服务器上的其他地方。3-层应用包括 Web 服务器、浏览器和第三层的硬件。CLP 在这里有什么用呢？答案就是，它可以将应用程序从这一架构中分割出来。如果您发现一个问题，那么可以将故障请求翻译成简单的 SQL，然后从以下机器上的 CLP 运行该 SQL：

服务器
客户机
Web 服务器

根据哪里没问题，哪里有故障，您就可以判定是 DB2 本身有故障（很可能在通过 CLP 提交 SQL的服务器上出故障的形式与客户机应用程序出故障的形式是一样的），还是问题出在通信上（请求在客户机出了故障，而在服务器上没有问题），或者问题是出在 Web 服务器上（问题只出在最远的地方）。

帮助

CLP 也是一个到 DB2 帮助的接口。如果您收到一条消息，而又不知道应该采取怎样的适当步骤，那么在 CLP 中输入：

C:\\SQLLIB>db2 ? sql0100 |more

FETCH、UPDATE 或 DELETE 语句的输出中应该有 SQL0100W No 这样一行；否则查询的结果就是一个空表。

解释：下面的情况中有一种情况为真：

No 这一行符合在 UPDATE 或 DELETE 语句中指定的搜索条件。
SELECT 语句的结果是一个空表。
当游标定位到了结果表的最后一行记录时，会执行一条 FETCH 语句。
在 INSERT 语句中使用的 SELECT 的结果为空。

这里没有检索、更新或者删除数据。

用户响应：不需要动作。处理可以继续。

sqlcode: +100

sqlstate: 02000

您可以看看在 Messages 手册中的相同的帮助，包括为避开问题而建议采用并且可以采用的动作。CLP 还将提供用于 DB2 命令的语法（作为例子，可以试试 DB2 ? backup）。

脚本编写

您可以使用 CLP 运行脚本，任何可以以常规操作处理的方式运行的实用程序（例如每夜运行的 LOAD、RUNSTATS 或者 BACKUP 命令），都可以是一个 CLP 脚本。CLP 的一些选项可以指定输入文件（-f)，一个用于消息的输出文件（-z），是否能够回送关于什么要在屏幕上运行的信息（-v），是否能够设置一个语句终止符（-t），如果设置了这个选项，脚本就可以包含多个 DB2 命令和 SQL 语句。通常我会这样运行脚本：DB2 -tvf filename.ddl -t，后面加上默认的终止符（;），-v 选项允许回送文件的内容到屏幕（这样就可以检查我告诉了 DB2 要做些什么），-f 选项告诉 CLP 使用一个输入文件。通过 DB2 ? options可以得到关于所有选项的帮助：

C:\\SQLLIB>db2 ? options

db2 [option ...] [db2-command | sql-statement |

[? [phrase | message | sqlstate | class-code]]]

option: -a, -c, -e{c|s}, -finfile, -lhistfile, -n, -o, -p, -rreport, -s, -t,

-td;, -v, -w, -x, -zoutputfile.

选项	描述	默认设置
-a	显示 SQLCA	OFF
-c	自动提交	ON
-e	显示 SQLcode/SQLSTATE	OFF
-f	从输入文件读内容	OFF
-l	历史文件中的日志记录命令	OFF
-n	删除换行字符	OFF
-o	显示输出	ON
-p	显示 db2 交互性命令	ON
-r	将输出报告保存到文件	OFF
-s	出现命令错误时停止执行	OFF
-t	设置语句终止符	OFF
-v	回送当前命令	OFF
-w	显示 FETCH/SELECT 警告消息	ON
-x	省略列标题的打印	OFF
-z	将所有输出保存到输出文件	OFF

在 DB2 ControlCenter 中的脚本中心那里提供了更加高级的脚本编写，包括时间安排（scheduling），并且可以使用日志来查看发生过什么事情。在原型（prototyping）模式下，CLP 只是一种启动程序的快速方式。注意上述输出中的 -x 选项：我们添加了这个能力，以便可以在 DB2 v7 的 fixpak 1 中不输出列标题。如果客户要求输出列标题，他们可能是关心列标题，因为他们希望输出好看一些（或者要将输出作为另一个程序的输入）。对于我来说，这意味着人们现在将 CLP 不仅仅用于原型。

编写 DDL 脚本

如果对于每个产品和开发数据库您都遵循这个简单的规则，那么您将对我感激不尽：您可以将所有用于创建或更改数据库对象的 DDL 保存在一个文件中。更令人振奋的是，可以用一个编辑器将 DDL 输入到一个文件中，然后通过提交这个文件给 CLP 来运行这个文件。如果您不采纳上述建议，那么用于在一个数据库中重新创建视图、检查约束、SQL 过程以及触发器的语句的文本也可以这样获取：

select text from syscat.views

select text,tabname from syscat.checks

select text from syscat.procedures

select text from syscat.triggers

您也可以通过使用 -e 选项（或者对于特定的表使用 -t 选项和 -e 选项）运行 db2look 来收集将大部分的这些信息。 DB2 Command Reference 为 db2look 编写了文档。不过，预先将这样的信息保存在一个用于提交 DDL 的文件中，比在一个依赖于另一个数据库的模式的新项目中途匆忙地重新创建它要更显得专业些。

原型

您不必将所有的 DML（SELECT，INSERT，UPDATE，DELETE）保存在一个文件中。毕竟，这些 DML 将会处在您所编写的程序中，并且可以通过使用像监视器和 DB2 Query Patroller 这样的 DB2 工具动态地捕获。不过，在将 SQL 输入到一个应用程序之前，您可以先使用 CLP 对其进行原型试验，看看自己是否对结果满意（并且可以检查语法）。使用 -a 选项来查看 SQL Communications Area (SQLCA)。例如，如果语句没有对任何东西进行更新，或许在语法上这条语句是对的，但是没有任何记录行符合更新条件是由于采用了限制性非常强的 where 子句造成的。在这种情况下，您会得到一个值为 100 的 SQLcode：

C:\\sqllib>db2 -a update employee set salary=5 where salary "<" -5

SQLCA Information

sqlcaid : SQLCA sqlcabc: 136 sqlcode: 100 sqlerrml: 0

sqlerrmc:

sqlerrp : SQLRIEXU

sqlerrd : (1) -31743 (2) 1 (3) 0

(4) 0 (5) 0 (6) 0

sqlwarn : (1) (2) (3) (4) (5) (6)

(7) (8) (9) (10) (11)

sqlstate: 02000

何时使用 DB2 Command Center

DB2 Command Center 是一个图形化的命令行（对于那些偏爱形而上学逻辑的读者来说，这是一种似是而非的说法，而不是简单的自相矛盾）。任何命令或者 SQL 语句，甚至是操作系统命令，都可以通过 Command Center 提交。虽然 CLP 直接调用 DB2 编程接口，但是 Command Center 要经由 DB2 的 Call Level Interface （即 CLI，这基本上是 ODBC 的一个超集）。这意味着当有故障发生时，您可能会得到一条 CLI 错误消息，而不是一个 SQLcode。这还意味着如果您怀疑某个地方有 CLI bug，您可以试着通过 CLP 提交 SQL 到 Command Center。如果在 CLP 部分是成功的，而在 Command Center 部分是失败的，那么问题显然就出在 CLI 那一部分。在 DB2 中，这一点很重要，因为对 DB2 的所有 ODBC、Java、JDBC 和 SQLJ 访问都要经由 CLI。由于这个原因，Command Center 是用 Java 编写的。比起 CLP，Command Center 有以下优点：

可以在操作系统命令提示符的边界外部水平地和垂直地滚动：这包括针对您提交的命令和 SELECT 语句的结果的独立选项卡（或页面）。
对剪切和粘贴的完全剪贴板支持。
可以访问 Script Center （在 ControlCenter 中）和 Visual Explain （以便图形化地显示对 SQL 语句的访问计划——这是查看哪些索引得到使用的最佳方式）。可以高亮显示 SQL，通过点击 Access Plan 可以可视化地解释一条 SQL 语句。
能够更容易地通过用鼠标拓展或者收缩可见区域来显示（或隐藏）多个列。
提供了一个下拉式的菜单，用于检索和编辑先前提交的命令（而不是依赖于操作系统的命令，比如 Windows 和 OS/2 操作系统中的向上箭头和向下箭头）。
在会话中提交的命令和结果的更好的历史记录——这有利于将交互式的体验转变成脚本（或者取得所有 CREATE TABLE 语句并将其存入一个文件中）。
支持所有的 CLP 选项（例如显示 SQLCA）。

因为接口各异，其中有些微妙之处需要知道。接下来的语句在 Command Center 中和在 CLP 中将得到迥然不同的结果：

create user temporary tablespace usetemp

pagesize 4k managed by system using (‘C:\\usetemp‘);

declare global temporary table t1 (col1 int) not logged;

select count(*) from session.t1;

这与 DB2 对用于用户声明的临时表的会话所有权的处理方式有关。对于在 Command Center 中启动的每个 Java 进程，所有权并不是一成不变的。上面的 select count(*) 语句在 CLP 中将获得成功，但是在 Command Center 中却不能找到 session.t1。

CLP 设计

DB2 Command Reference 在第 2 章中解释了 CLP 设计。CLP 设计由两个过程组成：

一个前端进程（或者是在 Windows 和 OS/2 上的线程），用于处理与操作系统命令提示符的通信。
一个后端进程，用于处理与数据库的通信。这确保了在您连接到 DB2 之后，如果用 Control-C 或 Control-Break 中止来自一个大型选择（例如 SELECT * FROM SYSCAT.TABLES）的输出，那么输出会顺利地被中止，而不会断开到 DB2 的连接。

命令窗口与 Windows 上的 CLP 的比较

那么，为什么 DB2 要求您用 Windows 上的 DB2CMD.EXE（或者用 DB2 Command Window，或者用 DB2 Command Line Processor，又或者用开始菜单）启动一个 CLP 呢？在 UNIX 和 OS/2 上，前端进程和后端进程之间的链接非常简单：如果父进程死亡，则其所有子进程都将被操作系统终止。在 Windows 上，父线程在死亡时并不会终止其子线程。这里我们不去冒险产生 Windows 上的大量 phantom 线程，而是决定用一个 cookie 来为 Windows 上的 CLP 链接前端线程和后端线程。这要求 CLP 要通过 DB2CMD.EXE 来启动。这样做确保了如果杀死了父线程，那么子线程也不会保留下来，从而避免了资源的浪费。不必担心叫做"DB2 Command Processor"的特定选项。它会创建像这样的一个特殊的提示符：

db2 =>

这样，您就不必在所有发送到 DB2 的指令之前都加上 DB2。另一方面，现在您必须在操作系统命令之前加上一个惊叹号（!），就像在 Command Center 中一样。

何时不使用命令行处理器

对于用于检查语法和您希望用户看到的结果的原型 SQL 来说，CLP 很有用。但是，由于 CLP 是如此的多用，能够提供到每条 DB2 SQL 语句、命令以及编程接口的访问途径，所以通常来讲，比起动态 SQL（例如那些通过 ODBC 提供的动态 SQL）来 CLP 会产生更大的开销。为了对性能进行原型试验，可以使用 db2batch，这在 DB2 Command Reference中有文档说明。

一个需要避免的典型例子（或者说不值得拷贝的例子）

下面是一个简单的例子，其中充斥着许多坏的编程习惯，这个例子展示了为什么 CLP 对于应用程序逻辑来说不是很好。对于简单的脚本编写，下面的例子可以更加强大，甚至可以处理一些出错的情况，如果它是用 PERL 编写的话。不过，对于我来说，编写和做一些有用的工作是十分有趣的。先决条件：

已经创建了样本数据库。
从 DIR 命令得到的输出与我测试时所在的系统（带有 ServicePak 5 的 NT 4.0，以及 Windows 2000）相匹配。
您正处在 Windows 上的一个 DB2 Command Window 中。

这个例子做些什么

是不是曾经碰到过用完了磁盘空间的情况。下面的例子捕获在 C:\\ 上的一个递归的目录清单的输出，为每个大小为 1 MB 或大于 1 MB 的文件导入一行，并列出 C: 盘中 10 个最大的文件。不要删除 PAGEFILE.SYS！只能删除那些明确知道是垃圾的文件（例如 C:\\TEMP 中大型的 .PDF 文件）。 这个例子中有什么好的地方？

它可以帮助我清理我的硬盘。
它可以在两种系统上工作（我成功地完成了系统测试并发布了它！）

这个例子有什么不好的地方？

在开发者园地即将发布的一个 500 页的自述文件（readme）中，您将看到对这个例子的缺点的文档说明。

仔细研究这个例子： (我是这样运行的：db2 -tvf dirpub)

!dir c:\\*.* /s>dir.out;

connect to sample;

create table dirlist (size_in_mb int not null, name char(21) not null

CONSTRAINT CHECKBYTES CHECK (name not like ‘ bytes%‘));

import from c:dir.out of asc method l (28 30, 40 60)

commitcount 1000 replace into dirlist;

select * from dirlist order by size_in_mb desc

fetch first 10 rows only;

第 1 行将 C: 上所有文件的列表发送到 dir.out。

第 2 行连接到样本数据库。

第 3 行创建一个带有两列的表：一列是以 MB 为单位的每个文件的大小，一列是每个文件的文件名中的前 21 个字符。既然在 Windows 告诉您关于目录的信息时会显示短语"bytes"，那么我们使用一个检查约束来消除这些不需要的行。

第 4 行将文件 dir.out 中的行导入表 dirlist 中。我们假设以 MB 为单位的文件大小是从第 28 列到第 30 列的一个整数，并将从第 40 列到第 60 列的内容拿来作为文件名。我们每 1000 行提交一次，并且替换 dirlist 的内容，以防多次运行这种提交。

第 5 行获得 10 个最大的文件的文件名。

所以，用 5 行代码，我们就可以清理一个硬盘分区（或者至少查看出哪些文件占用了所有的空间）。处理出错情况，允许搜索其他驱动器，以及删除输出中的系统文件，这些功能能够轻松地将这个小小的程序扩展成数百行，我不想编写这么多的代码。毕竟，CLP 的精神就是“快而脏（quick and dirty）”，即花费的精力中有 20% 满足了 80% 的需求。如果您小心地应用这个 80/20 法则，令用户感到满意，您就总能接到更多的项目。