com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=AK51602;CREATE TABLE;DSNDB04, DRIVER=4.19.26

There are no available Samebug tips for this exception. Do you have an idea how to solve this issue? A short tip would help users who saw this issue last week.

  • *Description* In our [Compatibility Matrix|https://web.liferay.com/documents/14/21598941/Liferay+DXP+Compatibility+Matrix.pdf], we claim to support DB2, yet during the upgrade process, we send many commands to DB2 that are invalid syntax for it. In particular, we send a lot of {{alter_column_type \[tableName\] \[columnName\] \[newType\]}} commands, which is invalid syntax for DB2. The correct syntax for DB2 would be {{alter table \[tableName\] alter column \[columnName\] set data type \[newType\]}}. *Steps to Reproduce* # Start up Liferay 6.2 connected to a DB2 database (I used DB2 10.5). # Shut down Liferay. # Use the DXP Upgrade tool to upgrade your DB2 database to DXP. # Observe the logs as the upgrade moves forward. (/) *Expected Results:* The upgrade would complete successfully with no errors. (x) *Actual Results:* The upgrade completes, but many errors of the following nature appear in the logs: {noformat} com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=alter_column_type RepositoryEntry;BEGIN-OF-STATEMENT;<create_variable>, DRIVER=4.19.66 at com.ibm.db2.jcc.am.kd.a(kd.java:747) at com.ibm.db2.jcc.am.kd.a(kd.java:66) at com.ibm.db2.jcc.am.kd.a(kd.java:135) at com.ibm.db2.jcc.am.fp.c(fp.java:2788) at com.ibm.db2.jcc.am.fp.d(fp.java:2776) at com.ibm.db2.jcc.am.fp.b(fp.java:2143) at com.ibm.db2.jcc.t4.bb.i(bb.java:226) at com.ibm.db2.jcc.t4.bb.c(bb.java:48) at com.ibm.db2.jcc.t4.p.b(p.java:38) at com.ibm.db2.jcc.t4.vb.h(vb.java:124) at com.ibm.db2.jcc.am.fp.kb(fp.java:2138) at com.ibm.db2.jcc.am.fp.a(fp.java:3325) at com.ibm.db2.jcc.am.fp.c(fp.java:765) at com.ibm.db2.jcc.am.fp.executeUpdate(fp.java:744) at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:120) at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java) at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:292) at com.liferay.portal.dao.db.DB2DB.runSQL(DB2DB.java:81) at com.liferay.portal.dao.db.BaseDB.runSQL(BaseDB.java:262) at com.liferay.portal.kernel.dao.db.BaseDBProcess.runSQL(BaseDBProcess.java:66) at com.liferay.portal.kernel.upgrade.UpgradeProcess.alter(UpgradeProcess.java:406) at com.liferay.portal.upgrade.v7_0_0.UpgradeRepositoryEntry.doUpgrade(UpgradeRepositoryEntry.java:27) at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:90) at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:117) at com.liferay.portal.upgrade.UpgradeProcess_7_0_0.doUpgrade(UpgradeProcess_7_0_0.java:98) at com.liferay.portal.kernel.upgrade.UpgradeProcess.upgrade(UpgradeProcess.java:90) at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil._upgradeProcess(UpgradeProcessUtil.java:175) at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil.upgradeProcess(UpgradeProcessUtil.java:143) at com.liferay.portal.kernel.upgrade.util.UpgradeProcessUtil.upgradeProcess(UpgradeProcessUtil.java:125) at com.liferay.portal.events.StartupHelper.upgradeProcess(StartupHelper.java:164) at com.liferay.portal.events.StartupHelperUtil.upgradeProcess(StartupHelperUtil.java:81) at com.liferay.portal.tools.DBUpgrader.upgrade(DBUpgrader.java:153) at com.liferay.portal.tools.DBUpgrader.main(DBUpgrader.java:99) {noformat} This causes many columns to retain their old type instead of being given the new type that is necessary for DXP. (!) *Can't test in master* because DB2 is not supported in master. However, the necessary logic to fix this issue appears to be missing from the {{DB2SQLTransformerLogic}} class in master, so we should commit the fix to master too (c8370cfc99afbd064a3905d0b4b5239b3766eca7). *Reproduced in ee-7.0.x* (f51c2a99cb6c9b5e7fd6dab46b73a322029c8faa)
    via by Michael Bowerman,
    • com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-551, SQLSTATE=42501, SQLERRMC=AK51602;CREATE TABLE;DSNDB04, DRIVER=4.19.26 at com.ibm.db2.jcc.am.kd.a(Unknown Source) at com.ibm.db2.jcc.am.kd.a(Unknown Source)

    Users with the same issue

    Unknown visitor1 times, last one,
    Unknown visitor1 times, last one,
    Unknown visitor1 times, last one,