0

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 ?

mao
  • 11,321
  • 2
  • 13
  • 29
sushmithaP
  • 133
  • 1
  • 3
  • 12
  • Your example does not make sense. You have two columns and want to drop a non-existent column? – data_henrik May 13 '20 at 06:11
  • Apologies. I've missed copying the line "alter table One add column age" line in the code block – sushmithaP May 13 '20 at 07:15
  • 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 May 13 '20 at 08:16
  • @data_henrik, Yes I' mnt using db2 z/OS. I'm on the following databases – sushmithaP May 13 '20 at 08:57
  • 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 May 13 '20 at 09:08
  • Looks like the restriction is still in place up to Db2 v11.5 at current date. You must change your code to comply with this restriction. – mao May 13 '20 at 09:11
  • @mao, Yes but this is a problem when we want to drop an unused (XML) column. In this case, all the other columns need to be dropped / either we restrict no XML column should be deleted. – sushmithaP May 13 '20 at 09:16
  • Yes, that is the restriction, as IBM states __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.__ You can always get creative with using temporary tables to avoid data loss. – mao May 13 '20 at 10:26

1 Answers1

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.

mao
  • 11,321
  • 2
  • 13
  • 29