DB2下數(shù)據(jù)轉(zhuǎn)移任務(wù)操作實例
使用 DB2 LOAD 實用程序的 FROM CURSOR 選項簡化 DB2 for Linux, UNIX, and Windows的數(shù)據(jù)轉(zhuǎn)移過程。本文介紹 LOAD FROM CURSOR 特性并提供兩個接口 Command Line Processor 和 ADMIN_CMD 存儲過程的使用示例。
簡介
典型的 DB2 數(shù)據(jù)轉(zhuǎn)移任務(wù)涉及三個步驟：
◆把數(shù)據(jù)以二進制或文本格式從源數(shù)據(jù)庫導(dǎo)出到一個臨時數(shù)據(jù)交換文件
◆在系統(tǒng)之間轉(zhuǎn)移生成的文件
◆把數(shù)據(jù)從文件導(dǎo)入或裝載到目標數(shù)據(jù)庫中
在數(shù)據(jù)量很大的情況下，使用 EXPORT 實用程序生成數(shù)據(jù)交換文件常常要花費很長時間。另外，在把數(shù)據(jù)移入和移出數(shù)據(jù)庫時，必須考慮不同的數(shù)據(jù)庫編碼頁和操作系統(tǒng)。
可以使用 LOAD 實用程序的 FROM CURSOR 選項避免這些問題。當(dāng)指定 FROM CURSOR 選項時，LOAD 實用程序直接把一個 SQL 查詢的結(jié)果集作為數(shù)據(jù)裝載操作的來源，這樣就不需要生成臨時數(shù)據(jù)交換文件。因此，LOAD FROM CURSOR 是在不同的表空間或數(shù)據(jù)庫之間快速輕松地轉(zhuǎn)移數(shù)據(jù)的方法。可以在命令行上執(zhí)行 LOAD FROM CURSOR，也可以通過使用 DB2 的 ADMIN_CMD 存儲過程在應(yīng)用程序或存儲過程中執(zhí)行它。本文介紹 LOAD FROM CURSOR 特性并提供兩個接口 Command Line Processor (CLP) 和 ADMIN_CMD 存儲過程的使用示例。
把表轉(zhuǎn)移到另一個表空間
首先，看看如何把表從一個表空間轉(zhuǎn)移到另一個表空間。如果創(chuàng)建表的表空間的頁面大小不合適，或者應(yīng)該用另一個緩沖區(qū)池訪問表，就可能需要執(zhí)行這種數(shù)據(jù)轉(zhuǎn)移。在 9.1 以前的 DB2 版本中，常常由于達到表空間的最大大小而在表空間之間轉(zhuǎn)移表。但是，在 DB2 9.1 和更高版本中，這應(yīng)該不再是問題了，因為表空間大小限制已經(jīng)顯著提高了(前提是使用大表空間，而不是以前使用的常規(guī)表空間)。
這個示例場景首先創(chuàng)建 DB2 SAMPLE 數(shù)據(jù)庫。這可以通過在命令行上調(diào)用 db2sampl 命令來完成，見清單 1。
清單 1. 創(chuàng)建 SAMPLE 數(shù)據(jù)庫
C:\>db2sampl |
除了其他表之外，SAMPLE 數(shù)據(jù)庫包含一個名為 SALES 的表。在默認情況下，在表空間 USERSPACE1 中創(chuàng)建這個表。可以通過對 DB2 編目視圖 SYSCAT.TABLES 和 SYSCAT.TABLESPACES 執(zhí)行查詢來確認這一點。
清單 2. 判斷 SALES 表的表空間
C:\>db2 "CONNECT TO SAMPLE" |
除了 USERSPACE1 表空間之外，還有第二個表空間 IBMDB2SAMPLEREL，它也用于存儲用戶數(shù)據(jù)。在這個示例場景中，IBMDB2SAMPLEREL 作為轉(zhuǎn)移 SALES 表的目標表空間。執(zhí)行 DB2 命令 LIST TABLESPACES，就可以看到一個數(shù)據(jù)庫的所有表空間。清單 3 演示具體做法。
清單 3. 列出 SAMPLE 數(shù)據(jù)庫的所有表空間
C:\>db2 "LIST TABLESPACES" |
在把 SALES 表的內(nèi)容復(fù)制到 IBMDB2SAMPLEREL 表空間之前，必須在目標表空間中創(chuàng)建一個空表，此表的結(jié)構(gòu)應(yīng)該與 SALES 表相同。因為在同一個數(shù)據(jù)庫模式中不可能有兩個同名的表，所以臨時用 SALES_TMP 這個名稱創(chuàng)建新的表。通過在 CREATE TABLE 命令中指定 LIKE 選項，創(chuàng)建一個與現(xiàn)有表結(jié)構(gòu)相同的空表(清單 4)。通過 IN 選項顯式地定義新表 SALES_TMP 的表空間。
清單 4. 創(chuàng)建數(shù)據(jù)轉(zhuǎn)移操作所需的目標表 SALES_TMP
C:\>db2 "CREATE TABLE FECHNER.SALES_TMP LIKE FECHNER.SALES IN IBMDB2SAMPLEREL" |
現(xiàn)在，可以執(zhí)行數(shù)據(jù)轉(zhuǎn)移操作了。使用 DECLARE CURSOR 命令定義一個游標，它使用 SELECT 語句讀取源表 SALES 的所有數(shù)據(jù)。可以自由選擇游標的名稱，在此示例中使用 C1。然后，在用來填充目標表 SALES_TMP 的 LOAD 命令中引用此游標。此示例中的 LOAD 命令把它的消息寫到日志文件 load_sales_tmp.msg 中。執(zhí)行的 LOAD 操作包含 NONRECOVERABLE 選項。這意味著在數(shù)據(jù)庫恢復(fù)的前滾階段無法重新執(zhí)行這個 LOAD 操作。因此，在執(zhí)行數(shù)據(jù)轉(zhuǎn)移操作之后，應(yīng)該執(zhí)行數(shù)據(jù)庫備份，至少是表空間備份。LOAD 命令有其他選項可以避免這種情況，但是這些選項超出了本文的范圍。更多信息請參見 DB2 Information Center 中對 LOAD 命令的說明(參見 參考資料)。
清單 5. 執(zhí)行 LOAD FROM CURSOR 操作把 SALES 表中的所有行復(fù)制到 SALES_TMP 表
C:\>db2 "DECLARE C1 CURSOR FOR SELECT * FROM FECHNER.SALES" |
在把 SALES 表中的所有行成功地復(fù)制到 SALES_TMP 表之后，可以刪除源表(DROP TABLE 語句)。然后，把目標表 SALES_TMP 重命名為 SALES(RENAME TABLE 語句)。在使用 RENAME TABLE 時，只能修改表名，而不能修改表的模式名。因此，一定要在正確的模式中創(chuàng)建 SALES_TMP 表。
清單 6. 刪除源表 SALES 并重命名目標表 SALES_TMP
C:\>db2 "DROP TABLE FECHNER.SALES" |
再次對 DB2 編目視圖執(zhí)行查詢，可以確認 SALES 表已經(jīng)從原來的表空間 USERSPACE1 轉(zhuǎn)移到了新的表空間 IBMDB2SAMPLEREL 中，見清單 7。
清單 7. 確認新 SALES 表的表空間
C:\>db2 "SELECT TABLES.TABSCHEMA, TABLES.TABNAME, TBSPACES.TBSPACE FROM SYSCAT.TABLES AS |
#p#
使用別名在另一個數(shù)據(jù)庫中創(chuàng)建表拷貝
除了可以在一個數(shù)據(jù)庫中的表空間之間轉(zhuǎn)移表之外，還可以在不同的數(shù)據(jù)庫之間轉(zhuǎn)移表。這意味著：通過使用 LOAD FROM CURSOR，也可以把表從一個數(shù)據(jù)庫轉(zhuǎn)移到另一個數(shù)據(jù)庫。這可以通過兩種方法完成：
方法 1 - 在目標數(shù)據(jù)庫中，使用 DB2 的聯(lián)邦數(shù)據(jù)庫機制訪問源數(shù)據(jù)庫。
方法 2 - 使用 LOAD FROM CURSOR 命令的遠程訪問特性。
這兩種方法各有優(yōu)點，下面分別討論。
方法1 使用 DB2 的聯(lián)邦數(shù)據(jù)庫機制
通過對源數(shù)據(jù)庫執(zhí)行聯(lián)邦訪問來復(fù)制表內(nèi)容。方法 1 要求目標數(shù)據(jù)庫被配置為聯(lián)邦數(shù)據(jù)庫。因此，對應(yīng)的 DB2 實例的 FEDERATED 參數(shù)必須設(shè)置為 YES(UPDATE DBM CFG)。在修改 DBM CFG 參數(shù) FEDERATED 之后，必須重新啟動 DB2 實例(db2stop/db2start)。在這個示例場景中，源和目標數(shù)據(jù)庫在同一個 DB2 實例中運行。創(chuàng)建一個名為 MYSAMPLE 的空數(shù)據(jù)庫作為目標數(shù)據(jù)庫。對于沒有特殊需求的測試數(shù)據(jù)庫，可以使用 CREATE DATABASE 命令創(chuàng)建此數(shù)據(jù)庫，不需要其他選項。
清單 8. 在 DBM CFG 中啟用聯(lián)邦數(shù)據(jù)庫支持并創(chuàng)建空的目標數(shù)據(jù)庫 MYSAMPLE
C:\>db2 "UPDATE DBM CFG USING FEDERATED YES" |
與前面一樣，必須在目標數(shù)據(jù)庫 MYSAMPLE 中創(chuàng)建一個空表，它的結(jié)構(gòu)與 SAMPLE 數(shù)據(jù)庫中的 SALES 表相同。因此，應(yīng)該使用 db2look 實用程序提取源數(shù)據(jù)庫中 SALES 表的 DDL。
清單 9. 使用 db2look 實用程序提取源表 SALES 的 DDL
C:\>db2look -d sample -e -z fechner -t sales -o sales.ddl |
db2look 調(diào)用的結(jié)果是 sales.ddl 文件，其中包含 SALES 表的 CREATE TABLE 語句。如果在 SALES 表上定義了約束和/或索引，也會提取出對應(yīng)的定義并寫到 sales.ddl 文件中。清單 10 顯示這些結(jié)果。
清單 10. db2look 調(diào)用的結(jié)果文件 sales.ddl
-- This CLP file was created using DB2LOOK Version 9.5 |
在對目標數(shù)據(jù)庫 MYSAMPLE 執(zhí)行 sales.ddl 文件中的語句之前，在文本編輯器中打開此文件并對生成的語句做兩處修改：
在文件的開頭，db2look 為源數(shù)據(jù)庫 SAMPLE 生成 CONNECT 語句。因為希望對目標數(shù)據(jù)庫 MYSAMPLE 執(zhí)行下面的語句，所以要相應(yīng)地修改 CONNECT 語句。
因為在目標數(shù)據(jù)庫 MYSAMPLE 中沒有用于存儲用戶數(shù)據(jù)的表空間 IBMDB2SAMPLREL，所以要把 CREATE TABLE 語句中的表空間名替換為 USERSPACE1。
清單 11. 結(jié)果文件 sales.ddl 中創(chuàng)建目標表所需的修改
CONNECT TO SAMPLE; -> CONNECT TO MYSAMPLE; |
在修改 sales.ddl 文件之后，通過調(diào)用 DB2 CLP (command line processor) 執(zhí)行此腳本。
清單 12. 在 MYSAMPLE 數(shù)據(jù)庫中創(chuàng)建目標表
C:\>db2 -tf sales.ddl |
到目前為止，只在目標數(shù)據(jù)庫 MYSAMPLE 中創(chuàng)建了 SALES 表的空拷貝。準備數(shù)據(jù)轉(zhuǎn)移操作的下一步是把源數(shù)據(jù)庫 SAMPLE 編目為遠程數(shù)據(jù)庫。顯然，這對于這個示例場景并不是必需的，因為源和目標數(shù)據(jù)庫在同一個服務(wù)器上的同一個 DB2 實例中運行。但是在真實的環(huán)境中，必須對運行目標數(shù)據(jù)庫的 DB2 實例執(zhí)行以下 CATALOG 命令，從而允許對源數(shù)據(jù)庫進行 TCP/IP 訪問。
清單 13. 在節(jié)點和數(shù)據(jù)庫目錄中創(chuàng)建訪問 SAMPLE 數(shù)據(jù)庫所需的條目
C:\>db2 "CATALOG TCPIP NODE SRCNODE REMOTE localhost SERVER 50000" |
準備 LOAD FROM CURSOR 操作的最后一步是配置對源數(shù)據(jù)庫 SAMPLE 中 SALES 表的聯(lián)邦訪問。這需要在目標數(shù)據(jù)庫 MYSAMPLE 中創(chuàng)建對另一個數(shù)據(jù)庫執(zhí)行聯(lián)邦訪問所需的幾個特殊對象：
包裝器
包裝器支持訪問外部數(shù)據(jù)源。外部數(shù)據(jù)源可以是另一個 DBMS(數(shù)據(jù)庫管理系統(tǒng))，比如 Oracle 或 SQL Server，也可以僅僅是 Excel 文件。根據(jù)應(yīng)該訪問的數(shù)據(jù)源，需要適當(dāng)?shù)陌b器。這些包裝器包含在單獨的 IBM product WebSphere® Federation Server 中。如果只希望訪問 DB2 產(chǎn)品系列(DB2 LUW 或 DB2 z/OS)的另一個數(shù)據(jù)庫，那么需要 DRDA 包裝器。DB2 LUW 中已經(jīng)包含這個包裝器，所以在這種情況下不需要 WebSphere Federation Server。通過在目標數(shù)據(jù)庫中執(zhí)行以下命令，創(chuàng)建 DRDA 包裝器：CREATE WRAPPER DRDA。
服務(wù)器
“服務(wù)器” 這個詞在這里有點容易引起誤解，因為這實際上意味著源數(shù)據(jù)庫扮演(數(shù)據(jù))服務(wù)器的角色。為了在目標數(shù)據(jù)庫中識別源數(shù)據(jù)庫，要創(chuàng)建一個服務(wù)器對象，它指定數(shù)據(jù)源的類型 (DB2/UDB VERSION 9.5)、要使用的包裝器 (DRDA) 和訪問源數(shù)據(jù)庫所需的用戶名/密碼組合。使用 DBNAME 選項提供源數(shù)據(jù)庫本身的名稱。用戶名/密碼必須在引號中指定。為了避免命令行解釋器刪除引號，應(yīng)該加上一個反斜杠 (\)。可以自由選擇服務(wù)器對象的名稱。在這個示例場景中，使用名稱 SRCSRV。
用戶映射
對于希望用前面定義的服務(wù)器對象訪問遠程數(shù)據(jù)庫的每個用戶，都必須創(chuàng)建一個用戶映射。用戶映射定義本地數(shù)據(jù)庫 (MYSAMPLE) 中的授權(quán) ID 如何映射到遠程數(shù)據(jù)庫 (SAMPLE) 中的授權(quán) ID。在這個示例場景中，本地用戶和遠程用戶是相同的，但是仍然必須定義用戶映射。
別名
別名是源數(shù)據(jù)庫中的遠程表的本地別名。指定了別名，就可以在目標數(shù)據(jù)庫中用 SQL 語句查詢遠程表，就像查詢?nèi)魏伪镜乇硪粯印?/P>
清單 14. 創(chuàng)建聯(lián)邦訪問所需的數(shù)據(jù)庫對象
C:\>db2 "CONNECT TO MYSAMPLE" |
注意：這里描述的設(shè)置聯(lián)邦訪問所需的步驟完全獨立于 LOAD FROM CURSOR 功能。這意味著這些是為遠程數(shù)據(jù)庫中的表創(chuàng)建別名的通用步驟。
配置了對源數(shù)據(jù)庫表的聯(lián)邦訪問之后，就可以像前面一樣執(zhí)行 LOAD FROM CURSOR 操作。首先，定義一個游標，它使用上面創(chuàng)建的別名讀取遠程表中的所有行。然后，在 LOAD 命令中引用這個游標。
清單 15. 使用別名執(zhí)行遠程 LOAD FROM CURSOR 操作
C:\>db2 "DECLARE C1 CURSOR FOR SELECT * FROM FECHNER.SRCTAB" |
正如前面提到的，與方法 2 相比，結(jié)合使用 LOAD FROM CURSOR 操作和聯(lián)邦訪問需要做的配置工作比較多。但是，聯(lián)邦訪問的主要優(yōu)點是可以從非 DB2 數(shù)據(jù)源裝載數(shù)據(jù)。通過使用聯(lián)邦方式，可以訪問 Oracle、SQL Server 等數(shù)據(jù)源以及其他許多關(guān)系和非關(guān)系數(shù)據(jù)源，通過創(chuàng)建別名并執(zhí)行引用別名的 LOAD FROM CURSOR 操作來復(fù)制內(nèi)容。WebSphere Federation Server 產(chǎn)品提供訪問非 DB2 數(shù)據(jù)源所需的包裝器。
#p#
方法2 使用 LOAD FROM CURSOR 命令的遠程訪問特性
既然已經(jīng)了解了使用別名的遠程 LOAD FROM CURSOR 操作方法，現(xiàn)在看看另一種比較簡便的方法。為此，首先刪除剛才在目標數(shù)據(jù)庫 MYSAMPLE 中的 SALES 表中導(dǎo)入的所有行，見清單 16。
清單 16. 刪除目標表中的所有行，以便再次執(zhí)行 LOAD FROM CURSOR 操作
C:\>db2 "CONNECT TO MYSAMPLE" |
對于第二種方法，不需要配置對遠程數(shù)據(jù)庫的聯(lián)邦訪問。只需使用 DATABASE 選項在 DECLARE CURSOR 語句中指定遠程數(shù)據(jù)庫。為此，要在本地 DB2 實例的系統(tǒng)數(shù)據(jù)庫目錄中對遠程數(shù)據(jù)庫進行編目。前面已經(jīng)給出了所需的 CATALOG 命令。另外，在定義游標時要指定遠程訪問所需的用戶名和密碼。LOAD 命令本身保持不變。
清單 17. 在不使用別名的情況下執(zhí)行遠程 LOAD FROM CURSOR 操作
C:\>db2 "DECLARE C1 CURSOR DATABASE SRCDB USER fechner USING password FOR SELECT * FROM |
從 DB2 9.1 開始，可以以這種方式執(zhí)行遠程 LOAD FROM CURSOR 操作，這種方式基于 DB2 8 中的聯(lián)邦訪問方法。這種新方法有兩個優(yōu)點 —— 容易使用且性能好。顯然，新方法非常容易使用。性能比聯(lián)邦方法好是因為涉及的數(shù)據(jù)傳輸層更少。但是，不應(yīng)該忘記聯(lián)邦方法的優(yōu)點，即可以訪問非 DB2 數(shù)據(jù)源。
CLP 和 ADMIN_CMD 在 LOAD FROM CURSOR 方面的差異
可以通過特殊的存儲過程 ADMIN_CMD 執(zhí)行許多管理命令，從而把管理命令嵌入在應(yīng)用程序代碼中。這也適用于 LOAD FROM CURSOR 操作。存儲過程 ADMIN_CMD 的使用與應(yīng)用程序代碼的位置無關(guān)，也就是說，在客戶端代碼(例如 Java 應(yīng)用程序)和服務(wù)器端代碼(例如 SQL/PL 存儲過程)中都可以使用它。下面的示例在一個定制的 SQL/PL 存儲過程中使用 ADMIN_CMD 存儲過程。create_load_routine.sql 文件包含示例存儲過程 REMOTE_LOAD_FROM_CURSOR 的 SQL/PL 源代碼。
清單 18. 包含示例存儲過程的 create_load_routine.sql 文件
CREATE PROCEDURE FECHNER.REMOTE_LOAD_FROM_CURSOR () |
存儲過程中的第一個語句是 DELETE，它刪除本地目標表 SALES 中現(xiàn)有的行。接下來，用適當(dāng)?shù)?LOAD 命令調(diào)用 ADMIN_CMD，從而執(zhí)行遠程 LOAD FROM CURSOR 操作。這種方式與從命令行執(zhí)行 LOAD FROM CURSOR 操作的差異如下：
不需要通過執(zhí)行 DECLARE CURSOR 單獨定義所需的游標。在 LOAD 命令中提供相應(yīng)的 SELECT 語句，就會隱式地定義游標。只有在 ADMIN_CMD 調(diào)用中嵌入 LOAD FROM CURSOR 操作的情況下，這種語法才是有效的，在命令行上是無效的。
在 LOAD 命令中通過 DATABASE 選項定義遠程數(shù)據(jù)庫。不可能指定遠程訪問所需的用戶名/密碼組合。請在測試存儲過程時觀察這一限制的影響。
但是，首先應(yīng)該在目標數(shù)據(jù)庫 MYSAMPLE 中創(chuàng)建存儲過程。
清單 19. 創(chuàng)建示例存儲過程
C:\>db2 "CONNECT TO MYSAMPLE" |
第一個測試調(diào)用失敗，返回消息 SQL30082N Security processing failed with reason "3" ("PASSWORD MISSING"). SQLSTATE=08001。
清單 20. 對示例存儲過程的第一次測試失敗
C:\>db2 "CALL FECHNER.REMOTE_LOAD_FROM_CURSOR" |
這個錯誤消息是由于建立數(shù)據(jù)庫連接的方式造成的：db2 "CONNECT TO MYSAMPLE"。在執(zhí)行 CONNECT 語句時，沒有提供用戶名和密碼，因此使用登錄操作系統(tǒng)所用的用戶名建立連接。在這種情況下，DB2 不知道此用戶的密碼。在存儲過程中執(zhí)行 LOAD FROM CURSOR 操作時，DB2 嘗試用本地用戶的授權(quán) ID 連接遠程數(shù)據(jù)庫 SAMPLE。但是，由于使用隱式的 CONNECT，DB2 不知道相應(yīng)的密碼，所以遠程訪問失敗。因此，這個錯誤的原因如下：
因為通過 ADMIN_CMD 執(zhí)行的 LOAD FROM CURSOR 操作不允許指定遠程訪問所用的用戶，所以 LOAD 操作自動地用屬于本地數(shù)據(jù)庫連接的授權(quán) ID 連接遠程數(shù)據(jù)庫。
如果本地連接的用戶執(zhí)行隱式的 CONNECT 而沒有指定密碼，DB2 就不知道他/她的密碼，因此在嘗試連接遠程數(shù)據(jù)庫時沒有密碼可用。
通過 ADMIN_CMD 執(zhí)行的遠程 LOAD FROM CURSOR 操作的這一特點(無法指定遠程訪問所用的用戶)還有一個影響：當(dāng)前連接本地數(shù)據(jù)庫的用戶必須用相同的授權(quán) ID 訪問遠程數(shù)據(jù)庫。在使用聯(lián)邦方法訪問遠程數(shù)據(jù)庫時，沒有這一限制，因為必須以用戶映射的形式定義額外的抽象層。
既然找到了錯誤的原因，就可以重新連接本地數(shù)據(jù)庫，這一次顯式地指定用戶名和密碼。對存儲過程的第二次調(diào)用應(yīng)該會成功 (Return Status = 0)，見清單 21。
清單 21. 對示例存儲過程的第二次測試成功
C:\>db2 "CONNECT TO MYSAMPLE USER fechner" |
結(jié)束語
本文通過示例場景演示了如何使用 DB2 的 LOAD FROM CURSOR 特性在一個數(shù)據(jù)庫中以及不同的數(shù)據(jù)庫之間快速輕松地復(fù)制數(shù)據(jù)。還解釋了通過 ADMIN_CMD 存儲過程在應(yīng)用程序代碼中執(zhí)行 LOAD FROM CURSOR 操作時的特殊問題。另外，還講解了如何為另一個數(shù)據(jù)庫配置聯(lián)邦訪問，從而像訪問本地表或視圖一樣透明地讀寫遠程數(shù)據(jù)庫中的表和視圖。
【編輯推薦】
