search

flyway / flyway-community-db-support

Offical Flyway Community Supported Database Plugins
https://flywaydb.org/
Apache License 2.0
5 stars 30 forks source link

Add DB2Z support to Flyway Community DB Support #44

Closed Barry-RG closed 4 months ago

Barry-RG commented 5 months ago

This is to transfer https://github.com/flyway/flyway/pull/3577 from flyway/flyway to the Community DB Support repository.

Thank you @gahoekstraibm for all your help.

gahoekstraibm commented 5 months ago

@Barry-RG Do you expect any action from my side at this moment?

Barry-RG commented 5 months ago

Not at this point. Thank you

Barry-RG commented 3 months ago

@gahoekstraibm We have started shipping the DB2zOS integration with Flyway. We have yet to push the documentation as we were wondering if you would be able to try the new release and confirm its still as working as expected?

gahoekstraibm commented 1 month ago

Hi @Barry-RG , Sorry it took me so long to try the new release. Had some busy weeks after returning from my vacation.

I took the latest command line client from the Redgate website (10.18.0) and after some fiddling with the TOML configuration, I managed to have flyway creating database objects on DB2 for z/OS.

However, for some reason, it looks like the db2z plugin's DB2ZJdbcTemplate is not being used for CALL statements, while this is working in my local 9.x fork of flyway.

I get the exception below, which shows that the regular JdbcTemplate is trying to execute the CALL statement, and although detectStatement identifies it as DB2Z CALL statement, it looks like it isn't created as DB2ZCallProcedureParsedStatement (in DB2ZParser.createStatement() ). Actually, I think DB2ZParser.createStatement() is not even called :

DEBUG: Parsing V4.17.4.0.2__MESSAGTS_AlterCompressREORG.sql ...
DEBUG: detectStatementType: simplifiedStatement=CALL
DEBUG: detectStatementType: DB2Z CALL statement found
DEBUG: Found statement at line 1: CALL SYSPROC.DSNUTILU('17401', 'NO','TEMPLATE SYSCOPY DSN(''E&DB(1,3)..&DB..&SN..P&PA(2,4)..DB2&IC.IC.&UQ.'') UNIT SYSDA REORG TABLESPACE GEERTDB.MESSAGTS AUX NO LOG NO NOSYSREC COPYDDN (SYSCOPY) SORTDEVT SYSDA SORTNUM 10 STATISTICS TABLE(ALL) INDEX(ALL) FREQVAL NUMCOLS 5 COUNT 10 SHRLEVEL REFERENCE','')
DEBUG: Starting migration of schema "FLYWTEST" to version "4.17.4.0.2 - MESSAGTS AlterCompressREORG" ...
Migrating schema "FLYWTEST" to version "4.17.4.0.2 - MESSAGTS AlterCompressREORG"
DEBUG: Executing SQL: CALL SYSPROC.DSNUTILU('17401', 'NO','TEMPLATE SYSCOPY DSN(''E&DB(1,3)..&DB..&SN..P&PA(2,4)..DB2&IC.IC.&UQ.'') UNIT SYSDA REORG TABLESPACE GEERTDB.MESSAGTS AUX NO LOG NO NOSYSREC COPYDDN (SYSCOPY) SORTDEVT SYSDA SORTNUM 10 STATISTICS TABLE(ALL) INDEX(ALL) FREQVAL NUMCOLS 5 COUNT 10 SHRLEVEL REFERENCE','')
... 
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: NO AUTHORIZED PROCEDURE NAMED SYSPROC.DSNUTILU HAVING COMPATIBLE ARGUMENTS WAS FOUND. SQLCODE=-440, SQLSTATE=42884, DRIVER=4.32.28
        at com.ibm.db2.jcc.am.b7.a(b7.java:810)
        at com.ibm.db2.jcc.am.b7.a(b7.java:66)
        at com.ibm.db2.jcc.am.b7.a(b7.java:140)
        at com.ibm.db2.jcc.am.lc.b(lc.java:2515)
        at com.ibm.db2.jcc.am.lc.c(lc.java:2496)
        at com.ibm.db2.jcc.t4.ab.n(ab.java:917)
        at com.ibm.db2.jcc.t4.ab.f(ab.java:154)
        at com.ibm.db2.jcc.t4.p.e(p.java:81)
        at com.ibm.db2.jcc.t4.av.k(av.java:175)
        at com.ibm.db2.jcc.am.lc.ao(lc.java:2457)
        at com.ibm.db2.jcc.am.lc.a(lc.java:3429)
        at com.ibm.db2.jcc.am.lc.e(lc.java:1138)
        at com.ibm.db2.jcc.am.lc.execute(lc.java:1117)
        at org.flywaydb.core.internal.jdbc.JdbcTemplate.executeStatement(JdbcTemplate.java:978)
        at org.flywaydb.core.internal.sqlscript.ParsedSqlStatement.execute(ParsedSqlStatement.java:856)
        at org.flywaydb.core.internal.sqlscript.DefaultSqlScriptExecutor.executeStatement(DefaultSqlScriptExecutor.java:980)
        ... 24 more
Barry-RG commented 1 month ago

@gahoekstraibm Thank you for trying. I think the error is due to an API drift between the OSS and Redgate editions of Flyway. To be sure, could you try again with one of the OSS versions of Flyway 10.18.0? https://repo1.maven.org/maven2/org/flywaydb/flyway-commandline/10.18.0/

gahoekstraibm commented 1 month ago

@Barry-RG The OSS version of Flyway 10.18.0 works fine.

Barry-RG commented 1 month ago

@gahoekstraibm we have made the changes to apply to all versions of Flyway. Would you mind trying with our latest release? https://download.red-gate.com/maven/release/com/redgate/flyway/flyway-commandline/10.18.2/

gahoekstraibm commented 1 month ago

Hi @Barry-RG , also the latest release is working fine now!

gahoekstraibm commented 1 month ago

I just created a new PR to update supported DB2z versions: #62