Temporary Batch Tables Not Cleaned From the Showcase Server Library

 

Problem Statement:

Temporary batch tables are not being cleaned up from the ShowCase server library.
In the showcase.log, see errors like below:

CUSTOM DEBUG DEP Thread-0 08/14/2009 09:32:22.535 AM SCUserExitPointExe.loadExitPrograms... JDBC DEBUG null Thread-0 08/14/2009 09:32:22.535 AM SCRemotePreparedStatementImpl.init: Customized SQL is DROP TABLE SHC80/B424849285 JDBC ERROR null Thread-0 08/14/2009 09:32:22.717 AM B424849285 in SHC80 type *FILE not found. JDBC ERROR null Thread-0 08/14/2009 09:32:22.717 AM com.ibm.db2.jdbc.app.DB2SQLSyntaxErrorException: B424849285 in SHC80 type *FILE not found. at java.lang.Throwable.init(Throwable.java:196) at java.sql.SQLException.init(SQLException.java:40) at com.ibm.db2.jdbc.app.DB2SQLSyntaxErrorException.init(DB2SQLSyntaxErrorException.java:47) at com.ibm.db2.jdbc.app.DB2SQLException.createException(DB2SQLException.java:119) at com.ibm.db2.jdbc.app.DB2PreparedStatementRuntimeImpl.SQLPrepare(Native Method) at com.ibm.db2.jdbc.app.DB2PreparedStatementRuntimeImpl.prepare(DB2PreparedStatementRuntimeImpl.java:224) at com.ibm.db2.jdbc.app.DB2PreparedStatement.setupPreparedStatement(DB2PreparedStatement.java:394) at com.ibm.db2.jdbc.app.DB2PreparedStatement.init(DB2PreparedStatement.java:313) at com.ibm.db2.jdbc.app.DB2Connection.prepareStatement(DB2Connection.java:3071) at com.ibm.db2.jdbc.app.DB2Connection.prepareStatement(DB2Connection.java:2990) at com.ibm.db2.jdbc.app.DB2Connection.prepareStatement(DB2Connection.java:2941) at com.ibm.db2.jdbc.app.DB2Connection.prepareStatement(DB2Connection.java:2761) at com.showcasecorp.jdbc.SCRemotePreparedStatementImpl.init(SCRemotePreparedStatementImpl.java:77) at com.showcasecorp.jdbc.SCRemoteConnectionImpl.prepareStatement(SCRemoteConnectionImpl.java:1450) at com.showcasecorp.jdbc.SCConnection.prepareStatement(SCConnection.java:125) at com.showcasecorp.ojdbc.OJStatement.SQLPrepare(OJStatement.java:321) at c

Solution:

The batch query temporary table clean up code gets executed by one of the 2 scenarios below:

The following two properties are set in the config file and the server restarts:

• showcasecorp.batchquery.removeExpired=true
• showcasecorp.batchquery.cleanupRunInterval=0

After every batch query execution, the temporary tables clean up code checks for the expiration of the temporary tables.

• A temporary table gets deleted only after 7 days from start time of the corresponding batch query and this clean up of temporary table is applicable only if batch query is scheduled to run only once.
• If the batch query is set to run daily or monthly then clean up won't happen for the corresponding temporary tables.

Note:  Since you can only set the start date and start time of the batch query in batch option, it is impossible to decide when to clean up those tables. If the batch query is scheduled for daily or monthly execution then it replaces the old temporary table with new one (i.e. deletes the old table and creates a new table with the same name).

Ref#: 1484362