Blog
DB2utor

Categories

November 04, 2008

XML: A (Lower) Case Study

I've been taking you on my journey to learn more about XML and, specifically, how to support applications using the new XML data type with DB2 9 for z/OS.

When I covered publishing XML using pureXML functions in a previous DB2utor blog entry, I mentioned I was starting the process of writing a simple COBOL program to insert an XML document read-in from a file. It seemed simple, but for some reason I was having problems. Well my problem turned out to be a very simple issue: Since I used ISPF edit to enter this XML document, keywords like 'XML' were written in uppercase in the document header. Once I lower-cased the different keywords, the insert worked fine. 

Since I couldn't find a sample program for XML data type processing in COBOL, let me give you some quick information that will hopefully jump-start your XML development.

  • DCLGEN generator--I'd use the DCLGEN to declare the table and host variables to be used in your program. This example contains two columns on table MYCUSTOMER. The first column is the customer ID (CID), the second is the XML document containing customer information (INFO). The DCLGEN generates the default size for the XML column as CLOB(1M).  I couldn't find a limit defined in the SQL Reference Guide, but seeing that the DCLGEN defined the column as CLOB, and the CLOB limit is 2,147,483,647 bytes,  I know the limit for an XML document is 2GB.

EXEC SQL DECLARE MYCUSTOMER TABLE 
 ( CID                            BIGINT,
   INFO                           XML   
 ) END-EXEC.                            

01  DCLMYCUSTOMER.                                           
    10 CID                  PIC S9(18) USAGE COMP.           
    10 INFO                 USAGE SQL TYPE IS XML AS CLOB(1M).

See the SQL Reference Guide for more examples on defining the XML host variable.

  • Setup for an INSERT--To verify that I can actually do an insert, I defined my XML document in my COBOL program. I used WS-DOC-LEN to define the length of the document and WS-XMLDOC-IN to contain the actual data.

01  WS-DOC-LEN            PIC S9(09) USAGE COMP VALUE 155.
01  WS-XMLDOC-IN.                                            
    10  WS-XMLDOC-IN-L001           PIC X(50) VALUE          
    '<?xml version="1.0" encoding="IBM1047"?>          '.    
    10  WS-XMLDOC-IN-L002           PIC X(55) VALUE          
    '<CUSTOMERINFO xmlns:INFO="HTTP://MAN.COM" CID="100006">'.
    10  WS-XMLDOC-IN-L003           PIC X(50) VALUE          
    '<NAME>TROY</NAME></CUSTOMERINFO>                  '.    

Now notice these keywords are in lowercase: xml, encoding and xmlns. While most mainframe files are in upper-case, as noted, that doesn't work in XML. When I first initially ran this using upper-cased keywords, I received this error:

SQLCODE -20398, ERROR:  ERROR ENCOUNTERED DURING XML PARSING

  • Moving WS-XML-DOC-IN--Another tricky task is moving WS-XMLDOC-IN to INFO. In the output that follows, you'll see that the DB2 precompiler changes the DCLMYCUSTOMER and expands the XML column INFO to include LENGTH and DATA components. As with VARCHAR data, you must move the length to the INFO-LENGTH field and the data to the INFO-DATA field.

01  DCLMYCUSTOMER.                                     
    10 CID                  PIC S9(18) USAGE COMP.     
    10  INFO.                                              
    11  INFO-LENGTH                      PIC S9(9) COMP.  
    11  INFO-DATA.                                        
         49 FILLER         PIC X(32767).
          ……. 31 more lines of PIC X(32767)                         
         49 FILLER         PIC X(160).        

So before doing the insert, move in the length and the data:

MOVE WS-DOC-LEN              TO    INFO-LENGTH.                                           
MOVE WS-XMLDOC-IN         TO    INFO-DATA. 

Now execute the INSERT:

EXEC SQL                    
    INSERT INTO MYCUSTOMER  
      ( CID, INFO)          
    VALUES                  
       ( :CID, :INFO)       
END-EXEC.                   

Hopefully this very simple program gives you a feel for XML. Really, developing a program using XML isn't hard. If you're accustomed to developing programs that use VARCHAR columns, you'll have no problem with XML columns. Just make sure the XML keywords are in lowercase.