Register DTD for pureXML with DB2 9.7

Problem

Even though XML schema has been around for a decade, still many APIs reference a DTD in the XML documents sent back and fourth. Such is the OpenSRS API. DB2 requires an XML schema or a DTD to be registered before it will process XML documents that refer to an XML Schema or a DTD. The process of registering such XSROBJECTs with DB2 has changed in DB2 since version 8.

Solution

  • register a DTD using the DB2 9.7 command line:
    db2 CONNECT TO SAMLE
    db2 REGISTER XSROBJECT 'dtd-file-name.dtd' FROM /path/to/dtd/file AS sample.dtd-name DTD

    If the command is successful, the output is
    DB20000I The REGISTER XSROBJECT command completed successfully.
  • Register a DTD from a Java program:
    String schemaName = "SAMPLE";
    String dtdName = "dtd-name";
    String dtdFile = "dtd-file-name.dtd";
    Reader fis = new FileReader(dtdFile);
    byte[] contentDtd = FileIO.readerToString(fis).getBytes();
    String registerProcQuery = "CALL SYSPROC.XSR_DTD (?,?,?,?,?)";
    PreparedStatement registerProcStatement = conn.prepareCall(registerProcQuery);
    registerProcStatement.setString(1, schemaName);
    registerProcStatement.setString(2, dtdName);
    registerProcStatement.setString(3, dtdFile);
    registerProcStatement.setString(4, "PUBLIC ID");
    registerProcStatement.setObject(5, contentDtd, java.sql.Types.BLOB);
    registerProcStatement.execute();

    The FileIO class can be found in Java Cookbook, Second Edition

References



Upgrading DB2 LUW from 9.5 with XML Extender to 9.7

Summary

The IBM documentation has good information on how to do the upgrade in general, and how to go through the preparation steps. Unfortunately the information about XML Extender is sparse, and all you get is

ADM4104E One or more databases are enabled for XML Extender. You must remove the XML Extender functionality from the instance and databases before upgrading. Please refer to the DB2 Information Center for details on the steps to upgrade XML Extender including how to disable XML Extender in databases.

This is misleading at best, since chances are that the XML extender was installed with the default DB2 install and never used, which was the case for the first upgrade I had to do. You get this error message regardless of having any column, table, database, or instance using the XML Extender. However, to find out how to get rid of the XML Extender is quite a challenge.

Steps

Follow the instructions here until step 6: Upgrading a DB2 server with XML Extender to DB2 Version 9.7

Once you’re at step 6, here are the details, assuming you have installed DB 9.5 to the default path.

  • First, check that XML Extender is installed:

    db2ls -a -q -b /opt/ibm/db2/V9.5
  • Then proceed to uninstall XML extender:

    cd /path/to/install/media
    ./db2_deinstall -F XML_EXTENDER -b /opt/ibm/db2/V9.5

This completes step 6 and you can proceed with the following step – step 7 – in the documentation. Make sure you use the correct db2iupdt executable:

/opt/ibm/db2/V9.5/instance/db2iupdt

Caveat

Even after fixing everything the upgrade tool complains about, you may still get odd error messages, that claim that you have not corrected the problems. That is some odd behavior of the error message reporting in DB2. For example, the front end was still complaining about VIEWs even though there weren’t any, and the back-end was still complaining about XML Extender even after it was uninstalled. Thus, if you are sure that all the issues are in fact fixed, you can go ahead and force the upgrade with the “-F” option.

References


XQuery and pureXML in DB2

I just came across the following two articles that have some good pointers for using the native XML capabilities of DB2: