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