Dropping XML columns in DB2

Question

I'm trying to drop an XML column in db2 in the following manner. But every time I do this, it is resulting in an error

create table One(name int, address XML);
alter table One add column age xml;
alter table One drop column age;

Error starting at line : 5 in command -
alter table One drop column age
Error report -
DB2 SQL Error: SQLCODE=-1242, SQLSTATE=42997, SQLERRMC=7, DRIVER=4.11.77

DB2 official documentation suggests that issue is fixed in DB 9.7. I'm currently checking on 10.5 & 11.5 versions but still facing the same issue. https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/xml/src /tpc/db2z_alterxml.html

DB2 documentation suggests to run CHECK pending status on a table after a re-org but there were no commands that are available.

Is there a way to resolve this drop column issue for XML datatypes? Or else DB2 is not allowed to drop XML columns in a table by default?

Can someone suggest on this issue?

https://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.wn.doc/doc/c0055038.html

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.messages.sql.doc/com.ibm.db2.luw.messages.sql.doc-gentopic5.html#sql1242n

IBM suggests all the XML columns in a table need to dropped in a single alter statement. Is this still a restriction in 10.5 & higher versions of db2 ?

Answer 1

This remains a restriction in Db2-Linux/Unix/Windows up to and including Db2 v11.5.

IBM states (in the help for SQL1242N reason code 7)

"For a table containing multiple columns of data type XML, either do not drop any XML columns or drop all of the XML columns in the table using a single ALTER TABLE statement

"

This restriction only applies to tables with more than one XML column.

You can workaround in various ways, for example create a new table and copy the existing data into it etc, or arrange your physical data model differently.