Task Cleanup options used in the Process Admin console may cause BadSqlGrammarException in IBM Business Process Manager (BPM)

Technote (troubleshooting)

Problem(Abstract)

In IBM Business Process Manager, using Task Cleanup options in the Process Admin console to remove tasks and attachments from a DB2 task database may result in the exception BadSqlGrammarException.

Symptom

The exception BadSqlGrammarException is typically issued in the following format:

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{CALL LSW_HOUSE_KEEPING(?)}];

Cause

A user temporary table space is required to run stored procedures delivered with IBM Business Process Manager. In DB2 Version 9.7, a user temporary table space named SYSTOOLSTMPSPACE is created by default. However, beginning with DB2 Version 10.1, the user temporary table space is no longer automatically allocated.

Note that when you are using Task Cleanup options in the Process Admin console, it is possible to receive the exception BadSqlGrammarException regardless of the version of DB2 that is used.

Resolving the problem

To resolve the problem, you must create a new user temporary table space.

You can create the table space at the same time that you create the DB2 task database. Alternatively, you can create the table space after the DB2 task database has already been created. Regardless of the approach you choose, the deployment environment must already exist as a result of running either the BPMConfig command-line utility or the Admin Console DE wizard.


To create the table space at the same time that you create the DB2 task database:

1. Edit the following file (where <de_name> is the name of the deployment environment and <db_name> is the name of the IBM Process Server database):
   
   <profile_root>\dbscripts\<de_name>\DB2\<db_name>\createDatabase.sql
   
   The default name of the IBM Process Server database is BPMDB.
   
   
2. In the createDatabase.sql file, enable the following statement:
   
   CREATE USER TEMPORARY TABLESPACE USRTMPSPC1;
   
   
3. Save your changes and then run the createDatabase.sql file to create the database and the user temporary table space.

To create the table space after the DB2 task database has already been created, complete the following steps:

1. Open a DB2 command window.
   
   
2. Run the following command to connect to DB2 (where <db_name> is the name of the IBM Process Server database):
   
   db2 connect to <db_name> user <user_name> using <db2_password>
   
   The default name of the IBM Process Server database is BPMDB. For example:
   
   db2 connect to BPMDB user db2admin using db2password
   
   
3. Run the following command to create the user temporary table space:
   
   db2 CREATE USER TEMPORARY TABLESPACE USRTMPSPC1
   
   
4. Run the following command to reset the DB2 connection:
   
   db2 connect reset