• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: DB2 For i XML Composition And The IFS

    June 11, 2018 Michael Sansoterra

    Hey, Mike! Regarding Composing An XML Document From Relational Data, Part 1, I have built an XML document using DB2 and i. When I run the query, I get a worthless result set. How do I use the SQL XML functions to get a usable XML file?

    This question comes from reader RA, and he doesn’t exaggerate. The result set from his XML-based query looks like this:

    ....+....1....+....2....+....3....+....4....+
    ************Beginning of data************** 
    
    XMLDATA 
    -------- 
    #CGULIB#
    
      1 RECORD(S) SELECTED.
    
     ************End of Data********************
    

    Notice that DB2 for i does nothing to make the XML attractive to human eyes as the entire XML document is stored on one line. Since only the XML data is desired, the header and row count information lines should be removed. Fortunately, the grep utility can be used to return just the XML data. The grep utility acts like a filter so it will be used to return line(s) in the file that have a lower-case “xml” value. With the addition of grep, the db2 results will be filtered to include only the XML data line:

    db2 'select * from qgpl.my_schemas' |grep xml >/tmp/schemas.xml
    

    To summarize the behavior of the above qshell statement, the db2 utility runs the query and hands the data over to the grep utility, which returns only lines that contain the value “xml”. Thereafter, the result of the query (after the grep filter) is placed in the IFS file /tmp/schemas.xml. Now when you review the IFS file using dspf ‘/tmp/schemas.xml’, only the desired XML data appears.

    By default, the IFS file on my system was tagged with CCSID 37 (US EBCDIC). If you have an XML declaration (<?xml version=”1.0″ encoding=”UTF-8″?>), such as the one I included in my XML file where UTF-8 is specified, then you will want to change your IFS file’s CCSID to match the declaration. On the IBM i, UTF-8 is specified using CCSID 1208. You can change the CCSID encoding of the file with this CL command:

    CHGATR OBJ('/tmp/schemas.xml') ATR(*CCSID) VALUE(1208)
    

    This CL command can also be run in a qshell script by using the system utility.

    Alternative Approaches

    I tried using CPYTOIMPF to see if I could simplify this mess but unfortunately it doesn’t like to play with tables containing large object data types. If your XML will always be a single-byte character set and less than 32KB, you could cast your XML to VARCHAR and then use CPYTOIMPF:

    CREATE OR REPLACE VIEW QGPL.MY_SCHEMAS AS
    SELECT XMLSERIALIZE(
           XMLELEMENT(NAME "Schemas",
           XMLAGG(XMLELEMENT(NAME "Schema", SCHEMA_NAME)))
           AS VARCHAR(32700) CCSID 1208 INCLUDING
           XMLDECLARATION)  AS XML
      FROM SYSSCHEMAS
    
    CPYTOIMPF FROMFILE(QGPL/MY_SCHEMAS)
              TOSTMF('/tmp/schemas2.xml')
              MBROPT(*REPLACE)
              STMFCCSID(1208)
              RCDDLM(*LF)
              DTAFMT(*DLM)
              STRDLM(*NONE)
    

    This option is a little easier to follow than qshell and even lets you set the CCSID on the IFS file. On IBM i 7.3, it looks like CPYTOIMPF issues a CPF2973 truncation warning even when the data has not reached 32KB.

    One other approach you can take that’s beyond the scope of this tip is to use the CLOB_FILE file reference SQL data type that is used in embedded SQL programming. Using CLOB_FILE will allow you to dump the content of a CLOB variable directly into an IFS file. This is a great feature which you can read about in the Embedded SQL Programming Guide (RPG data structure shown in the link).

    RELATED STORIES

    Composing An XML Document From Relational Data, Part 1

    Embedded SQL Programming Guide

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, CL, DB2 for i, FHG, Guru, IBM i, qshell, SQL, XML

    Sponsored by
    LaserVault

    Integrate Virtual Tape For Better Backups, Faster Recovery, And More Flexibility

    Virtual tape and virtual tape libraries offer a way to both simplify and strengthen backup and recovery operations. By incorporating virtual tape technology, automation of backups becomes possible resulting in hundreds of hours saved annually for IT departments and personnel.

    LaserVault ViTL is a virtual tape and tape library solution developed specifically for use with IBM Power Systems (from AS/400 to iSeries to Power 9s). See a demo and get a $50 gift card.

    With ViTL you can:

    • Replace physical tape and tape libraries and associated delays
    • Automate backup operations, including the ability to purge or archive backups
    • Remotely manage your backups – no need to be onsite with your server
    • Save backups to a dedupe appliance and the cloud
    • Recover your data at lightspeed greatly improving your ability to recover from cyberattacks
    • And so much more

    “The ViTL tapeless solution has truly made my job easier. It has given me more confidence in our full system recovery ability – but at the same time I hope it is never needed.” IBM i Administrator at a financial services company

    Sign-up now to see a ViTL online demo and get a $50 Amazon e-gift card when the demo is complete as our way of saying thanks for your time. Plus when you sign-up you’ll receive a free facts comparison sheet on using virtual tape vs tape so you can compare the functionality for yourself.

    LaserVault.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    As I See It: The Hunt For Perfect Storage Profound Logic Shows Off Innovation At PowerUp

    Leave a Reply Cancel reply

TFH Volume: 28 Issue: 40

This Issue Sponsored By

  • Profound Logic Software
  • Software Concepts
  • T.L. Ashford

Table of Contents

  • Guru: DB2 For i XML Composition And The IFS
  • As I See It: The Hunt For Perfect Storage
  • IBM i PTF Guide, Volume 20, Number 22

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • COMMON Set for First Annual Conference in Three Years
  • API Operations Management for Safe, Powerful, and High Performance APIs
  • What’s New in IBM i Services and Networking
  • Four Hundred Monitor, May 18
  • IBM i PTF Guide, Volume 24, Number 20
  • IBM i 7.3 TR12: The Non-TR Tech Refresh
  • IBM i Integration Elevates Operational Query and Analytics
  • Simplified IBM i Stack Bundling Ahead Of Subscription Pricing
  • More Price Hikes From IBM, Now For High End Storage
  • Big Blue Readies Power10 And IBM i 7.5 Training for Partners

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.