【虎の巻】第10回「Db2 Web Query for i バージョン2.3と共に　ストアドプロシージャを使うための基礎知識（前編）」

ストアドプロシージャを使うことで、既存の表から容易にデータ分析用の仮想的な表を作り出すことができ、簡単に分析レポートを作成することができます。この表はプログラミング言語で処理することも可能ですが、最近機能強化されたWeb Queryと組み合わせることでとりわけ強力な分析ツールになります。今回と次回の2回に渡り、ストアドプロシージャの作成方法と最新版のWeb Queryの組み合わせによる分析レポートの作成法についてご紹介します。
今回は、導入部としてストアドプロシージャの作成方法とそれを利用するWeb Queryの初期作業についてご説明します。（編集部）

1つまたは複数のWeb QueryレポートのデータソースとしてSQLストアドプロシージャを使用する方法の説明と関連して、リック・フラグラーがDb2 Web Queryのインターフェース拡張機能について考察します。

リック・フラグラー　08/02/2021

Db2 Web Queryの最新リリースで、ホームページ、メタデータ管理、レポートデザインに対するインターフェースの機能拡張が行われました。本記事では、SQLストアドプロシージャ（以下SPと略記します）を１つまたは複数のWeb Queryレポートのデータソースとして使用する方法の説明と関連して、これらの変更のいくつかについて考察するつもりです。このプロシージャはデータを提示しますが、そのデータに対してWeb Queryメタデータはドリルダウン階層を定義できます。これは、SPからのデータを使用するホームページ、メタデータエディタとレポートを紹介しながら、SPの威力を強調することになるでしょう。

SPの概要

SPは情報をアプリケーションに提供する方法を提示するSQLプログラムです。Db2 Web Queryはそのようなデータの使用者としての役目を果たすことができます。あなたはプロシージャを作成し、それからあらゆるDb2テーブルまたはビューと同様にWeb Queryにそれが存在することを通知します。

いくつかのプロシージャ・コードと作成プロセスを調べてみましょう。プロシージャの作成ステップは以下の通りです：

• ACSをオープンする
• Run SQLスクリプトを開始する
• SPを定義するためにコードを書く(または貼り付ける)（今の場合DASH5という名前を付けます）
• CREATE PROCEDURE文を実行する
• DASH5を「呼び出し」プロシージャをテストする

CREATE PROCEDURE文でプロシージャにDASH5()という名前を付けました。このプロシージャには入力パラメータは必要ありませんが、もしパラメータがあるなら名前の後の括弧の中にこれを書き、このプロシージャは2つの結果を返すので、DYNAMIC RESULT SET 2とコーディングしなければなりません。そしてもちろんこれはSQLですから、LANGUAGE SQLと宣言します。それに続けてDECLARE文を書きます。

助言：プロシージャ定義をコーディングする前に、個別にSELECT文をテストしなさい。

このプロシージャは、Web Queryに対して2つの結果セットを返すためにC1およびC2という2つのカーソルを定義しています。このコードの中で、各DECLAREはライブラリQWQCENT内のテーブルからいくつかの基本データを取り出すために共通表式（訳注）を使用し、そのデータを要約しています。

カーソルC1は地理的フィールド、期間の選択と収入総額、コスト、そして利益の合計を宣言しており、カーソルC2は製品フィールド、期間の選択と収入総額、コスト、そして利益の合計を宣言しています。

見て分かるように、グルーピングを行うために使用されているこれら地理的フィールドおよび製品フィールドは、詳細度の観点で高いものから低いものの順に並べられています。これらはメタデータの中でWeb Queryの階層になります。また、時間経過に伴う傾向情報を図示するのに役立つかもしれない日付情報が含まれています。

DECLARE文に続いて各カーソルをOPENしなければなりません。最終的に各結果セットをカーソルにセットします。これは本質的に返されたデータに対するポインターを提供し、Web Queryまたは別のプロセスがそれを使用できるようにします。

プロシージャが作成されたら、そのプロシージャが望む結果セットウィンドウを生成するか検査するために、ACSのSQL実行スクリプトにあるSQLの CALLコマンドを使ってそのプロシージャをテストすることができます。

呼び出し例：CALL DASH5();

-- プロシージャ・コードの開始

-- 月毎の対前年比較をするためのSP

CREATE OR REPLACE PROCEDURE Dash5()

    DYNAMIC RESULT SETS 2

    LANGUAGE SQL

    BEGIN

 DECLARE C1 CURSOR FOR

 WITH a
   AS (SELECT country, region, state, city, YEAR(orderdate) AS ordYY, MONTH(orderdate) AS ordM, 
              MONTHNAME(orderdate) AS ordMn, (LINETOTAL * QUANTITY) AS REVENUE, 
              (costofgoodssold * QUANTITY) AS Extcost, 
              (LINETOTAL * QUANTITY) - (costofgoodssold * QUANTITY) AS ExtProfit
         FROM qwqcent.orders o join qwqcent.stores s on o.storecode = s.storecode )
   SELECT country, region, state, city, ordYY, 
          ordYY || '-' || SUBSTR(DIGITS(ordM), 9, 2) as Period, 
          ordMn , sum(revenue) AS PeriodRev, sum(ExtCost) AS PeriodCost, 
          sum(ExtProfit) AS PeriodProfit  
     FROM a 
   GROUP BY country,region, state, city, ordYY, 
            ordYY || '-' || SUBSTR(DIGITS(ordM), 9, 2) , ordMn 
   ORDER BY country,region, state, city, ordYY, 
            ordYY || '-' || SUBSTR(DIGITS(ordM), 9, 2) , ordMn DESC ; 


DECLARE C2 CURSOR FOR   

WITH a
  AS (SELECT ProductType, ProductCategory, ProductName, Model, o.productNumber, 
             YEAR(orderdate) AS ordYY, MONTH(orderdate) AS ordM, 
             MONTHNAME(orderdate) AS ordMn, (LINETOTAL * QUANTITY) AS REVENUE, 
             (costofgoodssold * QUANTITY) AS Extcost, 
             (LINETOTAL * QUANTITY) - (costofgoodssold * QUANTITY) AS ExtProfit
        FROM qwqcent.orders o join qwqcent.inventory i on o.productnumber = i.productnumber )
  SELECT ProductType, ProductCategory, ProductName, Model, productNumber, 
         ordYY || '-' || SUBSTR(DIGITS(ordM), 9, 2) as Period, ordMn, sum(revenue) AS PeriodRev, 
         sum(ExtCost) AS PeriodCost, sum(ExtProfit) AS PeriodProfit  
    FROM a 
  GROUP BY ProductType, ProductCategory, ProductName, Model,
           productNumber, ordYY || '-' || SUBSTR(DIGITS(ordM), 9, 2) , ordMn 
  ORDER BY ProductType, ProductCategory, ProductName, Model, productNumber,
           ordYY || '-' || SUBSTR(DIGITS(ordM), 9, 2) , ordMn DESC;


  OPEN C1;

  OPEN C2;

  SET RESULT SETS CURSOR C1,CURSOR C2;

END;        

--　プロシージャ・コードの終了

新旧のWeb Queryインターフェースの概要

図1に示されているのは、バージョン2.2.1のDb2 Web Query for iのホームページで、そこにはあなたが作成したかアクセスできるレポートが名前順にリストされています。レポートはしばしばフォルダ内の主題毎にグループ分けされます。これはメインホームページで、レポートの使用者はここでレポートを実行することができます。

▲図1

図２に示されているのはバージョン2.3のホームページで、初期画面として最近の作業またはあなたのお気に入りが表示されます。

▲図2

パネル上部にあるワークプレース(Workplaces)と表示されている部分をクリックすると、新しいインターフェースを古いインターフェースに似たレイアウトに変更できます。そこにはフォルダが表示されており、レポート名を表示するためにフォルダを展開したり、折り畳んだりでき、新しいレポートオブジェクトを作成することもできます。図２と図3は、レポート開発またはレポート実行が行われる新しいWeb Queryインターフェースを図示しています。

▲図3

IBMは、選択すれば古いインターフェースに戻れるように、新しいインターフェースで(ユーザーIDの下に)「伝統的なホームページ」へのリンクを提供しました。実際のところ、必要であればブラウザーの異なるタブで両方のインターフェースを使うことができます。

SQLプロシージャ―からデータを作る

ユーザーIDとパスワードを使いWeb Queryにログインし、ホームページを表示します。

メタデータ選択ウィンドウをオープンするために、「データ取得（Get Data）」アイコンをクリックします。「データ取得」パネルでDb2/Db2ウェアハウスCLIを右クリックし、次に「接続表示（Show Connection）」をクリックします（図４を参照）。

▲図4

シノニム作成（Create Synonym）ページが開くと、オブジェクト・タイプはSPではなくデフォルトの表（TABLES）になっています（図５を参照）。利用可能なプロシージャを検索するためにオブジェクト・タイプを表からSPに変える必要があります。プロシージャがQGPLに無い場合、自分のライブラリを指定します。オブジェクト名の一部またはすべてを入力します。下図の例ではDASH%を使っていますが、これはDASHという名前で始まるオブジェクトの総称名リストを取得することを意味します。一般的なIBMの総称名と同様にDASH*と入力すると結果は何も帰ってこないことに注意してください。この場合%は＊の代りになっています。Web Queryに条件に合致するものを探させるために、ウィンドウの右下の「次へ（Next）」ボタンをクリックします。結果として、指定した条件に合致するオブジェクトの一覧が表示されます。項目を「選択（Select）」し、「次へ（Next）」をクリックします。

▲図5

最後から2番目の「シノニムの作成（Create Synonym）」パネルには、これから作成するシノニムが表示され、どのWeb Queryのアプリケーション（Application）フォルダ内にシノニムを保存するかが選択できます（図6を参照）。オプションで名前に「前頭辞（Prefix）」または「接尾辞（Suffix）」を追加することができます。この例では、データがIBMの例題であるCentury Electronicsデータセットのものなので、Cen_を前頭辞として追加しました。 このパネルで「次へ（Next）」ボタンをクリックすると、シノニムの作成処理が行われます（図7を参照）。

▲図6

▲図7

そして成功を示すパネルが表示されます（図8を参照）。

▲図8

(後編へ続く)

(訳注) 高度なSQLに馴染みがない読者のために： 例題のプロシージャ（DASH5）にあるように、１つのWITHキーワードに続けて指定した１つ（または複数）の表を、それに続くSELECT文のFROM節で表として指定できます。このWITHキーワードに続けて指定された表を共通表式（common-table-expression）と呼びます。