Create or replace view with liquibase on DB2

Question

I would like to create or replace a view on DB2 using liquibase and its changeSet tag: XML Sample

This is what I include in the changelog.xml file:

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    logicalFilePath="lon-service-mpd/gin/15.100/15.100.0.0.changelog.xml"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

    <changeSet author="mas-gin-gestion-echelon-service" id="create-view-from-table-periodeavancement-type-personnel">
        <createView schemaName="GIN" viewName="V_PERIODEAVANCEMENT_1">select IDPERIODE, CAMPAGNETA from GIN.PERIODEAVANCEMENT</createView>
    </changeSet>

</databaseChangeLog>

However, during the creation of the view, DB2 returns the following error liquibase.exception.DatabaseException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703

I do not find the way to fix the problem, even if the SQL syntax is correct.

are you sure that your columns in query are ok? sqlerror.de/db2_sql_error_-206_sqlstate_42703.html — bilak
then try to verify what is the output of the script with updateSQL command. — bilak

Alex Blasco Alex Blasco · Accepted Answer · 2019-04-16T13:16:46

I have fixed the problem by calling directly a sql file instead of using the XML sample. Here is my solution:

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
logicalFilePath="mas-gin-gestion-echelon-service-mpd/gin/15.100/15.100.0.0.changelog.xml"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

<changeSet id="create_view_periodeavancement" author="mas-gin-gestion-echelon-service">
    <sqlFile path="sql/create_view_periodeavancement.sql" relativeToChangelogFile="true"/>
</changeSet>

And the sql file:

CREATE OR REPLACE VIEW GIN.V_PERIODEAVANCEMENT_1 (IDPERIODE, TS_INSERT, BL_DELETE ) 
AS SELECT IDPERIODE, TS_INSERT, BL_DELETE
FROM PERIODEAVANCEMENT;

Create or replace view with liquibase on DB2

1 Answers