(Microsoft Word - IBM i \203C\203\223\203^\201[\203l\203b\203g\203Z\203~\203i\201[_XMLTABLE.doc)

(1)

【

【DB2 for i XMLTABLE】

】

XML

データ・タイプを使用した

データ・タイプを使用したRPGアプリケーション開発のご紹介

アプリケーション開発のご紹介

――――――――――――――――――――――――――――――――――――――――― ―――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――― ――――――――――――――――――――――――――――――――――――――――― はじめにはじめにはじめに はじめに アプリケーションの近代化、Web化が近年多くの企業において検討されています。IBM i では、ILE RPG(RPGⅣ)により、Java等の複数言語の混在、XMLサポートなどが可能となり、アプリケーションの近代化、Web化へ対応できるよう機能拡張がなされています。Webアプリケーションではデータの受け渡しにXMLが使用されるケースがあります。IBM i 7.1以降では、DB2 for i においてXMLをサポートする機能拡張がされており、XMLを利用したWebアプリケーション開発が可能です。今回は、DB2 for i でサポートされているXMLTABLE関数を組み込みSQLとして使用したRPGアプリケーションについてご紹介します。XMLTABLE関数により、Webアプリケーションで使用している XMLデータをDB2 for i に格納し、RPGアプリケーションなど他の処理においても、データを抽出し、活用することが可能です。

【参考リンク】第3回 IBM i 6.1（i5/OS V6R1）現代の開発言語としてのILE-RPG http://www-06.ibm.com/systems/jp/power/software/i/seminar/estory2/est03/ XMLTABLEとは？_とは？_とは？とは？

XMLTABLEは、SQL関数であり、XPathを用いてXMLデータをRDBフォーマットで結果セットを返す機能を提供します（図1）。XMLTABLEはIBM i 7.1のDB2 for i グループPTF SF99701 Level 14で機能拡張がされ、IBM i 7.1 グループPTF SF99701 Level 14以降、また、IBM i 7.2の環境で使用できます。図図11.. XXMMLL_デー_デ_ータ_タの_のリ_リレ_レー_ーシ_ショ_ョナ_ナル_ル・_・フ_フォ_ォ_ーマ_ー_マッ_ット_トで_での_の取_取得_得 XMLTABLEのサンプルのサンプルのサンプルのサンプル今回のインターネット・セミナーでは、XMLTABLEを使用したRPGのサンプルをご紹介します。まずは、RPGの組み込みSQLのプログラムを紹介する前に、IBM i Navigatorから単純な XMLTABLE_{を使用したSQL文のサンプルをご紹介します。} IBM i 7.1では、図2のようにXMLデータ･タイプのカラムを含むテーブルを作成することができます。図図22..XXMMLLデー_データタ・・タタイイププののカカララムムをを含含むむテテーーブブルルのの照照会会

(2)

ここでは、XML文書をINSERTしたテーブルに対して、XMLTABELを使用し、XMLデータをリレーショナル・フォーマットで取得して、結果を表示するSQL文を紹介しています。 ※ここで紹介しているSQL文の検証をされる際は、System i ナビゲーターの「データベース」機能の「SQL スクリプトの実行」の利用を推奨します。STRSQLなどの対話型SQLでは、XMLデータ･タイプをXML文書の形式で表示することができません。 1. テーブルの作成 xmltestスキーマにカラム名doc、データ・タイプXMLのカラムを持つempテープルを作成します。 2. XMLデータの挿入 XMLデータを2行分、挿入します。

CREATE TABLE xmltest.emp (doc XML)

INSERT INTO xmltest.emp(doc) VALUES('<dept bldg="101"> <employee id="901"> <name> <first>John</first> <last>Doe</last> </name> <office>344</office> <salary currency="USD">55000</salary> </employee> <employee id="902"> <name> <first>Peter</first> <last>Pan</last> </name> <office>216</office> <phone>905-416-5004</phone> </employee> </dept>’), (‘<dept bldg="114"> <employee id="903"> <name> <first>Mary</first> <last>Jones</last> </name> <office>415</office> <phone>905-403-6112</phone> <phone>647-504-4546</phone> <salary currency="USD">64000</salary> </employee> </dept>');

(3)

3. XMLTABELによる参照

XMLTABELを使用し、XMLデータをリレーショナル・フォーマットで取得して、結果を参照します。

図

図33..XXMLMLTTAABBLLEEにによよるる参参照照結結果果 SELECT X.* FROM xmltest.emp,

XMLTABLE ('$d/dept/employee' passing xmltest.emp.doc as "d" COLUMNS

EMPID INTEGER PATH '@id',

FIRSTNAME VARCHAR(20) PATH 'name/first', LASTNAME VARCHAR(25) PATH 'name/last') AS X

(4)

XMLTABLEを使用したを使用したを使用したを使用したRPGアプリケーションアプリケーションアプリケーションアプリケーションでは、本題のXMLTABLE関数を組み込みSQLとして使用したRPGアプリケーション開発についてご紹介します。このセミナーで紹介するサンプル・アプリケーションでは、ある日時情報を含むXML文書をDB2 for i に格納し、XMLTABLE関数を用いて、データを抽出し、さらに、演算子や関数により期間の平均を計算し、平均日数の降順の結果をスプール・ファイルに出力する処理を実装しています。・・・・サンプルのサンプルのサンプルのサンプルの作成手順作成手順作成手順 作成手順 1. アプリケーションの対象となるテーブルを作成します。System i ナビゲーターの「データベース」機能の「SQL スクリプトの実行」画面で、以下のSQL文を入力し「すべてを実行」をクリックします。カラム名requestがXMLデータ･タイプのカラムとなり、requestにXML文書が格納されます。図図4.4. _【補_【_補足_足】_】作_作成_成し_した_たテ_テー_ーブ_ブル_ルの_の単_単純_純な_な照_照会_会結_結果_果 CREATE TABLE xmltest.order_info(

request_id BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL, request XML NOT NULL,

request_ts TIMESTAMP DEFAULT CURRENT TIMESTAMP, PRIMARY KEY (request_id) );

INSERT INTO xmltest.order_info(request) VALUES ('<OrderInfoRequest> <CustEmail>[email protected]</CustEmail> <MinTs> 2012-04-14T00:00:00 </MinTs> <MaxTs> 2012-04-30T23:59:59 </MaxTs> </OrderInfoRequest>'), ('<OrderInfoRequest> <CustEmail>[email protected]</CustEmail> <MinTs> 2012-03-01T00:00:00 </MinTs> <MaxTs> 2012-06-30T23:59:59 </MaxTs> </OrderInfoRequest>'), ('<OrderInfoRequest> <CustEmail>[email protected]</CustEmail> <MinTs> 2013-04-01T00:00:00 </MinTs> <MaxTs> 2013-05-30T23:59:59 </MaxTs> </OrderInfoRequest>');

(5)

2. 以下のSQL文を組み込みSQLとしてRPGプログラム内で実行します。この手順は、SQL文の確認を目的としているため、実際の検証手順では、省略可能です。 XMLTABLE関数のなかで、xs:dateTime関数を用いて、XML文書内のMaxTsとMinTsの差を期間として算出し、fn:days-from-duration関数を用いて、期間の日数をカラムDAYSに返しています。図図5.5. XXMMLLTTAABBLLEE_関数_関_数を_を使_使用_用し_した_た照_照会_会結_結果_果 3. 2を組み込みSQLとしたRPGプログラムを作成します。ここでは、開発ツールIBM Rational Developer for i を使用してサンプル・プログラムを作成しています。また、以下の参考リンク先で紹介していますIBM i 7.1 RPGⅣ Free-Format 機能拡張を使用できる環境での開発のため、C 仕様書はFree-Formで記述し、/FREEと/END-FREEの記載も省略しています。【参考リンク】IBM i 7.1 RPGⅣ Free-Format 機能拡張 http://www-01.ibm.com/support/docview.wss?uid=jpn1J1011446 SELECT AVG(X.DAYS) AS AVG_DAY_RANGE, X.CUSTOMER FROM XMLTEST.ORDER_INFO,

XMLTABLE('OrderInfoRequest'

PASSING XMLTEST.ORDER_INFO.request COLUMNS

CUSTOMER VARCHAR(255) PATH 'CustEmail', DAYS INTEGER PATH

'fn:days-from-duration(xs:dateTime(MaxTs) - xs:dateTime(MinTs))' ) X

(6)

サ

サンンププルル・・ププロロググララムム：：PPRRIINNTTAAVVGG..ssqqllrprpggllee

HALWNULL(*USRCTL) DEBUG(*YES) MAIN(PRINTAVG) DFTACTGRP(*NO) ACTGRP(*NEW) * Output File, we don't need an input file

FQPRINT O F 107 PRINTER

********************************************************************************* * These Variables are used to hold the values for each column of the Result Rows ********************************************************************************* D CUST_Email S 25A

D Average S 10I 0

********************************************************************************* * These variables are used to hold information about an SQL error if

* an error should occur.

********************************************************************************* D SQLMESSAGE S 3200A varying D SQLMESSAGEL S 107A D SQLMESSAGEI S 10I 0 INZ(0) D RETSCODE S 5P 0 DPRINTAVG PR EXTPGM('PRINTAVG') ********************************************************************************* * Page Header and Column Headings

********************************************************************************* OQPRINT E Headings 1 2

O 8 'PAGE' O PAGE 13

O 40 'AVERAGE NUMBER OF DAYS' O 50 'DATE' O *DATE Y 60 O E Headings 1 O 15 'AVG DAYS' O 21 'EMAIL' O E Headings 1 O 15 '---' O 24 '---' ********************************************************************************* * Order Details ********************************************************************************* O E Detail 1 O Average N 15 O CUST_Email 41

(7)

********************************************************************************* * These lines are output if an SQL Error Occurs.

* It includes the SQL code and message text

********************************************************************************* O E RPTERR 2

O 20 'An Error Occurred!' O E RPTERR 2 O 10 'SQL CODE: ' O RETSCODE P 38 O E RPTERR_Msg 2 O SQLMESSAGEL 107 ********************************************************************************* * PROCEDURE IMPLEMENTATION ********************************************************************************* PPRINTAVG B D PI

* Need commitment control for working with LOB locators exec sql Set Option commit=*CHG

// declare the cursor

// This cursor is the key for everything, it handles both the parsing // of the input XML document, the extraction of the data into columns, // and the conversion of the columns to the correct SQL data types. exec sql DECLARE C1 CURSOR FOR

SELECT AVG(X.DAYS) AS AVG_DAY_RANGE, X.CUSTOMER FROM XMLTEST.ORDER_INFO,

XMLTABLE('OrderInfoRequest'

PASSING ORDER_INFO.REQUEST COLUMNS

CUSTOMER VARCHAR(255) PATH 'CustEmail' , DAYS INTEGER PATH

'fn:days-from-duration(xs:dateTime(MaxTs) - xs:dateTime(MinTs))' ) X

GROUP BY X.CUSTOMER ORDER BY AVG_DAY_RANGE DESC; // open cursor

exec sql OPEN C1;

// output spool file headings EXCEPT Headings;

// fetch first row

exec sql FETCH C1 INTO :AVERAGE, :CUST_EMAIL; // for each successful fetch

DOW SQLCOD = 0; // output details EXCEPT Detail;

(8)

4. 3のサンプル・プログラム・ソースをコンパイルします。コンパイル・コマンド例

CRTSQLRPGI OBJ(TESTLIB/PRINTAVG) SRCFILE(TESTLIB/QRPGLESRC) SRCMBR(PRINTAVG) OPTION(*EVENTF) REPLACE(*YES) DBGVIEW(*SOURCE)

5. サンプル・プログラムを実行します。

実行コマンド例

CALL TESTLIB/PRINTAVG // fetch next row

exec sql FETCH C1 INTO :AVERAGE, :CUST_EMAIL; ENDDO;

/////////////////////////////////////////////////////////////////// // check for error

/////////////////////////////////////////////////////////////////// IF SQLCOD <> 100 ; // 100 is no more records, which is what we expect exec sql GET DIAGNOSTICS CONDITION 1 :SQLMESSAGE = MESSAGE_TEXT, :RETSCODE = DB2_RETURNED_SQLCODE; // dump sqlcode and message into report

EXCEPT RPTERR;

// Print the SQL Message text in lines of 100 bytes DOW SQLMESSAGEI < %len(SQLMESSAGE);

IF %len(SQLMESSAGE) - SQLMESSAGEI > %len(SQLMESSAGEL);

SQLMESSAGEL = %subst(SQLMESSAGE:SQLMESSAGEI+1:%len(SQLMESSAGEL)); ELSE;

SQLMESSAGEL = %subst(SQLMESSAGE:SQLMESSAGEI+1); ENDIF;

SQLMESSAGEI = SQLMESSAGEI + %len(SQLMESSAGEL); EXCEPT RPTERR_Msg;

ENDDO; endif;

///////////////////////////////////////////////////////////////////// // close cursor and spool file

///////////////////////////////////////////////////////////////////// exec sql close C1;

CLOSE(E) *ALL; // program complete EVAL *INLR = *ON; RETURN;

(9)

6. 実行結果として、スプール・ファイルが生成されます。図6の結果のスプール・ファイルは、System i ナビゲーターの「基本操作」、「プリンター出力」機能から参照しています。図図66.. 結果結果ののススププーールル・・フファァイイルル下記、参考リンク先にもXMLTABLEの使い方のサンプル情報が記載されています。【参考リンク】

・IBM i セミナー資料： 2012年 Power Systems スキルアップ・セミナー IBM i （5月～6月） (PDF 4.04MB) p17- DB2 for i 機能面での拡張：XMLTABLE

http://www-06.ibm.com/systems/jp/power/software/i/library/pdf/2012skillup2.pdf ・IBM i Technology Updates ： XMLTABLE（英語）

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20T echnology%20Updates/page/XMLTABLE さいごにさいごにさいごに さいごに DB2 for i でサポートされているXMLTABLE関数を組み込みSQLとして使用したRPGアプリケーションについてサンプル・プログラムの作成手順を含め、ご紹介しました。考慮点としては、XMLTABLEを使用し、XMLデータを照会する場合、通常のRDBのパフォーマンスほどよくはない可能性があります。また、2014年4月時点では、IBM i においてXQueryのサポートはされていません。今回は、簡単なデータ抽出とスプール・ファイルへの出力を例としましたが、Webアプリケーションで XMLデータを使用される場合、RPGアプリケーションと組合せ、さらに応用できる機能と考えます。ぜ

ひIBM i 7.1、IBM i 7.2で機能拡張されたDB2 for i のXMLTABLEをご活用ください。参考リンク：developerWorks: Using RPG to exploit IBM DB2 XML support (英語) https://www.ibm.com/developerworks/ibmi/library/i-using-rpg/