1

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

Improve this question
3
  • Look at the documentation for this error message and reason code. For Db2-LUW it is at ibm.com/docs/en/db2/… . 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. Commented Oct 17, 2022 at 8:27
  • Thanks for your comment, but I don't have any MQT on my database ... strange Commented Oct 17, 2022 at 9: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 Commented Oct 17, 2022 at 9:30

1 Answer 1

Reset to default
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.
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.