When trying to run an At a Glance Report or Trend Report, (UMP > USM portlet > Reports dropdown > At a Glance Report | Trend), it is possible to get an error like this in the wasp log:
Apr 07 15:27:34:346 ERROR [http-bio-80-exec-288, org.apache.catalina.core.ContainerBase.[wasp-engine].[localhost].[/nisapi].[resteasy-servlet]] invoke() Servlet.service() for servlet [resteasy-servlet] in context with path [/nisapi] threw exception
Apr 07 15:27:34:346 ERROR [http-bio-80-exec-288, org.apache.catalina.core.ContainerBase.[wasp-engine].[localhost].[/nisapi].[resteasy-servlet]] org.jboss.resteasy.spi.UnhandledException: java.lang.RuntimeException: java.sql.SQLDataException: ORA-01878: specified field not found in datetime or interval
UIM/UMP 8.31 or 8.4 or 8.51
The problem is due to an Oracle DB ojdbc6 driver issue. These versions of the data_engine use version 220.127.116.11.1 of this driver. There is a known defect in this driver that causes date data inserted into the database to be incorrect if it is inserted any time during the hour right after the clocks move ahead in the spring for daylight savings time. This driver has be updated in later releases of UIM. The fix is to correct the bad data in the database.
The corrupted date data can appear in any RN_QOS_DATA_NNNN or HN_QOS_DATA_NNNN table that contains date data stored during the first hour after clocks moved ahead.
1) Please ensure a DBA is involved in any DB changes and backup the database before ANY modification is completed.
2) These instructions assume that your servers are using US Eastern time. If your servers are not on Eastern time, please let us know so we can alter the query using the correct UTC offset. You can find this offset by looking at the tz_offset column in any one of your RN or HN tables. Please run a query like:
select * from <RN/HN table>;
where <RN/HN table> is a table name like RN_QOS_DATA_0004 or HN_QOS_DATA_0004.
The tx_offset field should have a value of 18000 for US Eastern time (14400 for entries added after the daylight savings time change over). If it does not, please let us know.
3) The following query executed on the RN and HN tables can be used to verify that they have bad data. This will be any date data that was stored during the hour immediately after daylight savings time initiated.
select * from <RN/HN table> where sampletime >= 'DD-MAR-YY 09.00.00.000000000 PM' and sampletime <= 'DD-MAR-YY 10.00.00.000000000 PM' ;
where DD-MAR-YY corresponds to the day before the time changed over. For example, in 2017, the time change over occurred at 2am EST on March 12. The above SQL query would be as follows for 2017:
select * from RN_QOS_DATA_0004 where sampletime >= '11-MAR-17 09.00.00.000000000 PM' and sampletime <= '11-MAR-17 10.00.00.000000000 PM' ;
Time is stored in the UIM database in UTC time zone. 2am EST on March 12 corresponds to 9pm UTC on March 11 which is why you are looking for entries in the time range specified in this SQL query.
One or more records should be returned for most, if not all of the RN and HN tables. If you find records for any one of these tables, this confirms there is bad data.
4) To correct this, the DBA can do one of two things. EITHER
a. Remove each record altogether:
delete from <RN/HN table> where sampletime >= 'DD-MAR-YY 09.00.00.000000000 PM' and sampletime <= 'DD-MAR-YY 10.00.00.000000000 PM' ;
b. Update each record’s time stamp and set it to 1 second AFTER 3 AM UTC. This is accomplished with a query similar to the following for each RN and HN table affected:
update <RN/HN table> set sampletime = to_date('YYYY/03/DD 22:00:01', 'yyyy/mm/dd hh24:mi:ss')
where sampletime >= 'YY-MAR-DD 09.00.00.000000000 PM' and sampletime <= 'YY-MAR-DD 10.00.00.000000000 PM' ;
where <'YYYY/03/DD 22:00:01'> corresponds to the day before the time changed over. An example of this command for the RN_QOS_DATA_0004 table would be as follows (for 2017):
update <RN/HN table> set sampletime = to_date('2017/03/11 22:00:01', 'yyyy/mm/dd hh24:mi:ss')
where sampletime >= '17-MAR-11 09.00.00.000000000 PM' and sampletime <= '17-MAR-11 10.00.00.000000000 PM' ;
5) MAKE SURE TO ‘commit’ the data once it has been updated. Simply execute:
6) Now, exit UMP and log back in, then try the report again.