SQL Queries against NUMLOGVAL on the UNIX EH/IMS for Advant stop working
Numeric log data is stored on the UNIX EH in what is known as a ‘flat file’. One of the access methods we provide is a NUMLOGVAL tool. This allows the user to query for the history data using Oracle SQL statements.
An example query may be something like this:
Select time, entryvalue from numlogval where name = <logname> and time > sysdate-1/24
This query would be expected to return the values from the previous hour from the specified logname.
Here is another example:
Select time, entryvalue from numlogval where name = <logname> and time > sysdate-30
This query returns values for the past 30 days.
Queries such as this would exist in custom application code, reports written in Excel, reports written in Oracle report writer, etc.
For some reason, starting on the first week of June 2018, these queries started failing. One customer site noticed that if they were asking for 30 days of data, the 2 nd week of June they were only getting 23 days of data. The following week they were getting only 16 days of data, etc.
In these queries, the IMPLIED time range is sysdate – 30 (ie, 30 days ago) to now, with the ‘now’ being implied. This function has successfully worked this way for the life of the product until June 2018. For some reason, the coding of the NUMLOGVAL tool is no longer working as it used to.
We discovered as a workaround that if the query explicitly bounds the time with a range, then the queries will work as expected. So all one has to do is add another condition to the query ‘and time<= sysdate’ and the queries begin functioning as expected.