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 ?

Your example does not make sense. You have two columns and want to drop a non-existent column? — data_henrik
Apologies. I've missed copying the line "alter table One add column age" line in the code block — sushmithaP
What is the full error message because SQL01242N has reason codes? Why are you linking to Db2 z/OS docs (1st link) when you are on LUW? Please update — data_henrik
@data_henrik, Yes I' mnt using db2 z/OS. I'm on the following databases — sushmithaP
Databases : DB2/NT64 10.5.1 , DB2 v11.5.0.0 . So when I have a table with two or more XML columns , trying to delete only one of those columns at a time resulting in an error. DB2 SQL Error: SQLCODE=-1242, SQLSTATE=42997, SQLERRMC=7, DRIVER=4.11.77 . The reason code is "7". So, would like to know if this is a restriction in all db2 versions. The errors seem to be resolved only when I delete all XML columns in a single go. Why is the single XML column dropping is not supported? — sushmithaP

mao mao · Accepted Answer · 2020-05-13T10:49:07

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.

Dropping XML columns in DB2

1 Answers