Have an issue with scheduled reporting and wanted to see if others have seen the same.
We are using Service Manager 9.40. Our database is Oracle, with a primary instance for production and a replicated and synchronized database for Service Manager reporting. We have the appropriate entry in the sm.ini file for the replicated database parameter. We also have checked off the boxes for "Use Production Database when Replicated Database is disabled" and "Use Replicated Database by default when users create report" under Reporting > Administration.
We are using the built-in reporting tool to create a schedule and send reports via email in a PDF format to various recipients.
This has been very successful for us until this past weekend when UNIX work was performed which took the replicated database off line for a time. The DBAs and the logs confirm that it's back on-line and is functioning. But the reporting just hasn't been the same since.
As an example, one scheduled report that is sent out as a PDF is of a dashboard which includes two line charts, two bar charts, and one pivot table. The table supporting all this is the interaction table.
When we receive the email and open the PDF file, we see a report header and the title of each individual chart, but no chart is rendered by the report writer.
Now, if I were to go to SM via the web client, pull up that dashboard, export it to a PDF, and open it up there, it appears fine.
As a test, I removed all the reports from the dashboard, added one, generated the email, removed it, added a different one, generated an email, etc., just to see if any one report from that dashboard was causing an issue. What was curious was that of all the reports, only the pivot table would render correctly within the PDF file that was included in the email that was sent.
Just for grins, I changed one of the reports from Chart to List, generated the email, and checked the PDF. It rendered perfectly. I changed it back to Chart and the issue returned.
Again, all was fine until the maintenance work this past weekend. And this matter is affecting various scheduled reports that were previously working without issue.
Why would our charts not render correctly in the PDF files? Was it something do to with the reporting instance not being available? Is there some step that we needed to do during this database maintenance to ensure that it would be used correctly for Service Manager reporting once it became available again even though we had the system configured to flip to the production database when the replicated database is unavailable?
No, at least not yet. We have a ticket in with support and they initially indicate that reporting should have continued working following the maintenance with the current settings that we have. I'm waiting for further word from them but am wondering if a complete system reset is necessary in situations like this as a best practice.
When Service Manager starts to act like this and it doesn't make sense....bouncing is always a good first step.
Thanks (and kudos) to both of you. Reboot worked. Luckily, we have report.export configured to run on a secondary node so restarting that without affecting our users was a breeze (still had to go through the formalities of approvals, but still). Probably should have checked the log files before making my original post. Saw these entries following the maintenance work:
6040( 3388) 07/10/2016 07:00:30 RTE E Error: SQL code=1861 message=ORA-01861: literal does not match format string
6040( 3388) 07/10/2016 07:00:30 RTE E SQL code=1861 message=ORA-01861: literal does not match format string (report.export.prepare,do.prepare)
Also saw these:
6040( 3388) 07/10/2016 07:00:30 RTE E API=OCIStmtExecute [in sqociOpen], Statement=SELECT t0m1."LOCATION", COUNT(*) AS cnt FROM INCIDENTSM1 t0m1 WHERE t0m1."AFFECTED_ITEM"=:Y and t0m1."PRODUCT_TYPE"=:Y and t0m1."OPEN_TIME">=:Y GROUP BY t0m1."LOCATION" ORDER BY COUNT(*) DESC (report.export.prepare,do.prepare)
6040( 5428) 07/10/2016 07:00:46 error while exporting:29458,pivot, message:null
6040( 5428) 07/10/2016 07:00:46 error while exporting:29455,chart, message:null
6040( 5428) 07/10/2016 07:00:46 error while exporting:29453,numeric, message:null
6040( 5428) 07/10/2016 07:00:46 error while exporting:29516,chart, message:null
These were not seen before the maintenance, and they disappeared following the reboot.
Our report configuration is working insofar as the system flipping from/to the replicated database when it is unavailable, but it would appear that the best practice would be to include a restart of report.export just to be safe. All I know is that my users are happy.