Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Unable to Clean DB2 Database containing Modules #2856

Closed
monmallonga opened this issue Jun 12, 2020 · 2 comments
Closed

Unable to Clean DB2 Database containing Modules #2856

monmallonga opened this issue Jun 12, 2020 · 2 comments

Comments

@monmallonga
Copy link

@monmallonga monmallonga commented Jun 12, 2020

Which version and edition of Flyway are you using?

6.4.4

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin)

Gradle Plugin

Which database are you using (type & version)?

IBM DB2 LUW 11.1

Which operating system are you using?

Windows 10 Pro

What did you do?

This is related to an earlier issue I reported #2832

I performed a Clean Database and flyway was unable to drop Modules with Stored procedures.
Flyway generates drop procedure statements that includes Module members. This causes db2 to return an error 204 when it is unable to locate the procedure that is being dropped.

    private List<String> generateDropStatementsForProcedures() throws SQLException {
        String dropProcGenQuery =
                "select SPECIFICNAME from SYSCAT.ROUTINES where ROUTINETYPE='P' and ROUTINESCHEMA = '" + name + "'";
        return buildDropStatements("DROP SPECIFIC PROCEDURE", dropProcGenQuery);
    }

You can maybe try to add
and ROUTINEMODULENAME IS NULL

If you want to drop module members, syntax should be
ALTER MODULE <MODULE NAME> DROP PROCEDURE <PROC NAME>

What did you expect to see?

Should be able to clean database.

What did you see instead?
Unable to clean schema "TFS"
----------------------------
SQL State  : 42704
Error Code : -204
Message    : TFS.SQL200527150647939
@MikielAgutu
Copy link
Contributor

@MikielAgutu MikielAgutu commented Jun 18, 2020

I've taken your suggestion to amend generateDropStatementsForProcedures. It seems to work for the example on the IBM Db2 documentation:

     CREATE MODULE INVENTORY

     ALTER MODULE INVENTORY ADD
     TYPE ITEMLIST AS INTEGER ARRAY[VARCHAR(100)]

     ALTER MODULE INVENTORY ADD
     VARIABLE ITEMS ITEMLIST

     ALTER MODULE INVENTORY PUBLISH
     PROCEDURE UPDATE_ITEM(NAME VARCHAR(100), QUANTITY INTEGER)
     BEGIN
     SET ITEMS[NAME] = QUANTITY;
     END

     ALTER MODULE INVENTORY PUBLISH
     FUNCTION CHECK_ITEM(NAME VARCHAR(100)) RETURNS INTEGER
     RETURN ITEMS[NAME]

I'm not quite sure how to integrate your other suggestion;

If you want to drop module members, syntax should be ALTER MODULE <MODULE NAME> DROP PROCEDURE <PROC NAME>

Could you elaborate further? Perhaps even create a PR?

@monmallonga
Copy link
Author

@monmallonga monmallonga commented Jun 18, 2020

Actually, i just wanted to show a sample statement for dropping module members individually, prior to dropping the module, in case you want to individually drop members first. I wasnt really sure if other types of module members (udfs, types etc) would cause errors since my sample was just for stored procedure. But with your test program, i think youve covered it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants