How do I change a column from NOT NULL to NULL in DB2?

General Discussion
1

Hi,
When I use Liquibase to try to remove the NOT NULL constraint of a column I’m getting the following error:

    Migration failed for change set /var/tmp/2817394908676666105-diff.xml::1279899379588-1::root (generated):     Reason: liquibase.exception.DatabaseException: Error executing SQL ALTER TABLE TEST_SCHEMA.NOT_NULL_TBL ALTER COLUMN  BIGINT_ DROP NOT NULL:           Caused By: Error executing SQL ALTER TABLE TEST_SCHEMA.NOT_NULL_TBL ALTER COLUMN  BIGINT_ DROP NOT NULL:           Caused By: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: NOT NULL;COLUMN  BIGINT_ DROP; com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: NOT NULL;COLUMN  BIGINT_ DROP;

If I manually try to run “ALTER TABLE TEST_SCHEMA.NOT_NULL_TBL ALTER COLUMN  BIGINT_ DROP NOT NULL” on my DB2 database I also get the error.  Does anyone know what the right command is to make my ‘BIGINT_’ column nullable?

2

After talking to our DBA, he thinks it might only be possible in DB2 v9 (we’re using 8.2).

Maybe Liquibase should be checking something like:

    if ( (database instanceof DB2Database) && (database.getDatabaseMajorVersion() >= 9) )
before it tries to do something that was only added in v9...
3

Does he know if there is a comparable command for v8.2 we can use instead?

Nathan

4

The only thing he can think of is deleting the table and re-creating it again with the proper NULL/NOT NULL states.  I’m guessing that would be a bad thing to do if the table has data in it.

5

I’m guessing it’s a bad thing to do too :slight_smile:

I’ll add the check in there to fail with < 9.  Thanks for the info.

Nathan