Support unique index on DB2 to exclude nullable columns

[nfalco79]

Environment

DB2 11.5

Liquibase Version: 3.5.5 and 3.8.9

Liquibase Integration & Version: CLI, maven (version above)

Database Vendor & Version: Official IBM DB2

Operating System Type & Version: Windows 10

Description

When I create a unique index DB2 despite other database we are using (postgreSQL and Oracle) accept columns that could be not nullable. To provide the liquibase script compatbile also with DB2 we are changing a lot of changeset. Duplicating a lot of them to behave differently when on DB2 or other databases.

What we need to do is

<changeSet id="id-1" author="me">
    <preConditions onFail="CONTINUE">
        <not>
            <dbms type="db2" />
        </not>
    </preConditions>
    <addUniqueConstraint tableName="table1" columnNames="col1, col2" ... />
</changeSet>
<changeSet id="id-2" author="me" dbms="db2">
        <preConditions onFail="CONTINUE">
            <ext:dbVersion minVersion="10.5" minIncluded="true" />
        </preConditions>
        <sql><![CDATA[
            CREATE UNIQUE INDEX foo ON table1(col1, col2) EXCLUDE NULL KEYS
        ]]></sql>
</changeSet>

Expected/Desired Behavior

As you can see this is very heavy. We would like to have an attributes excludeNull="true" that for databases like DB2 will generate the statement with appropriate exclude keyword

┆Issue is synchronized with this Jira Bug by Unito

[molivasdat]

Hi @nfalco79 . Thank you so much for writing up this feature request. Before the team takes a look, based on the example included in the feature enhancement, you are asking for this on unique indexes? In the Oracle example listed, you specify a unique constraint and on the DB2 example you list a unique index. https://www.oratable.com/unique-constraint-vs-unique-index/

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0020151.html Thanks again!

[nfalco79]

I thought I replied but maybe I forgot to post. Anyway our intent is obtain a unique row based on specified columns. On PostgreSQL and Oracle NULL values are ignored, those columns will not partecipate to the index key. In DB2 is not possible create a unique constraint on nullable columns so we have to use unique index for DB2 >= 10.5 with "EXCLUDE NULL KEYS"

[sync-by-unito[bot]]

➤ Erzsebet Carmean commented:

Test size at 5 to account for an hour of getting DB2 environment ready for testing.

[nfalco79]

This is our implementation using the liquibase naming strategy distributed as liquibase extension jar:

/**
 * Support index creation for DB2 version {@literal >=} 10.5 z/os excluded if column contains null values.
 *
 * @author Nikolas Falco
 */
public class CreateIndexGeneratorDB2 extends CreateIndexGenerator {

    private VersionRange supportedVersion;

    /**
     * Default constructor.
     */
    public CreateIndexGeneratorDB2() {
        supportedVersion = new VersionRange(true, Version.parseVersion("10.5"), null, false);
    }

    @Override
    public int getPriority() {
        return PRIORITY_DATABASE;
    }

    @Override
    public boolean supports(CreateIndexStatement statement, Database database) {
        boolean supports = false;
        if (database instanceof DB2Database) {
            if (database.getConnection() == null) {
                supports = true;
            } else {
                try {
                    Version dbVersion = new Version(database.getDatabaseMajorVersion(), database.getDatabaseMinorVersion(), 0);
                    supports = supportedVersion.includes(dbVersion);
                } catch (DatabaseException e) { // NOSONAR
                    // let return false
                }
            }
        }
        return supports;
    }

    @Override
    public Sql[] generateSql(CreateIndexStatement statement, Database database, @SuppressWarnings("rawtypes") SqlGeneratorChain sqlGeneratorChain) {
        Sql[] generateSql = super.generateSql(statement, database, sqlGeneratorChain);
        if (generateSql.length == 0) {
            return generateSql;
        }
        String sql = generateSql[0].toSql() + " EXCLUDE NULL KEYS";
        return new Sql[] { new UnparsedSql(sql, getAffectedIndex(statement)) };
    }
}

[nvoxland]

An alternate approach that would work with the current liquibase version is specifying your changeset like this:

    <changeSet id="id-1" author="me">
        <addUniqueConstraint tableName="table1" columnNames="col1, col2" ... />
        <modifySql dbms="db2">
            <append value=" EXCLUDE NULL KEYS"/>
        </modifySql>
    </changeSet>

That will add the EXCLUDE NULL KEYS to db2 and not anywhere else.

There is an ongoing discussion on how and when to include database-specific parameters into the change functions. I'd like to include this as part of that discussion before just bringing it in.

[nfalco79]

the point is that <addUniqueConstraint tableName="table1" columnNames="col1, col2" ... /> will fails because col2 is nullable