通过 SQL 访问 DB2 管理 API

最新推荐文章于 2022-02-14 18:46:46 发布

Mycro

最新推荐文章于 2022-02-14 18:46:46 发布

阅读量2.7k

点赞数

分类专栏：数据库的文章文章标签： db2 sql api null 数据库服务器数据结构

本文链接：https://blog.csdn.net/Mycro/article/details/542162

版权

数据库的文章专栏收录该内容

7 篇文章 0 订阅

订阅专栏

完全使用 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 访问的命令。

参考资料

您可以参阅本文在 developerWorks 全球站点上的英文原文。
db2Runstats 管理 API 的规范说明。
RUNSTATS 命令的描述。
LOAD 命令的描述。
db2Load 管理 API 的规范说明。
阅读“DB2 UDB SQL 管理例程简介”（developerWorks，2005 年 5 月），了解如何使用其中一些例程，这些例程可以通过 DB2 命令行、命令脚本或包含嵌入式 SQL 的应用程序来调用。
文章“用 DB2 例程来简化迁移”（developerWorks，2004 年 9 月）展示了如何将数据库和应用程序从其他数据库产品移植到 DB2 UDB。