How can the XML Extender functionality be removed from a database, that has been previously enabled for XML Extender, if XML Extender is no longer installed?
Starting with DB2 Version 9.7 (188.8.131.52) the XML Extender on Linux, UNIX, and Windows is discontinued. Databases enabled for XML Extender on previous releases will not be able to use XML Extender functions on DB2 V9.7 on LUW platforms. XML Extender has been discontinued
Before installing DB2 V9.7, all XML Extender functionalities should be completely removed from a database that has been enabled for XML Extender as specified in Upgrading a DB2 server with XML Extender to DB2 Version 9.7.
If the XML Extender functionality was not disabled before the DB2 V9.7 was installed, it can be removed manually from each database after the upgrade using the procedure described in this tech note.
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.
- 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
- 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
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.
- 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.
- 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.
16 June 2018