IBM BPM, an Upgrade and a missing Stored Procedure

One of my colleagues saw an interesting issue with an IBM BPM upgrade today, switching from 8.5.6 to 8.5.7.

He's using BPM Standard, but that's not terribly relevant.

This is what he saw in the logs, after running the DBUpgrade.sh script: -

…

[Sep 28, 2017 12:02:12 PM] 00000001 SEVERE: com.lombardisoftware.tools.dbupgrade.UpgradeDatabase.upgradeStandardDatabase - Exception while upgrading your BPM installation
[Sep 28, 2017 12:02:12 PM] 00000001 SEVERE: com.lombardisoftware.tools.dbupgrade.UpgradeDatabase.upgradeStandardDatabase - Check the log files for details
[Sep 28, 2017 12:02:12 PM] 00000001 FINE: com.lombardisoftware.tools.dbupgrade.UpgradeDatabase.upgradeStandardDatabase - Errors occurred while running the schema upgrade.  Check the log files for details.
java.lang.RuntimeException: Errors occurred while running the schema upgrade.  Check the log files for details.

...

        at org.apache.tools.ant.Main.main(Main.java:268)
[Sep 28, 2017 12:02:12 PM] 00000001 SEVERE: com.lombardisoftware.ant.JULDefaultLogger.printMessage -
BUILD FAILED
/opt/IBM/BPM/v8.5/util/dbUpgrade/upgrade.xml:161: The following error occurred while executing this line:
/opt/IBM/BPM/v8.5/util/dbUpgrade/upgrade.xml:791: Java returned: 1
...

Whilst there were a few non-severe DB-related exceptions: -

…

[Sep 28, 2017 12:02:12 PM] 00000001 FINE: com.lombardisoftware.tools.dbupgrade.utils.dbtoleration.SQLFileExec4Toleration.isToleratableException - A database exception [ErrorCode = -204, SQLState = 42704, ErrorMessage = DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2INST1.LSW_HOUSE_KEEPING, DRIVER=4.21.29] ocurred.
The above exception will be tolerated and it is safe to ignore.
The SQL statement is
 DROP PROCEDURE db2inst1.LSW_HOUSE_KEEPING
…

[Sep 28, 2017 12:02:12 PM] 00000001 FINE: com.lombardisoftware.tools.dbupgrade.utils.dbtoleration.SQLFileExec4Toleration.isToleratableException - A database exception [ErrorCode = -204, SQLState = 42704, ErrorMessage = DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DB2INST1.LSW_ERASE_BPD_INST_NO_GROUP, DRIVER=4.21.29] ocurred.
The above exception will be tolerated and it is safe to ignore.
The SQL statement is
 DROP PROCEDURE db2inst1.LSW_ERASE_BPD_INST_NO_GROUP
…

etc., there was a more serious exception: -

[Sep 28, 2017 12:02:12 PM] 00000001 FINE: com.lombardisoftware.tools.dbupgrade.utils.dbtoleration.SQLFileExec4Toleration.isToleratableException - No matched error code is found, the error is not in the toleration list.
[Sep 28, 2017 12:02:12 PM] 00000001 SEVERE: com.lombardisoftware.tools.SQLFileExec.handleError - Error executing SQL statement: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=DB2INST1.LSW_ERASE_TEMP_GROUPS;PROCEDURE, DRIVER=4.21.29
[Sep 28, 2017 12:02:12 PM] 00000001 SEVERE: com.lombardisoftware.tools.SQLFileExec.handleError - SQL statement that failed: CREATE PROCEDURE db2inst1.LSW_HOUSE_KEEPING(IN modeHK DECIMAL(12,0))
…

[Sep 28, 2017 12:02:12 PM] 00000001 FINE: com.lombardisoftware.tools.SQLFileExec.handleError - DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=DB2INST1.LSW_ERASE_TEMP_GROUPS;PROCEDURE, DRIVER=4.21.29
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=DB2INST1.LSW_ERASE_TEMP_GROUPS;PROCEDURE, DRIVER=4.21.29
…

[Sep 28, 2017 12:02:12 PM] 00000001 FINE: com.lombardisoftware.tools.SQLFileExec.start - SQL file execution finished.
[Sep 28, 2017 12:02:12 PM] 00000001 SEVERE: com.lombardisoftware.tools.dbupgrade.UpgradeDatabase.upgradeStandardDatabase - Exception while upgrading your BPM installation
[Sep 28, 2017 12:02:12 PM] 00000001 SEVERE: com.lombardisoftware.tools.dbupgrade.UpgradeDatabase.upgradeStandardDatabase - Check the log files for details
[Sep 28, 2017 12:02:12 PM] 00000001 FINE: com.lombardisoftware.tools.dbupgrade.UpgradeDatabase.upgradeStandardDatabase - Errors occurred while running the schema upgrade.  Check the log files for details.
java.lang.RuntimeException: Errors occurred while running the schema upgrade.  Check the log files for details.
…

It took a while, but, after some digging around, it looks like the missing Stored Procedure ( LSW_ERASE_TEMP_GROUPS ) wasn't ever created when the environment was first built.

I had him re-run the initial build script: -

db2 connect to BPMDB
db2 -tdGO -vf /opt/IBM/BPM/v8.5/profiles/DmgrProfile/dbscripts/PCCell1.De1/DB2/BPMDB/createProcedure_Standard.sq

db2 terminate

and then re-run the DBUpgrade.sh script.

This time around … all went well.

So, the moral of the story, always read the logs, and dig to see what's actually going on - this time, we had a script that was creating a Stored Procedure ( CREATE PROCEDURE db2inst1.LSW_HOUSE_KEEPING ), that was calling another Stored Procedure ( DB2INST1.LSW_ERASE_TEMP_GROUPS ) … that didn't exist :-(