DB2 for iルーチンでクエリーロジックをアレンジする｜e-bellnet.com（IBM i （AS/400）ユーザーのための関連技術情報サイト）

IBM i 資料集

IBMｉ海外記事2019.03.14

DB2 for iルーチンでクエリーロジックをアレンジする

Michael Sansoterra 著

こんにちは、マイク:
ストアードプロシージャーを使用して、結果セットをJavaに返し、結果を画面に表示しています。このストアードプロシージャーに、結果セットを一時表に入れさせて、その一時表でいくつか処理を行うことはできないかと考えています。
このようなことは可能でしょうか。可能なら、どのようにすればこれを実現できるのでしょうか。よろしくお願いします。

ご質問者のジレンマはよく分かります。

データがクライアントへ返される必要があるときには、ストアードプロシージャーの結果セットは便利です。

しかし、結果セットは、生成された後に、一時表で、結合、ソート、または保存することができません。

幸いなことに、ユーザー定義表関数（UDTF）を使用することで、開発者はクエリー結果セットで追加の処理を実行することができるようになります。

この目的を達成する最も簡単な方法は、ストアードプロシージャーからユーザー定義表関数へクエリーのロジックを移動することです。

その後、クエリーコードの重複を防ぐために、ストアードプロシージャーに修正を加えることで、UDTFを使用してJavaプログラム用の結果セットを構築するようにすることができます。

UDTFにより、一時表に結果セットを保存する方法が提供されます。

シンプルな例を見てみましょう。

以下のコードは、顧客IDを受け取り、顧客注文の結果セットを返すストアードプロシージャーです（IBM i 7.3を使用していますが、それ以前のバージョンでも動作するはずです）。

CREATE OR REPLACE PROCEDURE GetOrders (@CustomerId IN INT)
LANGUAGE SQL
RESULT SETS 1
NO EXTERNAL ACTION
BEGIN

 DECLARE SalesOrders CURSOR FOR 
　SELECT SalesOrderId,CustomerId,
　　OrderDate,ShipDate,SubTotal
　FROM SalesOrderHeader
 WHERE CustomerId=@CustomerId
ORDER BY SalesOrderId;

OPEN SalesOrders;

SET RESULT SETS CURSOR SalesOrders;

END;

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

CALL GetOrders (11091);

結果セットを構築するSELECTステートメントには、重要なロジックが含まれており、次のようにしてSQL UDTFに組み込むことができます。

CREATE OR REPLACE FUNCTION GetOrders (
@CustomerID INT)
RETURNS TABLE (
SalesOrderId INT,
CustomerId   INT,
OrderDate    DATE,
ShipDate     DATE,
SUBTOTAL     DEC(19,4))
LANGUAGE SQL
NO EXTERNAL ACTION
DISALLOW PARALLEL
SET OPTION COMMIT=*NONE,USRPRF=*OWNER,DATFMT=*ISO
    RETURN
　　　 　SELECT SalesOrderId,CustomerId,
　　　　OrderDate,ShipDate,SubTotal
　　　FROM SalesOrderHeader
　　 WHERE CustomerId=@CustomerId
　 ORDER BY SalesOrderId;

SELECTステートメントが両方のルーチンで同じことであることに注目してください。

UDTFは、次のようにして呼び出すことができ、ストアードプロシージャーと同じ結果セットを返します。

SELECT * FROM TABLE(GetOrders (11091)) Orders

ここで、この関数とCREATE TABLE ASステートメントを使用して、一時表を作成することもできます。

CREATE TABLE TEMP_ORDERS AS (
SELECT * FROM TABLE(GetOrders (11091)) Orders
)
WITH DATA;

１つ注意しておきたいことがあります。

UDTFでのORDER BYの使用には慎重になってください。

ホストクエリーによって無視/オーバーライドされることがよくあるためです。

最近、IBMが変更したのであれば別ですが、CREATE TABLE ASステートメントは置換パラメーターをサポートしないため、顧客番号を渡すにはEXECUTE IMMEDIATEの使用が必要となるかもしれません。

以下に、SQLを使用してこれを行う方法についての簡単な例を示します。

BEGIN

DECLARE @SQL VARCHAR(1000);
DECLARE @CUSTOMER_ID INT DEFAULT 11091;

SET @SQL=
'CREATE TABLE TEMP_ORDERS AS 
(SELECT * FROM TABLE(GetOrders (' || VARCHAR(@CUSTOMER_ID) || ')) Orders) 
WITH DATA';

EXECUTE IMMEDIATE @SQL;

END;

EXECUTE IMMEDIATEほど面倒ではない、この問題に対するもうひとつのアプローチは、顧客IDを格納するグローバル変数を使用する方法です。

関数にその変数を渡すことができるからです。

元の作業に戻ります。

ロジックおよびクエリーの重複を最小化するために、ストアードプロシージャー内の元のSELECTステートメントを表関数の参照に置き換えることができます。

CREATE OR REPLACE PROCEDURE GetOrders (@CustomerId IN INT)
LANGUAGE SQL
RESULT SETS 1
NO EXTERNAL ACTION
BEGIN

DECLARE SalesOrders CURSOR FOR 
 SELECT Orders.*
   FROM TABLE(GetOrders(@CustomerID)) ORDERS;


OPEN SalesOrders;

SET RESULT SETS CURSOR SalesOrders;

END;

この手法は、コードの再利用が求められるときに有益です。

ただし、コードの再利用はパフォーマンスに悪影響を及ぼすこともよくあるため、状況によっては、このアプローチを採らないほうがよいかもしれません。

少なくとも、表関数ラッパーは、ストアードプロシージャーに対してオーバーヘッドを課すことになります。

しかし、UDTFのロジックが単純であり、IBM iがi 7.2 TR4以降である場合は、DB2の手を借りて、UDTFを「インライン」で実行するのもよいかもしれません。

そうすれば、UDTFの使用によるパフォーマンスへの負荷が最小で済むはずです。

何らかの理由で、ストアードプロシージャーが、結果セットを構築するコードをUDTFに適切に移動することができないほど複雑な場合は、ストアードプロシージャーの結果セットをラッッピングする外部UDTFを構築することができます。

その外部UDTFはストアードプロシージャーを呼び出し（上に示したものの逆）、結果セットを捕捉して返します。

これにより、開発者はDMLを使用してデータをさらに操作できるようになります。

今後の記事では、Java UDTFラッパーを作成してこれを行う方法について説明する予定です。

設計上の一般原則として覚えておくべきことは、UDTFは、さらなる操作が必要になるかもしれない結果セットを構築する場合に、ストアードプロシージャーに比べて融通が利くということです。

パフォーマンスへの負荷があまり高くなく、コードの再利用が求められる場合は、結果セットクエリーをUDTFでコーディングします。

このアドバイスは、結果セットの構築に関してのみ当てはまるものです。