When trying to change the dates on a CMR report users are unable to choose recent dates.
Identifying the Problem
- Version: Jive Custom (On Premise)
- Database: Oracle DB
Users are unable to change the dates in CMR reports.
You will also be able to find errors in the sbs.log file related to "ORA-00600", where ETL summary tasks are failing to run:
14 Jan 2014 01:27:11,141 [http-127.0.0.1-9001-5] [52708:username:REGULAR] ERROR tasks.AnalyticsChartSummationETL - Opps an error occured while summarizing
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT d.begin_ts, q.user_id, q.container_id, q.container_type, COUNT(DISTINCT q.thread_id) * 1 FROM jivedw_question q, jivedw_day d, jivedw_user_type y, (SELECT m.thread_id AS tid, MIN(os.status_ts) AS ts FROM jivedw_message m, jivedw_question q, jivedw_object_status os WHERE m.parent_message_id <> 0 AND q.thread_id = m.thread_id AND m.message_id = os.object_id AND os.object_type = 2 AND os.status = 2 GROUP BY m.thread_id HAVING MIN(os.status_ts) >= ? AND MIN(os.status_ts) <= ? )t1 WHERE q.user_id = y.user_id AND (y.external = 0 OR y.external = 2) AND y.visible = 1 AND q.thread_id = tid AND q.thread_id NOT IN (SELECT DISTINCT object_id FROM jivedw_deleted_objects WHERE object_type IN ( 1, 27) AND deleted_ts < ? ) AND q.thread_id NOT IN (SELECT DISTINCT o.object_id FROM jivedw_moved_objects o, jivedw_message m WHERE o.object_id = m.thread_id AND m.parent_message_id <> 0 AND o.moved_ts > m.creation_ts AND o.object_type IN ( 1, 27) ) AND t1.ts >= d.begin_ts AND t1.ts <= d.end_ts GROUP BY d.begin_ts, q.user_id, q.container_id, q.container_type ]; SQL state ; error code ; ORA-00600: internal error code, arguments: , [hash aggregation can't be done], , , , , , 
; nested exception is java.sql.SQLException: ORA-00600: internal error code, arguments: , [hash aggregation can't be done], , , , , , 
It was found that the database tables referenced in the Jive application had not been analyzed in over a month. This was causing the PLAN to return many more rows than needed.
Reanalyze all the tables in the database schema using ANALYZE command.
If you continue to see ORA-00600 errors after re-analyzing the database then please file a support case with Jive to further investigate the issue.