分析 SQL 过程流
Serge Rielau
高级软件开发人员, IBM Canada Ltd.
2004 年 11 月
本文描述了允许对 SQL 过程进行透明跟踪以及对 DB2® Universal Database™ (UDB) V8.2 for LUW 中的非 SQL 过程进行显式跟踪的 API。文中还给出了该 API 的一个 SQL 接口示例。
动机
自从引入 DB2 UDB V7 起，SQL 过程已经快速发展成为应用程序开发的强有力的工具。此外，SQL 过程语言（SQL PL）是用于从 Oracle PL/SQL、Microsoft® SQL Server/Sybase T-SQL 或 IBM® Informix® SPL 迁移或移植到 DB2 UDB 的应用程序的自然目标语言。
虽然 SQL/PSM 标准与 SQL PL 一起提供了先进的状况处理（condition handling）功能，但是这种语言特性没有提供一种方式来检测是什么导致某种特定状况的出现。而且，只有将状况处理程序编写在完全正确的位置，才能使之提供帮助。另外，如果应用程序是从一个不支持使用自动化工具（例如 migration tool kit（MTK））的状况处理程序的 DBMS 中移植过来的，那么在错误覆盖方面自然会留下很大的空档。
那么，作为一名开发人员，您该如何处理从嵌套的记帐（billing）过程那里收到的 SQL0445W Value "100000" has been truncated. SQLSTATE=01004 警告呢？您可以通读所有的过程代码，追踪嵌套调用，并希望就此发现问题。或者可以启动语句事件监视器，搜索到 SQLCA，并从那里反向跟踪到 SQL 过程源代码。为了发现是什么导致该过程遇到警告，您需要及时倒回来重复这一步骤。或者，您可以读完本文的后续部分，下载文中给出的文件，从而直接跟踪记帐过程。
考虑到一些读者没有耐心，我们将从相对有趣的部分开始。也就是从应用程序开发人员用来跟踪 API 的 SQL 过程开始。然后，更令您感到好奇的是，我将解释 DB2 底层的、低级的 API，您可以使用这种 API 在我已有的努力上做更多的工作。
安装
要安装跟踪 API，您有两种选择。如果您使用本文预先准备好的可执行平台，那么可以应用第一种选择。第二种选择要求您使用 C 编译器自己编译 API：
- 在 Windows® 32 位系统或 Linux Intel 32 位系统上:
- 下载各自的 zip 文件并对其解压。
- 将 sqpltrc 可执行文件复制到 sqllib\function\unfenced 中。
- 连接到您要跟踪的数据库，例如
db2 connect to sample 。
- 将 sqlpltrc.bnd 文件绑定到该数据库：
db2 bind sqlpltrc.bnd。
- 运行 DDL 脚本注册该过程：
db2 –tvf sqlpltrc.db2。
您可以对任意多个数据库重复步骤 4 和步骤 5。
- 对于 DB2 UDB 支持的其他平台，
- 下载 sqlpltrc_source.zip 文件并对其进行解压缩。
- 将
sqlpltrc.sqc 和 sqlpltrc.exp （在 UNIX/Linux 上）或 sqlpltrc.def （在 Windows 上）复制到 sqllib/samples/c 。
- 将
bldrtn 复制到 bldtrace ，并将 db2psmd 库添加到 bldtrace 的 link step 中，并将最后的 copy 修改成 sqllib/function/unfenced 。注意，在 Windows 上，这些文件需要以 .bat 结束，而在 UNIX 或 Linux 上，这些文件应该被标记为可执行的脚本。
在 Windows 上，这些新行看上去应该是这样的：
:link_step
rem Link the program.
link -debug -out:%1.dll -dll %1.obj db2api.lib
db2psmd.lib -def:%1.def
rem Copy the routine DLL to the 'function unfenced' directory
copy %1.dll "%DB2PATH%\function unfenced"
@echo on
|
在 AIX® 上， bldtrace 的修改部分如下：
# Link the program using the export file $1.exp,
xlc_r $EXTRA_CFLAG -qmkshrobj -o $1 $1.o -ldb2
-ldb2psmd -L$DB2PATH/$LIB -bE:$1.exp
# Copy the shared library to the sqllib/function
/unfenced subdirectory.
# Note: the user must have write permission to this directory.
rm -f $DB2PATH/function/
unfenced/$1
cp $1 $DB2PATH/function
/unfenced
|
- 针对要求的数据库来准备、绑定和部署库，例如
bldtrace sqlpltrc sample 。
- 运行 DDL 脚本注册该过程：
db2 –tvf sqlpltrc.db2 。
一旦在开发机器上完成了编译，便可以将编译好的 API 部署到其他兼容平台上，其方法与前面描述的针对 Linux 和 Windows 的方法一样。所以您只需使用一次 C 编译器来产生可执行文件。
SQL 过程跟踪 API
该 API 可以组成三组：
- 跟踪管理
- 跟踪报告
- 跟踪条目
让我们按先后顺序一一考察这些接口。
- 跟踪管理
在跟踪管理的时候，标出实际进行跟踪以及事后清除跟踪所需的工具。
TRACE. SQLPROC_TRACE_ON (IN USERTEMPSPACE VARCHAR(128))
该过程将打开用于当前会话的跟踪。
| 参数 |
描述 |
USERTEMPSPACE |
DB2 要用来存储跟踪信息的用户临时表空间。为了在包含 DPF 的 DB2 中进行跟踪时获得最佳性能，所选的表空间应该只在会话的协调器所在的数据库分区上。如果跟踪被存储在一个分区表空间中，这可能使跟踪报告混乱，从而导致时间戳上出现偏差，而上述做法可以避免这种风险。如果该参数的值为 NULL，那么应该按照 SQL Reference 手册中对全局临时表的描述来选择表空间 |
TRACE. SQLPROC_TRACE_OFF ()
该过程关闭用于当前会话的跟踪。所有到目前为止收集到的跟踪数据都将丢失。所消耗的用户临时表空间中的空间也将得到释放。
TRACE. SQLPROC_TRACE_CLEAR ()
如果跟踪是打开的，该过程将删除当目前为止收集到的跟踪数据。但是该过程不会将跟踪切换到关闭状态。所消耗的用户临时表空间中的空间将得到释放。
TRACE. SQLPROC_TRACE_DUMP ()
该过程将所有收集到的跟踪数据 dump 到一个结果集中，并将该结果集返回给调用者。该过程被包括在这里，作为更低层 API 实现的抽象层。如果您真的需要 dump 数据，那么就可以更方便地按照 API 编写 SQL，本文后面部分将对此进行说明。
| 列名 |
列类型 |
是否可以为空 |
描述 |
EVENTTIME |
TIMESTAMP |
否 |
这是编写跟踪事件时的时间戳 |
ROUTINEID |
INTEGER |
否 |
ROUTINEID 是 SYSCAT.ROUTINES 中用于记录事件的过程的 ID。可以使用这个 ID 寻找 SYSCAT.ROUTINES 中相应的 ROUTINENAME 或 SPECIFICNAME |
LINE |
INTEGER |
否 |
对于消息跟踪条目，该列包含 SQL 过程中错误或警告的大致行号。这个行号之所以是大致的，是因为 DB2 的 PSM Virtual Machine (PVM) 会对 SQL PL 进行重写。例如，它可能组合一系列的 SET 语句 |
EVENTTYPE |
INTEGER |
否 |
事件类型是 ENTRY 、 EXIT 、 MESSAGE 和 DATA 的编码。该编码可以从表 TRACE.SQLPROC_TRACE_EVENTS 中翻译出来 |
SQLCODE |
INTEGER |
否 |
该列包含在编写跟踪条目时的 SQLCODE |
SQLSTATE |
CHAR(5) |
否 |
该列包含在编写跟踪条目时的 SQLSTATE |
DATA |
VARCHAR(2000) |
否 |
对于跟踪数据条目，该列包含的是用户提供的文本。对于非零 SQLCODE，DATA 列包含的是来自 SQLCA 的消息标志 |
- 跟踪报告
目前只有两个过程属于这一类，但这两个过程却是过程跟踪 API 的中心部分。
- 跟踪条目
除了对 SQL 过程的透明跟踪外，该 API 还提供了对用其他语言（例如 C）编写的过程的显式跟踪。注意，与对 SQL 过程的透明跟踪不同的是，这些过程需要显式地调用。因此，即使跟踪被禁用，也仍然会有开销。这些过程主要是用于调试的。在高性能的编码中，应该将它们除去。
TRACE. SQLPROC_TRACE_ENTRY (IN SCHEMANAME VARCHAR(128), IN SPECIFICNAME VARCHAR(128), OUT ROUTINEID INT)
该过程跟踪对应于过程的条目。应该在过程逻辑的开始部分、紧接变量声明之后的地方调用该过程。
| 参数 |
描述 |
SCHEMANAME |
代码所描述的例程的模式名 |
SPECIFICNAME |
在 CREATE PROCEDURE 或 CREATE FUNCTION 语句中被指定为 SPECIFIC NAME 的名称。注意，指定一个特定的名称总是有益的。特定名称可以使得对例程的引用更加容易，DB2 的错误报告功能可以显式地使用这些特定名称 |
ROUTINEID |
该值是针对例程的 DB2 的内部 ID。它将作为随后描述的过程的输入参数 |
TRACE. SQLPROC_TRACE_DATA (IN ROUTINEID INT, IN DATA VARCHAR(2000))
该过程的目的是跟踪任意数据。这可以是一个错误状况，一条简单的“I was here" 消息，或者某些主机变量的内容。
| 参数 |
描述 |
ROUTINEID |
从 TRACE.SQLPROC_TRACE_ENTRY() 收到的例程 id |
DATA |
一个任意的字符串，它将作为格式化的跟踪打印出来 |
TRACE. SQLPROC_TRACE_EXIT (IN ROUTINEID INT)
该过程是 TRACE.SQLPROC_TRACE_ENTRY() 的对等物。应该在例程过程逻辑的最后来调用它，用以通知跟踪者例程结束。在指定 TRACE.SQLPROC_TRACE_ENTRY() 时，如果缺少了 TRACE.SQLPROC_TRACE_EXIT() ，将导致从跟踪报告过程返回错误的结果。
| 参数 |
描述 |
ROUTINEID |
从 TRACE.SQLPROC_TRACE_ENTRY() 收到的例程 id |
一个应用实例
我们已经解释了用于跟踪的外部 API，现在我们将考察一个简单的 “3a+1” 算法形式的例子。
a 1 : 用户输入
a n+1 ::= if a n is even then a n /2 else 3a n +1
我们对到 a变为 1为止所经历的除法、乘法的次数以及总共经历的步数感兴趣。
DROP PROCEDURE max_three_a_plus_one
DROP PROCEDURE compute_three_a_plus_one
DROP PROCEDURE three_a_plus_one
DROP PROCEDURE a_div_2
1
CREATE PROCEDUREa_div_2(
INOUT a
SMALLINT)
2
SET a = a / 2
1
CREATE PROCEDUREthree_a_plus_one(
INOUT a
INTEGER)
2
SET a = 3 * a + 1
1
CREATE PROCEDURE compute_three_a_plus_one
2 (
IN a
INTEGER,
3
OUT div
SMALLINT,
4
OUT mult
SMALLINT,
5
OUT steps
SMALLINT)
6
BEGIN
7
VALUES(0, 0, 0)
INTO div, mult, steps;
8
WHILE a <> 1
DO
9
IF (a / 2) * 2 = a
THEN
10
CALL a_div_2(a);
11
SET div = div + 1;
12
ELSE
13
CALL three_a_plus_one(a);
14
SET mult = mult + 1;
15
END IF;
16
SET steps = steps + 1;
17
END WHILE;
18
END
1
CREATE PROCEDUREmax_ three_a_plus_one
2 (
IN start
INTEGER,
3
IN stop
INTEGER,
4
OUT maxdiv
SMALLINT,
5
OUT maxmult
SMALLINT,
6
OUT maxsteps
SMALLINT)
7
BEGIN
8
DECLARE a
INTEGER;
9
DECLARE div
SMALLINT;
10
DECLARE mult
SMALLINT;
11
DECLARE steps
SMALLINT;
12
VALUES (0, 0, 0, start)
13
INTO maxdiv, maxmult, maxsteps, a;
14
WHILE a <= stop
DO
15
CALL compute_three_a_plus_one
16 (a, div, mult, steps);
17
SELECT MAX(d), MAX(m), MAX(s), a + 1
18
INTO maxdiv, maxmult, maxsteps, a
19
FROM TABLE(
VALUES (maxdiv, maxmult, maxsteps),
20 (div, mult, steps))
21
AS T(d, m, s);
22
END WHILE;
23
END
|
您可以用 db2 –td@ -vf trace_sample.db2 来定义这三个过程。
首先，我们测试过程的运行情况：
Database Connection Information
Database server = DB2/NT 8.2.0
SQL authorization ID = SRIELAU
Local database alias = DBMTECH
C:\docs\ibm\Tracing>db2 call max_three_a_plus_one(1, 400, ?, ?, ?)
Value of output parameters
--------------------------
Parameter Name : MAXDIV
Parameter Value : 91
Parameter Name : MAXMULT
Parameter Value : 52
Parameter Name : MAXSTEPS
Parameter Value : 143
Return Status = 0
C:\docs\ibm\Tracing>
|
到目前为止，一切正常。现在我们将“随机地”采用另一个范围的值：
C:\docs\ibm\Tracing>db2 call max_three_a_plus_one(430, 450, ?, ?, ?)
SQL0413N Overflow occurred during numeric data type conversion.
SQLSTATE=22003
C:\docs\ibm\Tracing>
|
您可以看到，结果并不如意。DB2 UDB 碰到了一个数学溢出错误。但是它既不能告诉我们错误发生在哪个过程当中，也不能告诉我们牵涉到哪些变量。注意，SQL PL 中适当的异常处理要求每个过程有一个错误处理程序，这个错误处理程序应该可以很容易地告诉我们出错的过程。当然，对于这么小的一组过程，要猜测问题出在哪里是比较容易的，但是举这个例子的目的是为了展示跟踪。所以，现在，我们将这样做：
C:\docs\ibm\Tracing>db2 set path = current path, trace
DB20000I The SQL command completed successfully.
C:\docs\ibm\Tracing>db2 call sqlproc_trace_on(NULL)
Return Status = 0
C:\docs\ibm\Tracing>db2 call max_three_a_plus_one(430, 450, ?, ?, ?)
SQL0413N Overflow occurred during numeric data type conversion.
SQLSTATE=22003
C:\docs\ibm\Tracing>db2 call sqlproc_trace_format() > trace.fmt
C:\docs\ibm\Tracing>
|
为了方便起见，我们首先将“TRACE" 模式添加到 PATH 中。这样我们就不必在每次调用例程时都拼写出模式名。然后，我们使用友好的 DBA 提供的默认用户临时表空间打开跟踪。在调用有错误的例程之后，我们收到一个格式化的跟踪输出。由于结果集包含一个很长的 VARCHAR 列，可取的做法是将输出重定向到一个文件。在这里，我们称之为 trace.fmt ：
2527 | |->SRIELAU.THREE_A_PLUS_ONE (1)
2528 | |<'SRIELAU.THREE_A_PLUS_ONE (1)
2529 | |->SRIELAU.A_DIV_2 (1)
2530 | |<'SRIELAU.A_DIV_2 (1)
2531 | |->SRIELAU.THREE_A_PLUS_ONE (1)
2532 | |<'SRIELAU.THREE_A_PLUS_ONE (1)
2533 | |->SRIELAU.A_DIV_2 (1)
2534 | |<'SRIELAU.A_DIV_2 (1)
2535 | |->SRIELAU.THREE_A_PLUS_ONE (1)
2536 | |<'SRIELAU.THREE_A_PLUS_ONE (1)
2537 | | ERR Line 10 SQL0413N Overflow occurred during numeric
data type conversion. SQLSTATE=22003
2538 |<'SRIELAU.COMPUTE_THREE_A_PLUS_ONE (4) SQL0413N Overflow occurred during
numeric data type conversion. SQLSTATE=22003
2539 | ERR Line 14 SQL0413N Overflow occurred during numeric
data type conversion. SQLSTATE=22003
2540 <'SRIELAU.MAX_THREE_A_PLUS_ONE (5) SQL0413N Overflow occurred during
numeric data type conversion. SQLSTATE=22003
2540 record(s) selected.
Return Status = 0
|
您很容易看到，错误的根源好像出在 COMPUTE_THREE_A_PLUS_ONE() 中的第 10 行。(4) 表明该过程带有 4 个参数，以防模式 “SRIELAU” 中存在有相同名称的其他过程。
由于跟踪本身没有达到 “A_DIV_2”自身，所以错误肯定是出在 “A” 的绑定期间，通过快速查找可以发现，A_DIV_2 带来一个 SMALLINT 类型的参数，而 “A” 被定义为 INTEGER。所以让我们来修改一下：
C:\docs\ibm\Tracing>db2 drop procedure a_div_2
DB20000I The SQL command completed successfully.
C:\docs\ibm\Tracing>db2 create procedure a_div_2(inout a integer) set a = a / 2
DB20000I The SQL command completed successfully.
C:\docs\ibm\Tracing>
|
现在我们可以清除跟踪，并再次运行代码：
C:\docs\ibm\Tracing>db2 call sqlproc_trace_clear()
Return Status = 0
C:\docs\ibm\Tracing>db2 call max_three_a_plus_one(430, 450, ?, ?, ?)
Value of output parameters
--------------------------
Parameter Name : MAXDIV
Parameter Value : 74
Parameter Name : MAXMULT
Parameter Value : 41
Parameter Name : MAXSTEPS
Parameter Value : 115
Return Status = 0
C:\docs\ibm\Tracing>db2 call sqlproc_trace_report() > trace.rpt
C:\docs\ibm\Tracing>
|
这一次运行正常，我们得到一个报告：
Result set 1
--------------
NUMCALLS ELAPSED NAME
-------- ------------ --------------------------------------
1 0.3284 SRIELAU.MAX_THREE_A_PLUS_ONE (5)
21 0.3257 SRIELAU.COMPUTE_THREE_A_PLUS_ONE (4)
983 0.0549 SRIELAU.A_DIV_2 (1)
501 0.0292 SRIELAU.THREE_A_PLUS_ONE (1)
4 record(s) selected.
Return Status = 0
|
一眼就能看出报告告诉了我们一些重要的事情。首先，我们知道现在实际执行了哪些过程。现在我们至少大致地知道这些过程执行的频率，它们的执行过程中花费了多少时间。关于 SQL 过程内部情况的更详细的分析，我建议您看一下 SQL PL Profiler，这也可以从 DeveloperWorks/DB2 下载。
低级 API
本节描述跟踪功能使用的低级 API。DB2 UDB V8.2 已经有了三个小的、但是非常关键的扩展，这里将用到它们：
- 一个会话级开关，用于开启或关闭跟踪。
db2psmd.h 中对此开关进行了原型制作，可以通过 db2psmd 库获得它。
#define
PSMD_TRACE_MODE_OFF 0
#define
PSMD_TRACE_MODE_BASIC 1
int DB2API
pda_SetTraceMode(
int TraceMode);
int DB2API
pda_GetTraceMode();
|
-
对全局临时表的一个通用扩展，允许没有日志记录的临时表完全脱离事务控制。我们需要这个扩展来允许跟踪 ROLLBACK 语句，而不会丢失至此已经跟踪到的信息。
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP
(c1
INTEGER)
NOT LOGGED ON ROLLBACK PRESERVE ROWS
|
-
Procedural Virtual Machine (PVM) 对跟踪模式的感知。
如果跟踪模式没有被设为 OFF （即默认情况），那么将使用下面的名称和格式，把每个入口、出口和非零 SQL 代码都记录到一个全局临时表中：
DECLARE GLOBAL TEMPORARY TABLE SESSION.SQLPROC_TRACE
(EVENTTIME
TIMESTAMP NOT NULL,
ROUTINEID
INTEGER NOT NULL,
LINE
INTEGER NOT NULL,
EVENTTYPE
INTEGER NOT NULL,
SQLCODE
INTEGER NOT NULL,
SQLSTATE
CHAR(5) NOT NULL,
DATA
VARCHAR(2000) NOT NULL)
ON COMMIT PRESERVE ROWS
NOT LOGGED ON ROLLBACK PRESERVE ROWS
|
EVENTTYPE 映射如下：
| EVENTTYPE |
描述 |
| 1 |
过程条目 |
| 2 |
过程出口 |
| 3 |
消息(SQLCODE <> 0) |
| 4 |
用户提供的数据条目 |
这些接口在 DB2 UDB V8.2 for LUW 中受 IBM 的支持。但是，该 API 可能会发生演化，或者在将来的版本中被完全取代，所以可能不具有向上兼容性。如果您选择使用该 API，并希望在发生更改时得到通知，请与本文的作者联系。
结束语和展望
在 DB2 UDB V8.2 for LUW 中提供了一个简单而强大的 API，用以允许跟踪 SQL 过程。本文给出了一个利用该 API 的例子，其中使用了一个过程接口。希望这个API的各个方面都能够得以改进，以下我想要列举一些方面，暂时不考虑时间期限的因素：
- 它不仅对于跟踪存储过程的入口和出口很有用，而且，在存储过程参数或局部变量的值发生变化时，该 API 对于跟踪这些值也很有用。然而，其中必须克服的一个挑战是安全性。当然，并不是每个可以执行存储过程的人都被允许跟踪过程内部可能是机密的数据。但是，在开发环境中，您可能并不希望让每个人都成为 DBA。
- 临时表的使用限制了对属于 MODIFIES SQL DATA 类别的过程的跟踪。而这一点刚好是默认的，所以大多数情况下这没有问题。但是，由于这方面的原因，仍然有一些过程是不能跟踪的。也许使用另一种接口更为合适。
- 这里的高级 API 仅支持简单的报告。其实可以进行更高级的分析。例如，可以很容易地从跟踪得到 stack-trace back。通过聚集这种反向跟踪，可以产生一个量化的调用链。哪个过程被谁调用，调用的频率如何？对于有好奇心的读者，我希望您以本文提供的内容作为起点，作进一步的延伸。
|
