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).
