DB2 基础知识: DB2 UDB SQL 管理例程简介

核心提示：IBM® DB2® Universal DatabaseTM（DB2 UDB）SQL 管理例程包含一个内置表函数、过程和标量函数集合，其工作就是执行各种各样的 DB2 管理任务，DB2 基础知识: DB2 UDB SQL 管理例程简介，这些任务包括执行某一项操作（例如，重组表）、检索报表（例如，文中还提

IBM® DB2® Universal Database^TM（DB2 UDB）SQL 管理例程包含一个内置表函数、过程和标量函数集合，其工作就是执行各种各样的 DB2 管理任务。这些任务包括执行某一项操作（例如，重组表）、检索报表（例如，返回包含系统快照数据的结果集）或返回应用程序中使用的某一个值（例如，返回当前连接的应用程序 ID）。

SQL 管理例程使通过基于 SQL 的应用程序发出管理命令成为可能，而且无需通过 CLP 进行解释。一些例程不接收任何参数，而另一些则拥有一个或多个输入参数或输出参数。表函数以可查询表的形式返回结果集。

SQL 管理例程的一个样例

表 1 总结了一些更常用的 SQL 管理例程，描述了每一个有名字的例程，并提供了展示其用法的例子。

表 1. SQL 管理例程

例程名称	例程类型	描述	例子
ADMIN_CMD	过程	使用 SQL CALL 语句执行 DB2 命令行处理器（CLP）管理命令。目前，该过程支持下列命令：DESCRIBE、EXPORT、PRUNE HISTORY/LOGFILE、REORG INDEXES/TABLE、RUNSTATS 和 UPDATE DATABASE CONFIGURATION。	call sysproc.admin_cmd('describe select * from staff') call sysproc.admin_cmd('export to /home/melnyk/output/sales.del of del messages /home/melnyk/output/export.msg select * from sales') call sysproc.admin_cmd('prune history 20050502') call sysproc.admin_cmd('reorg table sales allow no access') call sysproc.admin_cmd('runstats on table melnyk.employee') call sysproc.admin_cmd('update db cfg using logretain recovery userexit yes')
ADMIN_LIST_HIST	表函数	从与当前连接数据库分区相关的历史文件中返回信息	select eid, operation, start_time from table(sysproc.admin_list_hist()) as listhistory
APPLICATION_ID	标量函数	返回当前连接的应用程序 ID。该结果的数据类型是 VARCHAR(128)	select application_id() as appl_id from sysibm.sysdummy1
ENV_GET_INST_INFO	表函数	返回当前实例的有关信息	select inst_name, is_inst_partitionable, num_dbpartitions, inst_ptr_size from table(sysproc.env_get_inst_info()) as instanceinfo
ENV_GET_PROD_INFO	表函数	返回与所安装 DB2 产品有关的信息	select installed_prod, prod_release from table(sysproc.env_get_prod_info()) as productinfo
ENV_GET_SYS_INFO	表函数	返回系统的有关信息	select os_name, host_name, total_memory from table(sysproc.env_get_sys_info()) as systeminfo
GET_DBM_CONFIG	表函数	在一个有两行记录的表中返回数据库管理器的配置信息，每列代表一个参数。第一列是 DBMCONFIG_TYPE；此列中值为 0 的那一行包含保存在磁盘上的参数值，而此列中值为 1 的那一行包含存储在内存中的当前参数值	select dbmconfig_type, numdb, diaglevel from table(sysfun.get_dbm_config()) as dbmcfg
GET_DBSIZE_INFO	过程	以字节为单位计算并返回数据库的大小和最大大小	call sysproc.get_dbsize_info(?,?,?,0)
HEALTH_CONT_HI, HEALTH_CONT_HI_HIS, HEALTH_CONT_INFO, HEALTH_DB_HI, HEALTH_DB_HIC, HEALTH_DB_HIC_HIS, HEALTH_DB_HI_HIS, HEALTH_DB_INFO, HEALTH_DBM_HI, HEALTH_DBM_HI_HIS, HEALTH_DBM_INFO, HEALTH_TBS_HI, HEALTH_TBS_HI_HIS, HEALTH_TBS_INFO	表函数	这些例程返回来自正常快照的信息。并根据这些例程来返回关于容器、数据库、数据库管理器或表空间的信息。一些例程返回健康指示器（health indicator）信息，而另一些例程则返回健康指示器（health indicator）历史信息。所有正常例程都有一个 INTEGER 类型的输入参数，用于指定一个有效的分区编号；您可以用值 -1 表示当前分区，或者用 -2 表示所有分区。如果指定空（null）值，则隐式地将该值设置为 -1。大多数正常例程（但不包括返回数据库管理器信息的那些例程）都有一个 VARCHAR(255) 类型的输入参数，用于指定当前连接数据库的同一实例中的有效数据库名；您可以指定空值，以便从当前连接的数据库请求信息	select snapshot_timestamp, substr(container_name,1,24) as container_name, hi_id, hi_value, hi_timestamp, hi_alert_state from table(sysproc.health_cont_hi('sample',-1)) as health_cont_hi
REBIND_ROUTINE_PACKAGE	过程	重新绑定与 SQL 过程相关联的包	call sysproc.rebind_routine_package('P ','melnyk.update_inventory','ANY')
REG_LIST_VARIABLES	表函数	返回调用该函数的数据库分区目前正在使用的 DB2 注册表设置	select substr(reg_var_name,1,24) as reg_var_name, substr(reg_var_value,1,12) as reg_var_value, level from table(sysproc.reg_list_variables()) as registryinfo
REORGCHK_IX_STATS	过程	返回一个结果集，其中包含指示是否需要重组一个或多个索引的统计信息	call sysproc.reorgchk_ix_stats('s','melnyk')
REORGCHK_TB_STATS	过程	返回一个结果集，其中包含指示是否需要重组一个或多个表的统计信息	call sysproc.reorgchk_tb_stats('t','melnyk.employee')
SNAP_GET_CONTAINER, SNAP_GET_DB, SNAP_GET_DYN_SQL, SNAP_GET_STO_PATHS, SNAP_GET_TAB, SNAP_GET_TBSP, SNAP_GET_TBSP_PART	表函数	这些例程返回分别来自 tablespace_container 逻辑数据组、数据库和 detail_log 逻辑数据组、dynsql 逻辑数据组、storage_paths 逻辑数据组、表逻辑数据组、表空间逻辑数据组和 tablespace_nodeinfo 逻辑数据组的快照信息。所有快照例程都有一个 INTEGER 类型的输入参数，用于指定有效的分区编号；您可以指定值 -1 来表示当前分区，或者指定-2（多数情况下）表示所有分区。如果指定空（null）值，则隐式地将该值设置为 -1。所有 SNAP_* 例程都有一个 VARCHAR(255) 类型的输入参数，用于指定当前连接数据库的同一实例中的有效数据库名；您可以指定空值，从当前连接的数据库请求信息	select snapshot_timestamp, substr(tabschema,1,16) as tabschema, substr(tabname,1,16) as tabname, tab_type, data_object_pages, rows_written, dbpartitionnum from table(sysproc.snap_get_tab('sample',-1)) as tabinfo
SNAPSHOT_AGENT, SNAPSHOT_APPL, SNAPSHOT_APPL_INFO, SNAPSHOT_BP, SNAPSHOT_CONTAINER, SNAPSHOT_DATABASE, SNAPSHOT_DBM, SNAPSHOT_DYN_SQL, SNAPSHOT_FCM, SNAPSHOT_FCMNODE, SNAPSHOT_LOCK, SNAPSHOT_LOCKWAIT, SNAPSHOT_QUIESCERS, SNAPSHOT_RANGES, SNAPSHOT_STATEMENT, SNAPSHOT_SUBSECT, SNAPSHOT_SWITCHES, SNAPSHOT_TABLE, SNAPSHOT_TBREORG, SNAPSHOT_TBS, SNAPSHOT_TBS_CFG	表函数	这些例程返回关于代理、应用程序、缓冲池、容器、数据库、数据库管理器、动态 SQL、快速通信管理器（fast communication manager，FCM）、锁、quiescer、范围、语句、访问计划的子部分、数据库快照切换状态、表、表重组和表空间的快照信息。所有快照例程都有一个 INTEGER 类型的输入参数，用于指定有效的分区编号；您可以指定值 -1 来表示当前分区，或者指定 -2 表示所有分区。如果指定空（null）值，则隐式地将该值设置为 -1。大多数的 SNAPSHOT_* 例程（但不包括返回数据库管理器级信息的那些例程）都有一个 VARCHAR(255) 类型的输入参数，用于指定当前连接数据库的同一实例中的有效数据库名；您可以指定空值，以便从当前连接的数据库请求信息	select lock_mode, lock_object_type, substr(table_name, 1, 16) as table_name from table(sysproc.snapshot_lock('sample',-1)) as lockinfo where lock_object_type in (1,2,4,5,6,11,18,19)
SNAPSHOT_FILEW	过程	将系统快照数据编写到随后可以进行查询的文件中。该例程的输入参数是请求类型、数据库名和数据库分区编号。例如，请求类型 1 表示请求数据库管理器信息数据库分区值 -1 表示当前分区。如果指定该表函数的输入参数为空值，那么与指定请求类型相对应的快照表函数将返回前面调用 SNAPSHOT_FILEW 所捕获的信息	(1) call sysproc.snapshot_filew(1,'sample',-1) (2) select * from table(snapshot_dbm(cast(null as integer))) as snapshot_dbm

两个具体的例子

为了演示如何在嵌入式 SQL 应用程序中使用 SQL 管理例程，我们首先将在 SAMPLE 数据库中创建一个名为 TABLE_SNAPSHOTS 的表，SAMPLE 数据库包含在 DB2 UDB 中（清单 1）。然后，我们将用通过多次调用 SNAP_GET_TAB 表函数捕获的表快照数据来填充该表。我们为 TABLE_SNAPSHOTS 表定义的列对应于 SNAP_GET_TAB 函数返回的表中那些令人感兴趣的列。INSERT INTO 语句及其相关的 SELECT 语句嵌套在一个简单的 C 程序中（MYAPP1；清单 2），这些 SELECT语句将查询 SNAP_GET_TAB 例程返回的表。WHERE 子句限制结果集，拒绝系统生成的表和 TABLE_SNAPSHOTS 表本身。

清单 1. 创建来保存快照监视器数据

create　table　table_snapshots　(　
　snapshot_timestamp　timestamp　not　null,　
　tabschema　varchar(16),　
　tabname　varchar(16),　
　tab_type　bigint,　
　data_object_pages　bigint,　
　rows_written　bigint,　
　dbpartitionnum　smallint　
　);　

清单 2. 一个完整 C 程序（myapp1.sqc）的源代码，它展示了利用使用 SQL 管理例程的快照监视器数据来填充数据库表是多么容易

#include　<stdio.h>　
#include　<stdlib.h>　
#include　<string.h>　
#include　<sqlenv.h>　
#include　<sqlutil.h>　
#include　"utilemb.h"　
int　TbInsert(void);　
EXEC　SQL　BEGIN　DECLARE　SECTION;　
EXEC　SQL　END　DECLARE　SECTION;　
int　main(int　argc,　char　*argv[])　
{　
　int　rc　=　0;　
　struct　sqlca　sqlca;　
　char　dbAlias[SQL_ALIAS_SZ　+　1];　
　char　user[USERID_SZ　+　1];　
　char　pswd[PSWD_SZ　+　1];　
　/*　check　the　command　line　arguments　*/　
　rc　=　CmdLineArgsCheck1(argc,　argv,　dbAlias,　user,　pswd);　
　if　(rc　!=　0)　
　{　
　　return　rc;　
　}　
　printf("\nThis　sample　shows　how　to　use　the　SNAP_GET_TAB\n");　
　printf("SQL　administrative　routine　to　capture　DB2　monitoring\n");　
　printf("data　in　a　database　table.\n");　
　/*　connect　to　the　database　*/　
　rc　=　DbConn(dbAlias,　user,　pswd);　
　if　(rc　!=　0)　
　{　
　　return　rc;　
　}　
　rc　=　TbInsert();　
　/*　disconnect　from　the　database　*/　
　rc　=　DbDisconn(dbAlias);　
　if　(rc　!=　0)　
　{　
　　return　rc;　
　}　
　return　0;　
}　/*　end　main　*/　
int　TbInsert(void)　
{　
　int　rc　=　0;　
　struct　sqlca　sqlca;　
　EXEC　SQL　INSERT　INTO　table_snapshots　
　　SELECT　snapshot_timestamp,　SUBSTR(tabschema,1,16)　as　tabschema,　
　　　　SUBSTR(tabname,1,16)　as　tabname,　tab_type,　data_object_pages,　
　　　　rows_written,　dbpartitionnum　
　　　FROM　TABLE(sysproc.snap_get_tab('SAMPLE',-1))　AS　tabinfo　
　　　WHERE　SUBSTR(tabschema,1,3)　!=　'SYS'　AND　tabname　!=　'TABLE_SNAPSHOTS';　
　printf("\nInserted　data　into　TABLE_SNAPSHOTS.\n");　
　return　0;　
}　/*　end　TbInsert　*/　

现在，我们准备执行 MYAPP1 应用程序，使用执行 DB2 导入操作时捕获的快照数据来填充 TABLE_SNAPSHOTS 表。通过使用下列场景，我们可以很轻松地完成这项工作。我们将使用一个名为 staffdata.del 的分隔 ASCII 数据文件。该文件包含大量数据（22767 条记录），在执行导入操作时，这将为我们提供足够的时间来运行 MYAPP1 三、四次。通过将 STAFF 表（在 SAMPLE 数据库中）的导出数据的许多副本粘贴到一个文本文件中，然后使用电子表格中生成的序列号改写这个 ID（SMALLINT）列数据，可以快速汇编这样的文件。这将确保 ID 列中的值的惟一性。

假设我们将使用 STAFF 表的表定义来创建一个新表 NEWSTAFF。连接 SAMPLE 数据库之后，我们将通过发出下列 SQL 语句来创建 NEWSTAFF 表：create table newstaff like staff。然后通过使用以下 DB2 IMPORT 命令，用大型 DEL 文件中包含的数据来填充这个新表：import from staffdata.del of del modified by chardel"" coldel, decpt. insert into newstaff。

在执行导入操作时（Application 1），我们将通过连续多次运行 MYAPP1（Application 2）来捕获多个表快照（清单 3）。我们将在两个 DB2 命令窗口（会话）中执行 Application 1 和 Application 2。

所捕获的快照数据被写入 TABLE_SNAPSHOTS 表中，可以查询该表来显示其内容（清单 3）。

清单 3. 执行 DB2 导入操作（Application 1）时在数据库表中捕获快照监视器数据（Application 2）

　　Application　1：　
connect　to　sample　
create　table　newstaff　like　staff　
import　from　staffdata.del　of　del　modified　by　chardel""　coldel,　decpt.　
　insert　into　newstaff　
　　
SQL3109N　The　utility　is　beginning　to　load　data　from　file　"staffdata.del".　
SQL3110N　The　utility　has　completed　processing.　
　　　　　"22767"　rows　were　read　from　the　input　file.　
SQL3221W　...Begin　COMMIT　WORK.　Input　Record　Count　=　"22767".　
SQL3222W　...COMMIT　of　any　database　changes　was　successful.　
SQL3149N　"22767"　rows　were　processed　from　the　input　file.　
　　　　　"22767"　rows　were　successfully　inserted　into　the　table.　
　　　　　　　"0"　rows　were　rejected.　
Number　of　rows　read　　　　　=　22767　
Number　of　rows　skipped　　　=　0　
Number　of　rows　inserted　　　=　22767　
Number　of　rows　updated　　　=　0　
Number　of　rows　rejected　　　=　0　
Number　of　rows　committed　　=　22767　
connect　reset　
Application　2：　
elk　/home/melnyk/mysamples>myapp1　　　　　　　　　　　　==>　run　four　times　in　succession　
This　sample　shows　how　to　use　the　SNAP_GET_TAB　
SQL　administrative　routine　to　capture　DB2　monitoring　
data　in　a　database　table.　
　Connecting　to　'sample'　database...　
　Connected　to　'sample'　database.　
　Inserted　data　into　TABLE_SNAPSHOTS.　
　Disconnecting　from　'sample'　database...　
　Disconnected　from　'sample'　database.　
...　
elk　/home/melnyk/mysamples>db2　connect　to　sample　
elk　/home/melnyk/mysamples>db2　"select　*　from　table_snapshots"　
SNAPSHOT_TIMESTAMP　TABSCHEMA　TABNAME　TAB_TYPE　DATA_OBJECT_PAGES　ROWS_WRITTEN　DBPART...　
------...---------　-----...-　----...-　----...-　-----------...---　----...-----　---------　
2005-0...11.609616　MELNYK　　NEWSTAFF　　　　1　　　　　　　　23　　　　　2015　　　　　0　
2005-0...14.822350　MELNYK　　NEWSTAFF　　　　1　　　　　　　　102　　　　　9017　　　　　0　
2005-0...17.901561　MELNYK　　NEWSTAFF　　　　1　　　　　　　　192　　　　17051　　　　　0　
2005-0...20.506826　MELNYK　　NEWSTAFF　　　　1　　　　　　　　257　　　　22767　　　　　0　
　4　record(s)　selected.　
elk　/home/melnyk/mysamples>db2　connect　reset　

清单 4 显示了一个简单 C 程序（MYAPP2）的源代码，该程序展示了对 ADMIN_CMD 过程的调用。在本例中，ADMIN_CMD 过程包含用于 DB2 EXPORT 命令的命令串。无论何时执行该程序时，SALES 表中的数据都将导出到名为 sales.del 的分隔 ASCII 文件中，而导出消息将写入名为 export.msg 的文件中。ADMIN_CMD 的产品文档指出，“EXPORT 命令中使用的任何路径都必须是服务器上的有效完全限定路径”。它还指出，“输出文件是基于该过程的进程 ID 创建的；该 ID 必须能够对导出数据的目录进行写访问和执行访问，而且消息文件已经编写好”。例如，在基于 UNIX 的系统上，这意味着必须修改输出目录的访问模式，为组和其他人提供对该目录的写访问权限和执行权限（例如，chmod 733 <directory-name>）。

清单 4. 一个完整 C 程序（myapp2.sqc）的源代码，它展示了通过调用 ADMIN_CMD 过程来调用 DB2 命令是多么容易

#include　<stdio.h>　
#include　<stdlib.h>　
#include　<string.h>　
#include　<sqlenv.h>　
#include　<sqlutil.h>　
#include　"utilemb.h"　
int　TbExport(void);　
EXEC　SQL　BEGIN　DECLARE　SECTION;　
EXEC　SQL　END　DECLARE　SECTION;　
int　main(int　argc,　char　*argv[])　
{　
　int　rc　=　0;　
　struct　sqlca　sqlca;　
　char　dbAlias[SQL_ALIAS_SZ　+　1];　
　char　user[USERID_SZ　+　1];　
　char　pswd[PSWD_SZ　+　1];　
　/*　check　the　command　line　arguments　*/　
　rc　=　CmdLineArgsCheck1(argc,　argv,　dbAlias,　user,　pswd);　
　if　(rc　!=　0)　
　{　
　　return　rc;　
　}　
　printf("\nThis　sample　shows　how　to　use　the　ADMIN_CMD\n");　
　printf("SQL　administrative　routine　to　run　a　DB2　export　operation.\n");　
　/*　connect　to　database　*/　
　rc　=　DbConn(dbAlias,　user,　pswd);　
　if　(rc　!=　0)　
　{　
　　return　rc;　
　}　
　rc　=　TbExport();　
　/*　disconnect　from　the　database　*/　
　rc　=　DbDisconn(dbAlias);　
　if　(rc　!=　0)　
　{　
　　return　rc;　
　}　
　return　0;　
}　/*　end　main　*/　
int　TbExport(void)　
{　
　int　rc　=　0;　
　struct　sqlca　sqlca;　
　printf("\n　Exporting　data　to　/home/melnyk/output/sales.del...\n");　
　EXEC　SQL　CALL　sysproc.admin_cmd('export　to　/home/melnyk/output/sales.del　of　del　
　　messages　/home/melnyk/output/export.msg　select　*　from　sales');　
　printf("\n　Messages　have　been　written　to　/home/melnyk/output/export.msg.\n");　
　return　0;　
}　/*　TbExport　*/　

结束语

我们看到，DB2 UDB SQL 管理例程允许您通过 SQL 很方便地访问 DB2 管理函数。这些例程在应用程序中特别有用，在那里，它们表示了用来访问管理函数的 C API 的一个易用替代。除了描述许多例程之外，文中还提供了一些工作实例，其中包括那些包含过程调用的示例程序，以及使用表函数返回的结果集影响表更新的一些示例程序。