CODEWITHSUNDEEP
db2liquibasedb2-luw
Filippo
Filippo

Reputation: 11

Creating view in DB2 LUW with Liquibase

I'm using Liquibase 3.5.3 to create a view in DB2 LUW 11.0. I set "replaceIfExists" property to true and at runtime it give me an "replaceIfExists is not allowed on db2" error.

I know that officially this property is not supported for DB2 in Liquibase but the database api allows it (I manually execute sql script with create or replace and works fine).

There is some workaround to run this scripts in DB2 or maybe can you implement this feature?

Example:

CREATE OR REPLACE myView AS (
    SELECT * FROM myTable
)

Upvotes: 0

Views: 360

Answers (1)

user330315
user330315

Reputation:

I would put that script into a file and then include the file from within liquibase with the runOnChange="true" for the changeSet

<changeSet author="arthur.dent" id="42" runOnChange="true">
    <sqlFile path="create_view.sql"
             encoding="UTF-8"
             relativeToChangelogFile="true"
             stripComments="false"
             splitStatements="false"/>
</changeSet>

Because of the runOnChange, Liquibase will include a checksum of the actual SQL file and will only run it if the definition of the view changed.

Another option is to use runAlways="true" instead and then the view will be re-created every time you run Liquibase. This would be necessary to automatically pick up changes in the underlying table(s).

Upvotes: 1

Related Questions