IBM® DB2® Universal DatabaseTM（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 的一個易用替代。除了描述許多例程之外，文中還提供了一些工作實例，其中包括那些包含過程調用的示例程序，以及使用表函數返回的結果集影響表更新的一些示例程序。
