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

Size: px

Start display at page:

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

なおみこいたばし
6 years ago
Views:

DB2 for i XMLTABLE XML データタイプを使用した RPG アプリケーション開発のご紹介はじめにアプリケーションの近代化 Web 化が近年多くの企業において検討されています IBM i では ILE RPG(RPGⅣ) により Java 等の複数言語の混在 XMLサポートなどが可能となりアプリケーションの近代化 Web 化へ対応できるよう機能拡張がなされています

1 以降では DB2 for i においてXMLをサポートする機能拡張がされており XMLを利用したWebアプリケーション開発が可能です今回は DB2 for i でサポートされているXMLTABLE 関数を組み込みSQL として使用したRPGアプリケーションについてご紹介します XMLTABLE 関数により Webアプリケーションで使用している XMLデータをDB2 for i に格納し

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/S V6R1) 現代の開発言語としてのILE-RPG 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データタイプのカラムを含むテーブルの照会

2 ここでは 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> </phone> </employee> </dept> ), ( <dept bldg="114"> <employee id="903"> <name> <first>mary</first> <last>jones</last> </name> <office>415</office> <phone> </phone> <phone> </phone> <salary currency="usd">64000</salary> </employee> </dept>');

3 3. XMLTABEL による参照 XMLTABEL を使用し XML データをリレーショナルフォーマットで取得して結果を参照します SELECT X.* FRM xmltest.emp, XMLTABLE ('$d/dept/employee' passing xmltest.emp.doc as "d" CLUMNS EMPID INTEGER PATH 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 関数を用いてデータを抽出しさらに演算子や関数により期間の平均を計算し

4 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> <Cust >ntl@us.ibm.com</Cust > <MinTs> T00:00:00 </MinTs> <MaxTs> T23:59:59 </MaxTs> </rderinforequest>'), ('<rderinforequest> <Cust >george@nowhere.com</Cust > <MinTs> T00:00:00 </MinTs> <MaxTs> T23:59:59 </MaxTs> </rderinforequest>'), ('<rderinforequest> <Cust >george@nowhere.com</Cust > <MinTs> T00:00:00 </MinTs> <MaxTs> T23:59:59 </MaxTs> </rderinforequest>'); 図 4. 補足作成したテーブルの単純な照会結果

2. 以下の SQL 文を組み込み SQL として RPG プログラム内で実行しますこの手順は SQL 文の確認を目的としているため実際の検証手順では省略可能です XMLTABLE 関数のなかで xs:datetime 関数を用いて XML 文書内の MaxTs と MinTs の差を期間として算出し fn:days-from-duration 関数を用いて期間の日数をカラム DAYS

5 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 'Cust ', 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 機能拡張

6 サンプルプログラム :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_ 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 'PAGE' PAGE 'AVERAGE NUMBER F DAYS' 50 'DATE' *DATE Y 60 E Headings 1 15 'AVG DAYS' 21 ' ' E Headings 1 15 ' ' 24 ' ' * rder Details E Detail 1 Average N 15 CUST_ 41

7 * 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 'Cust ', 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_ ; // for each successful fetch DW SQLCD = 0; // output details EXCEPT Detail;

8 // fetch next row exec sql FETCH C1 INT :AVERAGE, :CUST_ ; 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

9 6. 実行結果としてスプールファイルが生成されます図 6 の結果のスプールファイルは System i ナビゲーターの基本操作プリンター出力機能から参照しています図 6. 結果のスプールファイル下記参考リンク先にも XMLTABLE の使い方のサンプル情報が記載されています参考リンク IBM i セミナー資料 : 2012 年 Power Systems スキルアップセミナー IBM i (5 月 ~6 月 ) (PDF 4.04MB) p17- DB2 for i 機能面での拡張 :XMLTABLE IBM i Technology Updates : XMLTABLE( 英語 ) 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 ( 英語 )

XML Consortium & XML Consortium 1 XML Consortium XML Consortium 2

XML Consortium & XML Consortium 1 XML Consortium XML Consortium 2 & 1 2 TCO DB2 DB2 UDB DB DB V8.2 V8.2 DB2 DB2 UDB V8.1 V8.1 DB2 9 3 CLOB XML XML DB2 9 purexml XML XML DOC XML DOC XML DOC XML DOC VARCHAR/CLOB XML ( ) 4 XML & XML ( & ) DB2 XML SQL/XML DB2 DB2 : DB2 /