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/S 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の環境で使用できます 図 1. XMLデータのリレーショナル フォーマットでの取得 XMLTABLE のサンプル今回のインターネット セミナーでは XMLTABLEを使用したRPG のサンプルをご紹介します まずは RPGの組み込みSQLのプログラムを紹介する前に IBM i Navigatorから単純な XMLTABLEを使用したSQL 文のサンプルをご紹介します IBM i 7.1では 図 2のようにXMLデータ タイプのカラムを含むテーブルを作成することができます 図 2.XMLデータ タイプのカラムを含むテーブルの照会
ここでは XML 文書を INSERT したテーブルに対して XMLTABEL を使用し XML データをリレーショナル フォーマットで取得して 結果を表示する SQL 文を紹介しています ここで紹介している SQL 文の検証をされる際は System i ナビゲーターの データベース 機能の SQL スクリプトの実行 の利用を推奨します STRSQL などの対話型 SQL では XML データ タイプを XML 文書の形式で表示することができません 1. テーブルの作成 xmltest スキーマにカラム名 doc データ タイプ XML のカラムを持つ emp テープルを作成します CREATE TABLE xmltest.emp (doc XML) 2. XML データの挿入 XML データを 2 行分 挿入します INSERT INT 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. XMLTABEL による参照 XMLTABEL を使用し XML データをリレーショナル フォーマットで取得して 結果を参照します SELECT X.* FRM xmltest.emp, XMLTABLE ('$d/dept/employee' passing xmltest.emp.doc as "d" CLUMNS EMPID INTEGER PATH '@id', FIRSTNAME VARCHAR(20) PATH 'name/first', LASTNAME VARCHAR(25) PATH 'name/last') AS X 図 3.XMLTABLE による参照結果
XMLTABLE を使用した RPG アプリケーションでは 本題のXMLTABLE 関数を組み込みSQLとして使用した RPGアプリケーション開発についてご紹介します このセミナーで紹介するサンプル アプリケーションでは ある日時情報を含む XML 文書をDB2 for i に格納し XMLTABLE 関数を用いて データを抽出し さらに 演算子や関数により期間の平均を計算し 平均日数の降順の結果をスプール ファイルに出力する処理を実装しています サンプルのサンプルの作成手順 1. アプリケーションの対象となるテーブルを作成します System i ナビゲーターの データベース 機能の SQL スクリプトの実行 画面で 以下のSQL 文を入力し すべてを実行 をクリックします カラム名 requestがxmlデータ タイプのカラムとなり requestにxml 文書が格納されます CREATE TABLE xmltest.order_info( request_id BIGINT GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) NT NULL, request XML NT NULL, request_ts TIMESTAMP DEFAULT CURRENT TIMESTAMP, PRIMARY KEY (request_id) ); INSERT INT xmltest.order_info(request) VALUES ('<rderinforequest> <CustEmail>ntl@us.ibm.com</CustEmail> <MinTs> 2012-04-14T00:00:00 </MinTs> <MaxTs> 2012-04-30T23:59:59 </MaxTs> </rderinforequest>'), ('<rderinforequest> <CustEmail>george@nowhere.com</CustEmail> <MinTs> 2012-03-01T00:00:00 </MinTs> <MaxTs> 2012-06-30T23:59:59 </MaxTs> </rderinforequest>'), ('<rderinforequest> <CustEmail>george@nowhere.com</CustEmail> <MinTs> 2013-04-01T00:00:00 </MinTs> <MaxTs> 2013-05-30T23:59:59 </MaxTs> </rderinforequest>'); 図 4. 補足 作成したテーブルの単純な照会結果
2. 以下の SQL 文を組み込み SQL として RPG プログラム内で実行します この手順は SQL 文の確認を目的としているため 実際の検証手順では 省略可能です XMLTABLE 関数のなかで xs:datetime 関数を用いて XML 文書内の MaxTs と MinTs の差を期間として算出し fn:days-from-duration 関数を用いて 期間の日数をカラム DAYS に返しています SELECT AVG(X.DAYS) AS AVG_DAY_RANGE, X.CUSTMER FRM XMLTEST.RDER_INF, XMLTABLE('rderInfoRequest' PASSING XMLTEST.RDER_INF.request CLUMNS CUSTMER VARCHAR(255) PATH 'CustEmail', DAYS INTEGER PATH 'fn:days-from-duration(xs:datetime(maxts) - xs:datetime(mints))' ) X GRUP BY X.CUSTMER RDER BY AVG_DAY_RANGE DESC; 図 5. XMLTABLE 関数を使用した照会結果 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
サンプル プログラム :PRINTAVG.sqlrpgle HALWNULL(*USRCTL) DEBUG(*YES) MAIN(PRINTAVG) DFTACTGRP(*N) ACTGRP(*NEW) * utput File, we don't need an input file FQPRINT 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 RETSCDE S 5P 0 DPRINTAVG PR EXTPGM('PRINTAVG') * Page Header and Column Headings QPRINT E Headings 1 2 8 'PAGE' PAGE 13 40 'AVERAGE NUMBER F DAYS' 50 'DATE' *DATE Y 60 E Headings 1 15 'AVG DAYS' 21 'EMAIL' E Headings 1 15 '--------' 24 '--------' * rder Details E Detail 1 Average N 15 CUST_Email 41
* These lines are output if an SQL Error ccurs. * It includes the SQL code and message text E RPTERR 2 20 'An Error ccurred!' E RPTERR 2 10 'SQL CDE: ' RETSCDE P 38 E RPTERR_Msg 2 SQLMESSAGEL 107 * PRCEDURE IMPLEMENTATIN PPRINTAVG B D PI * Need commitment control for working with LB locators exec sql Set ption 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 CURSR FR SELECT AVG(X.DAYS) AS AVG_DAY_RANGE, X.CUSTMER FRM XMLTEST.RDER_INF, XMLTABLE('rderInfoRequest' PASSING RDER_INF.REQUEST CLUMNS CUSTMER VARCHAR(255) PATH 'CustEmail', DAYS INTEGER PATH 'fn:days-from-duration(xs:datetime(maxts) - xs:datetime(mints))' ) X GRUP BY X.CUSTMER RDER BY AVG_DAY_RANGE DESC; // open cursor exec sql PEN C1; // output spool file headings EXCEPT Headings; // fetch first row exec sql FETCH C1 INT :AVERAGE, :CUST_EMAIL; // for each successful fetch DW SQLCD = 0; // output details EXCEPT Detail;
// fetch next row exec sql FETCH C1 INT :AVERAGE, :CUST_EMAIL; P ENDD; /////////////////////////////////////////////////////////////////// // check for error /////////////////////////////////////////////////////////////////// IF SQLCD <> 100 ; // 100 is no more records, which is what we expect exec sql GET DIAGNSTICS CNDITIN 1 :SQLMESSAGE = MESSAGE_TEXT, :RETSCDE = DB2_RETURNED_SQLCDE; // dump sqlcode and message into report EXCEPT RPTERR; // Print the SQL Message text in lines of 100 bytes DW 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; ENDD; endif; ///////////////////////////////////////////////////////////////////// // close cursor and spool file ///////////////////////////////////////////////////////////////////// exec sql close C1; CLSE(E) *ALL; // program complete EVAL *INLR = *N; RETURN; E 4. 3 のサンプル プログラム ソースをコンパイルします コンパイル コマンド例 CRTSQLRPGI BJ(TESTLIB/PRINTAVG) SRCFILE(TESTLIB/QRPGLESRC) SRCMBR(PRINTAVG) PTIN(*EVENTF) REPLACE(*YES) DBGVIEW(*SURCE) 5. サンプル プログラムを実行します 実行コマンド例 CALL TESTLIB/PRINTAVG
6. 実行結果として スプール ファイルが生成されます 図 6 の結果のスプール ファイルは System i ナビゲーターの 基本操作 プリンター出力 機能から参照しています 図 6. 結果のスプール ファイル 下記 参考リンク先にも 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/