DB2 change delimiter using --#SET TERMINATOR

[pswrdf]

There no way to create migration script with stored procedures with custom delimiter for DB2.
I've seen the http://stackoverflow.com/questions/15938000/set-terminating-character-in-flyway-for-db2-procedure-creation but we have a procedures with many enclosed begin-end statements, so current version of DB2SqlStatementBuilder fails.
At now I fix the issue with a trick:
http://stackoverflow.com/questions/30897357/flyway-db2-change-delimiter-not-working

So there a lot of possible solutions like set DELIMITER_KEYWORD evidently by java method for Flyway class, or set custom DbSupportFactory, or may be another solution that will fit the common concept of Flyway developing. Or may be parse native DB2 comment "--#SET TERMINATOR" the appreciated way.

[Manfred73]

We have the same issue for DB2 Z/OS. Seems this fix has not been implemented for DB2 Z/OS. Can this also be fixed for DB2 Z/OS?

[franciskittu]

Hi,
I really appreciate your work and I'm an enthusiastic Flyway entry user. However, I would like to clarify the way this "terminator feature" works.
I think it is necessary to manage the terminator from the point it is defined until another explicit definition is stated. The fix works only if the --#SET TERMINATOR statement is stated before any sql statement for which the char ";" is not the delimiter. For example, a developer who reasonably wants to execute an entire DDL file with @ as delimiter (like the command db2 -td@ -vf file.sql) will write:

--begin of file
--#SET TERMINATOR @
CREATE SCHEMA "A" AUTHORIZATION "DB2INST1"@
CREATE SCHEMA "B" AUTHORIZATION "DB2INST1"@
CREATE SCHEMA "C" AUTHORIZATION "DB2INST1"@
-- end of file

Instead, the script above will rollback due to the failure of the second statement. In order to execute the entire file using @ as delimiter the developer should write:

--begin of file
--#SET TERMINATOR @
CREATE SCHEMA "A" AUTHORIZATION "DB2INST1"@
--#SET TERMINATOR @
CREATE SCHEMA "B" AUTHORIZATION "DB2INST1"@
--#SET TERMINATOR @
CREATE SCHEMA "C" AUTHORIZATION "DB2INST1"@
-- end of file

I suppose this is not the intended support for this feature. Do you agree? Am I wrong with something?

[pswrdf]

The fix works only if the --#SET TERMINATOR statement is stated before any sql statement for which the char ";" is not the delimiter.

The fix works correct way (first one described by you). You may put SET TERMINATOR at any line and it will work until end of file or next SET TERMINATOR. You can test it if any suspicions.

[franciskittu]

Thanks for your reply.
I have already tested it. The same script failed with only one SET TERMINATOR definition at the beginning and worked after changing the script by repeating the SET TERMINATOR definition before each SQL statement. This is the reason why I wrote the comment above. I'm running FLYWAY command line version 4.2.0 against DB2 9.7 ESE.

[pswrdf]

We use org.flywaydb:flyway-core:3.2.1 as jar dependency. And we put java files into our project so it loads after flyway.jar and overrides flyway's classes. And it works correctly from SET until end of file. Thats all I know.

PS: there some other fixes for flyway db2. You can share them with developers.

[franciskittu]

Ok. Thanks for your time.

[FerdinandPr]

I use flyway 4.2.0 and still have the problem.

i need to repeat the SET TERMINATOR before each command. My Statement-file is about 500 commands.

Where is my mistake ?

[MikeH-Halo]

We couldn't get this to work with Flyway 4.2.0, which is a shame. We resorted to creating the procedure outside Flyway and then only calling the procedure from within Flyway. That worked for us.

[thomaskos]

I can confirm the issue for flyway 4.2.0. For this version the mentioned workaround (repeating SET TERMINATOR) is required.
Seems to work for flyway > 5.0.0