Topic/Category: Reporting, Performance
Applies to:Ephesoft v3.1.x.x
Reporting Clean up Stored Procedure is not working nightly.
ERROR in dcma-report-all.log:
[ERROR ] com.ephesoft.dcma.reporting.service.ReportLoaderServiceImpl.checkAndUpdateBulkData
(ReportLoaderServiceImpl.java:816) – Unable to clean up data for 25 batches.could not execute native bulk manipulation query
org.hibernate.exception.SQLGrammarException: could not execute native bulk manipulation query…
Caused by: java.sql.SQLException: Invalid object name ‘JBPM4_VARIABLE’.
The issue was due to the specification of the database schema used on the Ephesoft DB and the Reporting DB. BY Default all Ephesoft SQL queries are performed with the assumption that the default dbo schema is being used. If you use a DB schema other than dbo then the this can cause issues with stored procedure and specific queries.
Since the Reporting service calls on the Clean up Batch Instance Stored Procedure nightly, this error will occur every time until the stored procedure is corrected with the correct DB schema.
Once this is done the Reporting service Clean up stored procedure will perform the clean up properly.
Note: The nightly stored procedure can take a long time to perform depending on the number of records in the JBPM tables.