完全使用 SQL 语句来收集统计信息并将数据装载到表中
Knut Stolze
DB2 Websphere Information Integration Development, IBM 德国
2005 年 5 月 19 日
实现允许通过 SQL 使用 IBM® DB2® Universal Database™ (DB2 UDB) 管理 API 的函数和存储过程。本文演示了两个存储过程,一个过程调用
db2Runstats
来收集关于一个给定表的统计信息,另一个过程封装了
db2Load
函数,它允许通过使用 DB2 的 LOAD 功能从数据库服务器上的一个文件中装载数据。不管应用程序和数据库服务器之间的通信协议使用哪种机制,包括 CLI、JDBC、SQLCJ 和嵌入式 SQL,这两个过程都可以在任何使用 SQL 访问 DB2 数据库的应用程序中调用。
简介
DB2 是否通过某种 Java 或 SQL 功能提供对管理 API 的访问?最近版本的 DB2 UDB for Linux™、UNIX® 和 Windows® 添加了一些内置函数,使得快照功能可以通过用户定义函数(UDF)来使用。然而,并不是所有的 API 都可以通过那种方式访问。有些 API 只能从 C 代码中调用,必要时通过使用 JNI 或其他耦合设施(coupling facilities)调用。在本文中,您将看到一种容易而通用的使所有应用程序可以使用 API 的方法。
这种方法的机制是基于用 C 实现的存储过程。每个 API 调用被封装在一个专用的过程中。这种过程带有一些必要的参数 —— 例如需要处理的表的名称或者需要从中装载数据的文件的名称 —— 作为输入参数,准备所需的数据结构,并调用 API。
后续几个小节组织如下:
RUNSTATS 过程
第一个存储过程只是简单地封装 db2Runstats
API,以提供调用与 DB2 命令 RUNSTATS
相同的功能的一种方式(要了解关于 db2Runstats
API 和 RUNSTATS
命令的更多信息,请参阅 参考资料)。该过程以一个表的全限定名称作为输入参数,并设置必要的数据结构,以便将这些数据结构传递给 API 函数。记住提供包含表创建时所在模式的名称在内的全限定表名,这个需求是从 db2Runstats
API 继承过来的,所以也适用于该过程的参数。
统计信息是从所有列(带有分布信息)和所有索引(带有扩展的统计信息)上收集的。 在执行操作时,读访问将被允许。在存储过程中实现的功能和以下命令是同等的:
RUNSTATS ON TABLE <table-name>
ON ALL COLUMNS WITH DISTRIBUTION AND
DETAILED INDEXES ALL
ALLOW READ ACCESS
|
如果给出的表名为 NULL,则该过程不执行任何动作。如果 db2Runstats
API 遇到任何错误(例如,指定的表不存在,或者调用该过程的用户不具备所需的权限),则该错误将通过 SQLSTATE "38RS1" 返回给调用者。在下面的 清单 1 中可以看到这个过程的细节。注意,对做实际工作的 API 的调用在清单中以斜体 显示。您可以看到,这个调用本身实际上只占实现很小的一部分。在这个调用之前,很大一部分是准备数据结构。
清单 1. 调用 db2Runstats 的存储过程的代码
#include <sql.h>
#include <sqludf.h>
#include <db2ApiDf.h>
int SQL_API_FN runstats_proc(
SQLUDF_VARCHAR *tableName,
SQLUDF_NULLIND *tableName_ind,
SQLUDF_TRAIL_ARGS)
{
SQL_API_RC rc = SQL_RC_OK;
db2RunstatsData data;
struct sqlca sqlca;
if (SQLUDF_NULL(tableName_ind)) {
goto cleanup;
}
/* initialize data structures */
data.iSamplingOption = 0;
data.piTablename = (unsigned char *)tableName;
data.piColumnList = NULL;
data.piColumnDistributionList = NULL;
data.piColumnGroupList = NULL;
data.piIndexList = NULL;
data.iRunstatsFlags = DB2RUNSTATS_ALL_COLUMNS | DB2RUNSTATS_DISTRIBUTION |
DB2RUNSTATS_ALL_INDEXES | DB2RUNSTATS_EXT_INDEX |
DB2RUNSTATS_ALLOW_READ;
data.iNumColumns = 0;
data.iNumColdist = 0;
data.iNumColGroups = 0;
data.iNumIndexes = 0;
data.iParallelismOption = 0;
data.iTableDefaultFreqValues = -1; /* use default */
data.iTableDefaultQuantiles = -1; /* use default */
data.iUtilImpactPriority = 0;
data.iSamplingRepeatable = 0; /* unused */
memset(&sqlca, 0x00, sizeof sqlca);
rc = db2Runstats(db2Version820, &data, &sqlca);
if (rc != SQL_RC_OK || SQLCODE != SQL_RC_OK) {
memcpy(SQLUDF_STATE, "38RS1", SQLUDF_SQLSTATE_LEN);
sprintf(SQLUDF_MSGTX, "Error %d returned by db2Runstats.",
(int)(rc == SQL_RC_OK ? SQLCODE : rc));
goto cleanup;
}
cleanup:
return SQLZ_DISCONNECT_PROC;
}
|
为了使用这个存储过程,必须用下面的 SQL 语句将该过程注册到数据库中。
注意: 在全文中我们使用字符 '@
' 作为语句终止符,这个字符不是语句本身的一部分。
清单 2. SQL 语句
CREATE PROCEDURE runstats(IN tableName VARCHAR(256))
SPECIFIC runstats
DYNAMIC RESULT SETS 0
MODIFIES SQL DATA
NOT DETERMINISTIC
CALLED ON NULL INPUT
LANGUAGE C
EXTERNAL NAME 'db2_api!runstats_proc'
FENCED THREADSAFE
NO EXTERNAL ACTION
PARAMETER STYLE SQL@
|
增加对内部操作的控制
当然,该存储过程的调用者还可以进一步扩展它的执行。例如,您可以决定哪些索引或列将受到处理过程的影响。为此,应该更改 CREATE PROCEDURE
语句和 C 代码,以允许使用更多的参数。取决于那些参数的值,您可以按自己喜欢的方式设置用于数据结构的选项(例如允许在操作期间进行写访问)。
测试该过程
编译好源代码后,联编共享库,并将该过程注册到数据库中,然后就可以验证这个过程。清单 3 演示了一些例子。在这个清单中,首先创建一个表,接着插入一些数据,然后调用该过程。在 SYSSTAT
模式里的编目表中可以验证该调用的结果。下面的场景展示了在 DB2 编目本身的一个表 SYSIBM.SYSTABLES
上收集的统计信息。
清单 3. 测试 RUNSTATS 过程
$ db2 -td@
db2 => CREATE TABLE stolze.t ( a INT NOT NULL, b VARCHAR(100),
db2 (cont.) => PRIMARY KEY(a) )@
db2 => INSERT INTO stolze.t VALUES (1, 'text 1'),
db2 (cont.) => (3, 'another text'), (5, 'sample text')@
db2 => SELECT colname, colcard, avgcollen FROM sysstat.columns
db2 (cont.) => WHERE ( tabschema, tabname ) = ( 'STOLZE', 'T' )@
COLNAME COLCARD AVGCOLLEN
---------- -------------------- -----------
A -1 -1
B -1 -1
2 record(s) selected.
db2 => CALL runstats('stolze.t')@
Return Status = 0
db2 => SELECT colname, colcard, avgcollen FROM sysstat.columns
db2 (cont.) => WHERE ( tabschema, tabname ) = ( 'STOLZE', 'T' )@
COLNAME COLCARD AVGCOLLEN
---------- -------------------- -----------
A 3 4
B 3 15
2 record(s) selected.
db2 => SELECT colname, colcard, avgcollen FROM sysstat.columns
db2 (cont.) => WHERE ( tabschema, tabname ) = ( 'SYSIBM', 'SYSTABLES' )
db2 (cont.) => FETCH FIRST 10 ROWS ONLY@
COLNAME COLCARD AVGCOLLEN
------------------ -------------------- -----------
ACCESS_MODE -1 -1
ACTIVE_BLOCKS -1 -1
APPEND_MODE -1 -1
AST_DESC -1 -1
BASE_NAME -1 -1
BASE_SCHEMA -1 -1
CARD -1 -1
CHECKCOUNT -1 -1
CHECK_DESC -1 -1
CHILDREN -1 -1
10 record(s) selected.
db2 => CALL runstats('sysibm.systables')@
db2 => SELECT colname, colcard, avgcollen FROM sysstat.columns
db2 (cont.) => WHERE ( tabschema, tabname ) = ( 'SYSIBM', 'SYSTABLES' )
db2 (cont.) => FETCH FIRST 10 ROWS ONLY@
COLNAME COLCARD AVGCOLLEN
------------------ -------------------- -----------
ACCESS_MODE 1 1
ACTIVE_BLOCKS 2 8
APPEND_MODE 1 1
AST_DESC -1 -1
BASE_NAME 1 5
BASE_SCHEMA 1 5
CARD 2 8
CHECKCOUNT 1 2
CHECK_DESC -1 -1
CHILDREN 1 3
10 record(s) selected.
db2 => CALL runstats('unknown.table')@
SQL0443N Routine "STOLZE.RUNSTATS" (specific name "RUNSTATS") has returned an
error SQLSTATE with diagnostic text "Error -2306 returned by db2Runstats.".
SQLSTATE=38RS1
db2 => ? sql2306@
SQL2306N The table or index "<name>" does not exist.
|
从上面的例子中可以看到,该过程运行得很好,并导致给定表的统计信息得到更新。此外,最后的测试演示了在处理期间碰到的错误被返回给调用者,从这条错误消息中可以取得底层问题的更多信息。(在我们的例子中,我们指定了一个不存在的表的名称。)
LOAD 过程
LOAD
是将数据从文件系统中的文件里装载到数据库中的一个表里的非常快速而直接的方法。LOAD
不使用 SQL INSERT
语句,这与 IMPORT
有所不同。相反,它将数据直接写到表空间中的数据页。此外,由于需要做的日志记录更少,因此可以进一步提高装载过程的性能。(要了解关于 LOAD
命令的更多信息,请参阅 参考资料。)
由于该命令的非 SQL 特征,它不是直接作为数据库系统中的一个工作单位(事务)来提供的。然而,有时候从一个只使用 SQL 与数据库服务器会话的客户机应用程序开始装载过程是可取的。这一节中描述的存储过程的实现解决了这些需求,因为 CALL
本身是一个 SQL 语句。
清单 4 中显示的过程带有必要的输入参数 —— 文件的名称和类型,以及文件中的数据所装载到的目标表的全限定名称。它首先设置函数 db2Load
所需的数据结构,然后调用该函数。这个函数调用同样以斜体 显示。同样,准备步骤比调用本身更为复杂。这是因为 LOAD
支持很多参数,所有这些参数都需要初始化。
清单 4. 调用 db2Load 的存储过程的代码
#include <sql.h>
#include <sqludf.h>
#include <db2ApiDf.h>
int SQL_API_FN load_proc(
SQLUDF_VARCHAR *fileName,
SQLUDF_VARCHAR *fileType,
SQLUDF_VARCHAR *tableName,
SQLUDF_NULLIND *fileName_ind,
SQLUDF_NULLIND *fileType_ind,
SQLUDF_NULLIND *tableName_ind,
SQLUDF_TRAIL_ARGS)
{
SQL_API_RC rc = SQL_RC_OK;
db2LoadStruct data;
struct sqlu_media_list fileSources;
struct sqlu_location_entry fileLocation;
struct sqlca sqlca;
data.piActionString = NULL;
if (SQLUDF_NULL(fileName_ind) || SQLUDF_NULL(fileType_ind) ||
SQLUDF_NULL(tableName_ind)) {
memcpy(SQLUDF_STATE, "38LD1", SQLUDF_SQLSTATE_LEN);
memcpy(SQLUDF_MSGTX, "No file, file type, or table name specified.",
SQLUDF_MSGTEXT_LEN);
goto cleanup;
}
/*
* initialize data structures
*/
/* setup sources */
data.piSourceList = &fileSources;
fileSources.media_type = SQLU_SERVER_LOCATION;
fileSources.sessions = 1;
fileSources.target.location = &fileLocation;
if (strlen(fileName) > SQLU_MEDIA_LOCATION_LEN) {
memcpy(SQLUDF_STATE, "38LD2", SQLUDF_SQLSTATE_LEN);
memcpy(SQLUDF_MSGTX, "The file name is too long.",
SQLUDF_MSGTEXT_LEN);
goto cleanup;
}
fileLocation.reserve_len = strlen(fileName);
memcpy(fileLocation.location_entry, fileName,
fileLocation.reserve_len);
/* no lob support */
data.piLobPathList = NULL;
/* load all columns */
data.piDataDescriptor = NULL;
/* action: INSERT */
{
struct sqlchar *action = (struct sqlchar *)malloc(
sizeof(struct sqlchar) + 12 + strlen(tableName));
if (!action) {
memcpy(SQLUDF_STATE, "38LD3", SQLUDF_SQLSTATE_LEN);
memcpy(SQLUDF_MSGTX, "Memory allocation failed.",
SQLUDF_MSGTEXT_LEN);
goto cleanup;
}
action->length = sprintf(action->data,
"INSERT INTO %s", tableName);
data.piActionString = action;
}
/* file type is provided by the caller */
data.piFileType = fileType;
/* MODIFIED BY clause not supported */
data.piFileTypeMod = NULL;
/* all messages are discarded */
#if defined(SQLWINT)
data.piLocalMsgFileName = "NUL";
#else /* SQLWINT */
data.piLocalMsgFileName = "/dev/null";
#endif /* SQLWINT */
/* system defaults are used for the remaining parameters */
data.piTempFilesPath = NULL;
data.piVendorSortWorkPaths = NULL;
data.piCopyTargetList = NULL; /* no copies are created */
data.piNullIndicators = NULL; /* no null indicators for ASC files */
data.piLoadInfoIn = NULL;
data.poLoadInfoOut = NULL;
data.piPartLoadInfoIn = NULL;
data.poPartLoadInfoOut = NULL;
/* start the LOAD process */
data.iCallerAction = SQLU_INITIAL;
/* call the API */
rc = db2Load(db2Version820, &data, &sqlca);
if (rc != SQL_RC_OK || SQLCODE != SQL_RC_OK) {
memcpy(SQLUDF_STATE, "38XXX", SQLUDF_SQLSTATE_LEN);
sprintf(SQLUDF_MSGTX, "Error %d returned by db2Runstats.",
(int)(rc == SQL_RC_OK ? SQLCODE : rc));
goto cleanup;
}
cleanup:
if (data.piActionString != NULL) {
free(data.piActionString);
}
return SQLZ_DISCONNECT_PROC;
}
|
存储过程需要注册到您想要在其中使用它的每一个数据库中。下面(清单 5)显示的 CREATE PROCEDURE
语句必须与 C 代码函数的签名统一,以便 DB2 能够通过程序栈正确地传递所有的值。具体来说,参数的数量及数据类型,以及 null 指示符和其他参数都必须与 CREATE PROCEDURE
语句中声明的参数样式一致。
清单 5. CREATE PROCEDURE 语句
CREATE PROCEDURE load(IN fileName VARCHAR(256),
IN fileType VARCHAR(3), IN tableName VARCHAR(256))
SPECIFIC load
DYNAMIC RESULT SETS 0
MODIFIES SQL DATA
NOT DETERMINISTIC
CALLED ON NULL INPUT
LANGUAGE C
EXTERNAL NAME 'db2_api!load_proc'
FENCED THREADSAFE
NO EXTERNAL ACTION
PARAMETER STYLE SQL@
|
测试该过程
测试该过程的第一步是准备一个可以装载到一个表中的文件。这个文件必须在数据库系统的服务器端,因为该过程将在数据库服务器上执行,它只能访问那台计算机(因此,我们直接在服务器上运行示例)。使用 DB2 的 EXPORT
功能创建好该文件后,便可以使用该文件来测试这个存储过程。清单 6 中演示了这一步以及后面的更多步骤。
清单 6. 测试 LOAD 过程
$ db2 -td@
db2 => CREATE TABLE stolze.t ( a INT NOT NULL, b VARCHAR(100), PRIMARY KEY(a) )@
db2 => INSERT INTO stolze.t VALUES (1, '2'), (3, '4'), (5, '6')@
db2 => EXPORT TO /home/stolze/testdata.ixf OF IXF SELECT * FROM stolze.t@
SQL3104N The Export utility is beginning to export data to file
"/home/stolze/testdata.ixf".
SQL3105N The Export utility has finished exporting "3" rows.
Number of rows exported: 3
db2 => CREATE TABLE stolze.target LIKE stolze.t@
db2 => CALL load('/home/stolze/testdata.ixf', 'IXF', 'stolze.target')@
Return Status = 0
db2 => SELECT * FROM stolze.target@
A B
----------- -------------------
1 2
3 4
5 6
3 record(s) selected.
db2 => CREATE TABLE stolze.t2 ( a INT NOT NULL )@
db2 => CALL load('/home/stolze/testdata.ixf', 'IXF', 'stolze.t2')@
Return Status = 0
db2 => SELECT * FROM stolze.t2@
A
-----------
1
3
5
3 record(s) selected.
db2 => CALL load('/home/stolze/testdata.ixf', 'IXF', 'unknown.table')@
SQL0443N Routine "STOLZE.LOAD" (specific name "LOAD") has returned an error
SQLSTATE with diagnostic text "Error -3304 returned by db2Runstats.".
SQLSTATE=38LD4
db2 => ? sql3304@
SQL3304N The table does not exist.
Explanation:
The parameters specified on the command require that the table
exists.
|
这些步骤表明,该过程工作正常。其中成功地执行了 LOAD,数据被插入到各个表中。当使用这个过程时,要清楚 LOAD
本身的行为。例如,如果存在依赖表(例如表中有一个外键,该外键引用数据要被装载到的目标表),那么这些表可能被设为 CHECK PENDING
状态。此外,进一步的约束还可能导致在 LOAD 操作之后该表不可用,而要求您首先运行 SET INTEGRITY
SQL 语句。
构建和安装存储过程
上面给出的两个存储过程的源代码可以在 下载 小节中找到。为了构建这两个过程,可能需要使用随 DB2 的 C 代码示例一起提供的 bldrtn
脚本,该脚本在 DB2 实例的 sqllib/samples/c/ 目录中。
bldrtn
脚本完成所有必需的步骤。首先,它将 C 源代码编译成一个目标文件,随后创建共享库,在运行时当其中一个过程被调用时,DB2 将装载这个共享库。在最后一步中,这个库将被复制到 sqllib/function/ 目录中。清单 7 演示了如何与 db2_api.c
源代码一起使用这个脚本,该源代码包含了上面讨论的两个存储过程的实现。
清单 7. 构建过程和验证安装
$ cp ~/sqllib/samples/c/bldrtn .
$ ./bldrtn db2_api
$ ls -sh ~/sqllib/function/db2_api
24K /home/stolze/sqllib/function/db2_api
|
运行和测试这两个存储过程的实现到您满意之后,就可以应用于生产系统了。注意,您不必在生产系统上安装 C 编译器或 DB2 示例。您只需将共享库 db2_api
复制到目标系统,并将它放在 sqllib/function/ 目录中。当然,您需要将存储过程注册到目标系统的数据库中,这样就一切就绪了。
最后要说的是过程中的授权。从 参考资料 中列出的 API 文档中可以看出,DB2 API 执行它们自己的授权检查。您只需知道,这些 API 是用调用该存储过程的用户的授权隐式地调用的,通常该用户也是连接到数据库的用户。
结束语
很多 DB2 命令只能通过使用 DB2 命令行或 C API 来访问。在本文中,我们演示了如何将 DB2 命令封装在存储过程中,使得几乎任何应用程序都可以访问任何 DB2 命令。这种方法允许应用程序以任何语言实现,例如 Java 或 PHP。在本文的例子中,我们使用了 RUNSTATS
和 LOAD
命令的功能,但是这种机制当然也适用于任何其他可以通过 DB2 API 访问的命令。
参考资料