SQL照会をページに分割
最近では、IBM i プログラマーはますます Web アプリケーションを作成するようになってきたため、我々は皆、困難なステートレス・プログラミングを扱わなければなりません。特に、Web アプリケーションに「単一ファイル・ロード・サブファイル」と同等のものを実装するのは大変です。
従来の対話式アプリケーションでは、サブファイルに最後にロードしたレコードにカーソルをポイントするだけでファイルを開けておくことができます。ユーザーが Page Down キーを押すと、そのポイントから前方に読み込み、次のページをロードします。Page Up キーを押すと、後方に読み込み、次のページをロードします。この手法は、スクロール可能なカーソルを使用して、(RPG の READ 命令コードと READP 命令コードを使用した) ネイティブ I/O また SQL にも同様に使えます。
しかし、呼び出し間でファイルをオープンしておくことができず、オープン・カーソルで位置を維持できないような Web アプリケーションではどうすればよいのでしょうか。この問題の解決に役立つさまざまなオンライン・フォーラムに掲載された便利な SQL 手法がいくつかありました。この記事では、興味深いものを掲載します。
大きな塊から小さな塊をフェッチ
期待できそうな最初の回答は、Birgitta Hauser が System iNetwork フォーラムに掲載した回答でした。彼女のサンプル・コードは以下のとおりです。
With x as (Select Fld1, Fld2, ... FldN
From MyTable
Where ...
Order By Key1, Key2, ... KeyN
Fetch first 2025 rows only)
y as (Select *
From x
Order By Key1 Desc, Key2 Desc, ... KeyN Desc
Fetch first 25 rows only)
Select *
From y
Order By Key1, Key2, ... KeyN
この例で Birgitta は一度に 25 行の複数ページを記入していました。この場合、18 ページ目のデータが必要でした。そのページを取得するため、SQL に最初の 2,025 行のデータ (レコード 1 から 2025) を昇順でフェッチするよう指示しました。それから、2,025 行の最後から始めて同じデータを降順で表示し、その結果の最初の 25 行を取得するよう指示しました。非常に賢いですね。
コードを挿入してフェッチする数を計算し、それらの数を可変にしなければならないのは明らかです。しかし、このアイデアには利点があります。しかし、この手法の大きな欠点の 1 つは各行で一意のキー・セットが必要な点です。複写キーがある場合、この手法は問題です。
OLAP 行番号によるフェッチ
上記の同じスレッドで、Lev Ostromich は ROW_NUMBER() OVER() 関数を使用した手法を示しました。この関数は OS バージョン 5.4 から導入された DB2 の Online Analytical Processing (OLAP) 機能の一部です。この手法では複写キーの問題を解決しますが、5.4 と OLAP 機能が必要です。
SELECT * FROM ( SELECT A.*, ROW_NUMBER() OVER (ORDER BY key1,key2,key3) AS seqNumber FROM myTable A) AS temp_result WHERE seqNumber BETWEEN 2000 AND 2025;
この手法の以前の参照は、Centerfield Technology's Out In Left Field ニュースレターの2007 年 12 月 17 日の記事にあります。ニュースレターの最後のページに SQL の教祖 Elvis Budimlic は ROW_NUMBER() 手法について論じており、サンプル・コードを提供しています。
MySQL の LIMIT キーワード 最近は、i 上で MySQL を実行することができます。その場合、MySQL 向け IBMDB2I ストレージ・エンジンを使用して物理ファイルと論理ファイルにアクセスできますが、DB2 で提供されるクエリー・エンジンではなく、MySQL を使用します。
MySQL の良いところは LIMIT キーワードです。これは、受信データのページを制御する場合に使用できる標準外 SQL キーワードです。その例を下に示します。
select * from myTable order by key1, key2, key3 LIMIT 2000, 25
この欠点は、MySQL を経なければならないことです。MySQL がぴったりくる PHP でコーディングしている場合はそんなに悪くありません。しかし、RPG、C、Cobol、または Java などの他の環境の場合は、MySQL を使用するのは多少厄介です。さらに、DB2 は MySQL より i でより早く動作します。
相対カーソル配置の使用
このスレッドで、System iNetwork フォーラムに別の手法が掲載されていました。そのスレッドでは、ユーザー asmith が、毎回新しいカーソルを開き、相対開始位置で毎回適切なページに配置できると説明していました。
c/exec SQL
C+ FETCH RELATIVE :START FROM CURSOR INTO :DS
c/end-exec
前記のコードは組み込み SQL では良好に動作しますが、組み込み SQL を使用せず、FETCH RELATIVE コマンドを実行できない PHP では同じことができるでしょうか。最近、Kelly Cookson が midrange.com の WEB400 メーリング・リストにその回答を掲載していました。
$query = "SELECT EOPYEAR, EOPPRD, EOPBGSDT, EOPCLSDT FROM DOTDATA.EOPMAST";
$result = db2_exec($conn, $query, array('cursor' => DB2_SCROLLABLE));
Cookson からのメモ - 結果セットから個々の行を取得するため for ループを使用するスクリプト。変数 $rn は、ループを開始する行にあります。変数 $limit は、ループを終了する行にあります。
$i=$rn;
for ($i=$rn; $i<=$limit; $i++)
{
$row = db2_fetch_both($result, $i);
echo "".$row["EOPYEAR"]."";
echo "".$row["EOPPRD"]."";
echo "".$row["EOPBGSDT"]."";
echo "".$row["EOPCLSDT"]."";
}
この時点で、これらのオプションのうちどれが最適な動作をするのか判断するのは大変ですが、少なくともいくつかのオプションがあります。それらをすべて試してみて、どれが一番うまく動作するか判断してください。
