javaspringdb2liquibase

Liquibase: Change column to an autoincrement column (identity) in DB2


I'm trying to change a BIGINT Column to an autoincremented column in DB2 but I can't seem find how. I tried doing it like this:

    <changeSet id="08.01" author="...">
        <addColumn tableName="table_name">
            <column name="id" type="bigint">
                <constraints nullable="true"/>
            </column>

            <column name="member_type" type="varchar(100)">
                <constraints nullable="true"/>
            </column>
        </addColumn>
    </changeSet>

    <changeSet id="08.02" author="...">
        <addAutoIncrement tableName="table_name"
                          columnDataType="bigint"
                          columnName="id"/>
    </changeSet>

and when it runs I get this error:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in class path resource [org/springframework/boot/autoconfigure/liquibase/LiquibaseAutoConfiguration$LiquibaseConfiguration.class]: Invocation of init method failed; nested exception is liquibase.exception.MigrationFailedException: Migration failed for change set classpath:db/changelog/08-separation.xml::08.01::author:
     Reason: liquibase.exception.DatabaseException: DB2 SQL Error: SQLCODE=-270, SQLSTATE=42997, SQLERRMC=72, DRIVER=4.13.127 [Failed SQL: ALTER TABLE SCHEMATEST.table_name ALTER COLUMN id SET GENERATED BY DEFAULT AS IDENTITY]
    at org.springframework.bean
...
...
...
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set classpath:db/changelog/08separation.xml::08.01::author:
     Reason: liquibase.exception.DatabaseException: DB2 SQL Error: SQLCODE=-270, SQLSTATE=42997, SQLERRMC=72, DRIVER=4.13.127 [Failed SQL: ALTER TABLE SCHEMATEST.table_name ALTER COLUMN id SET GENERATED BY DEFAULT AS IDENTITY]
...
...
...
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-270, SQLSTATE=42997, SQLERRMC=72, DRIVER=4.13.127

Solution

  • An identity column cannot be nullable, this is what the error message tells you:

    A nullable column cannot be changed to become an identity column.

    Change the column constraint to nullable="false" I guess.