Flyway DB2 change delimiter NOT WORKING

Question

We use Flyway as a DB migration tool. Now we want to use it for executing some import statements on our database.

In the data are some stored procedures and commands that contain the ";" character.

So we wanted to change the delimiter in the sql script.

In IBM Data studio I achieved this by following sql code:

--#SET TERMINATOR @

insert into "CONTROL"."AP18_ADMIN_RIGHTS" ("REG_ID", "USERNAME") values(12,'testuser')@

--#SET TERMINATOR ;

But when I tried to execute this code using flyway I got the following error message:

 [ERROR] Failed to execute goal org.flywaydb:flyway-maven-plugin:3.0:migrate (default-cli) on project healthstat-database: org.flywaydb.core.internal.dbsupport.FlywaySqlScriptException: Error executing statement at line 3: insert into "CONTROL"."AP18_ADMIN_RIGHTS" ("REG_ID", "USERNAME") values(6,'testuser')@
 [ERROR] insert into "CONTROL"."AP18_ADMIN_RIGHTS" ("REG_ID", "USERNAME") values(9,'testuser')@
 [ERROR] insert into "CONTROL"."AP18_ADMIN_RIGHTS" ("REG_ID", "USERNAME") values(10,'testuser')@
 [ERROR]
 [ERROR] --#SET TERMINATOR: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601,SQLERRMC=(6,'testuser')@
 [ERROR] insert into;, "USERNAME") values;<space>, DRIVER=3.64.82

Is it possible to change the delimiter in the sql files?

Answer 1

There is no SQL statement that you can execute in DB2 to set the statement delimiter.

The syntax --#SET TERMINATOR x is specific to the DB2 Command Line Processor, and Data Studio implemented it (presumably) for compatibility.

It appears that Flyway would have to be modified to support different statement terminators with DB2.

Answer 2

I've override class org.flywaydb.core.internal.dbsupport.db2.DB2SqlStatementBuilder with the code, partially taken from MySQLSqlStatementBuilder:

package org.flywaydb.core.internal.dbsupport.db2;
import org.flywaydb.core.internal.dbsupport.Delimiter;
import org.flywaydb.core.internal.dbsupport.SqlStatementBuilder;

public class DB2SqlStatementBuilder extends SqlStatementBuilder {

private static final String DELIMITER_KEYWORD = "--#SET TERMINATOR";

public DB2SqlStatementBuilder() {
}

@Override
public Delimiter extractNewDelimiterFromLine( String line ) {
    if( line.toUpperCase().startsWith( DELIMITER_KEYWORD ) ) {
        return new Delimiter(
                line.substring( DELIMITER_KEYWORD.length() ).trim(),
                false );
    }

    return null;
}

@Override
protected Delimiter changeDelimiterIfNecessary( String line,
        Delimiter delimiter ) {
    if( line.toUpperCase().startsWith( DELIMITER_KEYWORD ) ) {
        return new Delimiter(
                line.substring( DELIMITER_KEYWORD.length() ).trim(),
                false );
    }

    return delimiter;
}

@Override
protected boolean isSingleLineComment( String line ) {
    return line.startsWith( "--" ) && !line.startsWith( DELIMITER_KEYWORD);
}

So I can use DB2 delimiter in my SQL sources. In Web application you should put this class in web/classes so it will be loaded before flyway.jar placed in web/lib due to http://download.oracle.com/otn-pub/jcp/servlet-2.4-fr-spec-oth-JSpec/servlet-2_4-fr-spec.pdf

PS: I create the issue https://github.com/flyway/flyway/issues/1163 vote if any.