flyway db2 create procedure

Question

I am using flyway commandline 2.0.3 to migrate a db2 LUW database. It works fine for various scripts holding create table statements etc. But I am not able to execute a create procedure statement. The begin-end block seems to be handled wrong.

SET CURRENT SCHEMA = DB2INST1;

SET CURRENT PATH = SYSIBM,SYSFUN,SYSPROC,SYSIBMADM,DB2INST1;

CREATE OR REPLACE PROCEDURE ${schema_name_node}.SP_E_H_A_D_V (
    IN "@M" VARCHAR(15000),
    IN "@S" TIMESTAMP,
    IN "@E" TIMESTAMP,
    IN "@T" DECIMAL(4, 2) )
  SPECIFIC "SP_E_H_A_D_V"
  DYNAMIC RESULT SETS 1
  LANGUAGE SQL
  NOT DETERMINISTIC
  NO EXTERNAL ACTION
  READS SQL DATA
  CALLED ON NULL INPUT
  INHERIT SPECIAL REGISTERS
  OLD SAVEPOINT LEVEL
BEGIN 
DECLARE V_MINUTES INTEGER DEFAULT 0;
...

Here is the output for the migrate-command:

 D:\flyway-commandline-2.0.3-dist\flyway-commandline-2.0.3>flyway.cmd migrate
Flyway (Command-line Tool) v.2.0.3

Current schema version: 1.13.1.201211151200.04.000
Migrating to version 1.13.1.201212101200.04.000
ERROR: com.googlecode.flyway.core.api.FlywayException: Error executing statement
 at line 5: CREATE OR REPLACE PROCEDURE JUDITH_NODE.SP_E_H_A_D_V
 (
    IN "@M"     VARCHAR(15000),
    IN "@S"     TIMESTAMP,
    IN "@E"     TIMESTAMP,
    IN "@T"     DECIMAL(4, 2) )
  SPECIFIC "SP_E_H_A_D_V"
  DYNAMIC RESULT SETS 1
  LANGUAGE SQL
  NOT DETERMINISTIC
  NO EXTERNAL ACTION
  READS SQL DATA
  CALLED ON NULL INPUT
  INHERIT SPECIAL REGISTERS
  OLD SAVEPOINT LEVEL
BEGIN
DECLARE V_MINUTES INTEGER DEFAULT 0
ERROR: Caused by com.ibm.db2.jcc.am.ro: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42
601, SQLERRMC=END-OF-STATEMENT;ES INTEGER DEFAULT 0;<psm_semicolon>, DRIVER=3.58
.82
ERROR: FlywayException: Migration to version 1.13.1.201212101200.04.000 failed!
Changes successfully rolled back.
ERROR: Occured in com.googlecode.flyway.core.migration.DbMigrator.applyMigration
() at line 266

I found a question to a similar problem with hsql and flyway 1.7.

Is there a way to handle the problem for db2-database?

Axel Fontaine · Accepted Answer · 2013-01-15 15:15:42Z

1

This is not supported yet. Please file an enhancement request in the Issue Tracker. Make sure to include a script containing a CREATE PROCEDURE statement so I can make sure your case is covered by tests.

answered Jan 15, 2013 at 15:15

Axel Fontaine

35.4k19 gold badges110 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

garrmark · Accepted Answer · 2013-09-04 15:00:22Z

1

We've been able to get past most BEGIN/END block issues with DB2 flyway scripts by using a trick of adding a comment immediately after the semicolon for statements inside the block.

BEGIN
  Statment1;--
  Statment2;--
  Statment3;--
END;

This works when applying scripts using DB2 CLP as well as JDBC.

answered Sep 4, 2013 at 15:00

garrmark

891 silver badge11 bronze badges

Collectives™ on Stack Overflow

flyway db2 create procedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related