文档选项 将此页作为电子邮件发送

级别： 初级

Sotaro Izuha, 咨询 IT 专家, IBM 日本

2003 年 2 月 01 日

本文描述了一个用户定义函数，它可以用于使文件能从 SQL 直接输出。该性能在调试存储过程时特别有用。文中还包括了样本代码。

简介

在 V7 中，DB2® Universal Database™ for Windows®、UNIX® 和 Linux 引入了 SQL 过程语言。SQL 过程语言允许人们像使用传统编程语言那样使用 SQL 来直接编写过程。利用 SQL 过程语言，您可以开发存储过程、函数、触发器和动态复合语句。

SQL 过程语言为程序员进行 SQL 开发提供了一种新的、简便的工具。但是，和任何新工具一样，它也有其缺陷。经常有人问我：是否有办法从 SQL 存储过程写到文件。答案是“没有”。仅仅使用 SQL 过程语句的话，您无法直接写到文件。

为帮助程序员处理这种需求，我开发出了一个称为 PUT_LINE的用户定义的函数（user-defined function，UDF）解决方案，它允许从纯 SQL（比如 SQL 存储过程）进行文件输出。我编写它的初衷是将它作为一种在 Windows 2000 下调试存储过程的工具，但是它也允许您出于其它用途而写到指定文件。您不仅可以在 Windows 2000 上运行它，也可以在 IBM AIX®、HP-UX 和 Sun Solaris 上运行它。

回页首

先决条件和测试环境

PUT_LINE UDF 需要有 DB2 V7.2 或 V8.1 for Windows、UNIX 或 Linux。

PUT_LINE 已在下列平台的单一分区数据库中经过测试：

• Windows 2000 SP2：DB2 V7.2 FP7，V8.1
• IBM AIX 5.1：DB2 V8.1
• HP-UX 11.0：DB2 V7.2
• Solaris 8：DB2 V7.2

回页首

在 Windows 2000 上安装

请按照下面这些步骤为在 Windows 上使用 PUT_LINE 做准备：

1. 下载 put_line.zip ，将 Windows 版本解压缩到适当的目录中，并更改到该目录。
2. 打开 DB2 命令窗口，然后建立数据库连接：

   db2 connect to sample

   
   
3. 使用 crefunc.sql 文件（该文件包含在 PUT_LINE 中）来执行 CREATE FUNCTION：

   db2 -tvf crefunc.sql

   
   
4. 将该 UDF 复制到 db2 function 目录：

   copy put_line.dll "%DB2PATH%\function\."

   
   

现在可以使用 UDF 了。

回页首

在 UNIX 上安装

请按照下面这些步骤为在 UNIX 上使用 PUT_LINE 做准备：

1. 从 put_line.zip 解压缩特定于平台的文件，将结果以二进制格式 ftp 到 UNIX 机器，解压并发出命令： tar -xvf it 。
2. 创建函数并将该模块复制在 sqllib/function 下面。

回页首

使用 UDF

该 UDF 支持以三种格式写出调试信息。

• 要写出消息，首先必须为 VALUES 语句声明一个变量：
  

  DECLARE dummy_variable VARCHAR(1);

  
  

  之后，按如下所示使用 PUT_LINE：

  VALUES (PUT_LINE('debug message')) INTO dummy_variable;

  
  
• 要写出编号，如下使用 PUT_LINE 函数：
  

  VALUES (PUT_LINE(debug number) INTO dummy_variable;

  
  
• 要写出编号和消息，如下使用 PUT_LINE：
  

  VALUES (PUT_LINE(debug number),'debug message')) INTO dummy_variable;

  
  

参数规范

您可以指定编号和／或文本。

debug number：

0 到 32767 之间的数字。可以将它声明为 SMALLINT 或 INTEGER。

debug message：

消息文本或变量 varchar(4000)。

dummy_variable：

声明为 varchar(1) 的任何变量名。

输出文件名

该 UDF 会为您生成输出文件名。该文件名会像下面这样被生成：

%DB2PATH%\%DB2INSTANCE%\sqlproc_debug_file.ProcessId.TXT

这里，ProcessId 是执行 UDF 的进程标识。当您使用样本中所指定的 NOT FENCED 选项来创建函数时，进程标识为 db2sysc 。它是在您启动 DB2 时由操作系统指派的一个变量，因此文件名不是固定的。只要您偶尔重新启动 DB2，就可以防止文件的大小不断增长。例如，如果您每天早上启动 DB2，而晚上再关闭它，那么每天都有一个文件。

但是，如果有多个用户调用该 UDF，那么输出都将被路由到同一个文件。因此，我建议您在个人开发环境（一个实例中只有一个用户）中使用它。在 V7 中，由于 UDF 使用了 FENCED 选项，因此分别为每个连接创建文件。但是，在 V8 中，由于 UDF 实现发生了改变，因此 FENCED 选项不会指派单独的进程标识。

调试的样本用法

我已经在我的样本代码中包括了一个称为 testcase.sqp 的安装验证过程。它将把 ORG 表转储到 SAMPLE 数据库中，同时显示了您所指定的编号或文本将执行哪个步骤。

清单 1. testcase.sqp

CREATE PROCEDURE TESTCASE() RESULT SETS 0 LANGUAGE SQL
L_TESTCASE:
BEGIN NOT ATOMIC 
	DECLARE V_DEPTNO	SMALLINT;
	DECLARE V_DEPTNAME	VARCHAR(20);
	DECLARE V_DIVISION	VARCHAR(20);
-- FOR DEBUG
	DECLARE V_NUM	SMALLINT DEFAULT 0;
	DECLARE V_MSG1	VARCHAR(4000);
	DECLARE	V_MSG2	VARCHAR(1);
-- END
-- SAMPLE1
	SET V_NUM=2000;
	SET V_MSG1='debugging start';
	VALUES(PUT_LINE(V_NUM,V_MSG1)) INTO V_MSG2;
-- SAMPLE2
	VALUES(PUT_LINE(SMALLINT(2),'this is no2')) 
	  INTO V_MSG2;
-- SAMPLE3
	VALUES(PUT_LINE(3)) INTO V_MSG2;
-- SAMPLE4
	VALUES(PUT_LINE('##number4##')) INTO V_MSG2;
-- SAMPLE5
	SET V_NUM = 0;
   FOR V_C1_REC AS C1 CURSOR FOR
	SELECT DEPTNUMB, DEPTNAME, DIVISION
	FROM ORG ORDER BY DEPTNUMB DESC
   DO
      	SET V_DEPTNO   = v_C1_REC.DEPTNUMB;
	SET V_DEPTNAME = v_C1_REC.DEPTNAME;
 	SET V_DIVISION = v_C1_REC.DIVISION;
	SET V_NUM = V_NUM + 1;
	SET V_MSG1 ='DEPTNO=' || CHAR(V_DEPTNO)||','
		||'DEPTNAME='||V_DEPTNAME||','
		||'DIVISION='||V_DIVISION;
	VALUES(PUT_LINE(V_NUM,V_MSG1)) INTO V_MSG2;
   END FOR;
	VALUES(PUT_LINE(32000,'end of the program')) 
	  INTO V_MSG2;

为了将它注册到数据库中并运行它，请按照下面这些步骤来进行：

1. 创建验证 SQL 过程：
   

   db2 -td@ -vf testcase.sqp

   
   
2. 执行验证 SQL 过程：
   

   db2 call testcase()

   
   
3. 查看消息写入其中的文件：
   

   TYPE "%DB2PATH%\%DB2INSTANCE%\SQLPROC_DEBUG_FILE.*"

   
   

清单 2. 消息

Wed Oct 30 15:35:53.275 ( 2000) debugging start
Wed Oct 30 15:35:53.275 (    2) this is no2
Wed Oct 30 15:35:53.345 (    3)
Wed Oct 30 15:35:53.345 ##number4##
Wed Oct 30 15:35:53.395 (    1) DEPTNO=84    ,DEPTNAME=Mountain
Wed Oct 30 15:35:53.395 (    2) DEPTNO=66    ,DEPTNAME=Pacific
Wed Oct 30 15:35:53.405 (    3) DEPTNO=51    ,DEPTNAME=Plains
Wed Oct 30 15:35:53.405 (    4) DEPTNO=42    ,DEPTNAME=Great Lakes
Wed Oct 30 15:35:53.415 (    5) DEPTNO=38    ,DEPTNAME=South Atlantic
Wed Oct 30 15:35:53.415 (    6) DEPTNO=20    ,DEPTNAME=Mid Atlantic

正如您所看到的那样，每一行前面都标有消息写入的日期和时间的前缀，这样您可以找到要寻找的消息。

回页首

修改和重构 UDF

源程序是 put_line.c 。如果您修改该文件并且想对其进行重编译和重构，请对 DB2 V7 Windows 发出下面这个命令：

"%DB2PATH%\SAMPLES\C\BLDMUDF.BAT" PUT_LINE

如果您想重构用于 UNIX 的函数，请使用所提供的样本脚本，并像下面这样添加特定于操作系统的定义： -Dxxxx 。请确保成功执行了脚本中的复制步骤。

• AIX DB2 V8：
  在 sqllib/samples/c/bldrtn 副本中添加 -Daix 。
  

  X="put_line" /usr/vac/bin/xlc_r -I$ HOME/sqllib/include -qmkshrobj $ X.c -o $ X -L$ HOME/sqllib/lib -ldb2 -bE:$ X.exp -Daix rm -f $ HOME/sqllib/function/$ X cp $ X $ HOME/sqllib/function/.

  
  
• HP-UX DB2 V7.2：
  在 sqllib/samples/c 的 bldudf 副本的 cc 参数中添加 -Dhpux 。
  

  cc +DAportable +u1 +z -Aa +e -I$ DB2PATH/include -c $ 1.c -Dhpux

  
  
• Solaris 8 DB2 V7.2：
  在 sqllib/samples/c 的 bldudf 副本的 cc 参数中添加 -Dsolaris 。
  

  cc $ CFLAGS_64 -Kpic -I$ DB2PATH/include -c $ 1.c -Dsolaris

  
  

回页首

写到文件

在上面的示例中，文件名是由 UDF 自动生成的。有时，您可能有这样的应用程序需求：即写到文件而不是写到 DB2 表。在这种情况下，您可能需要为输出指定文件名。该 UDF 允许您在指定的文件名中最多写入 4000 个字节的文本或 varchar 行。您可以用环境变量 PUT_LINE_OUT 来指定文件目录。如果您没有这样做，那么对于 UNIX，它将使用 /tmp ，而对于 Windows，则使用 %DB2PATH%\%DB2INSTANCE% 。

文件输出的示例

我已经包含了两个样本 DB2 命令行处理器（Command Line Processor）文件：用于 Windows 的 testcase.sql 和用于 UNIX 的 testcase2_unix.sql 。作为一个简单的示例，SELECT 语句将数据库中使用的所有模式名写到或附加到文件。

清单 3. 出错样本

-- file name, write mode, column name
select schemaname, put_line('XYZ.DAT','w',schemaname) 
from syscat.schemata;
-- file name, append mode, column name
select schemaname, put_line('XYZ.DAT','a','('||schemaname||')') 
from syscat.schemata;
-- ERROR SAMPLES
-- file name, write mode, column name
select schemaname, put_line('..\XYZ.DAT','w',schemaname) 
  from syscat.schemata;
-- file name, append mode, column name
select schemaname, put_line('..\XYZ.DAT','a','('||schemaname||')') 
from syscat.schemata;
-- file open error due to invalid path name

第一个参数是您想写到的文件的文件名，而第二个参数是文件打开方式，例如，“w”代表写替换（write replace），而“a”代表写附加（write append）。

如果成功，则返回“1”，如果没有成功，则返回 SQL NULL（表示文件打开出错）和“0”（表示出自 UDF 的任何其它错误）。

上面的出错示例表明：UDF 不允许文件不在要创建文件的目录之中。为完成这项任务，UDF 检查是否存在“..”字符串。如果找到了该字符串，则返回“0”，并且不会写任何内容。

样本输出文件

如果目录规范是缺省的，那么在 UNIX 中，上面的前两个 SELECT 语句会写到文件 /tmp/XYZ.DAT 。通过输入 cat /tmp/XYZ ，您可以看到如下所示的内容。

$  cat /tmp/XYZ.DAT
SYSIBM
SYSCAT
SYSFUN
SYSSTAT
NULLID
DB2INST1
ASN
(SYSIBM  )
(SYSCAT  )
(SYSFUN  )
(SYSSTAT )
(NULLID  )
(DB2INST1)
(ASN     )
$ 

回页首

指定目录

您可以覆盖缺省目录：UNIX 的 /tmp 或 Windows 的 %DB2PATH%\%DB2INSTANCE% ，如下所示：

在 Windows 上

单击 Start-> Settings-> Control Panel-> System-> Advanced-> Environment Variables
新建系统变量 PUT_LINE_OUT 并将 the directory name设置成它的值。
db2stop / db2start

在 Unix 上

输入以下命令：
export PUT_LINE_OUT= the directory name
db2set DB2ENVLIST=PUT_LINE_OUT
db2start
对于 V8，还要在 sqllib/db2profile 中添加下列几行：
PUT_LINE_OUT= the directory name
export PUT_LINE_OUT

回页首

结束语

文件 I/O 功能是操作系统所提供的常用功能之一，但超出了标准 SQL 的范围。不过，您可以使用这个用于调试和应用程序输出目的的用户定义函数来增强 SQL 功能。我希望您能试着用 PUT_LINE UDF 来提高您自己的 SQL 生产率。

回页首

下载代码

描述	文件类型	文件大小	下载方法
put_line.zip	zip	79 KB	HTTP

我该选择何种下载 方法？
下载 FAQ

关于作者

		Sotaro IzuhaSotaro Izuha 是 IBM 日本软件部（Software Group）中的一名咨询 IT 专家，他负责 DB2 UDB 在日本的技术销售支持。他是 DB2 认证高级技术专家（DB2 Certified Advanced Technical Expert）（DRDA、Cluster/EEE 和 Replication），同时还是 DB2 认证解决方案专家（DB2 Certified Solutions Expert）（数据库管理、应用程序开发和商业智能）。可以通过 ikalos@jp.ibm.com与他联系。