ストアード プロシージャー結果セットのUDTFラッパーを作成する

前回の「DB2 for iルーチンでクエリー ロジックをアレンジする」という記事では、DB2 for iでの、既存のストアード プロシージャーと同じ結果セットを返すユーザー定義表関数（UDTF）の作成方法に関する読者の質問について取り上げました。UDTFを作成する目的は、結果セットを別のクエリーと結合したり、分析のために一時表に結果セットをダンプしたりするなど、結果セットに対して追加の処理を行うことにあります。これを実現するために、ストアード プロシージャーのクエリー ロジックをUDTFに移動し、次いでストアード プロシージャー内のクエリーを新たなUDTFで置き換えるという手法を提案しました。

けれども、ストアード プロシージャーが、ベンダー提供のアプリケーション パッケージの一部であって修正できないなど、何らかの理由で、その提案が実装できないケースもあるかもしれません。また、ストアード プロシージャーが、たとえばCなど、あまり馴染みのない言語で書かれていて、ロジックをUDTFとして書き直す作業は時間がかかり、エラーの元にもなりそうだというケースもあるでしょう。こうしたケースでは、プロシージャーを実行すること、結果セットを利用すること、あるいは表関数が活用できる形式にデータをフォーマットすることを目的としてUDTFを書くことができます。

この記事で使用されているコードは、 ここからダウンロードできます。

このラッパー テクニックは強い影響力を持つ手法だと言えます。ストアード プロシージャーの結果セットが、他のDMLステートメント内で使用できるようになるためです。この手法は、JavaやRPGなど、多くの高水準言語を使用して実装することができます。ここでは、「外部」Java UDTFを使用してこの方法を説明します。このようなラッパーの場合、パフォーマンスに負荷が掛かることを留意しておいてください。利便性とパフォーマンスのコストを慎重に比較検討することも必要なことです。

ストアード プロシージャーと結果セット

簡潔にするために、前回の記事の例を引き続き使用することとします。そして、GetOrdersというストアード プロシージャーがあり、CustomerId(INT)というパラメーターを指定するものとします。また、説明の都合上、このプロシージャーはベンダー提供で、修正してはならないプロシージャーであり、なおかつ、SELECTまたはUPDATEなどのDMLステートメントでその結果セットを使用したいものとします。

プロシージャーは次のように呼び出すことができます。

CALL GetOrders (11091);

実行すると、プロシージャーは次の5つの列からなる結果セットを返します。

UDTFラッパー

GetOrdersJavaという「外部」ユーザー定義表関数（UDTF）を使用して、ストアード プロシージャーの結果セットを捕捉します。この種の関数は、コードがSQLではなく高水準言語で書かれているために外部と呼ばれます。この関数は、以下のように簡単なSELECTで呼び出すことができます。

SELECT *
  FROM TABLE(GetOrdersJava(11091)) X;

GetOrdersJava UDTFのGetOrders.javaソース コードは、ここからダウンロードできます。ここでは、Javaコードをコンパイルする方法を知っていることを前提としています。DB2 for iによって使用されるためには、スタンドアロンJavaクラスは以下の特別なIFSパスでコンパイルまたは配置される必要があることに注意してください。

/qibm/userdata/os400/sqllib/function  

最後に、Javaソース コードおよびクラス名（GetOrders.java）と、UDTF名のGetOrdersJavaとを混同しないようにしてください。

不慣れな方に向けて、外部表関数について一言、記しておきます。外部表関数は、（SQL表関数とは異なり）高水準言語でコーディングされており、DB2によって求められる特有のルールに従う必要があります。

表関数のコーディングの際に第一に留意すべき事項は、パラメーター リストについてです。パラメーターの数は、CREATE FUNCTIONステートメントで指定された言語およびパラメーター スタイルによってさまざまになることがあります（詳細は後述します）。一般に、RPGプログラム、サブプロシージャー、（このケースでは）Javaメソッドのパラメーター リストには、各UDTF入力パラメーターに対する入力パラメーターと、UDTFが返す各列に対するさらにいくつかの出力パラメーターが含まれる必要があります。

表関数のコーディングの際に次に留意すべき事項は、現在どのような呼び出しタイプ（すなわち処理段階）であるかをDB2がその表関数に伝えることができるという点です。次の表は、可能性のある5つの呼び出しタイプと、各呼び出しタイプの際にGetOrders.javaがどのような処理を行っているかについての説明を示したものです。

言い換えると、DB2は、数回、関数を呼び出し、データを初期化し、リソースを開くか割り当てるかする選択肢を関数に与えます。その後、DB2は、関数を「Fetch」リクエストで何度も呼び出します。Fetch呼び出しのたびに、表関数から1つのデータ行が返されます。

表関数の変数の状態が、呼び出し全体にわたって維持されることがあることに注目してください（関数がどのように書かれるかによりますが）。Javaプログラムでは、ストアード プロシージャーの結果セットはfetch呼び出しとfetch呼び出しの間でオープンのままとなり、それぞれのfetch呼び出しごとに1行ずつ次に進められます。各fetch呼び出しタイプのたびに、プロシージャーを呼び出し、結果セットをオープンし、結果セットを配置し直す必要があるとしたら、大変なことになるでしょう。

それ以上の行がないことをUDTFがDB2に示すと（言語によって示し方は異なりますが、JavaではSQLStateを「02000」（データの終わり）に設定）、DB2は関数をあと数回呼び出してリソースをクリーンアップする機会を与えます。

DB2GENERALパラメーター スタイルを使用してJavaで表関数をコーディングするのは、他の言語を使用した場合に比べて有利な点があります。IBM提供のUDFという名前のJavaクラスから数多くの「プラミング」コードを継承できるからです。たとえば、呼び出しタイプなどへの参照や、SQL状態を設定したり、原子タイプでヌルを検出したりするためのメソッドなどがすでに提供されており、コーディングで間違いを犯す可能性は少なくできます。ただし、Javaの場合、特にJavaルーチンの最初の呼び出しでは、いくらかの追加のオーバーヘッドが生じることがあります。DB2は、Java仮想マシン インスタンスを作成してコードを実行するための時間を取る必要があるためです。

Javaコードがクラスとしてコンパイルされたら（サンプルjavac QShellコマンドのソース コードのヘッダーを参照）、CREATE FUNCTIONステートメントを使用して、DB2での使用のためにUDTFとして登録することができます。以下に、GetOrdersJavaのCREATE FUNCTION定義を示します。

CREATE OR REPLACE FUNCTION GetOrdersJava (@CustomerID INT)
RETURNS TABLE (
SalesOrderId INT,
CustomerId   INT,
OrderDate    DATE,
ShipDate     DATE,
SUBTOTAL     DEC(19,4))
EXTERNAL NAME 'GetOrders.orders'
LANGUAGE JAVA
PARAMETER STYLE DB2GENERAL
DISALLOW PARALLEL
FENCED
SCRATCHPAD 
FINAL CALL 
RETURNS NULL ON NULL INPUT
MODIFIES SQL DATA

いくつかの注目すべきオプションについて見て行きましょう。1つ目はEXTERNAL NAMEで、UDTFが呼び出されたときに呼び出されるJavaクラス名（GetOrders）およびメソッド名（orders）を指定します。

2つ目はPARAMETER STYLE DB2GENERALで、特定のJava関数のコーディング方法と関連付けられます。このオプションを使用することで、DB2は、提供されるJavaコードがIBMのUDFクラスから継承されることを予期します。

3つ目のMODIFIES SQL DATAは、必須の場合と、必須でない場合があります。データの変更を行えるとDB2がみなす別のUDTFまたはストアード プロシージャーなどのリソースをUDTFが呼び出す場合は、MODIFIES SQL DATAは必須となります。ストアード プロシージャーGETORDERSがREADS SQL DATAとして定義された場合は、このUDTFもREADS SQL DATAとして定義できます。何が違うのでしょうか。DB2がSQLを使用してデータを変更できるとみなしていて、それによって追加のオーバーヘッドが生じることになるとすれば、DB2がコードの実行のしかたについてもう少し慎重になる必要があるというだけです。ストアード プロシージャーがデータを更新することはないとしても、READS SQL DATAとしてマークされていない場合は、おそらくUDTFをMODIFIES SQL DATAとして構築することが必要となるでしょう。

最後に、CARDINALITYオプションは、このコード例の中には出てきていませんが、CARDINALITYと、その後に続けて推定される行数を指定する（たとえば、CARDINALITY 5000）と、表関数が平均的に返す行数をDB2に知らせることができます。この情報は、表関数が他の関数、表、またはビューを含んだ複雑なクエリーに加えられたときに、DB2がより効率的なクエリー プランを作成するのに役立ちます。UDTFが平均的に返す行数が比較的一定している場合は、CARDINALITYオプションを指定する必要があります。関数によって返される行数に大きなばらつきがある場合は、このオプションは省略できます。

外部表関数の構築の詳細については、記事の最後の関連情報を参照してください。

Java UDTF GetOrdersJavaは、作成完了後、実行されると、GetOrdersストアード プロシージャーを呼び出し、一度に1行ずつその結果セットを読み取り（一度に1行ずつDB2にデータ行を作成し）、その後、自分でクリーンアップを行います。UDTFによってラッピングされるため、ストアード プロシージャーの結果セットは幅広いSQL DMLステートメントで使用できるようになります。

注意が必要な点

このUDTFは、基になる結果セットと密接に対応しています。プロシージャーから返される列名およびデータ型は、変更してはいけません。表関数も一緒に壊れてしまう可能性もあるからです。ストアード プロシージャーの結果セットから列名およびデータ型を取得するのに、もっと動的なアプローチを取ることも可能でしょうが、コーディングであれこれ奮闘してみたところで、結局は割に合わないのかもしれません。つまるところ、表関数における列定義は決して動的なものではあり得ないからです（もっとも、セッションごとに、結果セットに一致するように表関数をひっきりなしにDROPしてCREATEしようとするというのであれば別ですが）。

著者あとがき: 以下に、参考になるいくつかの関連情報を示します。

「The power of user-defined table functions」: Birgitta Hauser氏著の、SQLおよび外部UDTFについての素晴らしい概説書です。UDTFの初心者には、良い出発点になります。

「DB2 for i: Process Stored Procedure Result Sets as Cursors」: JavaではなくRPGでこの関数を実装する方法について検討する場合は、組み込みSQLを使用してRPGでストアード プロシージャーの結果セットを捕捉し、操作する方法について知っておく必要があります。

「IBM Developer Kit for Java」: このリファレンス ガイドには、「Java SQLルーチン」というセクションがあり、DB2が実行できるJavaコード（スカラー関数、表関数、およびストアード プロシージャーなど）の作成方法について詳細に説明しています。本記事では、詳しい説明をかなり省いている箇所も多々ありました。そうした箇所については、このIBMによるドキュメンテーションでご確認いただければと思います。すぐにすべてのオプションを理解できなくても、落胆する必要はまったくありませんので。

あわせて読みたい記事