Using RPG to exploit IBM DB2 XML support

This article presents several examples of using RPG to leverage the SQL/XML function delivered as part of IBM® DB2® for i 7.1. It shows how to embed SQL statements into an RPG program to perform relational queries that involve XML data and produce XML results. The SQL/XML publishing functions and the recently announced XMLTABLE function are demonstrated in this article.

Share:

Nick Lawrence (ntl@us.ibm.com), Advisory Software Engineer, IBM

Photo of  Nick LawrenceNick Lawrence is an Advisory Software Engineer at IBM in Rochester, Minnesota. His responsibilities include IBM DB2 for i development, including SQL/XML, XPath, and Full Text Search.You can reach him at ntl@us.ibm.com.



26 September 2012

Also available in Chinese

Using RPG to exploit DB2 XML support

Modernizing and web-enabling applications are some of the important goals for any business, and a challenge for enterprises that have been around for a while. These companies usually want to focus their IT efforts on improving the infrastructure that has brought them success in the past, rather than writing new applications. On the IBM i platform, modernizing frequently means web enabling a database application that is written in Integrated Language Environment (ILE) RPG. Due to the large number of XML standards that exist for transmitting data over the web, having XML capabilities available in RPG is very important. What an RPG programmer needs to realize is that when DB2 for i introduced a native XML data type, every language that supports embedded SQL (including RPG) received a whole set of new options for dealing with XML alongside traditional database data.

Using data from an XML document in a relational query

Let us suppose that I have an application that tracks customer orders in a relational database table. To accomplish this task, I need to create the following ORDERS table in my schema (RPG_ORDERS), using SQL and insert some records into it:

Listing 1: SQL create and insert statements
CREATE TABLE rpg_orders.orders( 
	order_id BIGINT GENERATED ALWAYS AS IDENTITY( 
	                START WITH 1000 INCREMENT BY 1 
	                NO MINVALUE NO MAXVALUE 
	                NO CYCLE NO ORDER 	CACHE 20 ),
	Cust_email VARCHAR(50), 
	Order_ts   TIMESTAMP , 
	Product    VARCHAR(50) CCSID 37, 
	Price      DOUBLE PRECISION , 
	PRIMARY KEY(order_id) )   
	; 

INSERT INTO rpg_orders.orders 
(cust_email, order_ts, product, price)
VALUES

('ntl@us.ibm.com',
 '2012-04-15 13:00:00',
 'Camera',
  999.50
),

('ntl@us.ibm.com',
 '2012-04-16 12:00:00',
 'lens',
 500.25),

('ntl@us.ibm.com',
 '2012-04-01 11:00:00',
 'Book',
  15.00),

('george@nowhere.com',
 '2012-04-15 13:05:00',
 'Book',
 20) ;

My web-based application provides an XML document to my program requesting that a report be generated for orders that have a matching email address and were made within a specific time period.

An example request document is shown in Listing 2.

Listing 2: Sample information request document
<?xml version="1.0" encoding="UTF-8" ?>
<OrderInfoRequest>
  <CustEmail>ntl@us.ibm.com</CustEmail>
  <MinTs> 2012-04-14T:00:00:00 </MinTs>
  <MaxTs> 2012-04-30T23:59:59 </MaxTs>
</OrderInfoRequest>

My first RPG program retrieves an XML information request from a file and uses it to produce a report in a spool file. We could imagine that we are reading this data from standard input, or from a socket — but for a simple example, a stream file is enough to get the idea.

Implementing the query in SQL allows the DB2 optimizer to determine the best way to retrieve the relational data, and revise that determination if another access plan that can perform better becomes available later on. This makes it highly desirable to use SQL as much as possible to retrieve data from the database.

A less-than-satisfying solution that has been done before is to extract the values from the XML document into host variables using RPG opcodes, convert the values into an SQL appropriate type, and finally use an SQL query to retrieve the relational records. Special consideration of type conversions is usually necessary with this approach, as XML data types often times do not share the same lexical representation as their SQL equivalents. For example, the data type for xs:dateTime does not have the same lexical format as the SQL timestamp data type, nor does it match a native RPG timestamp type.

In this example, the XML-INTO or XML-SAX opcode could be used to extract the email address and timestamp range into host variables, followed by some data-type conversion processing, and (finally) an SQL query to retrieve the matching records from the database table. This solution involves significant processing outside of the DB2 awareness, and introduces a greater risk of error.

Another option that has been used in the past is to use a transformation function to transform an XML document into an SQL query string. The query string is later prepared and run. This is often a complex process that has the same problem as the previous solution. It also has the additional concern that a malicious user might be able to construct an XML document that causes an unintended SQL statement to run.

With the new XML support built into DB2, the XML handling can be included in an embedded SQL query that returns the requested relational data. The embedded query handles the data type conversions and ensures that the data obtained from the XML document is only used as values in the query.

Listing 3: SQL query for program 1
SELECT 
    ORDER_ID, 
    ORDER_TS, 
    TRIM(PRODUCT),
    PRICE,
    TRIM("CustEmail")
  FROM RPG_ORDERS.ORDERS,
       XMLTABLE('OrderInfoRequest'
         PASSING XMLPARSE(DOCUMENT GET_XML_FILE(:file_name))
         COLUMNS "CustEmail" VARCHAR(255),
                 "MinTs"     Timestamp,
                 "MaxTs"     Timestamp
       ) info_req
  WHERE
    ORDERS.CUST_EMAIL = INFO_REQ."CustEmail"  AND
    ORDERS.ORDER_TS  >= INFO_REQ."MinTs"      AND
    ORDERS.ORDER_TS  <= INFO_REQ."MaxTs";

The GET_XML_FILE function returns a BLOB from the stream file at the specified path. The XML data in the BLOB has been converted to UTF-8 encoding. The function is a handy way to get XML data into the database from an IFS path and at the same time, avoids the need to copy the XML data from the file into a host variable.

The XMLPARSE function is used to convert the BLOB data into an instance of the XML data.

In the query in Listing 3, XMLTABLE returns a result set with one row for each OrderInfoRequest, having the columns CustEmail, MinTs, and MaxTs from the XML document that has been retrieved from the file. Because I have joined the result set from XMLTABLE with my database table, I can use this query to fetch the records that I am interested in.

The complete listing of the program is included in Listing 4.

Listing 4: Source code for program 1
HALWNULL(*USRCTL) DEBUG(*YES) MAIN(PRINTRPT) 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 ORDERID         S              9  0
D ORDER_TS        S               Z
D PRICE           S              9  2
D PRODUCT         S             25A
D CUST_Email      S             25A
 ********************************************************************
 * The XML Document is read from this file on disk
 ********************************************************************
D file_name       S             27A   INZ('/home/ntl/xml_input_doc.xml')
 ********************************************************************
 * 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
D*
DPRINTRPT         PR                  EXTPGM('PRINTRPT')
 *
 *
 **********************************************************************
 * Page Header and Column Headings
 ********************************************************************
OQPRINT    E            Headings          1  2
O                                            8 'PAGE'
O                       PAGE                13
O                                           63 'MATCHING ORDER REPORT'
O                                           96 'DATE'
O                       *DATE         Y    107
O          E            Headings       1
O                                           15 'ORDER ID'
O                                           21 'EMAIL'
O                                           52 'TIMESTAMP'
O                                           79 'PRICE'
O                                           89 'PRODUCT'
O          E            Headings       1
O                                           15 '--------'
O                                           21 '-----'
O                                           52 '---------'
O                                           79 '-----'
O                                           89 '-------'
 **************************************************************
 * Order Details
 **************************************************************
O          E            Detail         1
O                       ORDERID       N     15
O                       Cust_Email          41
O                       ORDER_TS            69
O                       PRICE         N     81 '$'
O                       PRODUCT            107
 *
 **************************************************************
 * 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
 ***************************************************************
 ***************************************************************
PPRINTRPT         B
D                 PI
 *
 *
 * Need commitment control for working with LOB locators
 *
C/Exec SQL
C+ Set Option commit=*CHG
C/End-Exec
 /FREE
  // 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 ORDER_ID, ORDER_TS, TRIM(PRODUCT), PRICE,
              TRIM("CustEmail")
       FROM RPG_ORDERS.ORDERS,
            XMLTABLE('OrderInfoRequest'
                     PASSING XMLPARSE(DOCUMENT GET_XML_FILE(:file_name))
                     COLUMNS "CustEmail" VARCHAR(255),
                             "MinTs"     Timestamp,
                             "MaxTs"     Timestamp
                    ) info_req
       WHERE
            ORDERS.CUST_EMAIL = INFO_REQ."CustEmail"  AND
            ORDERS.ORDER_TS  >= INFO_REQ."MinTs"      AND
            ORDERS.ORDER_TS  <= INFO_REQ."MaxTs";

    // open cursor
  exec sql OPEN C1;

  // output spool file headings
  EXCEPT Headings;
  // fetch first row
  exec sql FETCH C1 INTO :ORDERID, :ORDER_TS, :PRODUCT, :PRICE,
                         :Cust_Email;

  // for each successful fetch
  DOW SQLCOD = 0;
   // output details
   EXCEPT Detail;

   // fetch next row
   exec sql
   FETCH C1 INTO :ORDERID, :ORDER_TS, :PRODUCT, :PRICE,
                 :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;
 /END-FREE

P                 E

The spooled file that results from the program and the sample data is shown in Figure 1.

Figure 1: Spool file output from Program 1
Spool file output from Program 1

Creating an XML response from relational data

A second scenario needs to be considered. Suppose I have the same type of information request, but this time I want to send the requestor an XML response, instead of generating a spool file report from the query.

The trick here is to use our XML publishing functions to construct the XML response document. The simplest method is to use a common table expression to build the inner parts of the XML document, and then construct the outer layers of the document by arrogating the inner pieces.

The SQL query used in this second program is shown in Listing 5.

Listing 5: SQL query for program 2
WITH matching_orders as (
   SELECT
     XMLELEMENT(NAME "MatchingOrder",
                XMLFOREST(ORDER_ID   AS "OrderId",
                          CUST_EMAIL AS "CustEmail",
                          ORDER_TS   AS "OrderTs",
                          PRODUCT    AS "Product",
                          PRICE      AS "Price")
                ) AS ORDER
   FROM RPG_ORDERS.ORDERS,
        XMLTABLE('OrderInfoRequest'
         PASSING XMLPARSE(DOCUMENT GET_XML_FILE(:file_name))
         COLUMNS "CustEmail" VARCHAR(255),
                 "MinTs"     Timestamp,
                 "MaxTs"     Timestamp
        ) info_req
   WHERE
    ORDERS.CUST_EMAIL = INFO_REQ."CustEmail"  AND
    ORDERS.ORDER_TS  >= INFO_REQ."MinTs"      AND
    ORDERS.ORDER_TS  <= INFO_REQ."MaxTs"
  )
  SELECT
   XMLSERIALIZE(
    XMLDOCUMENT(
     XMLELEMENT(NAME "InfoRequestResponse",
                XMLAGG(matching_orders.ORDER)
                )
   ) AS VARCHAR(15000) CCSID 37 INCLUDING XMLDECLARATION
  ) AS RESPONSE
  INTO :ResultXML
  FROM matching_orders  ;

For the request document in listing 2, the resulting document will look like listing 6.

Listing 6: Data in output file created by program 2
<?xml version="1.0" encoding="IBM037"?> 
 <InfoRequestResponse>
    <MatchingOrder>
     <OrderId>1000</OrderId>
     <CustEmail>ntl@us.ibm.com</CustEmail>
     <OrderTs>2012-04-15T13:00:00.000000</OrderTs>
     <Product>Camera</Product>
     <Price>9.995E2</Price>
    </MatchingOrder>
    <MatchingOrder>
      <OrderId>1001</OrderId>
      <CustEmail>ntl@us.ibm.com</CustEmail>
      <OrderTs>2012-04-16T12:00:00.000000</OrderTs>
     <Product>lens</Product>
     <Price>5.0025E2</Price>
    </MatchingOrder>
</InfoRequestResponse>

Using the publishing functions to construct an XML document makes it a lot easier to create a well-formed XML document from relational data. The data types are automatically converted from SQL to XML. The constructor functions always ensure that each XML tag is well formed. DB2 manages the encoding of the XML document and makes sure that the encoding declaration is correct when the document is serialized.

Listing 7 contains the complete RPG example. It includes the code to load the information request from a stream file, perform the query, and write out an XML response to an output stream file. We can easily imagine how the example could be extended to perform the input and output using sockets or HTTP connections instead of files.

You should be aware that Listing 6 has been formatted in this article for readability. As the indenting and formatting of an XML document is not required by the standard, nor is it required by a consumer, DB2 will not add line breaks or indentation to the document during serialization. If you want to see an XML file formatted for display, you can easily open it in a web browser or an XML editor. When no style sheet is available, most web browsers will display the XML as a document tree.

This particular program encodes the XML response in CCSID 37 (EBCDIC). An actual web application is more likely to use UTF-8, but 37 is easier to work with from green-screen interfaces, and it is a trivial change to alter the program to work with CCSID 1208 instead.

A limitation in this example program is that the result document cannot be greater than about 15 K in size. If truncation or any other SQL problem occurs, the sample program constructs an XML document that includes the SQL error information and uses that as a response. A real program could of course have some better handling routines.

The method used to create a response for errors is interesting. Some developers might find the approach of using embedded SQL to create an XML document from host variables to be simpler to write than a traditional string concatenation solution, as it is easier to ensure that the XML document remains well formed.

Listing 7 contains the completed program.

Listing 7: Program 2
HALWNULL(*USRCTL) DEBUG(*YES) MAIN(SENDRPTX) DFTACTGRP(*NO) ACTGRP(*NEW)
 ********************************************************************
 * The XML Document is read from this file on disk
 ********************************************************************
D file_name       S             27A   INZ('/home/ntl/xml_input_doc.xml')
 ********************************************************************
 * The XML Response is written to this file on disk
 ********************************************************************
D resp_file       S             28A   INZ('/home/ntl/xml_output_doc.xml')
 ********************************************************************
 * Result XML Value
 * This has the limitation of only supporting 15000 bytes.
 * We could use an SQL BLOB to support up to 2G, but for this example
 * 15K is more than enough.
 ********************************************************************
D ResultXML       S          15000A   varying
 ********************************************************************
 * These variables are used to hold information about an SQL error if
 * an error should occur.
 ********************************************************************
D SQLMESSAGE      S           3200A   varying
D RETSCODE        S              5P 0
 *
D fd              S             10I 0
D rc              S             10I 0
 *
DSENDRPTX         PR                  EXTPGM('SENDRPTX')
 *
 * Definition of IFS Open Procedure
D open            PR            10I 0 extproc('open')
D   path                          *   value options(*string)
D   oflag                       10I 0 value
D   mode                        10U 0 value options(*nopass)
D   codepage                    10U 0 value options(*nopass)
 * Definition of IFS Write Procedure
D write           PR            10I 0 extproc('write')
D   filedes                     10I 0 value
D   buf                           *   value
D   nbyte                       10U 0 value
 * Definition of IFS Close Procedure
D close           PR            10I 0 extproc('close')
D  filedes                      10I 0 value
 *

 ***************************************************************
 ***************************************************************
 * PROCEDURE IMPLEMENTATION
 ***************************************************************
 ***************************************************************
PSENDRPTX         B
D                 PI
 *
 *
 * Need commitment control for working with LOB locators
 *
C/Exec SQL
C+ Set Option commit=*CHG
C/End-Exec
 /FREE
   exec sql declare :ResultXml  VARIABLE CCSID 37;
   exec sql declare :SQLMESSAGE VARIABLE CCSID 37;
   // This query is the key for everything, it handles both the parsing

   // of the input XML document, the extraction of the data into columns,
   // the conversion of the columns to the correct SQL data types for
   // the join, in addition, this query generates the XML result
   exec sql
   WITH matching_orders as (
       SELECT
        XMLELEMENT(NAME "MatchingOrder",
                   XMLFOREST(ORDER_ID   AS "OrderId",
                             CUST_EMAIL AS "CustEmail",
                             ORDER_TS   AS "OrderTs",
                             PRODUCT    AS "Product",
                             PRICE      AS "Price")
                  ) AS ORDER
       FROM RPG_ORDERS.ORDERS,
            XMLTABLE('OrderInfoRequest'
                     PASSING XMLPARSE(DOCUMENT
                                      GET_XML_FILE(:file_name)
                                     )
                     COLUMNS "CustEmail" VARCHAR(255),
                             "MinTs"     Timestamp,
                             "MaxTs"     Timestamp
                    ) info_req
       WHERE
            ORDERS.CUST_EMAIL = INFO_REQ."CustEmail"  AND
            ORDERS.ORDER_TS  >= INFO_REQ."MinTs"      AND
            ORDERS.ORDER_TS  <= INFO_REQ."MaxTs"

  )
  SELECT
   XMLSERIALIZE(
    XMLDOCUMENT(
     XMLELEMENT(NAME "InfoRequestResponse",
                XMLAGG(matching_orders.ORDER)
               )
   ) AS VARCHAR(15000) CCSID 37 INCLUDING XMLDECLARATION
  ) AS RESPONSE
  INTO :ResultXML
  FROM matching_orders;

  ////////////////////////////////////////////////////////////////////////
  // check for error
  // If error, build invalid request response
  ////////////////////////////////////////////////////////////////////////
  IF SQLCOD <> 0 ;
   exec sql GET DIAGNOSTICS CONDITION 1  :SQLMESSAGE = MESSAGE_TEXT,
                                         :RETSCODE = DB2_RETURNED_SQLCODE;

   exec sql VALUES
       XMLSERIALIZE(
       XMLDOCUMENT(
        XMLELEMENT(NAME "SQLError",
                   XMLELEMENT(NAME "SQLCODE",
                                    :RETSCODE),
                       XMLELEMENT(NAME "MESSAGE",
                                       :SQLMESSAGE)
                      )
           )
           AS VARCHAR(15000) CCSID 37
      ) INTO :ResultXML;
  ENDIF;
  /////////////////////////////////////////////////////////////////////
  // Write response into a stream file
  /////////////////////////////////////////////////////////////////////
  EVAL fd = open(resp_file: 74 : 511);
  EVAL rc = write(fd:%addr(ResultXML)+2:%len(ResultXML));
  EVAL rc = close(fd);
  // program complete
  EVAL *INLR = *ON;
  RETURN;
 /END-FREE

P                 E

Performing a query over an XML column

There is one more important case to consider when dealing with XML. Sometimes, XML documents might be stored in a DB2 column that is using the XML data type. The reason for this is that when there is a need to preserve every relationship defined by the document, it can be very difficult to define a relational model that keeps a complete representation of each document. If we keep the data in the XML model, then XMLTABLE can be used to perform relational queries over the XML data as needed. Using XMLTABLE is very useful for converting the XML data to the result set needed for a particular SQL query, assuming the XML data cannot be shredded to a relational model in advance. (The performance of using XMLTABLE is not as good as a relational query over relational data.)

Complex queries over XML data are possible in these scenarios. For more complicated queries, XMLTABLE supports many built-in functions and operators. These allow for advanced queries, rather than the simple extraction we have seen so far.

For the sake of discussion, let us assume that I will store the information requests received in a database table's XML column, and I currently have created a table and inserted the records described in Listing 8.

Listing 8: SQL statements to store information-request XML documents
CREATE TABLE order_info_requests(
   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 order_info_requests(request)
VALUES
('<?xml version="1.0"  ?>
<OrderInfoRequest>
  <CustEmail>ntl@us.ibm.com</CustEmail>
  <MinTs> 2012-04-14T00:00:00 </MinTs>
  <MaxTs> 2012-04-30T23:59:59 </MaxTs>
</OrderInfoRequest>'),

('<?xml version="1.0" ?>
<OrderInfoRequest>
  <CustEmail>george@nowhere.com</CustEmail>
  <MinTs> 2012-03-01T00:00:00 </MinTs>
  <MaxTs> 2012-06-30T23:59:59 </MaxTs>
</OrderInfoRequest>');

I would now like a spool file report that shows, for order information requests on each user, the average number of days between the minimum and maximum timestamps. This information might give me some insights into how customers are using my application and what kind of improvements can be made.

Without XMLTABLE, this might take a lot of code. Fortunately, the supported XPath syntax has a rich set of built-in functions for making these calculations.

I can do this calculation with the SQL query in listing 9.

Listing 9: SQL query for program 3
SELECT AVG(X.DAYS) AS AVG_DAY_RANGE, X.CUSTOMER
  FROM RPG_ORDERS.ORDER_INFO_REQUESTS,
       XMLTABLE('OrderInfoRequest'
                PASSING ORDER_INFO_REQUESTS.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;

The built-in operators for the dateTime arithmetic and the built-in function
fn:days-from-duration are used in this example to calculate the number of days between the MaxTs and MinTs dateTime values in the XML document. The integer result is returned in the Days Column.

The completed program is shown in Listing 10.

Listing 10: Source code for program 3
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                                           64 'AVERAGE NUMBER OF DAYS'
O                                           96 'DATE'
O                       *DATE         Y    107
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
 *
 **************************************************************
 * 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
 *
C/Exec SQL
C+ Set Option commit=*CHG
C/End-Exec
 /FREE
   // 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 RPG_ORDERS.ORDER_INFO_REQUESTS,
            XMLTABLE('OrderInfoRequest'
                     PASSING ORDER_INFO_REQUESTS.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;

    // 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;
 /END-FREE

P                 E
Figure 2: Output spool file from program 3
Output spool file from program 3

Summary

We have seen three examples where embedded SQL has been included into an RPG program to perform database activities that involve both the relational and XML data models. We have seen how to include XML data in an SQL query, and how to produce an XML document from an SQL query. Also, this article demonstrated the idea that XMLTABLE is not limited to simple extraction and can be used for more complex queries of the XML data.

Hopefully, you are convinced that there is a great deal of value in the new SQL/XML support in DB2 for i 7.1, it really does make it much easier to integrate XML and relational data together into an application. This article is only the beginning, and the SQL/XML support in DB2 for i is substantial enough to receive its own book in the IBM i information center, and it can take some time and effort to master.

Finding ways to use the new technology is worth the effort and resources. A good option that IBM i customers have is the IBM Systems Lab Services and Training. Lab Services can help with training and consulting, which for some customers may be the best path to a modernized application.


Resources

There is also very detailed information on SQL/XML for IBM i on the web. You can use the following online references to get more information.

Information center SQL/XML programming guide

Tutorial for XMLTABLE

i Can blog entry

Articles on XML data model for IBM i:

XML Meets DB2 for i: Getting Started With the XML Data Type Using DB2 for IBM i

Using XML With DB2 for i: How to Use XML Data Type With DB2 for IBM i

Now Introducing XML in SQL on DB2 for IBM i!

XMLTABLE announcement

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into IBM i on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=IBM i
ArticleID=834573
ArticleTitle=Using RPG to exploit IBM DB2 XML support
publish-date=09262012