Description:
TIBCO BusinessWorks ProcessMonitor (BWPM) job NJAMS.NJAMS_JOB_DATA_MANAGEMENT fails every night with the following error:-
Job NJAMS.NJAMS_JOB_DATA_MANAGEMENT threw a JobExecutionException:

org.quartz.JobExecutionException: java.sql.SQLException: ORA-12801: error signaled in parallel query server P010

ORA-01878: specified field not found in datetime or interval

ORA-06512: at "BWPM_DEV.PROC_DELETE_DATA", line 49

ORA-06512: at "BWPM_DEV.PROC_DATA_MANAGEMENT", line 6

ORA-06512: at line 1

Also, the NJAMS.NJAMS_JOB_DATA_MANAGEMENT job runs continuously and sows green in BWPM Server UI.

Symptoms:
TIBCO BusinessWorks ProcessMonitor (BWPM) job NJAMS.NJAMS_JOB_DATA_MANAGEMENT fails with the following error:

Job NJAMS.NJAMS_JOB_DATA_MANAGEMENT threw a JobExecutionException:
org.quartz.JobExecutionException: java.sql.SQLException: ORA-12801: error signaled in parallel query server P010
ORA-01878: specified field not found in datetime or interval
ORA-06512: at "BWPM_DEV.PROC_DELETE_DATA", line 49
ORA-06512: at "BWPM_DEV.PROC_DATA_MANAGEMENT", line 6
ORA-06512: at line 1

NJAMS_JOB_DATA_MANAGEMENT job runs and continuously shows green in BWPM Server UI.

The procedure PROC_DELETE_DATA runs frequently due to which NJAMS_JOB_DATA_MANAGEMENT job runs and continuously shows green in the BWPM Server UI.

Cause:
When the stored procedure PROC_DELETE_DATA runs, it compares the VALID_UNTIL field (in the procedure) to the DateTime fields namely TRACE_TS,TRACK_TS, jobstart and eventdatetime in tables such as NJAMS_T_MONITOR_MAIN, NJAMS_T_MONITOR_EVENTS, NJAMS_T_MONITOR_TRACKS etc which are a part of the TIBCO BusinessWorks™ ProcessMonitor (BWPM) database.
During the comaprision, if there are existing entries in the these tables, which have a timestamp from a day, where the switch to daylight saving time occured,(e.g. 2015-03-08 in the US and 2015-03-29 in Europe), invalid timestamps will be built e.g. 2015-03-29 02:15:00 CET, which do not exist (due to daylight saving these timestamps never occured) and hence the exception is thrown.
The issue only occurs during change from winter time to summer time due to daylight saving.

This issue occurrs due to daylight savings time. Data for the day when daylight saving occurs must be deleted. The timestamp (TRACE_TS,TRACK_TS,jobstart and eventdatetime) is stored in UTC. During the comparison, the timestamp is cast internally (Oracle) to a "timestamp with timezone" and gets the system timezone or client timezone attached. Then both "timestamp with timezone" is converted to UTC and compared. The following steps can be used to delete data from the TIBCO BusinessWorks ProcessMonitor (BWPM) database tables to ensure that the NJAMS_JOB_DATA_MANAGEMENT job runs successfully.


Resolution 1).  The root cause is a comparison of a "timestamp" with a "timestamp with timezone" in PROC_DELETE_DATA in the lines. In order to stop the exception from occurring (ORA-01878: specified field not found in datetime or interval), adjust the the following queries in the PROC_DELETE_DATA procedure in the configured database.

...
MT.TRACE_TS < LEAST(NVL(DO.VALID_UNTIL,to_timestamp('2222-01-01','YYYY-MM-dd')), L_TS_UTC - DO.RETENTION_DAYS_TRACES)
...
MT.TRACK_TS < LEAST(NVL(DO.VALID_UNTIL,to_timestamp('2222-01-01','YYYY-MM-dd')), L_TS_UTC - DO.RETENTION_DAYS_TRACKS)
...
MM.jobstart < LEAST(NVL(DO.VALID_UNTIL,to_timestamp('2222-01-01','YYYY-MM-dd')), L_TS_UTC - DO.RETENTION_DAYS)
...
eventdatetime < LEAST(NVL(DO.VALID_UNTIL,to_timestamp('2222-01-01','YYYY-MM-dd')), L_TS_UTC - DO.RETENTION_DAYS_EVENTS)
...

Adjust the lines above in the PROC_DELETE_DATA to:

...
MT.TRACE_TS < LEAST(NVL(CAST(SYS_EXTRACT_UTC(DO.VALID_UNTIL) AS TIMESTAMP),to_timestamp('2222-01-01','YYYY-MM-dd')), L_TS_UTC - DO.RETENTION_DAYS_TRACES)
...
MT.TRACK_TS < LEAST(NVL(CAST(SYS_EXTRACT_UTC(DO.VALID_UNTIL) AS TIMESTAMP),to_timestamp('2222-01-01','YYYY-MM-dd')), L_TS_UTC - DO.RETENTION_DAYS_TRACKS)
...
MM.jobstart < LEAST(NVL(CAST(SYS_EXTRACT_UTC(DO.VALID_UNTIL) AS TIMESTAMP),to_timestamp('2222-01-01','YYYY-MM-dd')), L_TS_UTC - DO.RETENTION_DAYS)
...
eventdatetime < LEAST(NVL(CAST(SYS_EXTRACT_UTC(DO.VALID_UNTIL) AS TIMESTAMP),to_timestamp('2222-01-01','YYYY-MM-dd')), L_TS_UTC - DO.RETENTION_DAYS_EVENTS)
...

Run the PROC_DATA_MANAGEMENT procedure in the configured database. This should take a while. 


Resolution 2). Delete all entries of the day, where the switch to daylight saving time occurred. Follow the steps below.

Stop data providers in the BWPM Server and delete all entries of the following tables in the configured database, where timestamp is from the day where the switch to daylight saving time occurred (e.g. Europe: 29-Mar-2015, US: 08-Mar-2015). The following tables should be checked for deletion.

- NJAMS_T_MONITOR_EVENTS ( check timestamp field: eventdatetime)

- NJAMS_T_MONITOR_MAIN (check timestamp field: jobstart)

- NJAMS_T_MONITOR_TRACKS (check timestamp field: TRACK_TS)

- NJAMS_T_MONITOR_BWTRACE (check timestamp field: TRACE_TS)

The delete statements for the monitor tables depend on the timezone region in the customers run environment. Alternatively, truncate all records from the tables mentioned above. Note that your records relative to this table will be lost. 

Internal defect #1950 has been created for this issue and will be considered in future versions of BWPM server.

[working] TIBCO BusinessWorks ProcessMonitor (BWPM) job NJAMS.NJAMS_JOB_DATA_MANAGEMENT fails with the error ORA-01878: specified field not found in datetime or interval.