引言
在我們以前的文章 將 DB2 Information Integrator 用於 J2EE 開發：成 本／效益分析中，我們描述了一個 Java™ 2 企業版（J2EE）項目，它被設計成用來 評估實現需要從三種不同的數據源檢索和合並數據的 servlet 時的折中方案。我們構建了 一組 servlet，它們使用 IBM® DB2® Information Integrator 來模擬不同數據 的單點映像，並且我們構建了另一組 servlet，它們直接使用每個必要的數據源。
在本文（我們系列中的第二篇）中，我們將完整地向您講述我們開發工作的細節。 我們將回顧我們的設計、向您展示我們的部分代碼並描述我們在實現這些 servlet 時所面 臨的挑戰。在我們的下一篇文章（我們系列中的最後一篇）中，我們將討論性能問題。
快速回顧
如果您沒有機會閱讀我們的 上一篇文章，我們將在這裡對它作一 下回顧。然而，我們強烈建議您回顧一下這篇文章，因為它提出了我們想要實現的應用方 案、描述了數據分布方案並說明了我們的每個 servlet 發出的查詢。
我們的兩組 servlet 都使用存儲在 Oracle 表、DB2 表和 Microsoft® Excel 電子表格中的數據 。這些數據基於一個部件分銷的數據庫，它跟蹤部件、供應商、客戶和訂單等。
我 們發現可以使用兩種軟件體系結構（即，使用 DB2 Information Integrator 或使用直接 數據訪問）中的任一種來成功地構建使用這些不同數據的 servlet。然而，我們也發現：
當我們使用 DB2 Information Integrator 時，我們減少了大約 40% 的 servlet 代碼和至少 50% 的開發時間。
當我們使用 DB2 Information Integrator 時，我 們不必處理復雜的查詢分解問題。如果這些問題處理不當將導致錯誤的結果或糟糕的性能 ，而 DB2 Information Integrator 的全局查詢優化器能夠自動處理這些問題。
使用 DB2 Information Integrator 的 servlet 的查詢響應時間通常與我們觀察到的 直接數據訪問實現的響應時間不相上下。有時候，我們甚至觀察到使用 DB2 Information Integrator 時性能更好。就我們所期望的 J2EE 開發者對該環境的了解程度而言，值得注 意的是我們可以做一些工作來使直接數據訪問的方案很好地被執行。
總體的體系 結構和設計
我們的 servlet 用 JDBC 通過 DB2 Information Integrator 來訪問 數據或直接訪問每個數據源。只要可能，我們就使用 DataSource 連接（合用連接），它 通常能更有效地利用系統資源。我們通過在每個 servlet 的 init方法中對這些 DataSource 執行 JNDI 查詢來進一步尋求保持代碼效率。
當通過 DB2 Information Integrator 訪問遠程數據時，我們的 servlet 對跨所有必要數據源的昵稱 和昵稱的 UNION ALL 視圖發出查詢。當直接訪問遠程數據時，我們的 servlet 對每個數 據源分別發出查詢，並且將結果集插入本地 DB2 實例中的輔助表，然後查詢這些表以得到 最後的結果。這極大地簡化了我們的編程任務，因為另一方面，在我們應用程序空間中對 所有數據進行組合、排序、聚集和分組需要相當大的工作量。所以我們反過來選擇利用本 地的 DBMS 表來幫助我們完成工作，因為根據我們的判斷，DBMS 對大多數 WebSphere® 開發者是可用的。注意，我們試圖在遠程數據源上過濾掉盡可能多的數據 來最小化網絡流量並進一步提高我們的 servlet 的效率，這些 servlet 直接使用每個數 據源。
我們的所有 servlet 的表示邏輯是簡單的。我們使用 HTML 或 JSP 來獲取 我們 servlet 的輸入參數並發布結果。
完成所有設置
在我們編寫 servlet 以前，我們必須設置我們的環境。幸運的是，這些工作並不難。
對於所有的工作，我們按標准過程安裝和配置 WebSphere Studio V5 的 Application Developer 配置。我們也在相同的機器上同樣按照 DB2 Information Integrator 的標准 配置過程來安裝 DB2 Information Integrator。對一組 servlet，我們使用 DB2 Information Integrator 的聯邦技術來處理不同的數據。對於另外一組 servlet，我們僅 將 DB2 Information Integrator 用作本地數據庫存儲管理器。也就是說，在我們的 servlet 手工地連接到每個必要的源並從該源檢索數據以後，我們用 DB2 Information Integrator 來為我們的 servlet 存放輔助表。
配置 DB2 Information Integrator
要使用 DB2 Information Integrator 的聯邦功能，我們安裝必備軟件（按照產品手冊 中的定義）來訪問目標數據源。然後我們創建必要的聯邦數據對象，包括每個目標數據源 的包裝器、每個必需的服務器實例的服務器對象、用於遠程認證的用戶映射以及遠程數據 的昵稱。
在大多數公司中，通常由數據庫管理員而不是 J2EE 程序員來完成這些工作，而且 DB2 Information Integrator 提供了一個 GUI 工具來簡化這個過程。然而，我們選擇編寫通 過命令行處理器發出 SQL 語句的腳本。這裡是一些 SQL 語句，我們發出這些 SQL 語句來 配置對 Oracle DBMS 的訪問權。在腳本編制環境中，兩個連字符（“--”）表示注釋。
-- Create the Oracle wrapper object.
CREATE WRAPPER net;
-- Create the Oracle server object.
-- We need to specify a previously defined wrapper object
-- and identify valid account information for our Oracle database.
-- We also need to specify the node where the Oracle instance is running.
CREATE SERVER jaqora TYPE oracle VERSION 8 WRAPPER net8 AUTHORIZATION "demo"
PASSWORD "cdidemo" OPTIONS (NODE 'iidemo2');
-- Create a user ID mapping between a valid DB2 Information Integrator
-- account ("db2admin") and a valid Oracle account ("demo").
CREATE USER MAPPING FOR db2admin SERVER jaqora OPTIONS (REMOTE_AUTHID 'demo',
REMOTE_PASSWORD 'cdidemo');
-- Create a local DB2 Information Integrator nickname("ora_part")
-- for a remote Oracle table ("jaqora.demo.part").
CREATE NICKNAME ora_part FOR jaqora.demo.part;
有時我們需要更改 DB2 Information Integrator 執行的缺省的數據類型映射。例如， 在缺省情況下，DB2 Information Integrator 將 Oracle 表中的數字數據映射為 DB2 DOUBLE 數據類型，但是這並不總是適合於我們的工作。更改缺省的映射很簡單；我們只要 改變我們的昵稱定義，如以下所示：
-- Represent the "p_partkey" column of the "ora_part" table as an integer
-- locally
ALTER NICKNAME ora_part altER COLUMN p_partkey LOCAL TYPE integer;
我們執行類似的語句來配置對遠程 DB2 服務器和本地 Excel 電子表格（我們用 DB2 Information Integrator 的 ODBC 包裝器通過 JDBC/ODBC 橋來訪問它）的訪問權。
最後，我們的某些查詢需要使用跨我們三個數據源的昵稱的 UNION ALL 視圖。我們通 過從相關的昵稱選擇所有列並添加一個服務器屬性列來創建這些視圖，該服務器屬性列用 來跟蹤最初存放這些數據的數據源。這個添加的列幫助我們保留某些查詢的正確的語義， 我們將稍後說明這一點。以下是我們的視圖定義之一：
-- Create a consolidated view of PART data stored in a Excel, Oracle, and DB2
CREATE VIEW fed_part AS
SELECT db2_part.*, 'db2' AS p_server
FROM db2_part
UNION ALL
SELECT ora_part.*, 'ora' AS p_server
FROM ora_part
UNION ALL
SELECT odbc_part.*, 'xls' AS p_server
FROM odbc_part;
如果您想要回顧作為我們系統配置工作的一部分而發出的 SQL 語句，請參閱 附錄 A。 如果您覺得所有這些顯得太繁復了，那麼請您記住：您可以使用可生成所有這些 DDL 的 GUI。
配置直接的數據訪問環境
對於直接的數據訪問，我們也需要安裝適當的必備軟 件。這些軟件隨所涉及的數據源的不同而不同，我們可以查閱特定於供應商的產品文檔來 完成這些工作。
當連接能夠正常工作時，我們創建本地 DB2 表來存儲將從我們的 查詢返回的中間結果。這些表隨所涉及的查詢的不同而不同。我們必須考慮計劃如何分解 每個目標數據源的原始查詢，並且確保我們創建的輔助表能夠容納返回的結果。我們總是 為每個查詢創建至少一個輔助表（在某些情況下，我們創建多個輔助表）。在數據庫管理 員和 J2EE 程序員合作並理解了該程序員的需求以後，很可能由數據庫管理員來創建這些 對象。
在了解了您要在 DB2 中創建的表的結構以後，這個過程就簡單了。我們選 擇從命令行處理器發出 SQL 語句。以下是我們要創建的輔助表（它存放我們第一個查詢的 中間結果）的一個例子：
create table temp_query1 (
p_partkey integer,
p_name varchar(55),
p_mfgr char(25),
p_type varchar(25))
如果您確實要了解每個 輔助表的定義，請參閱 附錄 B。
管理連接
在試圖發出查詢以前，我們所 有的 servlet 需要建立到相應數據源的連接。只要可能，我們就使用 WebSphere 合用連 接（DataSource 對象），因為這些連接會改善系統的效率。已經證實，對於使用 DB2 Information Integrator 來訪問數據的 servlet，我們可以方便地完成這些工作。對於直 接訪問每個數據源的 servlet，我們發現只能使用 WebSphere 合用連接來訪問我們的遠程 DB2 Universal Database™（UDB）和 Oracle 數據源。對於我們的 Excel 數據，我 們必須依賴於由用戶管理的連接（JDBC 1.0 類型的連接），等一下我們會討論其原因。
通過 DB2 Information Integrator 建立連接
當使用 DB2 Information Integrator 時，我們的 servlet 只建立一個連接（連接到 DB2 Information Integrator 服務器）。只要這些 servlet 在查詢中引用一個昵稱（或 涉及昵稱的一個視圖），DB2 Information Integrator 就會自動地建立到不同數據源所需 要的連接以便能檢索相應的數據。所有這些對我們是透明的。另外，因為 DB2 Information Integrator 對於 WebSphere Studio 就像是一個標准的 DB2 UDB V8 服務器 ，所以我們只需使用內置的向導定義一個 DataSource 對象。
這裡是我們的一個 servlet（它是使用 DB2 Information Integrator 的 servlet 之 一）中的連接代碼樣本：
// set connection parameters
private String jndiname = "jdbc/db2loc";
private String user = "db2admin";
private String pwd = "db2admin";
// connection variables
...
private Connection conn = null;
private InitialContext ctx = null;
private DataSource ds = null;
// look up data source. This is called from the servlet's "init" method.
private void getDS() {
System.out.println("> Jdbc2fed -- looking up datasource");
try {
ctx = new InitialContext();
if(ds == null) ds = (DataSource)ctx.lookup(jndiname);
ctx.close();
} catch (Exception e) {
System.out.println("Could not look up datasource: " + e);
return;
}
}
// get connections
private void getConn() {
System.out.println("> Jdbc2fed -- setting up connection");
try {
conn = ds.getConnection(user, pwd);
conn.setAutoCommit(true);
...
} catch (Exception e) {
System.out.println("Error establishing connection: " + e);
return;
}
通過本機數據訪問來建立連接
當我們創建直接訪問每個目標數據源的 servlet 時，我們要做更多的工作。當然，我 們必須為每個數據源建立連接。我們創建 DataSource 對象以支持到遠程 DB2 UDB 和 Oracle 數據的合用連接。我們也創建了一個 DataSource 對象以支持到本地 DB2 Information Integrator 服務器的合用連接，我們僅用該服務器來存儲到每個後端數據源 的查詢返回的中間結果。這意味著，我們按照標准的 WebSphere Studio 開發過程總共創 建了三個 DataSource 對象。
不幸的是，我們不能為 Excel 電子表格創建 DataSource 對象。我們的 JDBC／ODBC 橋（它提供我們需要的 Excel 訪問功能）不支持對該對象的創建。所以，我們的 servlet 回過來使用 JDBC 1.0 類型的連接（由用戶管理的連接）來訪問 Excel 數據。這使得我們 要在 servlet 中維護不同的數據庫連接類型。這雖然不難，但是這確實需要一些額外的編 碼和思考。
難怪我們的連接管理代碼的大小增加了。現在我們要建立（並且在以後釋放）四個連接 ：三個合用連接和一個由用戶管理的連接。只要可能，我們使用在數組上迭代的循環來壓 縮代碼庫大小並改善以後的代碼維護。
這裡摘錄了我們為一個 servlet（它是直接訪問每個數據源的 servlet 之一）編寫的 一些連接管理代碼：
// connection data
private static String[] dbname = new String[4];
private static String[] jndiname = new String[4];
private static String[] user = new String[4];
private static String[] pwd = new String[4];
private static String driver = "";
private static String url = "";
// connection objects
private static DataSource[] ds = new DataSource[4];
private InitialContext ctx = null;
private Connection[] conn = new Connection[4];
public void init() {
// ---------------- local db2 (host for temp data) ----------
dbname[0]= "db2_loc";
jndiname[0] = "jdbc/db2loc";
user[0] = "db2admin";
pwd[0] = "db2admin";
...
// ------------------------- DB2 remote ---------------------
dbname[1]= "db2_rem";
jndiname[1]= "jdbc/db2rem";
user[1]= "demo";
pwd[1]= "db2pass";
...
// ------------------------ Oracle remote -------------------
dbname[2]= "ora_rem";
jndiname[2] = "jdbc/ora";
user[2]= "demo";
pwd[2]= "cdidemo";
// ------------------------ Excel local ----------------------
// (fall back to JDBC1 connection type as JDBC-ODBC bridge does
// not support JDBC2 style connections)
dbname[3]= "xls_loc";
driver= "sun.jdbc.odbc.JdbcOdbcDriver";
url= "jdbc:odbc:ExcelJDBC";
user[3]= "";
pwd[3]= "";
...
// perform the data source lookups.
...
}
// lookup data sources. This is called by the servlet's "init" method.
private void getDS() {
System.out.println("> Jdbc2Oem -- looking up datasources...");
...
try {
ctx = new InitialContext();
for (int i = 0; i < 3; i++) {
if(ds[i] == null) ds[i] = (DataSource)ctx.lookup(jndiname[i]);
}
ctx.close();
} catch (Exception e) {
System.out.println("Could not look up datasource: " + e);
return;
}
...
}
// establish connections
private void getConn() {
System.out.println("> Jdbc2Oem -- setting up connections...");
// establish connections to local DB2 Information Integrator,
// remote DB2 UDB, remote Oracle
try {
for (int i = 0; i < 3; i++) {
conn[i] = ds[i].getConnection(user[i], pwd[i]);
conn[i].setAutoCommit(true);
}
// load the Excel driver & establish a connection
Class.forName(driver);
conn[3] = DriverManager.getConnection(url, user[3], pwd[3]);
conn[3].setAutoCommit(true);
} catch (Exception e) {
System.out.println("Error connection: " + e);
return;
}
}
設計和分解查詢
開發檢索數據的查詢顯然是我們工作中最具挑戰性的部分。這項任務涉及到確定實現目 標查詢的正確語法和語義。（如果您已經忘了它們，請參閱 附錄 C。）
當我們通過 DB2 Information Integrator 來訪問數據時，很容易就能找到如何正確設 計每個查詢的方法。實際上，您會看到我們發出的查詢非常象原始的目標查詢。
然而，當我們使用直接數據訪問來實現這些同樣的查詢時，事情馬上變得復雜起來。我 們必須找到如何正確分解每個查詢的方法，以便從每個數據源中檢索到正確的數據，同時 保持合理的性能。然後我們必須設計在中間結果上操作的“最終”查詢。要使所有的工作 不出差錯，說得容易做起來難。當然，這些工作的副作用是，我們需要設計、開發和維護 很多額外的代碼。
好奇嗎？那麼就讓我們從使用 DB2 Informcomparetion Integrator 的 servlet 方案 開始比較一下工作量吧。
開發使用 DB2 Information Integrator 來訪問數據的查詢
使用 DB2 Information Integrator，我們將每個目標查詢轉換為一個和環境相適應的 SQL 語句。在某些情況下，只需要作很少（如果有的話）的修改。在所有情況下，我們不 必為處理不同的 SQL 方言而擔心（即使我們的數據分布在不同的數據源中），而且我們不 必知道數據的物理位置。DB2 Information Integrator 會自動解決這些問題。
查詢 1 看上去和我們原始的目標查詢非常相似，除了將參數標記轉換為一個特定的 VARCHAR 大小。這是一種性能調節機制，我們將在我們系列的下一篇文章中討論它的使用 。在使用 DB2 Information Integrator 的 servlet 中，我們如下定義第一個查詢：
query[1] = "SELECT DISTINCT p_partkey, p_name, p_mfgr, p_type " +
"FROM fed_part " +
"WHERE p_type LIKE CAST(? AS VARCHAR(25)) " +
"AND p_name LIKE CAST(? AS VARCHAR(55)) " +
"ORDER BY p_partkey " +
"FETCH FIRST 20 ROWS ONLY";
查詢 2 看上去也和原始的查詢幾乎完全相同。我們只是為添加到我們的 UNION ALL 視 圖的服務器屬性列增加了連接謂詞。正如在前面我們已經提到，我們在 Excel、Oracle 和 DB2 數據的昵稱上創建 UNION ALL 視圖，並且添加一個服務器屬性列來指示數據的原始來 源。我們這樣做是因為我們的數據分布方式模擬了由三個獨立（並在以後合並）的公司維 護的訂單記錄。從而，我們知道可能有一些客戶對不止一個原來的公司下訂單，導致重復 的 CUSTOMER 數據，但是我們也知道在相同的日期中，一個特定的客戶不會對兩個不同的 公司下同樣的訂單。通過用視圖定義維護一個服務器屬性列，我們可以確保取回正確的信 息 － 即，我們可以消除對我們的業務情況沒有意義的跨地點連接。
以下是查詢 2 的實現
query[2] ="SELECT ps_partkey, s_name, s_suppkey, " +
"MIN(ps_supplycost) AS ps_supplycost " +
"FROM fed_partsupp, fed_supplier, fed_nation " +
"WHERE ps_partkey = ? " +
"AND ps_suppkey = s_suppkey " +
"AND s_nationkey = n_nationkey " +
"AND n_name = ? " +
"AND ps_server = s_server " +
"AND s_server = n_server " +
"GROUP BY ps_partkey, s_name, s_suppkey";
查詢 3、4 和 5 事實上和它們的原始版本沒有什麼不同。我們只是用參數標記替換了 搜索謂詞值，而且對於查詢 3，同樣也是在視圖中的服務器屬性列上增加連接謂詞：
query[3] = "SELECT c_custkey, c_name, SUM(o_totalprice) AS total_ordered, " +
"COUNT(*) AS num_orders " +
"FROM fed_customer, fed_orders " +
"WHERE o_custkey = c_custkey " +
"AND o_orderdate >= ? " +
"AND o_orderdate < ? " +
"AND c_server = o_server " +
"GROUP BY c_custkey, c_name " +
"ORDER BY total_ordered DESC " +
"FETCH FIRST 10 ROWS ONLY";
query[4] = "SELECT c_custkey, c_name, o_totalprice, n_name " +
"FROM db2_customer, ora_orders, db2_nation " +
"WHERE c_nationkey = n_nationkey " +
"AND c_custkey = o_custkey " +
"AND o_totalprice > ? " +
"AND n_name in ('JAPAN', 'CHINA', 'VIETNAM', 'INDIA')";
query[5] = "SELECT AVG(o_totalprice) AS avg_order, c_name, c_custkey, " +
"c_acctbal " +
"FROM db2_customer, fed_orders, db2_nation " +
"WHERE c_custkey = o_custkey " +
"AND c_nationkey = n_nationkey " +
"AND n_name = ? " +
"AND c_mktsegment = ? " +
"AND c_acctbal >>= ? " +
"AND c_acctbal <= ? " +
"GROUP BY c_custkey, c_name, c_acctbal " +
"ORDER BY avg_order DESC";
開發使用直接數據訪問的查詢
當我們直接使用每個數據源時，查詢開發任務變得更加艱苦了。
查詢 1 很簡單，僅涉及 PART 數據。即使這樣，我們還是必須寫四條 SELECT 語句、 一條 INSERT 語句和一條 DELETE 語句來完成我們的工作。如果您仔細觀察從每個後端數 據源檢索數據的三條 SELECT 語句，您會看到由於 SQL 方言的差異，這三條語句略有不同 。尤其請注意 DB2 UDB 和 Oracle 在支持檢索結果集的前 20 行方面是如何的不同。也要 注意對於 Excel 這項功能不可用。
最後，我們將提到取消限制每個數據源返回的結果集的大小並不總是對的。它對這個查 詢的語義沒有損害，但是對查詢 3 的語義卻有損害，我們將在稍後討論這些。
以下是使用直接數據訪問來支持查詢 1 的 servlet 的 SQL 代碼：
// ------------------------- DB2 remote -------------------------
// select for query #1
query1[1]= "SELECT DISTINCT p_partkey, p_name, p_mfgr, p_type " +
"FROM tpcd.part " +
"WHERE p_type LIKE ? " +
"AND p_name LIKE ? " +
"ORDER BY p_partkey " +
"FETCH FIRST 20 ROWS ONLY";
// ------------------------ Oracle remote -----------------------
// select for query #1
query1[2]= "SELECT * FROM (" +
"SELECT p_partkey, p_name, p_mfgr, p_type " +
"FROM part " +
"WHERE p_type LIKE ? " +
"AND p_name LIKE ? " +
"ORDER BY p_partkey" +
") WHERE ROWNUM <= 20";
// ------------------------ Excel local -----------------------
// select for query #1
query1[3]= "SELECT p_partkey, p_name, p_mfgr, p_type " +
"FROM [part$] " +
"WHERE p_type LIKE ? " +
"AND p_name LIKE ?";
// insert statement for intermediate data for query #1
temp_insert[1]= "INSERT INTO temp_query1(p_partkey, p_name,
p_mfgr, p_type) "
+ "VALUES(?, ?, ?, ?)";
// final statement for query #1
temp_select[1]= "SELECT DISTINCT * " +
"FROM temp_query1 " +
"ORDER BY p_partkey " +
"FETCH FIRST 20 ROWS ONLY";
// delete statements for temporary table
temp_delete[1] = "DELETE FROM temp_query1";
查詢 2 沒有任何特別的問題。從概念上講，它的實現和查詢 1 幾乎相同。我們必須寫 四條 SELECT 語句、一條 INSERT 語句和一條 DELETE 語句來完成我們的工作。對於其中 的三條 SELECT 語句，我們實際上取用查詢 2 的原始版本、用參數標記替換硬編碼搜索值 、修改 Excel 數據的 FROM 子句的語法之後這些語句就可以執行了。INSERT 語句只是從 每個數據源取得結果並將它們存放到本地輔助表。我們的第四條 SELECT 語句對該表執行 操作並且該語句的語法是原始查詢簡略版本。（我們省略了連接和搜索謂詞，因為它們已 經在前三條 SELECT 語句中應用了。）最後，我們發出一條簡單的 DELETE 語句來清除輔 助表中的內容。
為了保證完整性，我們在下面列出了我們的 servlet 中查詢 2 的 SQL 代碼。
// ------------------------- DB2 remote -------------------------
// select for query #2
query2[1]= "SELECT ps_partkey, s_name, s_suppkey, MIN(ps_supplycost)
AS min_supplycost " +
"FROM tpcd.partsupp, tpcd.supplier, tpcd.nation " +
"WHERE ps_partkey = ? " +
"AND ps_suppkey = s_suppkey " +
"AND s_nationkey = n_nationkey " +
"AND n_name = ? " +
"GROUP BY ps_partkey, s_name, s_suppkey";
// ------------------------ Oracle remote ------------------------
// select for query #2
query2[2]= "SELECT ps_partkey, s_name, s_suppkey, MIN(ps_supplycost)
AS min_supplycost " +
"FROM partsupp, supplier, nation " +
"WHERE ps_partkey = ? " +
"AND ps_suppkey = s_suppkey " +
"AND s_nationkey = n_nationkey " +
"AND n_name = ? " +
"GROUP BY ps_partkey, s_name, s_suppkey";
// ------------------------ Excel local ---------------------------
// select for query #2
query2[3]= "SELECT ps_partkey, s_name, s_suppkey, MIN(ps_supplycost)
AS min_supplycost " +
"FROM [partsupp$], [supplier$], [nation$] " +
"WHERE ps_partkey = ? " +
"AND ps_suppkey = s_suppkey " +
"AND s_nationkey = n_nationkey " +
"AND n_name = ? " +
"GROUP BY ps_partkey, s_name, s_suppkey";
// insert statement for intermediate data for query #2
temp_insert[2]= "INSERT INTO temp_query2(ps_partkey, s_name, " +
"s_suppkey, min_supplycost) " +
"VALUES(?, ?, ?, ?)";
// final statement for query #2
temp_select[2]= "SELECT ps_partkey, s_name, s_suppkey, MIN(min_supplycost)
AS ps_supplycost " +
"FROM temp_query2 " +
"GROUP BY ps_partkey, s_name, s_suppkey";
// delete statements for temporary tables
temp_delete[2] = "DELETE FROM temp_query2";
查詢 3 是更有趣的情況。可能您還記得，該查詢設計成在一定時間段內確定最佳的 10 個客戶 － 即，在某個時間段內向我們的合並公司訂貨金額最高的客戶。如果我們對一個 單獨的表（或 DB2 Information Integrator 提供的一個單獨的虛擬表）發出該查詢，我 們累計每個客戶的所有訂單、按降序返回結果並將返回的行數限制為 10。
但是當我們直接對不同的數據源提出這個問題時，事情變得更加復雜了。我們不能只發 出基本查詢而使其 ORDER BY 子句和結果大小限制保持原樣。為什麼？這是因為（如果這 樣做）我們可能得到不正確的結果。如果一個客戶向我們合並前的三個公司分別下了訂單 ，這些訂單不能列入每個公司的前 10 位，但是當它們加起來產生的總和卻排到合並後公 司的前 10 位，這種情況下怎麼辦？而我們不想遺漏這些客戶。要確保我們不遺漏這些客 戶唯一的方法是，我們從每個數據源檢索所有滿足條件的行並在合並（中間）的結果集上 應用 ORDER BY 和大小限制子句。
當然，如以下代碼說明的那樣，我們就是這樣做的。我們必須考慮好原始查詢的語義以 確保沒有違背我們的初衷，但是這並不是那麼明顯的。僅把整個查詢按照其原始的狀態在 每個目標數據源上執行，這本是很容易而且誘人的。畢竟這對查詢 1（它也有結果集大小 限制）是可行的。但是在這裡行不通，其原因我們剛才已經說明。要說明其中的區別需要 時間、精力和技巧 － 也許比馬上就能感覺到的還要多。
// ------------------------- DB2 remote -------------------------
// select for query #3
query3[1]= "SELECT SUM(o_totalprice) AS total_ordered, COUNT(*) AS
num_orders, c_custkey, c_name " +
"FROM tpcd.customer, tpcd.orders " +
"WHERE o_custkey = c_custkey " +
"AND o_orderdate >= ? " +
"AND o_orderdate < ? " +
"GROUP BY c_custkey, c_name";
// ------------------------ Oracle remote -------------------------
// select for query #3
query3[2]= "SELECT c_custkey, c_name, SUM(o_totalprice) AS total_ordered,
COUNT(*) AS num_orders " +
"FROM customer, orders " +
"WHERE o_custkey = c_custkey " +
"AND o_orderdate >= ? " +
"AND o_orderdate < ? " +
"GROUP BY c_custkey, c_name";
// ------------------------ Excel local ---------------------------
// select for query #3
query3[3]= "SELECT c_custkey, c_name, SUM(o_totalprice) AS total_ordered,
COUNT(*) AS num_orders " +
"FROM [customer$], [orders$] " +
"WHERE o_custkey = c_custkey " +
"AND o_orderdate >= ? " +
"AND o_orderdate < ? " +
"GROUP BY c_custkey, c_name ";
// insert statement for intermediate data for query #3
temp_insert[3]= "INSERT INTO temp_query3(c_custkey, c_name, total_ordered,
num_orders) " +
"VALUES(?, ?, ?, ?)";
// final statement for query #3
temp_select[3]= "SELECT c_custkey, c_name, SUM(total_ordered) AS
total_ordered, SUM(num_orders) AS num_orders " +
"FROM temp_query3 " +
"GROUP BY c_custkey, c_name " +
"ORDER BY total_ordered DESC " +
"FETCH FIRST 10 ROWS ONLY";
// delete statements for temporary tables
temp_delete[3] = "DELETE FROM temp_query3";
查詢 4 和 5 與前三個查詢在某些重要的方面是不同的。前三個查詢只對三個數據源的 數據的昵稱的 UNION ALL 視圖進行操作。查詢 4 執行跨地點連接；也就是說，它處理由 遠程 DB2 UDB 系統管理的某些數據和遠程 Oracle 系統管理的某些數據。它不使用 UNION ALL 視圖。查詢 5 使用了這樣的一個視圖，但是將該視圖和特定於某個數據源的數據相連 接。這些查詢的性質迫使我們修改我們的 servlet 邏輯。
我們將查詢 4 分成兩段：一段包含特定於遠程 DB2 服務器的所有搜索謂詞，另外一段 包含特定於遠程 Oracle 服務器的所有搜索謂詞。我們為每段構造 SELECT 語句，對遠程 DB2 UDB 和 Oracle 數據庫執行這些語句，並將結果插入我們本地 DB2 系統的輔助表。然 後我們發出最後的 SELECT 語句，該語句連接需要滿足我們原始查詢的輔助表。
如果您做過很多的 DBMS 應用程序編程工作，那麼您知道這是我們能編寫 servlet 邏 輯的唯一方法。我們考慮過其它可選的方法，例如在我們的 servlet 中編寫等效的嵌套循 環，但是我們不信這裡面有全勝的贏家。如果我們預先知道 DB2 UDB 或 Oracle 查詢都將 返回一個很小的結果集，那麼我們可以推斷，如果我們使用這些結果進一步限制其它數據 源返回滿足條件的行的數目，那麼將改善性能。
但是我們無法知道這些。我們無法訪問全局統計信息，而且我們不能假定 J2EE 開發者 熟悉數據分布方案或底層的業務活動（它們引起數據這樣或那樣的偏離）。此外，我們的 servlet 使用參數標記來容納用戶可能輸入的不同搜索值。這進一步禁止我們預先知道從 任一遠程數據源返回的結果集會很小。
因而，我們必須有些盲目地選擇一個合理的實現。此外，值得注意的是，即使我們以某 種方式知道（或正確地猜測到）結果集的大小，我們也還是不能保證修改過的實現經過一 段時間後性能不會降低。畢竟，在遠程 Oracle 和 DB2 UDB 系統中的數據可能會改變；結 果，以前運行得很好的 servlet 代碼可能工作得不再理想了。
使用 DB2 Information Integrator，我們不必擔心數據訪問的策略，因為系統的全局 優化器負責分析不同的訪問途徑並選擇有效率的一個。
以下摘錄了我們的 servlet 中包含的查詢 4 的 SQL 代碼：
// statements for query #4
q2db2fetch = conn[1].prepareStatement("SELECT c_custkey, c_name, n_name " +
" FROM tpcd.customer, tpcd.nation " +
" WHERE c_nationkey = n_nationkey " +
" AND n_name IN ('JAPAN', 'CHINA', 'VIETNAM', 'INDIA')");
q2orafetch = conn[2].prepareStatement("SELECT o_custkey, o_totalprice " +
" FROM orders WHERE o_totalprice > ? ");
q2insertfromdb2 = conn[0].prepareStatement("INSERT INTO temp_jquery2a " +
" (c_custkey, c_name, n_name) VALUES (?, ?, ?)");
q2insertfromora = conn[0].prepareStatement("INSERT INTO temp_jquery2b " +
" (o_custkey, o_totalprice) VALUES (?,?)");
q2tempselect = conn[0].prepareStatement("SELECT c_custkey, c_name, " +
" o_totalprice, n_name " +
" FROM temp_jquery2a, temp_jquery2b " +
" WHERE c_custkey = o_custkey");
q2tempdelete1 = conn[0].prepareStatement("DELETE FROM temp_jquery2a");
q2tempdelete2 = conn[0].prepareStatement("DELETE FROM temp_jquery2b");
最後，查詢 5 結果是這幾個查詢中最有技巧的一個。這裡我們需要找出訂單的平均成 本，這些訂單是一部分滿足條件的客戶對整個（合並）公司下的。我們不能僅僅拿出針對 每個數據源而計算的平均值；當我們跨 所有數據源來計算每個滿足條件的客戶的訂單平均 值時，我們最終得到的結果將是不正確的。但是不能成功地將某些聚集操作疊加到每個數 據源將產生大量的數據傳輸，其中很多是不必要的。
相反，我們決定對每個後端數據源發出 COUNT(*) 和 SUM 函數來計算每個客戶的訂單 數和這些訂單的總成本。通過合並這些信息（以及我們客戶的其它數據），我們可以跨所 有數據源來計算每個滿足條件的客戶所下的所有訂單的平均金額。
認識到要按這種方式來修改我們的查詢以保留我們的語義和維持合理的性能需要一些思 考。但是這是事情的唯一部分。因為原始的查詢按其它方式限制滿足條件的客戶，我們想 要利用這些增加的搜索謂詞來進一步最小化不必要的數據傳輸。
最後，我們為遠程 DB2 UDB 編寫了一個非常具有限制性的查詢，來獲取所有滿足條件 的客戶和有關訂單數量信息以及他們所下訂單的總成本信息。這些信息放入一個輔助表。 然後我們能夠使用此表中的唯一客戶標識（CUSTKEY 信息）來進一步限制從我們的 Oracle 和 Excel 系統返回的結果。如果我們在那裡找到任何匹配的行，那麼我們對每個客戶計算 訂單的總數和這些訂單的總成本，並更新輔助表中的數據來反映這一點。然後我們能夠使 用這些數據來計算出我們需要的信息。
所有這些工作具有類似於在我們的 servlet 加入嵌套循環連接所具有的實際效果。最 終我們有五條 SELECT 語句、一條 INSERT 語句、一條 UPDATE 語句和一條 DELETE 語句 。將這些和我們為這個查詢的 DB2 Information Integrator 版本所發出的單個 SELECT 語句進行比較，您對這種方法所增加的成本會有所了解。當然，這還沒有考慮我們為了正 確執行這些查詢並得到正確結果所必須設計和實現的額外邏輯。
以下是我們從使用直接數據訪問來實現查詢 5 的 servlet 中抽取的 SQL 代碼：
// statements for query #5
q3db2fetch = conn[1].prepareStatement("SELECT COUNT(*) AS num_order, " +
" SUM(o_totalprice) AS total_order, c_name, c_custkey, c_acctbal " +
" FROM tpcd.customer, tpcd.orders, tpcd.nation " +
" WHERE c_custkey = o_custkey " +
" AND c_nationkey = n_nationkey " +
" AND n_name = ? AND c_mktsegment = ? " +
" AND c_acctbal >= ? AND c_acctbal <= ? " +
" GROUP BY c_custkey, c_name, c_acctbal + +
" ORDER BY total_order, num_order");
q3orafetch = conn[2].prepareStatement("SELECT COUNT(*) AS num_order, " +
" SUM(o_totalprice) AS total_order, o_custkey " +
" FROM orders WHERE o_custkey = ? " +
" GROUP BY o_custkey");
q3xlsfetch = conn[3].prepareStatement("SELECT COUNT(*) AS num_order, " +
" SUM(o_totalprice) AS total_order, o_custkey " +
" FROM [orders$] WHERE o_custkey = ? " +
" GROUP BY o_custkey");
q3tempfetch = conn[0].prepareStatement("SELECT c_custkey FROM temp_jquery3");
q3insertfromdb2 = conn[0].prepareStatement("INSERT INTO temp_jquery3 " +
" (c_custkey, c_name, c_acctbal, total_order, num_order) " +
" VALUES (?, ?, ?, ?, ?)");
q3update = conn[0].prepareStatement("UPDATE temp_jquery3 " +
" SET num_order = num_order + ?, total_order = total_order + ? " +
" WHERE c_custkey = ?");
q3tempselect = conn[0].prepareStatement("SELECT AVG(total_order/num_order) " +
" AS avg_order, c_name, c_custkey, c_acctbal " +
" FROM temp_jquery3 " +
" GROUP BY c_custkey, c_name, c_acctbal " +
" ORDER BY avg_order DESC");
q3tempdelete = conn[0].prepareStatement("DELETE FROM temp_jquery3");
執行查詢
如果您還在，恭喜您。我們知道這需要了解很多東西。但是您幾乎要通過最後一關了。 我們只想簡要地談談用什麼來執行我們在上一節展示的這些查詢。
同樣的，您可能差不多預料到了這種情況：使用 DB2 Information Integrator 的 servlet 只需要做很少的工作，而直接訪問每個數據源的 servlet 要做多得多的工作。我 們不會在這裡一個接著一個地向您展示查詢，因為很多代碼是重復的。相反，我們只是首 先從 DB2 Information Integrator 開始討論一些有趣的案例。
使用 DB2 Information Integrator
每個為支持 DB2 Information Integrator 所寫的 servlet 只需要一種方法來執行其 支持的任一查詢。（根據我們的項目的性質，我們在一個 servlet 中實現查詢 1 到 3， 並在另一個 servlet 中實現查詢 4 和 5。我們還有兩個 servlet 來實現直接數據訪問： 一個支持查詢 1 到 3，另一個則支持查詢 4 和 5）。用戶輸入使我們的 servlet 能夠確 定要執行它所支持的哪個查詢。
當對我們的查詢語句對象應用了參數標記後，我們執行了語句並返回結果：
stmt[choice].execute();
return outputResult(choice);
對於我們的兩個 DB2 Information Integrator servlet 中的 executeQuery() 方法來 說，此代碼是完全相同的。
使用直接數據訪問
對每個查詢使用不同的方法，為直接訪問每個數據源所寫的 servlet 是最容易實現的 。這是因為每個查詢所返回的中間結果集都是不同的，這些結果需要插入到輔助表中來進 行進一步處理。
當對我們的查詢語句對象應用了參數標記後，我們寫了如下代碼來執行查詢 1：
ResultSet rs = null;
int rows = 0;
...
// fetch the data from each data source and insert it into temp table
for(int i = 1; i < dbname.length; i++) {
fetch1[i].execute();
rs = fetch1[i].getResultSet();
rows = 0;
while (rs.next()) {
insert[1].setInt(1, new Double(rs.getString("p_partkey")).intValue ());
insert[1].setString(2, rs.getString("p_name"));
insert[1].setString(3, rs.getString("p_mfgr"));
insert[1].setString(4, rs.getString("p_type"));
insert[1].executeUpdate();
rows++;
}
System.out.println("> " + rows + " rows inserted from " + dbname [i]);
}
if (rs != null) { rs.close(); }
return outputResult(1);
執行查詢 2 和 3 的邏輯與之非常相似，因此我們不在這裡重復了。查詢 4 和 5 的性 質有很大不同，並且查詢 5 肯定是所有查詢中最困難的。為了使您了解我們需要進行什麼 樣的編碼，以下是對執行查詢 5 的 servlet 方法的節選。同樣地，我們已經除去了讀取 用戶的輸入參數並設置這些值的代碼。
// code for query #5 execution
private String executeQuery3(HttpServletRequest req)
throws SQLException,
ParseException {
ResultSet rs = null;
int rows = 0;
// read the parameters
...
// insert from DB2
q3db2fetch.execute();
rs = q3db2fetch.getResultSet();
while (rs.next() ) {
Q3insertfromdb2.setInt(1,new
Double(rs.getString("c_custkey")).intValue());
q3insertfromdb2.setString(2, rs.getString("c_name"));
q3insertfromdb2.setDouble(3, new
Double(rs.getString("c_acctbal")).doubleValue());
q3insertfromdb2.setDouble(4, new
Double(rs.getString("total_order")).doubleValue());
q3insertfromdb2.setInt(5, new
Double(rs.getString("num_order")).intValue());
q3insertfromdb2.executeUpdate();
rows++;
}
System.out.println("rows inserted from db2: " + rows);
// for each c_custkey from temp table, select/update
from ora/odbc
q3tempfetch.execute();
rs = q3tempfetch.getResultSet();
rows = 0;
while (rs.next()) {
ResultSet rstemp = null;
// do oracle
q3orafetch.setInt(1, new
Double(rs.getString("c_custkey")).intValue());
q3orafetch.execute();
rstemp = q3orafetch.getResultSet();
if(rstemp.next()) {
q3update.setInt(1, new
Double(rstemp.getString("num_order")).intValue());
q3update.setDouble(2, new
Double(rstemp.getString("total_order")).doubleValue());
q3update.setInt(3, new
Double(rs.getString("c_custkey")).intValue());
System.out.println("update values from
ora for key " +
rs.getString("c_custkey") + " / " + rstemp.getString("num_order")
+ " / " +
rstemp.getString("total_order"));
}
// do excel
q3xlsfetch.setInt(1, new
Double(rs.getString("c_custkey")).intValue());
q3xlsfetch.execute();
rstemp = q3xlsfetch.getResultSet();
if(rstemp.next()) {
q3update.setInt(1, new
Double(rstemp.getString("num_order")).intValue());
q3update.setDouble(2, new
Double(rstemp.getString("total_order")).doubleValue());
q3update.setInt(3, new
Double(rs.getString("c_custkey")).intValue());
System.out.println("update values from
xls for key " +
rs.getString("c_custkey") + " / " + rstemp.getString("num_order")
+ " / " +
rstemp.getString("total_order"));
}
rows++;
System.out.println("row #" + rows + " checked");
}
System.out.println("rows checked for update by ora/xls: " + rows);
// process our final join query
q3tempselect.execute();
rs = q3tempselect.getResultSet();
// output result
String text = outputResult(rs);
if (rs != null) rs.close();
// cleanup
q3tempdelete.execute();
return text;
}
比較實現成本
如果您對在我們的項目中所需要編寫的代碼數量感到好奇，我們可以告訴您這個數目。 我們在一組 servlet 中實現前三個查詢，並在另一組 servlet 中實現查詢 4 和 5。
使用 DB2 Information Integrator 來進行數據訪問的前三個查詢的 servlet 由 289 行代碼組成；直接訪問每個數據源的前三個查詢的 servlet 由 521 行代碼組成。使用 DB2 Information Integrator 的最後兩個查詢的 servlet 由 280 行代碼組成，而直接訪 問數據的後兩個查詢的 servlet 由 430 行代碼組成。
當然，這不能完全反映出實現其中每個 servlet 所需的工作量。當直接處理每一個數 據源時，很難記錄分解查詢所需的“報文延遲時間”，而且我們經常會發現我們的設計、 編碼和測試的周期是不斷重復的。然而，我們通常花費大約雙倍的時間才能使直接數據訪 問 servlet 正常工作。對於復雜查詢（例如查詢 5），我們的實現階段則須花費更長的時 間。
總結
那麼，到目前為止，您應該對於我們的 servlet 是如何實現的有了一個比較清楚的了 解。當使用 DB2 Information Integrator 來幫助我們從三個不同的數據源訪問和合並信 息時，我們發現很顯然工作變得更簡單，而且更快了。我們減少了大約 40% 必須實現的代 碼行，並且減少了更多的實現時間。
我們節省的大部分工作量是由於能夠使用 DB2 Information Integrator 來更加抽象地 工作。我們不需要考慮數據的位置，我們不需要確定怎樣正確地分解對每個數據源的查詢 ，我們無須擔心數據訪問策略，而且我們甚至不必費心去考慮 SQL 方言的不同。DB2 Information Integrator 負責為我們處理所有這些內容。
那麼，我們得到了什麼呢？是的，我們的確需要安裝和配置一個 DB2 Information Integrator 環境。但是，這只是一次性工作，我們希望有一個數據庫或系統管理員至少承 擔了生產環境中的部分工作。但是，我們不必將就糟糕的性能，也許這與您不謀而合。請 繼續關注本系列中最後一篇文章，我們將在其中討論不同的查詢實現的性能問題。
附錄 A. 配置我們的 DB2 Information Integrator 環境的 SQL 語句
----------------------------------------------------
-- 1. get database connection
----------------------------------------------------
CONNECT TO test USER db2admin USING db2pwd;
----------------------------------------------------
-- 2.1. DB2 remote server
-- Node & authorization information here have been changed
from our in-house environment
----------------------------------------------------
-- catalog the node
UNCATALOG NODE jaqdb2;
CATALOG TCPIP NODE jaqdb2 REMOTE 9.99.99.91 SERVER 50000;
-- catalog the database
UNCATALOG DATABASE tpcd;
CATALOG DATABASE tpcd AT NODE jaqdb2;
-- create DB2 wrapper
DROP WRAPPER drda;
CREATE WRAPPER drda;
-- create DB2 server definition
CREATE SERVER jaqdb2 TYPE db2/udb VERSION 8.1 WRAPPER drda
AUTHORIZATION "demo"
PASSWORD "cdidemo" OPTIONS (DBNAME 'tpcd');
-- create User mapping
CREATE USER MAPPING FOR db2admin SERVER jaqdb2 OPTIONS
(REMOTE_AUTHID 'demo',
REMOTE_PASSWORD 'cdidemo');
-- create nicknames
CREATE NICKNAME db2_part FOR jaqdb2.tpcd.part;
CREATE NICKNAME db2_supplier FOR jaqdb2.tpcd.supplier;
CREATE NICKNAME db2_partsupp FOR jaqdb2.tpcd.partsupp;
CREATE NICKNAME db2_nation FOR jaqdb2.tpcd.nation;
CREATE NICKNAME db2_region FOR jaqdb2.tpcd.region;
CREATE NICKNAME db2_customer FOR jaqdb2.tpcd.customer;
CREATE NICKNAME db2_orders FOR jaqdb2.tpcd.orders;
----------------------------------------------------
-- 2.2. Oracle remote server
----------------------------------------------------
-- create Oracle wrapper
DROP WRAPPER net;
CREATE WRAPPER net;
-- create Oracle server definition
CREATE SERVER jaqora TYPE oracle VERSION 8 WRAPPER net8
AUTHORIZATION "demo"
PASSWORD "cdidemo" OPTIONS (NODE 'iidemo2');
-- create user mapping
CREATE USER MAPPING FOR db2admin SERVER jaqora OPTIONS
(REMOTE_AUTHID 'demo',
REMOTE_PASSWORD 'cdidemo');
-- create nicknames
CREATE NICKNAME ora_part FOR jaqora.demo.part;
CREATE NICKNAME ora_supplier FOR jaqora.demo.supplier;
CREATE NICKNAME ora_partsupp FOR jaqora.demo.partsupp;
CREATE NICKNAME ora_customer FOR jaqora.demo.customer;
CREATE NICKNAME ora_orders FOR jaqora.demo.orders;
CREATE NICKNAME ora_lineitem FOR jaqora.demo.lineitem;
CREATE NICKNAME ora_nation FOR jaqora.demo.nation;
-- adjust data type to match other dbms
ALTER NICKNAME ora_part altER COLUMN p_partkey LOCAL TYPE
integer;
ALTER NICKNAME ora_part altER COLUMN p_size LOCAL TYPE
integer;
ALTER NICKNAME ora_partsupp altER COLUMN ps_partkey LOCAL TYPE
integer;
ALTER NICKNAME ora_partsupp altER COLUMN ps_suppkey LOCAL TYPE
integer;
ALTER NICKNAME ora_partsupp altER COLUMN ps_availqty LOCAL TYPE
integer;
ALTER NICKNAME ora_supplier altER COLUMN s_suppkey LOCAL TYPE
integer;
ALTER NICKNAME ora_supplier altER COLUMN s_nationkey LOCAL TYPE
integer;
ALTER NICKNAME ora_nation altER COLUMN n_nationkey LOCAL TYPE
integer;
ALTER NICKNAME ora_nation altER COLUMN n_regionkey LOCAL TYPE
integer;
ALTER NICKNAME ora_orders altER COLUMN o_orderdate LOCAL TYPE
date;
ALTER NICKNAME ora_orders altER COLUMN o_custkey LOCAL TYPE
integer;
ALTER NICKNAME ora_orders altER COLUMN o_orderkey LOCAL TYPE
integer;
ALTER NICKNAME ora_orders altER COLUMN o_shippriority LOCAL TYPE
integer;
ALTER NICKNAME ora_customer altER COLUMN c_custkey LOCAL TYPE
integer;
ALTER NICKNAME ora_customer altER COLUMN c_nationkey LOCAL TYPE
integer;
----------------------------------------------------
-- 2.3. ODBC local data source (Excel)
----------------------------------------------------
-- create ODBC wrapper
DROP WRAPPER odbc;
CREATE WRAPPER odbc LIBRARY 'db2rcodbc.dll';
-- create ODBC server
CREATE SERVER jaqodbc TYPE odbc VERSION 3.0 WRAPPER odbc OPTIONS
(NODE 'ExcelDB2',
DB2_NULL_VARIANT 'Y', DB2_VARIANT 'Y', PUSHDOWN 'Y');
-- create nicknames
CREATE NICKNAME odbc_part FOR jaqodbc.part$;
CREATE NICKNAME odbc_supplier FOR jaqodbc.supplier$;
CREATE NICKNAME odbc_orders FOR jaqodbc.orders$;
CREATE NICKNAME odbc_partsupp FOR jaqodbc.partsupp$;
CREATE NICKNAME odbc_customer FOR jaqodbc.customer$;
CREATE NICKNAME odbc_nation FOR jaqodbc.nation$;
-- adjust nicknames to correct types
ALTER NICKNAME odbc_part altER COLUMN p_partkey LOCAL TYPE
integer;
ALTER NICKNAME odbc_part altER COLUMN p_name LOCAL TYPE
varchar(55);
ALTER NICKNAME odbc_part altER COLUMN p_mfgr LOCAL TYPE
char(25);
ALTER NICKNAME odbc_part altER COLUMN p_brand LOCAL TYPE
char(10);
ALTER NICKNAME odbc_part altER COLUMN p_type LOCAL TYPE
varchar(25);
ALTER NICKNAME odbc_part altER COLUMN p_size LOCAL TYPE
integer;
ALTER NICKNAME odbc_part altER COLUMN p_container LOCAL TYPE
char(10);
ALTER NICKNAME odbc_part altER COLUMN p_retailprice LOCAL TYPE
decimal(10,4);
ALTER NICKNAME odbc_part altER COLUMN p_comment LOCAL TYPE
varchar(23);
--
ALTER NICKNAME odbc_customer altER COLUMN c_custkey LOCAL TYPE
integer;
ALTER NICKNAME odbc_customer altER COLUMN c_name LOCAL TYPE
char(25);
ALTER NICKNAME odbc_customer altER COLUMN c_address LOCAL TYPE
varchar(40);
ALTER NICKNAME odbc_customer altER COLUMN c_nationkey LOCAL TYPE
integer;
ALTER NICKNAME odbc_customer altER COLUMN c_phone LOCAL TYPE
char(15);
ALTER NICKNAME odbc_customer altER COLUMN c_acctbal LOCAL TYPE
decimal(10,4);
ALTER NICKNAME odbc_customer altER COLUMN c_mktsegment LOCAL TYPE
char(10);
ALTER NICKNAME odbc_customer altER COLUMN c_comment LOCAL TYPE
varchar(117);
--
ALTER NICKNAME odbc_partsupp altER COLUMN ps_partkey LOCAL TYPE
integer;
ALTER NICKNAME odbc_partsupp altER COLUMN ps_suppkey LOCAL TYPE
integer;
ALTER NICKNAME odbc_partsupp altER COLUMN ps_availqty LOCAL TYPE
integer;
ALTER NICKNAME odbc_partsupp altER COLUMN ps_supplycost LOCAL TYPE
decimal(10,4);
ALTER NICKNAME odbc_partsupp altER COLUMN ps_comment LOCAL TYPE
varchar(199);
--
ALTER NICKNAME odbc_nation altER COLUMN n_nationkey LOCAL TYPE
integer;
ALTER NICKNAME odbc_nation altER COLUMN n_name LOCAL TYPE
char(25);
ALTER NICKNAME odbc_nation altER COLUMN n_regionkey LOCAL TYPE
integer;
ALTER NICKNAME odbc_nation altER COLUMN n_comment LOCAL TYPE
varchar(152);
--
ALTER NICKNAME odbc_orders altER COLUMN o_orderkey LOCAL TYPE
integer;
ALTER NICKNAME odbc_orders altER COLUMN o_custkey LOCAL TYPE
integer;
ALTER NICKNAME odbc_orders altER COLUMN o_orderstatus LOCAL TYPE
char(1);
ALTER NICKNAME odbc_orders altER COLUMN o_totalprice LOCAL TYPE
decimal(10,4);
ALTER NICKNAME odbc_orders altER COLUMN o_orderdate LOCAL TYPE
date;
ALTER NICKNAME odbc_orders altER COLUMN o_orderpriority LOCAL TYPE
char(15);
ALTER NICKNAME odbc_orders altER COLUMN o_clerk LOCAL TYPE
char(15);
ALTER NICKNAME odbc_orders altER COLUMN o_shippriority LOCAL TYPE
integer;
ALTER NICKNAME odbc_orders altER COLUMN o_comment LOCAL TYPE
varchar(79);
--
ALTER NICKNAME odbc_supplier altER COLUMN s_suppkey LOCAL TYPE
integer;
ALTER NICKNAME odbc_supplier altER COLUMN s_name LOCAL TYPE
char(25);
ALTER NICKNAME odbc_supplier altER COLUMN s_address LOCAL TYPE
varchar(40);
ALTER NICKNAME odbc_supplier altER COLUMN s_nationkey LOCAL TYPE
integer;
ALTER NICKNAME odbc_supplier altER COLUMN s_phone LOCAL TYPE
char(15);
ALTER NICKNAME odbc_supplier altER COLUMN s_acctbal LOCAL TYPE
decimal(10,4);
ALTER NICKNAME odbc_supplier altER COLUMN s_comment LOCAL TYPE
varchar(101);
----------------------------------------------------
-- 3. Union views over federated nicknames
----------------------------------------------------
--
-- for query #1
DROP VIEW fed_part;
CREATE VIEW fed_part AS
SELECT db2_part.*, 'db2' AS p_server
FROM db2_part
UNION ALL
SELECT ora_part.*, 'ora' AS p_server
FROM ora_part
UNION ALL
SELECT odbc_part.*, 'xls' AS p_server
FROM odbc_part;
--
-- for query #2
DROP VIEW fed_partsupp;
CREATE VIEW fed_partsupp AS
SELECT db2_partsupp.*, 'db2' AS ps_server
FROM db2_partsupp
UNION ALL
SELECT ora_partsupp.*, 'ora' AS ps_server
FROM ora_partsupp
UNION ALL
SELECT odbc_partsupp.*, 'xls' AS ps_server
FROM odbc_partsupp;
--
DROP VIEW fed_supplier;
CREATE VIEW fed_supplier AS
SELECT db2_supplier.*, 'db2' AS s_server
FROM db2_supplier
UNION ALL
SELECT ora_supplier.*, 'ora' AS s_server
FROM ora_supplier
UNION ALL
SELECT odbc_supplier.*, 'xls' AS s_server
FROM odbc_supplier;
--
DROP VIEW fed_nation;
CREATE VIEW fed_nation AS
SELECT db2_nation.*, 'db2' AS n_server
FROM db2_nation
UNION ALL
SELECT ora_nation.*, 'ora' AS n_server
FROM ora_nation
UNION ALL
SELECT odbc_nation.*, 'xls' AS n_server
FROM odbc_nation;
--
-- for query #3
DROP VIEW fed_customer;
CREATE VIEW fed_customer AS
SELECT db2_customer.*, 'db2' AS c_server
FROM db2_customer
UNION ALL
SELECT ora_customer.*, 'ora' AS c_server
FROM ora_customer
UNION ALL
SELECT odbc_customer.*, 'xls' AS c_server
FROM odbc_customer;
--
DROP VIEW fed_orders;
CREATE VIEW fed_orders AS
SELECT db2_orders.*, 'db2' AS o_server
FROM db2_orders
UNION ALL
SELECT ora_orders.*, 'ora' AS o_server
FROM ora_orders
UNION ALL
SELECT odbc_orders.*, 'xls' AS o_server
FROM odbc_orders;
附錄 B. 直接數據訪問的輔助表定義
我們在一個本地 DB2 Information Integrator 數據庫中創建了輔助表，來保存直接對 目標數據源（Oracle、DB2 UDB 和 Excel）執行的查詢所返回的中間結果集。以下是對我 們創建的每個輔助表的定義。
----------------------------------------------------
-- Temporary tables for native data access
-- This assumes you already have a database connection.
----------------------------------------------------
-- for query #1
DROP TABLE temp_query1;
CREATE TABLE temp_query1(p_partkey INTEGER,
p_name VARCHAR(55),
p_mfgr CHAR(25),
p_type VARCHAR(25)) DATA CAPTURE NONE;
-- for query #2
DROP TABLE temp_query2;
CREATE TABLE temp_query2(ps_partkey INTEGER,
s_name CHAR(25),
s_suppkey INTEGER,
min_supplycost DOUBLE) DATA CAPTURE NONE;
-- for query #3
DROP TABLE temp_query3;
CREATE TABLE temp_query3(c_custkey INTEGER,
c_name CHAR(25),
total_ordered DOUBLE,
num_orders INTEGER) DATA CAPTURE NONE;
-- for joined query #4
DROP TABLE temp_jquery1a;
CREATE TABLE temp_jquery1a(c_custkey INTEGER,
c_name VARCHAR(55),
c_phone VARCHAR(15)) DATA CAPTURE NONE;
DROP TABLE temp_jquery1b;
CREATE TABLE temp_jquery1b(o_custkey INTEGER) DATA CAPTURE NONE;
-- for joined query #5
DROP TABLE temp_jquery2a;
CREATE TABLE temp_jquery2a(c_custkey INTEGER,
c_name VARCHAR(25),
n_name VARCHAR(25)) DATA CAPTURE NONE;
DROP TABLE temp_jquery2b;
CREATE TABLE temp_jquery2b(o_custkey INTEGER,
o_totalprice DOUBLE) DATA CAPTURE NONE;
-- for joined query #6
DROP TABLE temp_jquery3a;
CREATE TABLE temp_jquery3a(c_custkey INTEGER,
c_name VARCHAR(25),
c_acctbal DOUBLE) DATA CAPTURE NONE;
DROP TABLE temp_jquery3b;
CREATE TABLE temp_jquery3b(o_custkey INTEGER,
total_order DOUBLE,
num_order INTEGER) DATA CAPTURE NONE;
-- new table for joined query #6
DROP TABLE temp_jquery3;
CREATE TABLE temp_jquery3(c_custkey INTEGER,
c_name VARCHAR(25),
c_acctbal DOUBLE,
total_order DOUBLE,
num_order INTEGER) DATA CAPTURE NONE;
附錄 C. 目標 SQL 查詢
在我們的第一篇文章中，介紹了一些我們的 servlet 需要實現的查詢。這裡我們也包 含了這些查詢，以供參考。請注意以下所示的 SQL 語句是概念性的。例如，它們出示了特 定的搜索謂詞；但在實際的實現過程中，我們使用了參數標記以使查詢更加靈活。另外， 我們把它們編寫得好像所有的數據都駐留在一個單一的數據庫中；如您所知，我們的數據 實際上是駐留在三個不同的數據源上的。
查詢 1 到 3 模擬一個在線目錄搜索應用程序。前兩者使用戶能夠找到帶有某些特性的 部件，然後確定所選擇的部件在某個給定的國家或地區中價格最低的供應商。第三個查詢 確定該用戶是否屬於一段給定時間裡最佳的 10 名客戶之一，如果是的話，他（或她）可 能有資格得到免費送貨或者打折優惠。
查詢 4 和 5 支持業務分析工作，這種工作可能出現在合並後方案中。它們試圖在兩個 或更多新合並的公司中識別共同客戶，這些客戶具有一些共同的有趣的特點 － 或許他們 都具有某種目標客戶統計特征、居住在競爭激烈的地域、發出了大的訂單等等。
當閱讀我們這裡的查詢時，您應該假定在 FROM 子句中引用的對象包括來自所有數據源 的數據，除非它的名稱中帶有特定數據源名的前綴。例如，我們使用“part”來表示合並 公司的所有部件數據，不論這些數據駐留在哪裡。我們使用“db2_customer”來表示特定 於某個合並前的公司（在本例中，是一個使用 DB2 UDB 作為其 DBMS 的公司）的客戶數據 。
查詢 1：查找一個感興趣的 partkey。
select p_name, p_mfgr, p_type, p_partkey
from part where
p_type like '%BURNISHED%' and
p_name like '%lavender%'
order by p_partkey
fetch first 20 rows only;
查詢 2：為一個特定的 partkey（例如“28”）搜索在一個給定國家或地區（例如 “GERMANY”）中價格最便宜的供應商
select ps_partkey, s_name, s_suppkey, min(ps_supplycost)
from partsupp, supplier, nation where
ps_partkey = 28 and
ps_suppkey = s_suppkey and
s_nationkey = n_nationkey and
n_name = 'GERMANY'
group by ps_partkey,s_name, s_suppkey;
查詢 3：獎勵那些在一個給定的時間段內發出了大規模訂單的客戶
select sum(o_totalprice)as totalordered , count(*) as num_orders, c_custkey, c_name
from customer, orders where
o_custkey = c_custkey and
O_ORDERDATE >= DATE('1997-10-01') and O_ORDERDATE < DATE('1998-10-01')
group by c_custkey, c_name
order by totalordered desc
fetch first 10 rows only;
查詢 4：從選定的國家或地區裡查找一個部分中的客戶，這些客戶從另一個部分發出規 模龐大的訂單。在本例中，有關這一類型的客戶和國家或地區的信息駐留在一個 DB2 DBMS 中，而這些客戶通過另一個部分發出的訂單信息則駐留在 Oracle DBMS 中。
select c_custkey, c_name, o_totalprice, n_name
from db2_customer, ora_orders, db2_nation where
c_nationkey = n_nationkey and
c_custkey = o_custkey and
o_totalprice > 450000 and
n_name in ('JAPAN', 'CHINA', 'VIETNAM', 'INDIA');
查詢 5：查找某個分公司的符合條件的客戶在整個（合並的）公司中所發出的訂單的平 均金額。符合條件的客戶是指那些居住在給定國家或地區（例如“Japan”）、屬於某個給 定的市場段（“HOUSEHOLD”）並有很少欠賬的客戶。在本例中，有關符合條件的客戶和國 家或地區的信息駐留在一個 DB2 DBMS 中。
select avg(o_totalprice) as avg_order, c_name, c_custkey, c_acctbal
from db2_customer, orders, db2_nation where
c_custkey = o_custkey and
c_nationkey = n_nationkey and
n_name = 'JAPAN' and
c_mktsegment = 'HOUSEHOLD' and
c_acctbal >= 0 and c_acctbal <= 1000
group by c_custkey, c_name, c_acctbal
order by avg_order desc;
