Removing XML Extender functionality manually from an enabled database in DB2 Version 9.7

Resolving The Problem

• If you plan to upgrade to DB2 Version 9.7 , it is very important that you disable your databases for XML Extender before you install DB2 V9.7. Refer to Upgrading a DB2 server with XML Extender to DB2 Version 9.7 in the DB2 Information Center for details on how to upgrade to DB2 Version 9.7. This task describes the supported procedure for upgrade to Version 9.7.

• If a database was upgraded to DB2 V9.7 before first disabling XML Extender, then follow these steps to remove XML Extender functionality manually from a database since XML Extender is no longer available.

1. Optional: Back up all DAD or DTD files from the db2xml.DTD_REF or db2xml.XML_USAGE table for each database that you enabled for XML Extender. The following example shows how to export the DTD files stored in the DTD_REF table to a specific directory:
   
   db2 EXPORT TO dtdfiles.del OF del LOBS TO dir-name
   MODIFIED BY lobsinsepfiles
   SELECT CONTENT FROM DB2XML.DTD_REF
   
   The following example shows how to export the DAD files stored in the db2xml.XML_USAGE table to a specific directory:
   
   db2 EXPORT TO dadfiles.del OF del LOBS TO dir-name
       MODIFIED BY lobsinsepfiles
       SELECT DAD FROM DB2XML.XML_USAGE
   
   
2. Drop all references to the XMLVARCHAR, XMLCLOB, and XMLFILE user-defined data types from tables, distinct types, structured types, user-defined functions (UDFs), methods, and dependent objects in all databases. The following example shows how to list the columns that use the XML Extender UDTs:
   
   db2 SELECT TABSCHEMA, TABNAME, COLNAME
      FROM SYSCAT.COLUMNS
      WHERE TYPESCHEMA='DB2XML' AND NOT TABSCHEMA='DB2XML'
   
   Alternatively, you could add a new column using a built-in type to keep the data in your table before dropping the column. You cannot use the ALTER TABLE statement with the ALTER COLUMN clause to change the data type. Refer to the EXPORT, IMPORT, and LOAD utilities for details on how to move the data.
   
   
   
3. Drop XML Extender functionality using the commands in the disabledb.txt script found at ftp://public.dhe.ibm.com/ps/products/db2extenders/software/xmlext/tools/disabledb.zip
   
   db2 -tvf disabledb.db2
   
   Error messages you might receive when running disabledb.txt:
   ERROR: SQL0458N In a reference to routine "DB2XML.xxx" by signature, a matching routine could not be found. SQLSTATE=42883
   ACTION: This message can be ignored. The script is trying to drop a function that has already been dropped.
   
   ERROR: SQL0204N "DB2XML.xxx" is an undefined name. SQLSTATE=42704
   ACTION: This message can be ignored. The script is trying to drop a stored procedure that has already been dropped.
   
   ERROR: SQL0478N DROP, ALTER, TRANSFER OWNERSHIP or REVOKE on object type FUNCTION" cannot be processed because there is an object "schema.trigger", of type "TRIGGER", which depends on it. SQLSTATE=42893
   ACTION: An enabled column with triggers that references one or more XML Extender object type functions exists. These triggers are most likely associated to a column that is defined with an XML Extender User-Defined Type. Be sure to drop the column to remove the triggers that will produce this error (see Step #2). Rerun the disabledb.txt script.
   
   ERROR: SQL0478N DROP, ALTER, TRANSFER OWNERSHIP or REVOKE on object type "TYPE" cannot be processed because there is an object "schema.table", of type "TABLE", which depends on it. SQLSTATE=42893
   ACTION: A column exists that is using an XML Extender User Defined Type. Be sure to drop this column in table schema.table (see Step #2). Rerun the disabledb.txt script.
   
   
4. Check for XML Extender routines
   
   db2 " select count(*) from syscat.routines where routineschema='DB2XML'and      ((routinetype='F' and substr(implementation,1,9) = 'db2xmlfn!') or(routinetype='P' and  substr(implementation,1,7) = 'db2xml!'))" 
   
   if count is not zero then execute the following.
   
   db2 " select routineschema , routinename, SPECIFICNAME  from syscat.routines where routineschema='DB2XML'and   ((routinetype='F' and substr(implementation,1,9) = 'db2xmlfn!') or(routinetype='P' and  substr(implementation,1,7) = 'db2xml!'))"
   
   drop all routines that are retuned.