Personal tools
You are here: Home DB2 Problem Resolution General errors Altering the type of an identity column is not supported - SQL0270N REASON CODE=98
Navigation
Log in


Forgot your password?
 
Document Actions

Altering the type of an identity column is not supported - SQL0270N REASON CODE=98

Drop the identity attribute, alter the type, then re-enable the identity attribute.


See the example below:


db2 "create table tableZ (cod decimal(5) generated always as identity (start with 0))"
 db2 describe table tableZ


Output:                                                               

Column name                Data type                                           Column
                                          schema    Data type name             Length      Scale  Nulls
------------------------------- ------------------------- ------------------- ------------- --------  ------
COD                                SYSIBM    DECIMAL                           5              0          No


db2 "alter table tableZ alter column cod set data type decimal(10)"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0270N  Function not supported (Reason code = "98").  SQLSTATE=42997


Below, the solution to the error above:

db2 "select max(cod) as cod from tableZ"

Output:

cod     
-------
      125

  1 record(s) selected.


db2 "alter table tableZ alter column cod drop identity"
db2 "alter table tableZ alter column cod set data type decimal(10)"
db2 "alter table tableZ alter column cod set generated always as identity (start with 126)"

Below, the table after the change:

db2 describe table tableZ

Output:                                                               

Column name                Data type                                           Column
                                          schema    Data type name             Length      Scale  Nulls
------------------------------- ------------------------- ------------------- ------------- --------  ------
COD                                SYSIBM    DECIMAL                           10              0      



DONT'T FORGET TO REORGANIZE THE TABLEZ


db2 reorg table tableZ
db2 runstats on table tableZ and indexes all


Security Awareness
Would you like your company to implement gamification into your security awareness program?





Polls