0

I have to write a sql script to modify a lot of types of columns in my db2 database. Everything goes well excpet for one specific table (script used is the same as others tables) and db2 returns always an error I don't understand.

Here is my script :

ALTER TABLE "TEST"."CLIENT"
     ALTER COLUMN C_CODE
         SET DATA TYPE CHAR(16 OCTETS);

and the error :

SQL Error [42997]: Function not supported (Reason code = "21").. SQLCODE=-270, SQLSTATE=42997, DRIVER=4.26.14

I try to modify some others columns on the same table, but I always receive the same error. Do you, by any chance, have an idea?

Thanks in advance

EBruno
  • 11
  • 7
  • Look at the documentation for this error message and reason code. For Db2-LUW it is at https://www.ibm.com/docs/en/db2/11.5?topic=messages-sql0250-sql0499#sql0270n . The reason code 21 is A column cannot be dropped or have its length, data type, security, nullability, or hidden attribute altered on a table that is a base table for a materialized query table. You may need to drop the MQT, make the change, and recreate and repopulate the MQT. – mao Oct 17 '22 at 08:27
  • Thanks for your comment, but I don't have any MQT on my database ... strange – EBruno Oct 17 '22 at 09:00
  • I finally found a MQT (in another schema) which used the table I want to modidy. I will try to delete the mqt, execute the script and re-create the mqt. Thank for your link and your response, it was very useful – EBruno Oct 17 '22 at 09:30

1 Answers1

1

The error SQL0270N (sqlcode = -270) has many possible causes, and the specific cause is indicated by the "reason code".

In this case the "reason code 21" means:

A column cannot be dropped or have its length, data type, security, nullability, or hidden attribute altered on a table that is a base table for a materialized query table.

The documentation for this sqlcode on Db2-LUW is at: https://www.ibm.com/docs/en/db2/11.5?topic=messages-sql0250-sql0499#sql0270n

Search for SQL0270N on that page, and notice the suggested user response:

To drop or alter a column in a table that is a base table for a materialized query table, perform the following steps:

  1.  Drop the dependent materialized query table.
  2.  Drop the column of the base table, or alter the length, data type, nullability, or hidden attribute of this column.
  3.  Re-create the materialized query table.
mao
  • 11,321
  • 2
  • 13
  • 29