After upgrade custom holiday exclusion calendars no longer appear in the Holiday dropdown list

Issue:

After upgrading TIBCO JasperReports® Server from version 7.5 to version 7.9, custom holiday exclusion calendars that were created in the previous version no longer appears in the Holidays drop-down list.  All that is available in the Holidays dropdown list are the defaults listed, 'None' and 'New Years Days' as shown:

Due to this issue, upgraded scheduled jobs were no longer associated to the custom holiday exclusion calendars.

Solution:

The TIBCO JasperReports® Server 7.9 upgrade has resulted in the qrtz_calendars table updated with scheduler server instance name being "JasperServerScheduler" running on the actual server instance/node. In the previous version the server instance name was "quartzScheduler" and custom holiday exclusion calendars were created with this former name. The only calendar entry that appears in the Holidays dropdown list is the "New Years Days" because it is the only entry having a scheduler server name of "JasperServerScheduler". 

Use a database query tool to connect to the jasperserver repository database and perform the following checks:

1> Query the qrtz_scheduler_state table for which scheduler server instance is running on the right server host. 

select * from qrtz_scheduler_state;

and determine the sched_name running on the instance_name matching the server host. 

The expectation is that the sched_name is "JasperServerScheduler" and the instance_name contains the actual server host name. There may also be another entry where the sched_name is "quartzScheduler" with the instance_name referring to a server host name that may be the same or different as the actual server host name as it is an entry that came from the pre-upgraded version.

2> Query the qrtz_calendars table in the jasperserver repository database :

select * from qrtz_calendars;

After the upgrade, there is only one entry, "New Years Days", for the scheduler name of "JasperServerScheduler". The custom entries in this table exists but were migrated across with a scheduler name of "quartzScheduler" and despite the entries appearing in the table itself, they do not appear in the UI.

Workaround:

This issue was raised as a defect (refer to Related Articles section for incident details). Until there is a fix to the upgrade scripts, there are two workaround approaches to resolve this issue.     

1> Use REST_v2 API to create/update the custom holiday calendars.

This would be the same approach as how the custom calendars were created in the previous version since it is the only approach available to create/update custom calendars. 

For more information, refer to the calendars Service in the TIBCO JasperReports® Server REST API Reference guide :

https://community.jaspersoft.com/documentation/tibco-jasperreports-server-rest-api-reference/v790/calendars-service

This approach will create additional entries in the qrtz_calendars table with reference to "JasperServerScheduler" as the scheduler name. 

Note: This is the supported/recommended method since it does not entail manual backend update modification of repository metadata.  

2> Backend table update method

This is a method which involves a sql update and such method should be tested in a Test/Dev environment first. 

i> BACKUP table qrtz_calendars  (IMPORTANT step in-case of user errors and to keep a record of pre-modification state)

ii> Update custom holiday exclusion calendars to reference the correct scheduler server name :  

update qrtz_calendars
set sched_name='JasperServerScheduler'
where calendar_name='<custom calendar name>';

iii> As a verification check, re-visit the Schedules page via the UI or even create a new schedule. You should now see the custom holiday exclusion calendar entry in the Holiday drop-down list.

iv> Repeat step ii> for any other custom holiday calendars

Note: You would have identified all the custom calendar names (which has sched_name="quartzScheduler") from querying the qrtz_calendars table step above. 

As a summary, the solution is to ensure the custom holiday calendars have the correct scheduler server name data in the qrtz_calendars table so that they appear in the Holiday dropdown list in the UI. Doing this will subsequently allow any existing scheduled jobs to be able to "associate" to the now existing custom holiday calendar. These existing scheduled jobs would have already have the association defined in the table column JIReportjobtrigger.calendar_name. 

Related Articles:

JS-62853: After upgrade custom holiday calendars no longer appear in the Holiday drop down list