J2EE環境において複数データ・ソースにアクセスするDB2 Information Integratorのパフォーマンス

米国カリフォルニア州サンノゼ IBMシリコンバレー研究所
Susanne Englert、C. M. Saracco、Ingmar Gebert
2003年6月

コンテンツ はじめに 結論：パフォーマンス結果のまとめ 概要 フェデレーテッド・クエリーのパフォーマンス リモート・データ・ソースと連合データベースの構成 ハードウェアとソフトウェア リモート・データ・ソース上のデータ リモート・データ・ソースの構成 - 索引 DB2 Information Integratorの構成 DB2 Information Integratorニックネームの統計情報 クエリーパフォーマンスの分析 クエリー1 クエリー2 クエリー3 クエリー4 クエリー5 まとめとガイドライン 参考資料 著者について 著者 Susanne Engler C. M. Saracco Ingmar Gebert 米国カリフォルニア州サンノゼ IBMシリコンバレー研究所

はじめに
この記事はIBM^® DB2^® Information Integrator^TMの使い方を探究するシリーズの3番目、そして最後の記事です。ここではDB2 Information Integratorを使用して複数の異なるデータ・ソースからデータを取得し、統合するJ2EEコンポーネントを実装しています。最初の「Using DB2 Information Integrator for J2EE Development: A Cost/Benefit Analysis」では、このようなアプリケーションを実装する際にDB2 Information Integratorを使用した場合と使用しない場合の開発費用を比較するプロジェクトについて説明しました。2番目の「J2EE Development Across Multiple Data Sources: Digging into the Details」では、開発した次のJ2EEサーブレットについて、その実装を詳しく説明しました。

• DB2 Universal Database^TM、Oracle、およびMicrosoft^® Excelの異なるデータ・ソースにまたがったデータにダイレクト・アクセスして、フェデレーテッド・クエリーを評価するサーブレット
  
  
• DB2 Information Integratorを使って、上と同じ一連のビジネス上の要求に応答するサーブレット。DB2 Information Integratorでは3つのデータ・ソース上のデータを単一のデータベースとして扱えます。

DB2 Information Integratorを使用すると、使用しない場合に比べ、はるかに簡単にサー ブレットを開発できます。なぜなら、フェデレーテッド・クエリーを分解して、それぞれのデータ・ソースへのアクセスを管理するといった面倒で、細かい作業に煩わされる必要がないからです。しかし、パフォーマンスはどうでしょう？DB2 Information Integratorを使った開発の容易性は、結局コストが高くついてしまうのでしょうか？こうした複数データ・ソースへのクエリーは、手操作でコーディングしたほうが、はるかに高速に実行できるのではないでしょうか？この記事では、私たちの検証を通してこうした疑問にお答えしていきます。この記事から、皆さんのそれぞれの環境でも適用可能な洞察を得られれば幸いです。

結論：パフォーマンス結果のまとめ
で、結局、どちらが速いのでしょう? サーブレットから複数のデータ・ソースにダイレクト・アクセスする方法でしょうか、それともDB2 Information Integratorを使用する方法でしょうか? パフォーマンスに関するいつもの疑問と同様、答えはもちろん「状況次第」です。より正確に言えば、クエリー次第です。今回のプロジェクトでは、両方の方法で5つのクエリーを実装しました。実装については以前の記事で詳細に説明しましたし、今回の記事でも後述しますが、ここでは、すべてのクエリーは複数データ・ソース上のデータにアクセスし、ほとんどが複数の表やビューを結合する、という点だけに注意してください。表1に経過時間（秒）の比較データを示します。

表1.クエリーの経過時間

表に示されているように、DB2 Information Integratorを使ったフェデレーテッド・クエリーのパフォーマンスは、J2EEコンポーネントからデータにダイレクト・アクセスした場合に十分、匹敵します。クエリー1の経過時間は両方のケースでほぼ同じですが、2つのケース（クエリー3と4）ではデータにダイレクト・アクセスするよりもDB2 Information Integratorを使用したほうが4〜17倍高速です。逆に、他の2つのケース（クエリー2と5）では40%〜50%低速でした。

これを詳細に見ていくと、たとえば、クエリー5のデータ・ソースにダイレクト・アクセスするサーブレットでは、DB2 Information Integratorでは不可能な実行方法を実装できたため、より高いパフォーマンスを得ています。しかし、この高いパフォーマンスは、ユーザーの独自開発サーブレットでは不可避な、それ相応の追加開発費用、エラーの可能性、非拡張性の代償を伴っています。データ・ソースにダイレクト・アクセスするサーブレットが特定のクエリーを実装するため、手作業でコーディングされたアプリケーションであるのに対し、DB2 Information Integratorは特別なプログラミングなしで任意のSQLクエリーを処理できる汎用インフラストラクチャーであることに注目してください。

私たちは5つのクエリーしか検証していませんが、これらのクエリーはWebコマースや意思決定支援環境で実行される代表的なクエリーとなるよう選ばれたもので、さまざまなパフォーマンス特性を持っています。テストの結果、パフォーマンスの観点から見ると、複数データ・ソースにまたがる分散クエリーを実装するJ2EEコンポーネントにおいてDB2 Information Integratorは、アプリケーションからデータ・ソースにダイレクト・アクセスする方法に代わる、有効な手段となり得ることが分かりました。

概要
まず最初に、フェデレーテッド・クエリーのパフォーマンスに影響を与える要因について簡単に考察し、次に、リモート・データ・ソースとDB2 Information Integratorをどのように設定、構成したかについて解説します。索引や統計などパフォーマンスに重大な影響を与える要素に焦点をあてます。最後に、5つの各クエリーについてその詳細を説明し、アプリケーションからデータ・ソースにダイレクト・アクセスした場合と、DB2 Information Integratorを使用した場合とで実行を比較します。この過程で、DB2 Information Integratorから最高のパフォーマンスを引き出すヒントと、クエリーごとに速くなった理由、遅くなった理由を説明します。

ページ上部へ

フェデレーテッド・クエリーのパフォーマンス
DB2 Information Integratorによるフェデレーテッド・サーバーも、データ・ソースにダイレクト・アクセスする場合も、複数リモート・データ・ソースにまたがるクエリーのパフォーマンスは、次のようないくつかの、自明の要因に左右されます。

• リモート・データ・ソースサーバー、フェデレーテッド・サーバー、ユーザー独自の統合コンポーネントが配置されたサーバーそれぞれの速度、およびこれらサーバー間のネットワークキャパシティー
• DB2 Information Integratorまたはユーザー独自の統合コンポーネントからサブミットされたクエリーを実行するため、リモート・データ・ソースで選択されたクエリー実行プラン

こうした要因以外にフェデレーテッド・クエリーのパフォーマンスを決定する最も決定的な要素は、フェデレーテッド・サーバーで言うクエリー実行プラン、ユーザー独自の統合コンポーネントで言えば、複数のデータ・ソースからデータを取得、統合する際の処理方法です。少数の例外を除いて通常は、なるべく多くのリモート・データ処理をリモート・データ・ソースで行うのが最善の方法です。なぜならリモート・データ・ソースからデータを取得し、フェデレーテッド・サーバー上でローカルに処理するにはコストがかかるからです。たとえばリモート・データ・ソース上で、リモート表から多数の行を絞り込む述部を評価し、DB2 Information Integrator、またはユーザー独自の統合コンポーネントに対して戻ってくる行数を最小化します。

同様に、同じリモート・データソース上の2つの表を結合するときは、多くの場合、その結合をリモート・データ・ソースに「プッシュダウン（ターゲット・データ・ソース側で処理を行うこと）」して実行するのが最善です。結合に関与する両方の表の合計行数よりも少ない行が結合によって生成される場合、これは有効な方法です。ただし、関与する両方の表の合計行数よりも多くの行が結合によって生成される場合は、おそらくローカルで結合を実行したほうがよいでしょう。ここで言う「ローカル」とは、リモート・データ・ソース上で実行される処理に対して、DB2 Information Integrator上で実行される処理を意味することに注意してください。リモートで結合を実行するか、ローカル、すなわちユーザー独自のコンポーネント、またはDB2　Information Integratorのどちらかの上で結合を実行するかは、主に、結合に関与するすべてのリモート・データ・ソースから戻される合計行数を最小限に抑える必要があるかどうかに左右されます。DB2 Information Integratorの場合、この作業はクエリーコンパイル時にニックネーム統計に基づいて自動的に選択されますが、ユーザー独自の統合コンポーネントの場合、開発者が実装時にこの決定を行わなければなりません。

今回の比較検証では最大限の努力を払って、それぞれのクエリーでできるだけ多くの処理をリモートのDB2 UDB、Oracle、Excelにプッシュダウンしました。ここでリモート・データにダイレクト・アクセスするJ2EEサーブレットは、フェデレーテッド・クエリーの分解、プッシュダウン、およびローカルでの統合処理を完全に制御できる代わりに、その詳細を実装する負担を負います。一方DB2 Information Integratorを使用するJ2EEサーブレットは、フェデレーテッド・クエリーの分割と、各データ・ソースに対するより小さく適切なクエリーの送信をDB2 Information Integratorに一任できます。DB2 Information Integratorが高度なプッシュダウンを備えたクエリー実行プランを実現するには、適切な構成と、リモート・オブジェクトのニックネームに対する正確な索引、および統計情報が必要です。

DB2 Information Integratorのパフォーマンス拡張機能として、マテリアライズド・クエリー表(MQT)と呼ばれる、リモート・データのローカルキャッシュコピーを作成できます。MQT（以前の名称は「自動サマリー表」）は、ローカルの表だけでなく、1つ、または複数のニックネームが関与する副クエリーの結果としても定義できます。したがってMQTは、リモート・データの一時的なスナップショットとしてローカルで使用できます。DB2 Information Integratorのニックネームを使用したクエリーを行うユーザーにとって、MQTは透過的です。オプティマイザーは、ニックネームを介したリモート・データへの実際のアクセスと、適切に定義されたMQT内のローカル・データの使用とを透過的に決定できます。リモート・データの代わりにローカル・データを使用することで、パフォーマンスは大幅に向上します。特にDB2 Information Integratorによってローカルに格納されたデータと結合する場合、パフォーマンスは著しく向上します。この記事で紹介する結果にはMQTの使用は反映されていませんが、サンプル中の2つのクエリーで、MQTを使ったパフォーマンス改善の可能性を説明します。

ページ上部へ

リモート・データ・ソースと連合データベースの構成
この節ではハードウェアとソフトウェアの設定について説明します。ネットワーク接続、データ・ソースにまたがったデータの配置、表の索引、ニックネームの統計についても触れます。

ハードウェアとソフトウェア
リモート・データ・ソースのDB2 UDBおよびOracleデータベースは、それぞれマルチプロセッサーWindows^®サーバーにインストールされました。DB2 Information Integratorフェデレーテッド・データベースはWindowsデスクトップ・ワークステーションに設定され、ここには今回のプロジェクトで使用されるExcelスプレッドシート・データも配置されました。すべてのシステムは、100 Mb/sイーサネット・ネットワークで接続されています。

データ・ソースにダイレクト・アクセスする場合、WebsphereR Application Serverテスト環境上で稼動するサーブレットは、Excel、Oracle、DB2 UDB の3つのデータ・ソースにアクセスします。サーブレットは、一時的な作業表をホスティングするためだけにローカルのDB2 Information Integratorインスタンスを使用します。連合データベースを使用する場合、サーブレットは、3つのデータ・ソースに接続するよう構成されたDB2 Information Integratorインスタンスにのみアクセスします。

図1.今回のプロジェクトで使用したソフトウェア・アーキテクチャー

ページ上部へ

リモート・データ・ソース上のデータ
テスト・データは、TPC-H ベンチマークで規定された6つの表から構成しました。最初の記事に表のDDLが含まれています。4つの表ORDERS、CUSTOMER、PART、PARTSUPPは、3つのデータ・ソースDB2 UDB、Oracle、Excelに分散されています。比較的小さな表NATIONおよびSUPPLIERは、各データ・ソースに複製しました。

分散表については、主キー値のほとんどをラウンドロビン方式で3つのデータ・ソースに分散しています。一部のキー値については、1つ以上のデータ・ソースで重複しています。主キーと外部キーのマッチングは、常に同じデータ・ソース上で保持されています。たとえばCUSTOMER表の場合、c_custkey値の6/7は3つのデータ・ソースの1つだけに存在し、残りの1/7は2つのデータ・ソースに重複するように分散されています。これは合併前の企業が顧客を共有する現実を反映しています。ただし、所定の顧客の注文はすべて同じデータ・ソースに配置されています。つまり、o_custkey = c_custkeyを満たすORDERS行とCUSTOMER行とは同じデータ・ソース上にあります。PARTおよびPARTSUPP表も同様に分散しています。ここでも、いくつかのパーツ・キーは複数のデータ・ソース上で重複していますが、p_partkey値とps_partkey値がマッチするデータは同じデータ・ソース上にあります。同じサプライヤーが複数のデータ・ソースで同じパーツを供給する場合、あるパーツ・キーとサプライヤーの卸売原価(ps_supplycost)はデータ・ソースによって異なります。つまり、あるサプライヤーは合併前の各企業に、同じパーツを異なる価格で供給していたかもしれません。

最大の表であるORDERSには、3つのデータ・ソースに分散した約500万行のデータが含まれています。PARTSUPP表には約270万行、CUSTOMER表とPART表にはそれぞれ50万行のデータが含まれています。最小の表はSUPPLIERとNATIONで、それぞれ約100,000行と25行のデータが含まれています。2つのデータ・ソースDB2 UDBとOracleの各表にデータを設定した後、DB2でRUNSTATS、OracleでANALYZEを使用して、統計情報を収集しました。Excelには同等の機能がないため、他のデータ・ソースと同じようには統計を収集できませんでした。DB2 Information Integratorを使用する際、この問題にどう対処するかについては、すぐ後で説明します。

リモート・データ・ソースの構成 - 索引
DB2 Information Integratorを使用するかどうかに関係なく、通常、リモート表で適切な索引を定義することは、良好なパフォーマンスを得るための決定的な要素です。Excelスプレッドシートには一切索引を定義できませんが、DB2 UDBとOracleの各データ・ソースの表には、いくつかの索引を定義しています。索引は今回の一連のテストで使用される結合と、述部の評価に有用なものを選びました。

DB2 UDBとOracleのORDERS表には、次の固有索引を設定しました。

1. O_ORDERKEY, O_CUSTKEY
2. O_ORDERDATE, O_CUSTKEY, O_TOTALPRICE
3. O_CUSTKEY, O_ORDERDATE, O_ORDERPRIORITY, O_TOTALPRICE
   

CUSTOMER表には次の2つの索引を設定しました。

1. C_CUSTKEY
2. C_CUSTKEY, C_NAME

PARTSUPP表には、次の1つのマルチ列索引を設定しました。

1. PS_PARTKEY, PS_SUPPKEY, PS_SUPPLYCOST

SUPPLIER表には、次の1つのマルチ列索引を設定しました。

1. S_SUPPKEY, S_NATIONKEY, S_NAME

NATION表には、次の2つの索引を設定しました。

1. N_NAME, N_NATIONKEY
2. N_NATIONKEY, N_NAME

DB2 Information Integratorの構成
すでに以前の記事で説明したように、3つのリモート・データ・ソースに対してDB2 Information Integratorラッパーおよびサーバー定義を作成するほか、各リモート表のニックネームを作成しました。またニックネームに対するUNION ALLビューを定義し、3つのデータソースにまたがるか、または各データ・ソースに複製された複数の表を1つの論理表として扱いました。Oracleデータ・ソースへのアクセスにはNet8を使用し、DB2データ・ソースへのアクセスにはDRDARラッパーを使用します。DB2 Information IntegratorにはリモートのExcelスプレッドシート専用のExcelラッパーがありますが、ODBCラッパーを使用してExcelにアクセスしたほうがはるかに優れたパフォーマンスを得られることが分かりました。これはODBCサーバー定義をカスタマイズして、重要な処理をExcelに「プッシュダウン」するようDB2 Information Integratorに対して直接指示できるためで、これはExcel ラッパーでは不可能な処理です。ただしExcelラッパーには、サポートするスプレッドシートの形式に制限が少ないなど、他の利点もあります。Excelスプレッドシートにアクセスする際のExcelラッパー、ODBCラッパーの選択に関する完全なガイドについては、http://www.ibm.com/jp/software/data/ii/を参照してください。

DB2 Information Integratorニックネームの統計情報
リモート表のニックネーム定義を作成すると、DB2 Information Integratorは自動的にリモート・データ・ソースから、索引と表の統計情報を取得しようとします。DB2 Information Integratorはリモート・データ・ソースによって収集、格納された索引および統計情報に依存しており、自らは統計情報の生成を試みません。現在、ニックネームの十分な統計情報はリモートDB2 UDBおよびOracleデータ・ソース上のオブジェクトについてのみ収集できます。他のデータ・ソース上にあるオブジェクトのニックネームについては、最小限の統計情報しか収集されないか、まったく収集されません。DB2 Information Integratorのニックネームを使用して非常に簡単なクエリーのみを実行する場合、統計情報の欠如は問題になりませんが、複雑なクエリーを実行する場合、優れた統計情報は重要です。

今回のプロジェクトでもODBCラッパーを介してアクセスするExcelスプレッドシートでは、統計情報を一切使用できません。DB2 Information Integratorのクエリー・オプティマイザーが効果的な実行プランを選択するには、非常に簡単なクエリー以外、すべてのクエリーで正確な統計を必要とするため、DB2 Information Integratorがリモートデータ・ソース上のニックネームに関する統計情報を取得できない場合、基本的な統計情報を別の方法で入力します。

DB2 Information Integratorのカタログに収められたニックネームに関する統計情報を設定する1つ目の方法は、get_statsと呼ばれるツールを使用する方法で、ここからダウンロードできます。get_statsは次のように起動します。

get_stats <user> <password> <ii_database_name> <schema_name.nickname>

get_statsの実体は、指定されたニックネームに対してCOUNT(*)、MIN()、MAX()、各列ごとのCOUNT DISTINCT()などの動的SQLクエリーを実行し、DB2 Information Integratorの統計情報を設定するプログラムです。これらのクエリーはコストが高くつきますが、結果として得られるニックネーム統計情報は多くの場合、最高のパフォーマンスを実現するクエリー実行プランを選ぶ際に役立ちます。次のようにgetstatsツールを使用してExcelスプレッドシートに対応するニックネームの統計情報を「収集」しました。

get_stats db2admin db2admin TEST DB2ADMIN.ODBC_PART
get_stats db2admin db2admin TEST DB2ADMIN.ODBC_PARTSUPP
get_stats db2admin db2admin TEST DB2ADMIN.ODBC_CUSTOMER
get_stats db2admin db2admin TEST DB2ADMIN.ODBC_ORDERS
get_stats db2admin db2admin TEST DB2ADMIN.ODBC_SUPPLIER
get_stats db2admin db2admin TEST DB2ADMIN.ODBC_NATION

get_statsツールは一回だけ使用すればよく、Excel内のデータが大幅に変更された場合を除き、繰り返し使用する必要はありません。

何らかの理由でget_statsを実行できない場合は、統計情報を必要とする各ニックネームに対してスクリプトを記述し、DB2 Information Integratorのカタログに含まれるSYSSTATビューの関連項目を更新できます。最も重要な項目は次のとおりです。

• SYSSTAT.TABLES: CARD列にニックネームで参照される表の行数を設定します。
• SYSSTAT.COLUMNS: COLCARD列に各ニックネームの各列で固有の値の数を設定します。
• SYSSTAT.COLUMNS: HIGH2KEY列に各ニックネームの各列の最大値を設定します（正確には"HIGH2KEY"は、2番目に大きな列の値を格納しますが、現実には最大値のほうが見つけやすく、また統計上、十分近い値です）。
• SYSSTAT.COLUMNS: LOW2KEY列にを各ニックネームの各列の最小値を設定します

ニックネーム作成時に自動的に収集された統計情報でも、get_statsの使用やスクリプトの記述で収集した統計情報でも、ニックネームに関する統計情報はDB2 Information IntegratorのSYSSTAT.COLUMNSおよびSYSSTAT.TABLESカタログ・ビューをクエリーして調べられます。たとえば、ニックネーム ODBC_CUSTOMERに関する列の統計情報を調べる方法を次に示します。

select char(tabschema, 10) as schema,
char(tabname, 15) as table,
char(colname, 15) as column,
int(COLCARD) as colcard,
char(HIGH2KEY,20) as high2key,
char(LOW2KEY,20) as low2key
from sysstat.columns where tabname = 'ODBC_CUSTOMER';

SCHEMA    TABLE          COLUMN        COLCARD     HIGH2KEY     LOW2KEY
--------- -------------- ------------- ----------- ------------ -------------
DB2ADMIN  ODBC_CUSTOMER  C_ACCTBAL            1620 009998.0700  -000998.7200
DB2ADMIN  ODBC_CUSTOMER  C_ADDRESS              -1
DB2ADMIN  ODBC_CUSTOMER  C_COMMENT              -1
DB2ADMIN  ODBC_CUSTOMER  C_CUSTKEY            1620 600000       598381
DB2ADMIN  ODBC_CUSTOMER  C_MKTSEGMENT            5 HOUSEHOLD    BUILDING
DB2ADMIN  ODBC_CUSTOMER  C_NAME             241664 Cust#598375  Cust#00002
DB2ADMIN  ODBC_CUSTOMER  C_NATIONKEY            25 24           0
DB2ADMIN  ODBC_CUSTOMER  C_PHONE            241664 999-723-9736 100-178-1709

クエリーパフォーマンスの分析
では、5つのクエリーを一つずつ詳しく見ていきましょう。ここではDB2 Information Integratorを使ったフェデレーテッド・クエリーの処理に焦点をあて、アプリケーションからデータ・ソースにダイレクト・アクセスする実装と適宜比較します。フェデレーテッド・クエリーの処理を理解する上で不可欠なツールがDB2 Explainです。DB2 ExplainはDB2 Information Integratorで使用されるクエリー実行プランを示します。またVisual Explainはコントロールセンターから起動でき、次に説明するすべての手順を自動的に実行するため、実行プランを簡単に表示できます。Visual Explainを使用しない場合は、コマンド行環境でExplainの実行プランを生成し、整形してファイルに出力します。次の手順に従ってください。

1. DB2 Information Integratorデータベースに接続し、次のコマンドを実行してDB2 Explain表を作成します。
   
   

   db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL.

   
   このコマンドは1回だけ実行すればよく、2回目以降は繰り返す必要はありません。いったんDB2 Information Integratorのカタログ内にExplain表を作成したら、データベースが存在する限り、この表を使用できます。
   
   
2. Explain表にクエリーの実行プラン情報を書き込みます。DB2 Information Integratorデータベースに接続し、次のコマンドを実行して、クエリーのExplain情報を取得してください。
   
   

   db2 explain plan for <SQL text of query>

   
   
3. Explainの情報を読み取り可能な形式でファイルに出力するには、db2exfmtが最適で、クエリーの実行に関する最大の情報を提供します。次のコマンドを実行して、最後に取得したクエリーのEXPLAIN情報をファイルに出力します。
   
   

   db2exfmt -d <DB2 II database name> -1 -o <output file name>

   
   

クエリー1
クエリー1は、今回の一連のクエリーの中で最も簡単なクエリーです。3つのデータソースすべてにまたがるPART表にアクセスし、特定の属性を持つパーツ・キーをリストします。クエリー1の「概念的」なSQLを次に示します。

Select distinct p_name, p_mfgr, p_type, p_partkey
from <PART tables on all sources>
where p_type like ? and
      p_name like ?
order by p_partkey fetch first 20 rows only

? = '%BURNISHED%',
? = '%lavender%'

複数のデータ・ソースに重複したパーツ・キーのある可能性があるため、DISTINCTを使用して重複を取り除いていることに注意してください。J2EEコンポーネントでこのクエリーをどのように実装するのか、データ・ソースにダイレクト・アクセスする方法と、DB2 Information Integratorを使用する方法とで、それぞれ詳細に見ていきましょう。

データ・ソースにダイレクト・アクセスする方法では、サーブレットは、各データ・ソースのPART表から対象となる行を取得してローカルの一時表に挿入し、一時表に対して最終的なSELECT DISTINCTを実行して重複した行を除去します。当初は、各リモート・データ・ソースの送信するクエリーに"order by"や"fetch first 20 rows only"文節を含めていなかったため、クエリーに非常に時間がかかりましたが、後に各データ・ソースから取得する行を上位20のパーツ・キーに制限することは有効であることに気がつきました。なぜなら全ソース中で上位20に入るパーツ・キーは必ず任意のデータ・ソースで上位20に入るためです。

DB2 Information Integratorを使用する方法では、FED_PARTに対して次のクエリーを実行します。FED_PARTは、各データ・ソース上のPART表に対するニックネームの、UNION ALLビューです。

Select distinct p_name, p_mfgr, p_type, p_partkey
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

このSQLで唯一の注目すべき点は、各述部のパラメーター・マーカーに対して、列の型を示す明示的なCAST関数を追加している点です。たとえば、p_typeはVARCHAR(25)型であるため、対応するパラメーター・マーカーをVARCHAR(25)としてキャストしています。このCASTがない場合、パラメーター・マーカーは「型指定なし」となり、デフォルトの型解釈が行われます。たとえばこの場合、タイプなしパラメーター・マーカーはLIKE述部のパターン式であり、デフォルトでVARCHAR(32672)型と仮定されます（「SQL Reference」のPREPAREに関する文書を参照してください）。

このデフォルトの仮定は、パフォーマンスに悪い影響を与えます。なぜでしょう? なぜならDB2 Information IntegratorがLIKEのパターン式を、非常に大きなVARCHAR式になり得ると仮定した場合、各リモート・データ・ソースへのクエリーも同じ仮定で生成するためです。しかし、OracleのLIKE述部に指定可能なパターン式は、最大で4000バイト長です。これではDB2 Information Integratorは、Oracleに対して安全にLIKEステートメントを送信できません。なぜなら4000バイトを超えるパターン式がLIKE述部には含まれているかもしれないからです。Oracleに送信できる程度にパターン式が小さいと確信できないDB2 Information Integratorは代わりに、Oracleから行を取得して、LIKE述部をローカル(!)で評価します。一般的にはこの場合、Oracle上でLIKE述部が評価された場合に比べると、はるかに多くの行がDB2 Information Integrator側に返されます。

教訓 :良好なパフォーマンスを得るには、LIKE述部に含まれるパラメーター・マーカーをCASTし、比較の対象となる列の型に確実に合わせること。この結果、LIKE述部がリモート・データ・ソースにプッシュダウンされる可能性が高くなります。

db2exfmtを使用してクエリー1のExplainを出力すると、どの処理がリモートで行われ、どの処理がDB2 Information Integratorによってローカルに行われたかが分かります。Visual Explainの出力も同様の結果を示します。

図2クエリー1のExplain出力

DB2 Information Integratorは、3つのリモート・クエリーの結果に対しローカルで、UNIONを実行しています。これはクエリー1に3つのデータ・ソース上の、PART表を参照するニックネームのUNION ALLビューが含まれるためです。

SHIP演算子は、実行プランの中でどの処理がリモート・データ・ソースに転送されたかを示します。各SHIP演算子のすぐ上の数字は、リモート・データ・ソースから戻される行数の、オプティマイザーによる推定値です。

ニックネームのすぐ上の数字は、リモート表に含まれる行数の、オプティマイザーによる推定値です。たとえば、DB2ADMIN.ORA_PARTニックネームは、総行数341,100行を持つOracle PART表を参照します。ORA_PARTに対するSHIP演算子が戻す行数13行は、総行数341,100行よりはるかに少ないため、LIKE述部の評価はOracleデータ・ソースにプッシュダウンされ、条件を満たす行のみがDB2 Information Integratorに戻されると想定できます。実際には、db2exfmtの出力ファイルにおいてSHIP演算子を詳細に調べることで、正確にどのクエリーがOracleデータ・ソースに送信されたかをつきとめられますが、ここでは簡便のため、該当の出力部分を割愛します。

UNIONに左下に延びる線に注目すると、LIKE述部がDB2ソースにプッシュダウンされていることが分かります。一方、UNIONの右下に延びる線に注目すると、ODBCソースへのSHIP演算子は、ODBC_PARTのすべての行4100行を取得しています。LIKE述部によるデータの絞り込みはDB2 Information Integratorによってローカルに実行されています。これはDB2 Information Integratorによって、ODBCデータ・ソースはp_type列およびp_name列上でLIKE述部を評価できない、と保守的に想定されているためです。実際には、ODBCソースの4100行のうち43行だけがLIKE述部の条件を満たしているため、LIKE評価をプッシュダウンできれば、DB2 Information Integratorに戻される行はずっと少なくなります。ODBCサーバー上でdb2_maximal_pushdownサーバー・オプションを使用可能にしてみましたが、この動作は有効になりませんでした。db2_maximal_pushdownサーバー・オプションは、DB2 Information Integratorによってリモート・データ・ソースで必要な処理を実行できると確証された場合にのみ、「プッシュダウン」実行プランを強制するからです。

DB2 Information Integratorは、データ・ソースにダイレクト・アクセスするサーブレットとほとんど同じぐらい高速にクエリー1を処理します。パフォーマンスの小さな(3%)差は、次の2つの要因によるものです。

• 今説明したようにDB2 Information Integratorは、ODBC_PART上のLIKE述部をExcelにプッシュダウンしません。ODBC_PARTからは条件を満たすものだけではなく、すべての行を取得します。データ・ソースにダイレクト・アクセスするサーブレットはLIKE処理を3つの各データ・ソースにプッシュダウンします。
  
  
• データ・ソースにダイレクト・アクセスするサーブレットは、各データ・ソースからクエリー結果のうち最初の20行だけを取得します。DB2 Information Integratorは同じ最適化を行えないため、20行への絞込みはUNION演算子の次の、最後のソート時にのみ行われます。

この2つの要因により、データ・ソースにダイレクト・アクセスするサーブレットと比べた場合、リモート・データ・ソースとDB

クエリー2
クエリー2は、3つの全ソース上のSUPPLIER、PARTSUPP、およびNATION表にアクセスして、所定のパーツ・キーに最低の供給コストを設定しているサプライヤーを所定の国で見つけます。このクエリーの概念的な公式は次のとおりです。

select ps_partkey, s_name, s_suppkey,
	min(ps_supplycost) as ps_supplycost
from	<PARTSUPP tables on all sources>,
	<SUPPLIER tables on all sources>,
	<NATION tables on all sources>
where ps_partkey = ? and
	n_name = ? and
	s_nationkey = n_nationkey and
	ps_suppkey = s_suppkey
group by ps_partkey,s_name, s_suppkey

? =  28
? = 'GERMANY'

ダイレクト・データ・アクセスを使ったこのクエリーの実装はシンプルなものです。各データ・ソースに個々にクエリーを実行して、対象のパーツ・キーの最低供給コストとサプライヤー名を見つけ出し、このデータをローカル一時表に挿入します。最後に、ローカル表を検索して、3つの全ソース中の最低供給コストを取得します。

DB2 Information Integratorを使用した場合、このクエリーは3つのUNION ALLビュー（FED_PARTSUPP、FED_SUPPLIER、およびFED_NATION）を使って次のように公式化されます。

select ps_partkey, s_name, s_suppkey,
	min(ps_supplycost) as ps_supplycost
from  fed_partsupp, fed_supplier, fed_nation
where ps_partkey = ? and
	n_name = ? and
	s_nationkey = n_nationkey and
	ps_suppkey =  s_suppkey and
	ps_server =   s_server and
	s_server =    n_server
group by ps_partkey, s_name, s_suppkey

ここで注目すべき重要な点は、当初のクエリーに"server"述部が追加されていることです（斜体）。それぞれの「表」は実際には、データ・ソースを識別する"server"属性を備えた、リモート・データ・ソース上の表を示すニックネームのUNION ALLビューであることに注意してください。2つのUNION ALLビューを結合するときは、ソース間結合、つまり、あるソース上の1つの表の1行を別のソース上の1つの表の1行と結合するものを、クエリー結果に含めたいかどうかを慎重に検討する必要があります。ここでは、UNION ALLビューの"server"属性に同等の述部を追加することで、ソース間結合を含めたくないことを示しています。

後ほど、ソース間結合を許可することで、UNION ALLビューと集計を伴う一部のクエリーで間違った結果が生じることを説明します。結局、このクエリーは間違った結果を生じることにはなっていません。しかし、間違った結果という問題が発生しない場合でも、サーバー述部の追加によりソース間結合を回避することで、パフォーマンスを非常に大幅に向上させることができます。この理由を探るために、このクエリーの結合の1つ、s_suppkey = ps_suppkey上のFED_SUPPLIERとFED_PARTSUPPの結合を調べてみましょう。これは実際には、次の2つの結合です。

一般的には、UNIONの結合を実行するのではなく、代数式で和の積を積の和に変換するように、多くの小さな結合のUNIONにこれを変換することが推奨されます。この変換は、なぜ多くの場合有用なのでしょうか？これを行わないと、ニックネームのUNIONSは、フェデレーテッド・サーバーのローカル結合が後続するローカルUNIONを処理するために、各ニックネームから条件を満たすデータをすべて取得することが必要になります。一方、最初にリモート・データ・ソースで（プッシュダウンされた）結合を実行できる場合は、結合はこれらのソースで利用できる索引の恩恵を享受できます。さらに、プッシュダウンされた結合は、いくつかのUNIONの結合を実行するためにフェデレーテッド・サーバーに送信する必要があったはずのリモート・データ・ソース上のデータをフィルターする可能性があるため、この変換ではリモート・データ・ソースとフェデレーテッド・サーバー間で移動するデータが全体的に少なくなる見込みが高くなります。

一般的に、このように変換されたUNIONの結合は、最初のUNIONの各要素を2番目のUNIONの各要素と結合する必要があり、その後で全結合の結果をともにUNIONする必要があります。したがって、3つの各ソース上のsupplier表は、ORA_PARTSUPP、DB2_PARTSUPP、およびODBC_PARTSUPPと結合して、合計9つの結合を行う必要があります。しかし、これらの結合のほとんどは不要なことが分かっています。複数ソース上にデータが分散されるやり方により、あるソース上の1つのパーツの全サプライヤーは、同じソース上のsupplier表にあることが分かっています。他の場所を探す必要はありません。パーツ・キーは他の2つのソースのいずれにも存在しないかも知れません。この場合は、ソース間のSUPPLIER - PARTSUPPの結合はゼロ行になります。付加的なソース間結合によってクエリーの応答が影響を受けることはありませんが、余分なオーバーヘッドが追加されます。このクエリーでは、サーバー述部を除去することで同じ応答が得られますが、処理に4倍も長い時間がかかります。

要約すると、2つ以上のソースに広がるニックネームのUNION ALLビューを結合するときは、正しい結果を得るためにソース間結合が必要と分かっている場合を除き、ソース間結合を許可しない"server"述部を追加すべきです。ほとんどの場合、ソース間結合を避けることで、クエリーを非常に大幅に高速化できます。さらに、ソース間結合が許可されていると、ニックネームの集約とUNION ALLビューを伴う一部のクエリーでは間違った結果が生成されます。これらで正しい結果を得るには、付加的な"server"述部を必要とします。次の節には、このようなクエリーの例が示されています。

クエリー2のDB2 Information Integrator Explain（図3）は、ソース間結合を回避した影響が示されています。NATION、SUPPLIER、およびPARTSUPPの3つのUNION ALLビューの結合は、各ソース上のこれら3つの表に対するプッシュダウンされた結合のUNION ALLにトランスフォームされます。DB2 Information Integratorは、結合結果の最終的な集約とUNIONのみを実行します。

UNION演算子の直下にあるSORT、TBSCAN、およびGRPBY演算子の3セットは、min(ps_supplycost)の集計はリモート・データ・ソースにプッシュダウンされずに、ローカルで実行されることを示します。リモート・データ・ソースからは極めて少量のデータのみが戻されるため（厳密に言えばそれぞれから4行）、ローカルで集計するためにこれをリトリーブしてもまったく不都合はありません。

ODBCソースにプッシュダウンされた結合は他の2つと少し違っていることに気づくでしょう。ODBCへのSHIP (30)の上のFILTER (29)やNLJOIN (27)といった付加的な演算子が、結合の出力がNULLにならないように作用します。DB2 UDBおよびOracleソースでは、これらの予防措置は必要ありません。なぜなら、すべてのDB2 UDBおよびOracleニックネーム列は、DB2 Information Integratorにより NOT NULLとして宣言されているからです。ただし、ODBC/Excelソースのすべてのニックネーム列はNULL可能としてマークされているため、DB2 Information IntegratorはNULLの可能性を慎重に処理します。

図3.クエリー2のExplain出力

クエリー3
このクエリーは、3つの全ソース上のCUSTOMERおよびORDERS表にアクセスして、所定期間の注文合計が最大の値となる10の顧客を見つけます。所定の顧客は合併前の複数企業に注文した場合もあるため、一部の顧客キーは複数ソースに重複しています。このクエリーでは、顧客の注文が複数ソースに格納されているかどうかに関係なく、合併された複数企業に対する顧客の注文合計を正確に反映したいと考えています。このクエリーの概念的な公式は、次のとおりです。

select sum(o_totalprice) as totalordered , count(*) as
num_orders, c_custkey, c_name
from 	<CUSTOMER tables on all sources> ,
	<ORDERS tables on all sources>
where o_custkey = c_custkey
and o_orderdate >= ?
and o_orderdate <  ?
group by c_custkey, c_name
order by totalordered desc fetch first 10 rows only;

? = DATE('1998-07-01')
? = DATE('1998-07-31')

ダイレクト・アクセス・サーブレットは、order-by文節を含まずに、各ソースに対してこのクエリーを実行します。この理由は、全ソースでの合計注文金額による顧客ランキングは、全ソースでの顧客の注文金額を集計するまでは分からないため、個々のソースにアクセスするときにソートするのは時機尚早だからです。したがってダイレクト・アクセス・サーブレットは、各ソースでそれぞれの顧客について条件を満たす全注文の合計価格を計算し、これらをローカル一時表に挿入する必要があります。この後で、全ソースでの各顧客の合計注文金額を計算して、結果を合計注文金額により降順でソートします。

次に、DB2 Information Integratorにサブミットされたほうのクエリー3を示します。

select sum(o_totalprice) as totalordered , count(*) as
num_orders, c_custkey, c_name
from fed_customer , fed_orders
where o_custkey = c_custkey
and   o_server = c_server
and o_orderdate >= ?
and o_orderdate <  ?
group by c_custkey, c_name
order by totalordered desc fetch first 10 rows only;

このクエリーでは2つのUNION ALLビューを結合するため、異なるソース上のCUSTOMER表とORDER表の結合が除外されるように、ここでも各ビューの"server"列に結合述部を挿入していることに注意してください（斜体）。今回は、良好なパフォーマンスを得るためだけでなく、正確なクエリー結果を得るためにも、これを行う必要があります。

ソース間結合が許可されている場合は、複数ソース上で重複する顧客キーによって発注された注文が重複してカウントされて、間違った結果を得ることになります。たとえば、ソースBに重複しているソースAの顧客キーは、自己のソース(A)と他方のソース(B)上に合致する注文を見つけます。その一方で、ソースB上の同じ顧客キーも、両方のソースに合致する注文を見つけるため、その顧客が発注したそれぞれの注文は2回カウントされることになってしまいます。UNION ALLビューを使用する場合は、集計に対する重複結合キーの影響をよく理解することが非常に重要です。

クエリー3のDB2 Information Integrator Explainプラン（図4）にも、UNION ALLビューの結合が、結合のUNION ALLにトランスフォームされていることが示されています。DB2 UDBニックネームとOracleニックネームの結合は、それぞれのソースに完全にプッシュダウンされて、ODBC_CUSTOMERとODBC_ORDERSの結合のみがDB2 Information Integratorによってローカルに実行されています。各ソースの顧客キー別にグループ化された集計sum(o_totalprice)の計算も、ローカルに実行されています。ただしこれは、少なくともOracleとDB2 UDBにはプッシュダウンすることが可能です。オプティマイザーは、集計によってリモート・データ・ソースから戻される合計行数は減らないと確信して、sumをプッシュダウンしないことに決定しています。しかし結局、これは正しい判断ではないことが判明しています。実際には、集計により、戻される行数が10分の1に減少します。この問題はさらに調査する必要があります。ただし、OracleおよびDB2 UDBサーバーのdb2_maximal_pushdownサーバー・オプションを使用可能にすると、集計とグループ化をこれらのソースにプッシュダウンする実行プランが提供されます。

図4. クエリー3のExplain出力

この実行プランの本当に完璧ではないプッシュダウン動作にも関わらず、DB2 Information Integratorはこのクエリーをダイレクト・アクセス・サーブレットよりはるかに高速に実行します。これは驚くべきことです。なぜなら、DB2 Information Integratorは条件を満たすo_orderdate値を持つすべてのOrders行を各データ・ソースからフェデレーテッド・サーバーに戻して、各顧客キーのsum(o_totalprice)をローカルで計算するからです。一方のダイレクト・アクセス・アプリケーションは、顧客キー別の合計注文価格の計算をそれぞれのリモート・データ・ソースにプッシュダウンし、ローカル一時表に挿入するためにソースごと、顧客ごとに1行だけしかリトリーブする必要がありません。

リモート・データ・ソースからより多くのデータをリトリーブし、結合をODBCソースにプッシュダウンしないにも関わらず、なぜDB2 Information Integratorはより高速に動作したのでしょうか？これは完全には解明されていませんが、詳しく調査できなかった要因は次のようにいくつか考えられます。

• ダイレクト・アプリケーションが実行したODBC/Excelへのプッシュダウン結合のパフォーマンスは実のところ非常に悪いもので、ODBC/Excelからフェッチしたデータを使ってDB2 Information Integratorによりローカルで結合を実行したほうが高いパフォーマンスが得られると考えられます。
  
  
• プッシュダウンされたCUSTOMERとORDERの結合が、DB2 Information Integratorとダイレクト・アクセス・サーブレットで異なって公式化されたために、OracleおよびDB2 UDBソースで別の実行プランを取得したと考えられます（後者にはc_custkey別にグループ化されたo_totalpriceの集計が含まれますが、前者には含まれません）。

クエリー4
このクエリーでは、合併前のある企業（DB2 UDBソース上）の顧客の中で、特定の国に住み、合併前の別の企業（Oracleソース上）に相当の金額を注文した顧客を探します。DB2 Information Integratorでは、これは次のように公式化されます。

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 (?,?,?,?)

? = 450,000
(?,?,?,?) = ('JAPAN', 'CHINA', 'VIETNAM', 'INDIA')

このクエリーを実装するダイレクト・アクセス・サーブレットは、2つのローカル一時表を作成します。そしてCUSTOMERとNATIONの結合をリモートDB2ソースに発行し、指定の国に住んでいるすべての顧客を最初の表に移送します。次に、o_totalprice述部を満たすすべての注文をOracleソースから2番目の表に移送します。最後に、2つの一時表のローカル結合を実行して、条件を満たす顧客を条件を満たす注文とマッチングします。これは最適な処理戦略ではないかも知れませんが、特に実装者がo_totalpriceやc_nationkeyの分散について詳しく知らない場合は、これは間違いなく簡単で、初めて処理を行う場合には適切な方法と言えます。

DB2 Information Integratorにサブミットされたクエリーは、まさに前述のとおりです。次に、クエリー4のExplainプランを示します。

図5. クエリー4のExplain出力

1. 最初に、DB2 Information Integratorが、o_totalprice述部を満たすすべてのORA_ORDERS行をOracleソースから取得します。そしてこれらをローカル・ハッシュ表（メモリー内）に挿入して、ハッシュ結合に備えます。
   
   
2. 次に、DB2 Information Integratorは、DB2_NATIONとDB2_CUSTOMERの結合をリモートDB2 UDBソースにプッシュダウンします。これによって条件を満たす顧客の数が約250,000から約40,000に減少するため、非常に賢明な方法です。
   
   
3. 条件を満たす顧客キー(c_custkey)による結合の結果行が、DB2 Information Integratorに戻されます。これらの各行については、最初にORA_ORDERSから構築されたハッシュ表をハッシュ結合演算子がチェックして、(o_custkey)の値が合致する行を見つけます。

これは優れた実行プランで、DB2 Information Integratorはこのクエリーをダイレクト・アクセス・サーブレットよりはるかに高速に処理します。なぜでしょうか？

まず、ダイレクト・アクセス・サーブレットは、条件を満たす2セットの行を一時表に書き込む必要があります（1つは顧客、もう1つは注文）。DB2 Information Integratorは、条件を満たす注文をローカル・ハッシュ表に格納します。結合の左側から取得される顧客行も、結合される前にディスクに書き込む必要はありません。このため、クエリー4の処理においてDB2 Information Integratorが行う入出力は、ダイレクト・アクセス・サーブレットよりはるかに少なくなります。

ダイレクト・アクセス・サーブレットを低速化する可能性のある2番目の要因は、統計が収集されていないローカルDB2データベース上で2つの一時表（1つは注文を保持、他方は顧客を保持）を結合する必要があることです。ローカル・データベースによって実行される2つの一時表の結合というプランは、結果的によくないのかも知れません。しかし、この理論を検証することはできませんでした。

クエリー5
このクエリーは、ある合併前企業の特定顧客が3つの全企業に発注した注文で支払った平均合計注文価格を計算します。このクエリーでは、特定の属性を持つ顧客について、3つの全ソースのOrdersを合計して、DB2 UDBソースで結合します。概念的な公式は次のようになります。

select avg(o_totalprice) as avg_order, c_name, c_custkey,
c_acctbal
from 	<CUSTOMER on DB2 source>,
	<NATION on DB2 source>,
	<ORDERS tables on all sources>
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

? = 'JAPAN'
? = 'HOUSEHOLD'
? = 0
? = 1000

最初の、ダイレクト・アクセス・サーブレットでのクエリー5の実装は、次のように非常に簡単なものでした。

1. 該当する列(customer_key, num_orders, total_orders)を含む1つのローカル一時表を作成しました。サーブレットは各ソース上のOrders表に順番にアクセスして、o_custkey別にグループ化してsum(o_totalprice)とcount(*)を計算したクエリー結果を使って一時表のデータを設定しました。したがって表は、異なるソースに顧客の重複があるかどうかに応じて、o_custkeyの各値について1〜3行を持つことになりました。
   
   
2. DB2_CUSTOMERとDB2_NATIONを結合するDB2ソースへのクエリー結果を保持する2番目の一時表を作成しました。これは条件を満たす顧客から(c_custkey, c_name, c_acctbal)を取得しました。
   
   
3. 最後に、両方の一時表をc_custkey = customer_keyでローカルに結合し、条件を満たすそれぞれのcustomer_keyについてsum(num_orders)でsum(total_orders)を除算することで、o_totalpriceの平均を計算しました。

この実装は機能しましたが、3つの全ソース上のOrders表全体の内容を読み取って集計してから、各o_custkey値の集計結果を最初の一時表に挿入しなければならない、という欠点がありました。そしてパフォーマンスが悪かったため、私たちは別の方法をとることにしました。

2番目のダイレクト・アクセス・サーブレットでは、より高度な方法でこのクエリーを実装します。o_custkey値が条件を満たすもののみ、リモート・データ・ソースからORDERSデータを取得する、という方法です。

1. 最初にサーブレットは、条件を満たすc_custkey値に対応する全注文についてsum(o_totalprice)とcount(*)を計算する次のクエリーをDB2 UDBソースに発行します。
   
   

   SELECT COUNT(*) AS num_order, SUM(o_totalprice) AS total_order, c_name, c_custkey, c_acctbal FROM customer, orders, 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;

   
   サーブレットは、結合の結果行をローカル一時表に挿入します。この一時表は、条件を満たす顧客をリストし、それぞれの顧客について、発注した注文カウントとその合計価格を含む1つの行を含んでいます。
   
   

   INSERT INTO temp_jquery3 (c_custkey, c_name, c_acctbal, total_order, num_order) VALUES (?, ?, ?, ?, ?)");

   
   
   
2. 次に、この顧客セットの注文を他の2つのソース（OracleとExcel）でも探し、これらのソースの注文カウントとo_totalprice値の合計金額を含めてローカル一時表を更新します。サーブレットは、すでに識別された顧客リストに対応する注文をOracleおよびExcelソースからリトリーブするという、一種のネストされた結合を実装します。ここでは、ステップ1で見つかったそれぞれのo_custkey値について、OracleソースとExcelソースに次のクエリーを発行します。
   
   

   SELECT COUNT(*) AS num_order, SUM(o_totalprice) AS total_order, o_custkey FROM orders WHERE o_custkey = ? GROUP BY o_custkey

   
   一時表に容易に追加できるように、このようにしてこれら2つのソースの注文を事前に合計して、顧客キー別にグループ化します。
   
   

   UPDATE temp_jquery3 SET num_order = num_order + ?, total_order = total_order + ? WHERE c_custkey = ?

   
   
3. 最後に、一時表は、各顧客が発注した全注文の合計価格と、全ソース上での合計注文カウントを含みます。サーブレットは、このローカル表に対して、各顧客の合計注文価格を注文カウントで除算してその顧客の平均注文価格を取得し、結果を降順にソートする、というシンプルなクエリーを発行します。
   
   

   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;

ダイレクト・アクセス・サーブレットの実装には、実装についてできる限り熟慮した上でのコーディングが必要となります。重要なのは、avg(o_totalprice)の計算は個々のデータ・ソースにプッシュダウンできない、という点を理解することです。なぜなら、全ソースからデータがリトリーブされるまでは、平均値は計算できないからです。したがってサーブレットは、最終ステップでavg(o_totalprice)を計算するために、各ソースからsum(o_totalprice)とcount(*)をリトリーブする必要があります。

DB2 Information Integratorで使用されるクエリー5の公式は次のようになります。ここには、DB2_CUSTOMER表とDB2_NATION表が、3つの全ソース上のORDERS表のUNION ALLビューであるFED_ORDERSと結合されていることが示されています。

select avg(o_totalprice) as avg_order, c_name, c_custkey, c_acctbal
from db2_customer, db2_nation, fed_orders
where
	c_custkey = o_custkey and
	c_nationkey = n_nationkey and
	n_name = ? and
      	c_mktsegment = ? and
	c_acctbal >l= ? and
	c_acctbal <= ?
group by c_custkey, c_name, c_acctbal
order by avg_order desc

DB2 Information Integratorのクエリー実行プランは図6に示されています。DB2_CUSTOMER、DB2_NATION、および3つのORDERS表のUNION ALLビューの結合は、DB2_CUSTOMER間、DB2_NATION間、および各ソース上のORDERS表間という3つの結合のUNIONにトランスフォームされます。

DB2_CUSTOMER、DB2_NATION、およびDB2_ORDERSの結合は、そうであるべきとおり、DB2 UDBに完全にプッシュダウンされます。UNIONの残り2つの「レグ」は、DB2 Information IntegratorがDB2_NATIONとDB2_CUSTOMERを結合してから、それぞれについてネストされたループ結合をリモートORDERS表に実行することを示しています。これは、DB2_NATIONとDB2_CUSTOMERの結合が、3回実行されることを意味します。つまり、DB2ソースにおいて3つの表を結合する課程で1回、残り2つのソースそれぞれについて1回ずつ実行されます。この結合が繰り返し実行されることで、クエリー5の処理においては、DB2 Information Integratorはダイレクト・アクセス・サーブレットより低速になります。

図6. クエリー5のExplain出力

このクエリーのパフォーマンスを改善するために考えられる1つの方法は、DB2_CUSTOMERとDB2_NATIONの結合をローカルにキャッシュするローカルMQTを定義することです。DB2 Information Integratorは、DB2_CUSTOMERとDB2_NATIONに対して繰り返されるリモート結合を、ローカルにキャッシュされたこれら2つの表の"prejoin"への参照で透過的に置き換えることができます。クエリー4でも同じ結合を参照するため、このMQTはクエリー4でも有用です。

クエリー5は、DB2 Information Integratorでは実装できない実行戦略をダイレクト・アクセス・サーブレットが実装して、より高いパフォーマンスを実現できる状況の例を示しています。しかし、このハイパフォーマンスは、カスタム・ダイレクト・アクセス・サーブレットでフェデレーテッド・クエリーを実装する際につきものの相当の追加開発コスト、エラーの可能性、および拡張性不足といった問題点と比較検討する必要があります。

まとめとガイドライン
DB2 Information Integratorを使用および使用しないでマルチソース分散クエリーを実装するJ2EEサーブレットを記述する経験は、非常に興味深いものでした。DB2 Information Integratorを使用する場合と、カスタムJ2EEサーブレットのダイレクト・データ・アクセスを使ってクエリーを実装する場合のどちらのケースでも、私たちは最高のパフォーマンスを得ようと試みました。カスタムダイレクト・アクセス・サーブレットについては、最良実例に従って効率的なコードを記述するように努めましたが、過度に複雑または専門的なクエリー処理戦略は実装しないようにしました。それにも関わらず、また、私たちのクエリーは比較的シンプルなものであったにも関わらず、適度なパフォーマンスで正しい結果を提供するカスタムダイレクト・アクセス・サーブレットを記述する難しさを痛感することが何回かありました。フェデレーテッド・クエリーを個々のソースの小さなクエリーに分解してからその結果を正しく統合するという作業を手動で行うのは、常に簡単なものではなく、何回かミスを犯しました。DB2 Information Integratorによって、綿密さが必要とあれる作業を少ない労力で行うことができました。

DB2 Information Integratorを使用した場合は、適切に構成されているかどうか、また場合によってはフェデレーテッド・クエリーを公式化する際のちょっとした注意によって、良好なパフォーマンスが得られることが分かりました。この記事シリーズで説明されているプロジェクトの経験に基づいて、パフォーマンスに関係する次のガイドラインを考慮することが推奨されます。

• 該当するワークロードに妥当な索引が全リモート・オブジェクトで定義されていること、また、可能な場合はリモート・オブジェクトに対するニックネームが定義される前に統計が収集されていることをチェックします。DB2 Information Integratorのニックネーム統計をチェックし、get_statsまたはユーザー作成スクリプトを使って不足している重要な統計を入力します。
  
  
• DB2 Information Integratorでリモート・サーバー定義を構成する際は、リモート・データ・ソースの特性を記述する文書化されたサーバー・オプションが正しく設定されていることを確認します。詳細は「Federated Systems Guide」をご覧ください。
  
  
• 複数リモート・データ・ソース上のニックネームのUNION ALLビューを定義するのは、DB2 Information Integratorを使って関連データを結び付けるための自然な方法です。ただし、ニックネームのUNION ALLビューが関与するクエリーを公式化する際は、特別な注意を払う必要があります。ビュー自体に、UNIONの各要素のソースを示す"server"列を含める必要があります。一般的にこのようなUNION ALLビューが複数関与するクエリーでは、正確さと良好なパフォーマンスを得るために、"server"列に結合述部を追加する必要があります。この記事で提供されている例をご覧ください。
  
  
• ニックネームのUNION ALLビューの結合で良好なパフォーマンスが得られるかどうかは、プッシュダウンされた結合のUNION ALLへの（DB2 Information Integratorオプティマイザーによる）変換が成功するかどうかによって決まります。ただし、この変換は、クエリーに関与するUNION ALLビューの数とソースの数が多過ぎない場合のみ機能します。それぞれがm個の別個のソース上のニックネームを伴う、n個のUNION ALLビューを結合するクエリーでは、mn ? 36である限り変換は成功します。たとえば、それぞれが2つのソース上のニックネームを参照する4つのUNION ALLビューの結合の場合は、24=16? 36であるため、問題なく変換できます。結合のUNIONにうまく変換されないUNION ALLビューが関与する結合は、パフォーマンスが非常に悪くなりがちです。
  
  
• LIKE述部とパラメーター・マーカーを伴うクエリーには注意してください。このような述部をリモート・データ・ソースにプッシュダウンできるようにするには、パラメーター・マーカーを、これと比較される列のタイプとしてCASTする必要があります。詳細はクエリー1をご覧ください。
  
  
• db2_maximal_pushdownサーバー・オプションを使って、機能上可能なときは常に、クエリー操作をリモート・データ・ソースにプッシュダウンしたい旨を指示できます。この詳細は「Federated Systems Guide」をご覧ください。今回のプロジェクトではこのオプションは使用されていませんが、慎重に使用すれば実際にかなり役立ちます。多くの場合リモート・データ・ソースへのクエリー処理のプッシュダウンは、フェデレーテッド・クエリーによる良好なパフォーマンスを得るうえで最も重要な唯一の要因であることに注意してください。
  
  
• DB2 Information Integratorインスタンスにローカル・データがまったく格納されていない場合も、ローカル・ソート、ハッシュ結合、および一時表の作成を行うために十分なローカル・リソースを構成することが必要です。SHEAPTHRES、SORTHEAP、一時表スペース、および一時表スペースに関連付けられたバッファー・プールの設定をチェックしてください。
  
  
• DB2 Information Integratorは、ニックネームに定義されたマテリアライズクエリー表(MQT)を使用することで、リモート・データのローカル・キャッシングを可能にします。この記事では広範にカバーされていませんが、頻繁に変更されない小さなリモート・データ・オブジェクト（またはその結合）のローカル・レプリカを使用する方法は、パフォーマンスを向上させるための最も効果的なテクニックの1つです。

全体的に見れば、DB2 Information Integrator製品を適切に構成し、前述のガイドラインに従ってフェデレーテッド・クエリーを公式化した場合は、5つのサンプルクエリーでDB2 Information Integratorを使ったJ2EEサーブレットが概してダイレクト・アクセス・サーブレットに負けないパフォーマンスを実現できたことに私たちは満足しています。DB2 Information Integratorは、複数ソースの情報にアクセスして統合する必要があるJ2EEコンポーネントの開発を大幅に簡素化すると同時に、良好なクエリーパフォーマンスを提供します。

参考資料
DB2 Information Integratorを使用および使用しないでマルチソース分散クエリーを実装するJ2EEサーブレットを記述する経験は、非常に興味深いものでした。DB2 Information Integratorを使用する場合と、カスタムJ2EEサーブレットのダイレクト・データ・アクセスを使ってクエリーを実装する場合のどちらのケースでも、私たちは最高のパフォーマンスを得ようと試みました。カスタムダイレクト・アクセス・サーブレットについては、最良実例に従って効率的なコードを記述するように努めましたが、過度に複雑または専門的なクエリー処理戦略は実装しないようにしました。それにも関わらず、また、私たちのクエリーは比較的シンプルなものであったにも関わらず、適度なパフォーマンスで正しい結果を提供するカスタムダイレクト・アクセス・サーブレットを記述する難しさを痛感することが何回かありました。フェデレーテッド・クエリーを個々のソースの小さなクエリーに分解してからその結果を正しく統合するという作業を手動で行うのは、常に簡単なものではなく、何回かミスを犯しました。DB2 Information Integratorによって、綿密さが必要とあれる作業を少ない労力で行うことができました。

• IBM DB2 Universal Database V8 Federated Systems Guide
• Using DB2 Information Integrator for J2EE Development: A Cost/Benefit Analysis
• J2EE Development Across Multiple Data Sources: Digging into the Details
• Get statistics utilities for relational and nonrelational data sources.

著者について

C.M.Saraccoは、IBMのシリコンバレー研究所のシニア・ソフトウェアエンジニアであり、UCサンタクルーズ校の公開プログラムではソフトウェア・テクノロジーの元インストラクターを務めていました。北米、南米、ヨーロッパ、中東を通じて、幅広いテクノロジー関連のトピックについてのレクチャー経験を持ちます。

Susannne Englertは、IBMのシリコンバレー研究所のシニア・ソフトウェアエンジニアであり、ソフトウェアのパフォーマンス管理および分析に15年間の経験がありますが、特に、大規模データベースでの複雑なクエリーのパフォーマンスと最適化に関心を寄せています。1996年から2000年の間、トランザクション処理性能協議会（TPC）の下位委員会、Decision Supportの議長を務めていました。

Ingmar Gebertは現在、ドイツ・ロストック大学でコンピューター・サイエンスおよびビジネスを学んでいます。このほどIBMシリコンバレー研究所でのインターンシップを修了しました。同研究所では、分散したデータ・ソースにアクセスするサーブレットおよびセッションEJBを開発するためのさまざまなテクノロジーを研究しました。


IBM、DB2、DB2 Information Integrator、DB2 Universal Database、およびWebSphereは米国またはその他の国、あるいは米国内外の両方におけるIBMコーポレーションの商標または登録商標です。報
その他の社名、製品名、サービス名は、その他の所有者の商標またはサービス名である可能性があります。

原文はこちら

ページ上部へ