CA UIM / CABI - cabi_external error when creating an Ad Hoc View in the Jasper Report Server Console

Issue/Introduction:

When attempting to create an "Ad Hoc View" in the Jasper Report Server Console we face the below error:

The server has encountered an error. Please excuse the inconvenience.

Error Message
Error preparing statement for executing the report query: SELECT DISTINCT CASE WHEN UPPER('[email protected]') LIKE '%ORACLE%' THEN 'CM_CONFIGURATION_ITEM_METRIC_D' ELSE 'CM_CONFIGURATION_ITEM_METRIC_DEFINITION' END AS ccimd FROM s_qos_data

Steps taken to reproduce the error:

1. Access the CABI JRS Web Console using superuser account.
2. Create New Ad Hoc View
3. Select the UIM Metrics Topic
4. The error occurs here before the input screen pops up.

The problem can be resolved by restarting the "CA Business Intelligence Tomcat" service OR logging out and accessing the JRS again via the superuser account.

Cause:

Development worked with Tibco Support to determine the root cause. We found that this problem occurs when the connection to the database is closed.

Environment:

- UIM 8.51 SP1
- Jasper Reports Server 6.3.0
- cabi_external 3.32
- cabi packages deployed to cabi robot: uim_core_dashboards_pack v2.40, uim_cabi_health_report_pack v1.20, uim_unified_reporter_pack v1.02
- cabi packages deployed to ump robot: ump_cabi v3.32
- UIM database / JRS database: SQL Server 2014

Resolution:

1. On the server where the Jasper Report Server is running, stop the tomcat ca business intelligence service.

2. Create a backup copy of the context.xml file (CA\SC\CA Business Intelligence\apache-tomcat\webapps\jasperserver-pro\META-INF)

3. Edit the context.xml file so the UIM datasouce ends with:

"validationQuery="SELECT 1"/>"

Example of how the section will look after making this change:

Resource auth="Container" defaultTransactionIsolation="READ_UNCOMMITTED" driverClassName="tibcosoftware.jdbc.sqlserver.SQLServerDriver" factory="com.jaspersoft.jasperserver.tomcat.jndi.JSCommonsBasicDataSourceFactory" maxActive="100" maxIdle="30" maxWait="10000" name="jdbc/uim" password="ENC-ENCRYPTED_PASSWORD-" type="javax.sql.DataSource" url="jdbc:tibcosoftware:sqlserver://<DATABASE_IP>:1433;databaseName=CA_UIM" username="cauim" validationQuery="SELECT"/>
</Context>

4. Restart the the tomcat ca business intelligence service.