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

DB2: Clean excludes user-defined functions based on new classifications #2395

Closed
armondoortega opened this issue May 22, 2019 · 3 comments

Comments

Projects
None yet
2 participants
@armondoortega
Copy link

commented May 22, 2019

When running clean on a DB2 database it was noticed that not all user-defined functions are being dropped.

The following query is using an older deprecated catalog view and function classification.

 @Override
    protected Function[] doAllFunctions() throws SQLException {
        List<Map<String, String>> rows = jdbcTemplate.queryForList(
                "select p.SPECIFICNAME, p.FUNCNAME," +
                        " substr( xmlserialize( xmlagg( xmltext( concat( ', ', TYPENAME ) ) ) as varchar( 1024 ) ), 3 ) as PARAMS" +
                        " from SYSCAT.FUNCTIONS f inner join SYSCAT.FUNCPARMS p on f.SPECIFICNAME = p.SPECIFICNAME" +
                        " where f.ORIGIN = 'Q' and p.FUNCSCHEMA = ? and p.ROWTYPE = 'P'" +
                        " group by p.SPECIFICNAME, p.FUNCNAME" +
                        " order by p.SPECIFICNAME", name
        );

The ORIGIN filter appears to need to include 'E' and 'U' to include all user-defined functions.
based on the DB2 Documentation

Which version and edition of Flyway are you using? 5.2.0 Enterprise Edition
Which client are you using? Command-line
Which database are you using (type & version)? Db2 10.5+
Which operating system are you using? RHEL 6/7

@axelfontaine

This comment has been minimized.

Copy link
Contributor

commented May 22, 2019

Thanks for the report. Please share the smallest possible SQL snippet that we could use to reproduce this issue.

axelfontaine pushed a commit to flyway/flywaydb.org that referenced this issue May 22, 2019

@axelfontaine axelfontaine added this to the Flyway 6.0.0 milestone May 22, 2019

@axelfontaine

This comment has been minimized.

Copy link
Contributor

commented May 22, 2019

Thanks again for reporting. We went ahead and rewrote the relevant code to use the new ROUTINES view instead and this was fixed as part of that process.

axelfontaine pushed a commit that referenced this issue May 22, 2019

@armondoortega

This comment has been minimized.

Copy link
Author

commented May 22, 2019

Thanks a lot! Appreciate the quick turn around. If you end up still wanting me to send over some SQL just let me know.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.