DropAll doesn't work anymore on DB2 for i

[wardvijf]

Environment

Liquibase Version: 4.0.0

Database Vendor & Version: DB2 for i (AS400 iSeries)

Description

When dropping all tables, Liquibase tries to lookup foreign key constraints. It executes a database specific query to do this.
It has a specific query for DB2, but this only works for DB2 UDB, and not for DB2 for i.

Steps To Reproduce

List the steps to reproduce the behavior.
Execute a dropAll against a DB2 for i database.

Actual Behavior

An exception occurs:
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'liquibase' defined in com.essers.legplanning.domain.support.internal.DomainSupportConfiguration: Invocation of init method failed; nested exception is liquibase.excep tion.DatabaseException: liquibase.command.CommandExecutionException: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0204] REFERENCES in SYSCAT type *FILE not found.

Expected/Desired Behavior

No exception occurs and tables are dropped.

Additional Context

This worked on 3.6.3, but not anymore on 4.0.0. The reason for this is some internal Liquibase refactoring, where dropping the tables is now done differently. See StandardLockService.destroy 7fa3d53

A similar check has already built in to retrieve unique constraints. See https://liquibase.jira.com/browse/CORE-1915
An extra db2 check was built in (see https://github.com/liquibase/liquibase/commit/b54ee3faee00c7d9a99036dc711094556b50298e))
{{ if (database.getDatabaseProductName().startsWith("DB2 UDB for AS/400"))}}

Test Requirements (Internal Liquibase QA)

This issue is a special case where we do not have access to the database platform in order to perform testing. Our validations will be to ensure the changes have not impacted other platforms.

Manual Test Requirements

Setup: Populate the database with tables with a foreign key.

Verify successful drop-all of tables associated by a foreign key.

• Oracle
• MSSQL
• MySQL
• Postgres
• Maria DB

Automated Test Requirements

• Verify all existing functional tests pass on the build server.
• Implement hasForeignKey and doesNothaveForeignKey methods for the DataBaseInstance class.
  • MySQLInstance.java
  • MariaDbInstance.java
  • MsSqlInstance.java
  • OracleInstance.java
  • NOTE: PostgresInstance.java is already implemented.
• Add a new test to DropAllTests.java that validates drop-all when a foreign key exists.
  • Test without a Pro license
  • Test CLI only (no Maven)
  • Test all supported platforms (multidb test)
  • Use the new format of CLI commands (LiquibaseNewCli) to construct Liquibase command line.
  • Make sure to add labels to new foreign key changesets added to the update changelog.
    • labels=”lb-683, community, addFk”

┆Issue is synchronized with this Jira Bug by Unito
┆Fix Versions: Community 4.x

[molivasdat]

Hi @wardvijf Thanks for creating this issue and the PR to fix it. This may belong in the db2i extension issues page

[wardvijf]

Hi @molivasdat, the bug is situated in the Liquibase core, that's why I created the issue here.
I am a bit confused now, do you expect me to change something?

[nvoxland]

I'm fine just bringing it into liquibase-core since it was working for you before on 3.x.

The liquibase-db2i extension adds some additional improvements to our db2 on as/400 support, but there is also existing checks for as/400 in liquibase-core too. We really need to bring the extension into the main codebase soon to avoid the confusion, and no need to make it worse with this fix.

I don't have an as/400 to test the actual SQL so I assume it works for you :) Worst case it stays broken on as400 and is nice and isolated so doesn't break anything else.