More on Migration Planning

Last week I mentioned IBM's free DB2 10 migration planning workshops. IDUG has its own offering for those who will be attending the group's upcoming conference.

The title of IDUG's workshop, "DB2 10 for z/OS Migration Planning," shouldn't mislead you. It's not just for systems programmers; it's designed for everyone who works with DB2 for z/OS, including application developers, data architects, database adminstrators, IT managers, new users.

Technical areas this presentation applies to:

• New Release (DB2 for z/OS)
• DB2 10 for z/OS Overview
• DB2 9 Recap (For those migrating from V8)
  o Application Development focused topics
  o SQL & Application Access
  o XML
  o Temporal (Versioned Data)
  o LOBs
  o Advance
• Transitional Topics
  o Optimization Evolution
  o Index Improvements
  o Security Enhancements
  o Utilities
  o Data Sharing
  o Distributed Communications
• Systems oriented topics
  o Virtual Storage
  o Serviceability / Instrumentation
• DB2 10 Migration
  o Review of V8 to V10 & V9 to V10
  o Preparations
  o Planning
  o Process

Register for this free one-day workshop. IDUG's DB2 Tech Conference is May 14-18 in Denver.

XML's Growing Importance on the Mainframe

At the recent IDUG conference in Melbourne, Australia, I attended Phil Grainger's day of education. The topic of XML came up and someone asked if it was being used in DB2 for z/OS. While I'm amazed at the slow adoption of XML on DB2 for z/OS, to be honest, the first implementation of XML using CLOBs wouldn't scale and performed very poorly. However, since the introduction of XML data types with DB2 9, we can now index into documents and the processing scales very well.

I think another reason customers have been reluctant to use XML on z/OS is that, prior to DB2 10, developers could only update an entire document, rather than just parts of it. Obviously that poses its own performance and logging implications. But again, DB2 10 removes this barrier. Now you can update parts of XML documents by using the standard UPDATE statement with the XMLMODIFY function. XMLMODIFY uses basic XQuery updating expression to insert, delete or replace nodes. You can also replace values of nodes in XML documents stored in XML columns.

With its latest version, DB2 fully supports storing, updating and processing XML documents. Now is the time for DB2 DBAs on z/OS to embrace XML and use it in their applications. Why? Because XML has an important place on the mainframe. XML ever-increasily serves as the data transport layer between businesses. Those who don't use XML in DB2 for z/OS must store their data off of the mainframe (in some other database such as SQL Server, Oracle or DB2 LUW). So where is your company processing XML documents?

Heading to SHARE

I'll be on hand for the SHARE conference in Anaheim, Calif., from Feb. 27 to March 4.

Despite SHARE's relevance to the mainframe world, I actually hadn't attended a conference in many years until I went to the one in Boston last summer.

I enjoyed that conference because I got a chance to see old friends from the DB2 community. I also made plenty of new friends between networking with folks in different areas of z/OS management and talking to young professionals involved in the zNextGen Project.

At the Anaheim conference, SHARE is specifically organizing sessions on these topics:

• Using zEnterprise to deliver value while reducing the cost of managing your data center.

• Virtualization to make better use of existing resources.

• Virtualization as a cost-effective approach to delivering new services.

• Security and privacy practices that keep you compliant and that avoid compromise costs.

• Effective application technologies and architectures that deliver timely business solutions.

Since my primary interest is DB2 (surprise), I searched and found that this conference will have 74 DB2 sessions.

If you're going to the conference, here are a few DB2 sessions you won't want to miss:

• 8356 -- DB2 10 performance topics (Roger Miller, IBM)

• 8786 -- IBM Smart Analytics Optimizer for DB2 for z/OS: How Does it Work? (Willie Favero, IBM)

• 8789 -- The DB2 Catalog Has Come a Long Way Since 1983 (Favero)

• 8823 -- Comparing and Contrasting XML Features of DB2 and COBOL: When to Use Which One? (Tom Ross, IBM)

Roger and Willie are simply two of the best speakers you'll ever hear. And as one who's written extensively about XML and COBOL, I'm also looking forward to Tom's presentation on that topic. It's good to see IBM involved in this area.

If you're attending SHARE, please stop by and say hello. I'll be at CA Technologies booth showing off my new product, Chorus.

DB2 10 pureXML

The introduction of pureXML in DB2 9 for z/OS gave administrators a way to store well-formed XML documents in their hierarchical form and retrieve all or portions of these documents. Prior to DB2 9, storing XML was a chore -- the available options were to either shred the documents and archive the XML data in relational tables as relational columns, or store them in a LOB, which posed its own performance and processing problems. DB2 9 users quickly grew to appreciate the labor savings, shortened development cycles and greater flexibility that pureXML offers.

DB2 9 pureXML offered these additional benefits:

•    New storage techniques for efficient management of XML documents.
•    New index technology to speed the process of accessing XML documents.
•    New query language support for XQuery, a new graphical query build for XQuery, and new query optimization techniques.
•    Support for validating XML data based on user-supplied schemas.

DB2 10 will include a second release of pureXML. This updated version is designed to provide numerous performance and usability improvements such as automatic XML schema validation. With this new function, you won't have to specify XML schema for validation -- DB2 will search and find a schema. DB2 also supports multiple schema versions, so existing older documents won't need to be changed.

DB2 10 pureXML is also expected to include:

•    A binary XML exchange format that's designed to improve performance.
•    XML multi-versioning, which is designed to improve concurrency and performance.
•    Support UPDATE on sub-parts of XML documents.
•    Enhanced XML support in stored procedures, UDF and triggers.
•    XML index matching with date and timestamp.
•    CHECK XML and CHECK DATA utilities. The latter will verify the consistency of XML documents stored in a separate XML table space.

If you're just getting started with pureXML, be sure to check out these IBM Redbooks: "DB2 9 pureXML Overview and Fast Start" and "DB2 9 pureXML Guide."

Recovering XML Table Space

I firmly believe that DBAs should always be prepared for the worst-case scenario. With this in mind, an imagecopy backup of data is your best friend. However, if you haven't worked with tables containing XML data, you may not know that both the table space (which contains the base table data) and the auxiliary table space (which contains the XML column) must be backed up. In other words, an auxiliary table space is needed for each XML column in the table.

XML's unique characteristics complicate the task of recovering tables when the table space has been dropped. Once you drop a table space, all recovery information about that table space is no longer available to the recovery utility, even if you re-create the table space. The only way to recover the table space is to rebuild it using the original DDL; then use the DSN1COPY utility to restore the imagecopy into the new table space. For more about DSN1COPY, see the "DB2 Version 9.1 for z/OS Utility Guide and Reference."

Recovering tables with XML data requires preparation. First, you must maintain a list of all the table space, table and index information with internal object identifiers. DSN1COPY needs this information if the newly built or target VSAM files have different object identifiers. Because all auxiliary table spaces must be backed up and restored to the same point in time as the base table spaces, you also need a report listing this information. I wrote a DB2utor blog entry that explains how to get a list of base tables along with associated XML tables and table space names.

REPORTS
Next, you must generate a report showing all table space and index space internal DBID, PSID, OBID and IX_OBID. This query provides the information needed for DSN1COPY:

SELECT SUBSTR(TS.NAME,1,8) AS TSNAME      
     , TS.DBID                            
     , TS.PSID                            
     , TS.PGSIZE                          
     , SUBSTR(T.CREATOR,1,8) AS CREATOR   
     , T.OBID                             
     , SUBSTR(T.NAME,1,18) AS TBNAME      
     , SUBSTR(I.INDEXSPACE,1,8) AS IXSPACE
     , I.DBID AS IX_DBID                  
     , I.ISOBID AS IX_ISOBID              
     , I.OBID AS IX_OBID                  
     , SUBSTR(I.NAME,1,18) AS IXNAME      
     , I.PGSIZE                           
FROM SYSIBM.SYSTABLESPACE TS              
   , SYSIBM.SYSTABLES T                   
   , SYSIBM.SYSINDEXES I                  
WHERE TS.DBNAME = 'CSBPDB'                
  AND TS.NAME = T.TSNAME                  
  AND T.DBNAME = TS.DBNAME                
  AND T.DBNAME = 'CSBPDB'                 
  AND T.CREATOR = 'CSBP'                  
  AND T.NAME IN ('PRODUCT','XPRODUCT')    
  AND T.TYPE IN ('T','P')                 
  AND I.DBNAME = T.DBNAME                 
  AND I.TBCREATOR = T.CREATOR             
  AND I.TBNAME = T.NAME                   
ORDER BY T.DBNAME                         
       , T.TSNAME                         
       , T.CREATOR                        
       , T.NAME                           
WITH UR ;   

In this example I qualified the result to database CSBPDB and tables PRODUCT and XPRODUCT. For your production system, generate this report for the entire subsystem and keep it in a safe place.

TSNAME      DBID    PSID      PGSIZE      CREATOR     OBID    TBNAME 
---------------+---------+---------+-------------+----------------+---------+-------------
CSBPTS02       272          61                4       CSBP                    62    PRODUCT
XPRO0000       272         64               16       CSBP                    65    XPRODUCT

                                             IX          IX            IX
TBNAME       IXSPACE       DBID  ISOBID   OBID    IXNAME                        PGSIZE
-----------------+---------------+--------+-----------+-------+----------------------------

--+---------
PRODUCT     PROD1GEQ       272            74         73   PROD_NAME_XMLIDX         4 
PRODUCT     IRDOCIDP         272            70         69   I_DOCIDPRODUCT                 4
PRODUCT     PRODRNAM     272            72         71   PROD_NAME_PIDX                4
PRODUCT     PRODRDET       272            76         75   PROD_DETAIL_XMLIDX      4
XPRODUCT  IRNO11TN         272            68         67   I_NODEIDXPRODUCT           4

BACKUP
I usually just back up the table space, but now that you can backup indexes, I figured I would test the DSN1COPY restore for all table and index spaces related to the PRODUCT table. Before you can use the imagecopy utility on an index, COPY must be enabled on it. Use the ALTER INDEX DDL statement to do this. For example:

ALTER INDEX CSBP.I_DOCIDPRODUCT      
   COPY YES ;

DSN1COPY
Because I want to simulate what happens when a table space is dropped, I went ahead and executed DDL to drop and re-create the table space containing the PRODUCT table and all indexes. Once the table and index space are rebuilt, they must be stopped before you run the DSN1COPY utility. Use the DB2I panels option 7 DB2 commands to do this. For example:

–STOP DB(CSBPDB) SPACE(CSBPTS02)

Now run DSN1COPY to restore the table and index space with the imagecopies taken earlier.

Here's a sample to restore table space CSBPDB.CSBPTS02. Note that:

• OBIDXLAT isn't really needed in this case because the object IDs didn't change.
• FULLCOPY should be used when using a full imagecopy as input.
• PAGESIZE should be taken from the report above. XPRO0000 is 16K.

//DSN1COPY EXEC PGM=DSN1COPY,                                     
//           PARM='OBIDXLAT,RESET,CHECK,FULLCOPY,PAGESIZE(4K)'    
//STEPLIB  DD DISP=SHR,DSN=DSN910.SDSNEXIT                        
//                  DD DISP=SHR,DSN=DSN910.SDSNLOAD                        
//SYSPRINT DD SYSOUT=*                                            
//SYSUT1 DD DISP=OLD,DSN=CSBP.IC.CSBPDB.CSBPTS02.D031109.T160400  
//SYSUT2 DD DISP=OLD,
//                DSN=DB2DB9G.DSNDBC.CSBPDB.CSBPTS02.I0001.A001
//*  SOURCE DBID, TARGET DBID                                     
//*  SOURCE/TARGET PSID OR ISOBID FOR INDEX                       
//*  ALL OTHER SOURCE/TARGET TABLE OBID                           
//SYSXLAT DD *                                                    
272,272                                                           
61,61                                                             
62,62        
/*
//

Once you've completed the restore of all the table and index spaces, restart the objects:

–START DB(CSBPDB) SPACE(*).

Remember: Prepare for the worst-case scenario. Practice now before you really need to use this procedure in an emergency situation.

Finding Implicit XML Table and Table Space

DBAs must be concerned with the name of the table space in which a table is stored. This is because the DB2 utilities operate at the database table space level.

So if you need to collect statistics for a table to help the optimizer choose a better access path, execute the runstats utility on the table space in which that table is stored. Likewise, to backup a table, run the image-copy utility against the appropriate table space. Or, to recover a table, run the recovery utility. This is why DBAs usually restrict table creation in a default database to an implicitly created table space. However, with the introduction of XML data type, DBAs no longer can control the table space name that's implicitly created to store the XML column.

When you issue the CREATE TABLE statement and include a column with an XML data type, DB2 implicitly creates a new table and table space in which to store the XML document. In this example I create a table space (CSBPTS02) in database CSBPDB. Then I create the PRODUCT table to be stored in CSBPDB.CSBPTS02:

CREATE TABLESPACE CSBPTS02 IN CSBPDB   
  USING STOGROUP CSBPSG                
    PRIQTY            -1                
    SECQTY           -1                
  PCTFREE          20                  
  SEGSIZE           32                  
  BUFFERPOOL     BP0                   
  LOCKSIZE       PAGE ;                 

CREATE TABLE CSBP.PRODUCT                               
  ( PID                          VARCHAR(10)   NOT NULL PRIMARY KEY 
   ,NAME                     VARCHAR(128)                       
   ,PRICE                     DECIMAL(30, 2)                     
   ,PROMOPRICE       DECIMAL(30, 2)                     
   ,PROMOSTART       DATE                               
   ,PROMOEND           DATE                               
   ,DESCRIPTION       XML )                              
  IN CSBPDB.CSBPTS02                                    
  CCSID EBCDIC;

Because the PRODUCT table has an XML column (DESCRIPTION), DB2 implicitly creates a new table and accompanying new table space in which to store the XML data. Of course, DBAs must make sure to simultaneously backup the base table and XML table space. But how do you determine which XML tables are related to a given base table? There are two ways to find this information.

One way is to run the following SQL against the DB2 catalog. This retrieves a list of tables in a given database, along with any XML tables and table spaces. For this example I want to retrieve everything in database CSBPDB.

  SELECT SUBSTR(                                                               
         STRIP(T.CREATOR) CONCAT '.'                                           
         CONCAT                                                                
         STRIP(T.NAME),1,25) AS TBNAME                                         
--     , SUBSTR(T.DBNAME,1,8)  AS DBNAME                                       
       , SUBSTR(T.TSNAME,1,8)  AS TSNAME                                       
       , SUBSTR(XR.COLNAME,1,18) AS COLNAME                                    
       , SUBSTR(XR.XMLTBOWNER,1,8) AS XMLTBOWNER                               
       , SUBSTR(XR.XMLTBNAME,1,18) AS XMLTBNAME                                
       , SUBSTR(VALUE(XT.TSNAME,' '),1,8) AS XMLTSNAME                         
  FROM (((SELECT A.CREATOR, A.NAME, A.DBNAME, A.TSNAME                         
         FROM SYSIBM.SYSTABLES A                                               
         WHERE A.DBNAME = 'CSBPDB'                                             
           AND A.TYPE = 'T'                                                    
         ) AS T                                                                
  LEFT OUTER JOIN                                                              
       SYSIBM.SYSXMLRELS XR                                                    
  ON T.CREATOR = XR.TBOWNER                                                    
    AND T.NAME = XR.TBNAME )                                                   
  LEFT OUTER JOIN                                                              
       SYSIBM.SYSTABLES  XT                                                    
    ON  XR.XMLTBOWNER = XT.CREATOR                                             
    AND XR.XMLTBNAME  = XT.NAME)                                               
  ORDER BY T.DBNAME, T.TSNAME                                                  
  #                                                                            

TBNAME                           TSNAME       COLNAME  
--------------------------------+---------------+---------------------
CSBP.MYCUSTOMER     CSBPPG13     INFO
CSBP.EMP                         CSBPTS01     --------------------
CSBP.PRODUCT               CSBPTS02     DESCRIPTION

                                         XMLTBOWNER     XMLTBNAME           XMLTSNAME
                                         ------------------------+--------------------------+---------------------
                                         CSBP                         XMYCUSTOMER      XMYC0000 
                                         ------------------------   -----------------------       -------------------           
                                         CSBP                         XPRODUCT                XPRO0000  

The other option is to run the REPORT utility using the TABLESPACESET option. This retrieves all objects related to a given database and table space. In this example I'm looking for all objects related to the table stored in CSBPDB.CSBPTS02:

The following JCL
//REPORT1 EXEC DSNUPROC,SYSTEM=DB9G,             
//          UID='REPORTTS',UTPROC='',            
//          SYSTEM='DB9G',                       
//          COND=(0,NE)                          
//DSNUPROC.SYSIN DD *                            
  REPORT TABLESPACESET TABLESPACE CSBPDB.CSBPTS02
        SHOWDSNS                                 
//SYSTSPRT DD SYSOUT=X                           
//SYSPRINT DD SYSOUT=X                           

The output below shows that table CSBP.PRODUCT is stored in CSBPDB.CSBPTS02. I also have two indexes, CSBP.I_DOCIDPRODUCT and CSBP.PROD_NAME_PIDX:

TABLESPACE SET REPORT:                                                 

TABLESPACE         : CSBPDB.CSBPTS02                                   
               DSN : DB2DB9G.DSNDBC.CSBPDB.CSBPTS02.I0001.A001         
  TABLE            : CSBP.PRODUCT                                      
  INDEXSPACE       : CSBPDB.IRDOCIDP                                   
               DSN : DB2DB9G.DSNDBC.CSBPDB.IRDOCIDP.I0001.A001         
  INDEX            : CSBP.I_DOCIDPRODUCT                               

  INDEXSPACE       : CSBPDB.PRODRNAM                                   
               DSN : DB2DB9G.DSNDBC.CSBPDB.PRODRNAM.I0001.A001         
  INDEX            : CSBP.PROD_NAME_PIDX                               

Within the CSBP.PRODUCT table, I have an XML column DESCRIPTION. The DESCRIPTION column is stored in CSBP.XPRODUCT, in table space CSBPDB.XPRO0000.

XML TABLESPACE SET REPORT:                                             

TABLESPACE                 : CSBPDB.CSBPTS02                           

  BASE TABLE               : CSBP.PRODUCT                              
  COLUMN                   : DESCRIPTION                               
    XML TABLESPACE         : CSBPDB.XPRO0000                           
      PART: 0001       DSN : DB2DB9G.DSNDBC.CSBPDB.XPRO0000.I0001.A001 
      XML TABLE            : CSBP.XPRODUCT                             
      XML NODEID INDEXSPACE: CSBPDB.IRNO1ARQ                           
                       DSN : DB2DB9G.DSNDBC.CSBPDB.IRNO1ARQ.I0001.A001 
      XML NODEID INDEX     : CSBP.I_NODEIDXPRODUCT                     
      XML INDEXSPACE       : CSBPDB.PROD18FW                           
                       DSN : DB2DB9G.DSNDBC.CSBPDB.PROD18FW.I0001.A001 
      XML INDEX            : CSBP.PROD_NAME_XMLIDX                     

      XML INDEXSPACE       : CSBPDB.PRODRDET                           
                       DSN : DB2DB9G.DSNDBC.CSBPDB.PRODRDET.I0001.A001 
      XML INDEX            : CSBP.PROD_DETAIL_XMLIDX      

It took me awhile to find this information. Hopefully sharing it here will give you a jump start on managing tables containing XML data.                                                                                   

Using XML Schema Validation

In last week's blog entry, I discussed the XML schema registration process, but I didn't explain what the schema registration is used for. DBAs like it when the DBMS enforces the existence or non-existence of a column (not NULL versus NULL). It's also nice to know that data values are valid for the column's data type (e.g., DECIMAL, DATE, TIME, TIMESTAMP). DATE and TIME data types didn't exist in early DB2 releases, so applications were developed that inserted invalid date data values. It wasn't until DB2 1.3 that the DBMS provided a native data type for DATE and enforced a valid date value. With this provision, the DBMS also could now provide date arithmetic operations to add and subtract dates. The drawback was that people had to go back and cleanup the date data before they could convert the column to a date data type.

Developers love how XML's flexibility gives them the freedom to define the structure and content of XML documents as they choose. They don't have to wait for a DBA to insert or delete a column from their table. However, to avoid problems associated with not enforcing data types, the developer must ensure that the application is using the correct structure and data types defined for the XML document. While DB2 doesn't provide a DBMS-enforced method for validating the XML data, it does provide a user-defined function (SYSFUN.DSN_XMLVALIDATE) that validates the XML document based on a named XML schema. As I noted last week, an XML schema includes a description of the XML document, along with constraints on the document's structure and contents. Before you can invoke the SYSFUN.DSN_XMLVALIDATE function, all schema documents that make up an XML schema must be registered with the built-in XML schema repository (XSR).

Invoking XML schema validation creates a significant performance overhead. You use two to three times more CPU than you would if you didn't validate the document. For that reason, XML schema validation should be used during development and testing to verify that the application is working correctly--but turn off schema validation in production. One way to do this is to define triggers in the test environment that invoke DSN_VALIDATE. Make sure you don't create these triggers in a production environment.

If you're currently working with the DSN_VALIDATE function, please share your experiences in comments.

A Tip on XML Schema Registration

Enforcing or validating XML documents against an XML schema is one concern DBAs have when storing XML documents in DB2. An XML schema contains a description of the corresponding XML document, along with constraints on the document's structure and contents.

DB2 9 for z/OS provides XML validation management through the XML schema repository (XSR). Once you add your schema to XSR, you can use it to validate XML documents before storing them in the XML column.

The XML schema registration can be handled manually through one of these methods:

• Call the following DB2 stored procedures:sysproc.xsr_register, sysproc.xsr_addschemadoc, sysproc.xsr_complete
• Invoke the following JDBC method that performs all the stored procedure steps: com.ibm.db2.jcc.DB2Connection.registerDB2XmlSchema
• Invoke the following commands from command line processor: –REGISTER XMLSCHEMA, -ADD XMLSCHEMA DOCUMENT, -COMPLETE XMLSCHEMA

I was happy to learn that I didn't have to write a program to call the stored procedures or invoke the JDBC methods. IBM delivered Data Studio Developer for free to handle this process for you.

I mention this because I want to help you avoid the pitfall that I fell into when I tried to get the Data Studio XML schema registration process to work with DB2 V9 for z/OS. First, if you're installing DB2 and JDBC support, read the DB2 Version 9.1 for z/OS Application Programming Guide and Reference for Java. This guide provides all the steps necessary to install JDBC support on z/OS. 

My problem was that I received an SQLCODE -805 on the SYSSTAT package. When I checked SYSIBM.SYSPACKAGE, I found SYSTAT with the default collection ID of NULLID. After searching, I found the error message in the WLM address space. The stored procedure was trying to find the package in the SYSXSR collection. I've not found any documentation stating that JDBC drivers must be installed with collection ID SYSXSR. Since I'm not aware of a way to change the collection Data Studio uses, the solution is to install the drivers in the SYSXSR collection.

To install the drivers, modify the DB2Binder parameters "collection" as I have here:

java com.ibm.db2.jcc.DB2Binder -url jdbc:db2://192.87.15.123:496/DB9GLOC
  -user sysadm -password db2rocks -collection SYSXSR

Tracking XML Changes Changes for the Better

I spent part of 2008 learning and blogging about XML support delivered in DB2 V9 for z/OS. Well, the learning process (as well as the blogging process) are on-going. Read two of my DB2utor blog entries here and here. This week I'm pleased to report that IBM is making life easier for XML developers.

For those who develop applications using XML technology, it's important to know what's new and what's changed each and every time maintenance is applied. DB2 systems programmers typically turn to the ++HOLD data to learn of the latest changes. However, IBM recently released APAR II14426. This informational APAR essentially puts everything in one place by linking all of the XML support delivery APARs.

These APARs aren't just for fixing problems--they also introduce new features. Some of the new features provided through DB2 maintenance include:

XMLTABLE and XMLCAST--two new XML functions introduced in PK51571, PK51572 and PK515723.

fn:name, fn:local-name, fn:max, fn:min,
fn:distinct-values, fn:upper-case, fn:lower-case,
fn:translate, fn:last, fn:position,
fn:matches, fn:replace, fn:tokenize
These 13 new XPATH functions are provided with PK55585 and PK55831.
                                               
APAR II14426 also includes various other fixes that are designed to improve performance, locking and more.

If you're experiencing an XML-related problem and want to know if someone else has reported this issue, go here for a list of all known XML-related problems. Using the search function, you can pinpoint your APAR search.

Remember, you may have maintenance on your system that provides new XML features that aren't documented in the XML Guide. For instance, I just checked the latest XML Guide and the 13 new functions listed above aren't in there.

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.