CODEWITHSUNDEEP
db2sqlcode
EBruno
EBruno

Reputation: 21

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

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

Upvotes: 1

Views: 3559

Answers (1)

mao
mao

Reputation: 12267

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.

Upvotes: 1

Related Questions