絶対にRPGのUDTF
私は、ビジネス・ルールとユーザー・インターフェースを分けるべきだと固く信じています。より堅固でモダンなシステムでは、ユーザー・インターフェースやバッチ処理から呼び出すことができ、また他のアプリケーションから独立してテストできる「サービス」として、ビジネス・ルールを作成する機能からスタートします。これを実現するための私のお気に入りのツールとして、User Defined Table Function (UDTF) があります。この関数は、SQL SELECT ステートメントから照会できる仮想「テーブル」 (いわゆる従来から物理ファイルと呼ばれているもの) を実装しています。UDTF は IBM i 5.3 以降でサポートされています。
当然、UDTF を提供するすべてのデータベース・ソフトウェアでは、 SQL で UDTF を作成できます。しかし、DB2 for i は独特です。RPG など任意の高級言語で UDTF を作成できるからです。ビジネス・ロジックを UDTF で作成し、UDTF を RPG で作成して、会社の既存の RPG コードとスタッフの既存の RPG スキルを活用することができます。この記事では、そのやり方についてお話しします。
UDTF の正しい使い方
UDTF は、ビジネス・ロジックを作成してデータを計算し、「リスト」や「表」に表示させたい場合にふさわしいツールです。別の言い方をすれば、従来からサブファイルにロードしたり、レポートに印刷したりするようなデータに最適です。
新しいレポートを作成する場合、ビジネス・ロジックを UDTF として作成します。次に、データを UDTF から読み取り、印刷する比較的単純な RPG プログラムを作成します。数か月後、レポートを画面に表示するよう、ユーザー要求を受けることになります。その場合、同じ UDTF を読み取り、画面に出力する別の小規模なプログラムを作成します。多くの場合ユーザーは、モバイル装置が呼び出すことができるレポートを Excel 形式で電子メール送信したり、HTML ページに表示したり、Web サービスで利用できるようにしたりしたくなるでしょう。既存の UDTF を呼び出し、データを別のメディアに出力するだけで元のレポートを作成したときのように、こうしたことをすべて行うことができます。とても便利なのです。
UDTF は SQL 関数
UDTF は SQL 関数であるため、SQL ステートメントから呼び出されます。このステートメントは、表の代わりになります。SQL ステートメントの呼び出し方のイメージを伝えるため、コードを比較して従来のデータベース表のデータを読み取ります (図 1)。
図 1: 従来の SQL SELECT ステートメント
これに UDTF を呼び出すコードが付きます (図 2)。
図 2: UDTF から読み取る SQL SELECT ステートメント
おわかりのように、UDTF は SELECT ステートメントの FROM 節から呼び出されます。関数名を括弧内にコーディングし、その後に TABLE という単語を続けます。「as XYZ」(別名「相関節」) という部が必要です。
図 2 の基本的な例に付属するものはありませんが、SQL SELECT ステートメントをすでに理解しているなら、取り掛かる準備は十分できていると言えます。表関数の出力で SELECT ステートメントのすべての機能を利用できますのでご安心ください。例えば、ORDER BY 節 で UDTF から出力されたデータをソートしたり、WHERE 節で UDTF の出力で特定の文字列を検索したりすることができます。SUM や AVG といった集計関数を使って、列の統計情報を取得することさえできます。
RPG コードを UDTF として作成する最も切実な理由の 1 つに、そのように作成することで、さまざまな場所から呼び出すことができるようになるという点があります。こう考えることができます。システムに対して SQL ステートメントを実行できるソフトウェアは UDTF を実行できます。RPG プログラムから呼び出すことができるだけでなく、Java、PHP、.NET から簡単に呼び出すことができます。こうした環境もまた DB2 for i データベースに対して SQL ステートメントを実行できるためです。
UDTF の RPG コードを作成する方法
OS に組み込まれたデータベース・ソフトウェアは、従来の RPG プログラムがループ内でファイルを読み取ることがある場合と同様に、ループ内で繰り返しプログラムを呼び出します。従来の RPG プログラムでは、OPEN 命令コードを使ってファイルを開き、%EOF BIF によりファイルの終わりを告げられるまで、そのファイルを読み取るループをコーディングし、最後に CLOSE 命令コードを呼び出してファイルを閉じます。
UDTF はこの同じパターンに従います。ただし、役割は逆になっています。UDTF では、コードがファイルのふりをしていることを思い出してください。ファイルを開くか閉じるタイミングを決めるのは OS であり、レコードを読み取るループは、プログラム内ではなく OS のどこかにあるのです。ファイルとしての役割を果たしているプログラムは、エラーが発生したり、ファイルの終わりになったりしたときに、システムに通知する責任があります。
どのように動作するのでしょうか。OS がプログラムを繰り返し呼び出し、「call type」というパラメーターを渡します。SQL はこのパラメーターを、「open」を示す場合は値 -1、「fetch」を指定する場合は値 0、「close」を示す場合は値 1 に設定します。また、システムにエラーを通知する場合に使う SQL の状態とエラー・メッセージ・パラメーターをユーザーに渡します。SQL 状態を 02000 に設定した場合、システムにはファイルの終わりになったことが通知されます。
図 3 のダイアグラムは UDTF のイベントの流れを示します。まず、SQL はプログラムを open で呼び出し、SQL 状態 02000 を返すまで、 fetch で繰り返しそのプログラムを呼び出します。最後に、close でプログラムを呼び出します。
プログラムと OS の間で通信するには、パラメーターを使います。図 4 は UDTF の動作に必要なパラメーターを示しています。
図 4: UDTF で SQL から RPG に渡されるパラメーター
SQL は、 RPG コードをプログラム (*PGM オブジェクト) または *SRVPGM オブジェクトのサブプロシージャーとして呼び出すことができます。プロシージャー・インターフェース (PI) 定義でパラメーターを定義する場合は、 D 仕様を使うことをお勧めします。このアプローチは、両方のコール・タイプで問題なく機能します。プログラムを SQL に記述する方法はすぐに説明しますが、最初に RPG コードの例を見てみましょう。
RPG コードの UDTF の例
ある顧客の過去 30 日間の請求書をすべて示したレポートを作成する仕事を受けたとしましょう。戻された請求書それぞれについて、請求書番号、日付、重要度、金額、支払い状況を表示することにします。私のショップでは、これは単純な照会にはとどまりません。請求書の総額を計算するため、品目行を追加して、最終行に追加されたディスカウントや課金を適用しなければなりません。重要度を算定するため、出荷数量を判断し、単位当たりの品目の重要度で乗算する必要があります。請求書に対して支払いが行われたことを確認するため、顧客支払額が記載された別の「キャッシュ・ファイル」で、その請求書を確認する必要があります。こういったことをしばらく考えた後、入力パラメーターとして顧客番号と日数を指定できる UDTF を作成することが、このプログラムの最適な設計だと判断します。UDTF がすべてのビジネス・ロジックを行って請求書リストを計算し、呼び出し側 SQL ステートメントに「仮想テーブル」として返します。
図 4 は、この CustInvoices RPG ルーチンのパラメーター・リストを示しています。UDTF のパラメーター・リストは常に、入力引数ごとに 1 つのパラメーターで構成されています (図 4 の A)。
各レコードの列 (または「フィールド」) ごとに 1 つのパラメーター (B)
各入力引数の NULL 標識としての役割を果たす 1 つの整数 (C)
レコードの各出力列の NULL 標識としての役割を果たす 1 つの整数 (D)
SQL 状態 (E)
完全修飾関数名と固有名 (F)
メッセージ・テキスト (G)
コール・タイプ (H)
入力引数とそれに対応する NULL 標識は、 SELECT ステートメントから OS のデータベース・エンジンに渡され、引き続き OS から RPG コードに渡された値です。入力パラメーターには常にデータが設定されます。コール・タイプが open、fetch、close のいずれでもかまいません。
出力列パラメーターとそれに対応する NULL 標識の行 (または「レコード」) ごとの列 (または「フィールド」) 値を返します。コール・タイプが fetch のコールごとに、行を 1 つ返すことになります。コール・タイプが fetch でない場合、または SQL 状態がファイルの終わりまたはエラーを示している場合は、これらのパラメーターの値は無視されます。
SQL 状態とメッセージ・テキストは、出力専用パラメーターで、自分の仮想ファイルがいつファイルの終わりになるか、またはいつエラーが発生するか OS に通知します。コール・タイプは、 OS がどの操作を要求しているか教えてくれる場合に使う入力専用パラメーターです。コール・タイプ -1 は OS が open 操作を要求していることを示し、0 はレコードのフェッチ、1 は仮想ファイルのクローズを示しています。
図 5 は UDTF コードの「幹」をどのように構造化しているかを示しています。
図 5: CustInvoices RPG コードの「幹」
call type パラメーターと NULL 標識で受信すると思われる値の名前付き固定情報を定義したいと思います (図 5 の A)。
私のビジネス・ルールでは null custno パラメーターまたは daysback パラメーターは指定できないため、ヌルが渡されると、SQL 状態とメッセージ・テキストを使って呼び出し元にエラーを報告します (B)。
最後に、OS が仮想ファイルを開く、行をフェッチする、またはファイルを閉じる、のいずれを要求するかに応じて、異なるルーチンを実行します (C)。
ありがたいことに、この要件を受け取ったとき、既存の RPG コードが必要な作業を多少処理してくれました。別のプログラムに 1 つ既存のルーチンがあり、請求書の金額を計算してくれました。別のルーチンが重要度を判断し、3 つ目のルーチンは請求書が支払い済みかどうか確認してくれました。
RPG で UDTF を作成するメリットとして、最初から始める必要はなく、既存のコードを活用でき、時間と費用を節約できるということがあります。既存のコードをプログラムにコピーして、変更を加え、 UDTF として機能させることができます。コピーした既存のルーチンは、 RPG のネイティブ入出力ルーチンを使って作成されたため、それらを自分の UDTF でも使いました。しかし、ネイティブ入出力ルーチンではなく、組み込み SQL を使って請求書データを取得したほうがうまく機能したでしょう。
図 6 は、仮想テーブルを開くよう OS に要求されたときに実行するルーチンを示しています。
図 6: OS が仮想テーブルを開くときに実行されるルーチン
ここでは、ネイティブ入出力ルーチンに使おうとしているファイルを開いています (図 6 のA)。この例ではルーチンを使ってファイルを開いたのですが、このためだけにルーチンが使えるという印象を読者に与えたくありません。open ルーチンでは、関数の開始時に 1 回実行したい RPG コードを実行できます。UDTF の *INZSR と考えてみてください。実際、この機会を利用して取得する請求書の開始日を計算し、INVCSDT (顧客および日付別の請求書) 論理ファイルで SETLL を行っているのがわかるでしょう。
OS がレコードをフェッチする場合、図 7 にあるルーチンを実行します。
図 7: OS が各レコードをフェッチする場合に実行されるルーチン
このルーチンでは、INVCSDT ファイルから次のレコードを顧客別に読み取っています。そのファイルの終わりになると、この顧客について取得する請求書はそれ以上ないため、SQL 状態を 02000 に設定してファイルの終わりを示します (図 7 の A)。fetch ルーチンの残りは、行の列に OS に返すデータを設定します。数字を短くシンプルにするために、請求書の金額と重要度を計算するコードは省略しましたが、iProDeveloper.com/code から完全なコードを遺憾なくダウンロードできます。あえて言うなら、これは RPG ルーチンであるため、通常の RPG プログラムで作成するどのコードもここで使うことができます。
図 8 は、ファイルを閉じるよう OS に要求されたときに使うロジックを示しています。 ここでは、単純にネイティブ入出力ルーチンに対して開いていたファイルを閉じています。このルーチンを使って、関数の終了時に 1 回実行する RPG コードを実行できます。
図 8: OS が仮想テーブルを閉じる場合に実行されるルーチン
CustInvoices の例では、RPG コードをエクスポートされたサブプロシージャーが 1 つ入ったサービス・プログラムとして作成しました。他の RPG サービス・プログラムの場合と同様に、CRTRPGMOD (PDM オプション 15) と CRTSRVPGM コマンドを使ってRPG コードをコンパイルしています。ここまでは、それだけで通常のサービス・プログラムですが、次のステップでは、それを SQL 関数に変換します。
RPG コードを SQL 関数に変換する
SQL 関数は、Create Function SQL ステートメントを実行して作成します。Create Function を実行すると、SQL は、サービス・プログラムの名前、サブプロシージャーの名前、SQL にパラメーターのフォーマット方法を指示する作成時に使った言語など、私のルーチンに関する情報を保存します。
図 9 は CustInvoices の例の Create Function ステートメントを示しています。
図 9: SQL の関数呼び出しの設定
図 9 の A では、SELECT ステートメントから関数が呼び出されたときに渡す入力パラメーターを定義します。
これらのパラメーターは、 RPG コードで定義した入力引数に直接対応しています (図 4 の A)。次に、関数が仮想テーブルを返すよう OS に指示し、仮想テーブルに含める列を定義します (図 9 の B)。
これらは、RPG コードの出力列に直接対応しています (図 4 の B)。RPG コードで他のパラメーターを定義する必要はありません。OS が自動的に DB2SQL パラメーター・スタイルの一部としてそれらを追加するからです。DB2SQL は UDTF に指定できる唯一のパラメーター・スタイルです。
External name and program type sub (図 9 のC) で、呼び出すライブラリー、サービス・プログラムおよびサブプロシージャーの名前が OS に与えられます。
サブプロシージャー名は括弧で囲み、大文字と小文字を区別します。タイプ「sub」は、サブプロシージャー・コールであることを意味しています。代わりにプログラム呼び出しへの呼び出しを指定する場合は、外部プログラム節からサブプロシージャー名を削除し、プログラムのタイプを「main」に変更します。
D の language rpgle は、プログラムが ILE RPG で作成されていることをシステムに伝えています。これは、言語観で定義が異なる場合がある任意の変数の書式を設定する方法を SQL に指示します。例えば ILE RPG では、SQL VARCHAR が 2 バイト長で渡され、その後に RPG VARYING フィールドのフォーマットと一致する文字データが続きます。ただし、言語に C を指定すると、language C はデータをゼロで終了した文字列として渡します。通常 C プログラムは文字列をこのように処理するためです。
Parameter style db2sql (E) は SQL に、どのパラメーターを関数に渡すかを伝えます。これにより、NULL 標識、関数名、SQL 状態、メッセージ・テキスト、およびコール・タイプを渡すことができます。UDTF については、DB2SQL が唯一指定できるパラメーター・スタイルです。表を戻さない他の関数は、それらをどのように作成するかにより、その他のパラメーター・スタイルを使うことができます。
F では、No SQL により SQL は、どういった SQL コードが呼び出されている RPG ルーチンにあるか認識できます。この例では、RPG コードは SQL を使っていないため、No SQL をコーディングしました。また、プログラムがどのように SQL を使うかによって、 contains sql、reads sql data、または modifies sql data をコーディングすることもできます。考えてみると、UDTF は SQL ステートメントの一環として動作しています。それが UDTF だからです。そのため SQL ステートメントを RPG コードで実行することは、SQL ステートメントを別の SQL ステートメントで実行するようなものです。そうすることもできますが、データベース・エンジンの観点から多少余分な作業が必要です。No SQL を指定すると、データベースはその余分な作業をしなくてすみます。Contains sql により、 SET ステートメントなど、いくつか SQL ステートメントを実行できますが、No SQL より動作が多少遅くなっています。Reads SQL data はさらに動作が遅いですが、データを読み取り、レコードをフェッチできます。これはかなり便利です。中でも最も遅い Modifies SQL data では、ほぼすべての SQL ステートメントを実行することができます。このオプションを実験したことがあり、パフォーマンス上のデメリットはわずかであることがわかりました。私のユーザーはパフォーマンスの違いに気が付きませんでした。そのため、プログラムで SQL データを変更する必要が生じた場合でも、ためらわずそのようにコーディングします。しかし、CustInvoices など SQL を使わないコードがある場合、No SQL を指定して強化されたパフォーマンスを活用したいと思います。
Not Deterministic and External Action (G) は SQL に、入力パラメーターが同じ場合にコードが常に同じ結果を返すかどうかを伝えます。同じ結果を返す場合は、コードを deterministic と見なします。システムは出力をキャッシュし、入力データが変更されていない場合はルーチンを再度呼び出さなくも済むので、この技法は便利です。システムは単にキャッシュ・コピーを使うことができます。一方、not deterministic は同じ入力でも結果が異なる場合があることを意味しているため、SQL はキャッシュ結果に頼ることができません。External Action も同様です。no external action を使う関数はその都度呼び出される必要はないかもしれませんが、キャッシュ結果は使うことができます。ただし、external action のある関数はキャッシュ・データに頼ってはいけません。プログラムが SQL ステートメント外で余分な動きをすることを意味するからです。この場合、システムはキャッシュを使いませんが、必要に応じて関数を呼び出します。
Disallow Parallel (H) は、関数を再度呼び出すまで最初の呼び出しが終了するのを待つ必要があることを SQL に伝えます。複数の並行スレッドを処理するプログラムを作成した場合、SQL が RPG コードを複数のスレッドで呼び出すことができるよう、allow parallel をコーディングすることができます。このキーワードには常に Disallow Parallel を使っていました。
Create Function SQL ステートメントは関数を作成しますが、実際にディスク・オブジェクトを作成するわけではありません。SQL は、 RPG コードの呼び出し方の情報をそのシステム・カタログ表に保存します。SYSFUNCS や SYSROUTINES などの表は、関数がどのように機能するかという情報で更新されます。この関数定義を削除する場合は、DROP FUNCTION SQL ステートメントで削除できます。
通常、Create Function SQL ステートメントは、作成する関数ごとに 1 回だけ実行する必要があります。データベースは関数定義を記憶するため、後で再度 Create Function を実行する必要はありません。RPG コードを変更しても、パラメーターが変更されない限り Create Function を呼び出す必要はありません。その場合、DROP FUNCTION SQL ステートメントでその関数だけドロップし、新しいパラメーターを指定して作成し直します。
UDTFをテストする
さて UDTF を作成しましたので、SQL ステートメントから呼び出してテストしてみます。図 10 は、UDTF を呼び出して出力がどうなるか確認する場合に実行できる単純な SQL ステートメントを示します。
図 10: CustInvoices UDTF の呼び出し
このステートメントを SQL インターフェイスから実行できますが、テストの場合、IBM i Navigator の Run SQL Scripts ツールが最も便利だと思います。RPG コードをデバッグする必要がある場合、Run SQL Scripts で Run/Debugger オプションを指定して、サービス・プログラムの GUI デバッガーを起動し、実行しながらコードをたどり、ブレークポイントを設定できます。
UDTF のテストが完了したら、プログラムから呼び出します。他の SQL SELECT ステートメントの場合と同様に、EXEC SQL ステートメントでコーディングすることで、RPG の組み込み SQL インターフェイスから UDTF を呼び出します。UDTF を使ってサブファイルを読み込む方法の例については、"UDTFs and Subfiles Make a Great Pair" をご覧ください。
多様性を実現するUDTF
UDTF の良い点は、それによりもたらされる多様性です。過去 30 日間の顧客の請求書を示したレポートを印刷することが今日の要件でしたが、明日はどうでしょうか。Web サイトですか? 営業担当者に電子メールで送信された Excel ドキュメントですか? ユーザーがこうした要求をしても、ビジネス・ロジックを作成し直す必要はありません。同じ UDTF を再度呼び出して、出力を違うように作成します。
データを請求書金額で並べたいでしょうか。問題ありません。図 11 で示すように ORDER BY を追加してソートするだけです。
図 11: ORDER BY を使って出力をソートする
会計で未払いの請求書のみが必要ですか。図 12 で示すように WHERE 節を追加します。
図 12: 未払い請求書のみ
この 1 年間の顧客の平均請求額を知りたいですか。SQL AVG 関数を使って行うことができます (図 13)。
図 13: この 1 年間の平均請求額
過去 180 日間で $1,000 超相当の未払い請求額のある顧客を確認できたら便利だと思いませんか。UDTF を subselect に配置して行うことができます (図 14)。
図 14: 洗練された subselects で使用された UDTF
UDTF は非常に多様性があるため、ビジネス・ロジックを再作成しなくてもこれらの作業をすべて実現できます。
絶対にUDTF
今日のインターネットでは「for the win (絶対に！)」(略して FTW) というポピュラーなフレーズがあります。実にスマートで、うまく機能するものに対して FTW と呼んでいます。私は User Defined Table Functions にもそう感じています。巧みで、作成していて楽しく、信じられないくらい便利です。UDTF FTW!
