Editing domains in Jasper spinning forever or throwing error messages

Products

CA Service Management - Service Desk Manager CA Service Desk Manager

Issue/Introduction

As soon as the end-user clicks on Edit (Domains -> View List), it keeps spinning forever or throws an error message.

Nothing happens and it keeps spinning.

CA Service Desk (with DEBUG level in sqlclass.c) shows the complex query below:

Clause (SELECT DISTINCT casd_in.id AS "id", casd_in_caused_by_chg_assignee.last_name + CASE WHEN ( casd_in_caused_by_chg_assignee.first_name IS NULL OR casd_in_caused_by_chg_assignee.first_name = ? ) AND ( casd_in_caused_by_chg_assignee.middle_name IS NULL OR casd_in_caused_by_chg_assignee.middle_name = ? ) THEN N'' ELSE N', ' END + isnull(casd_in_caused_by_chg_assignee.first_name,N'') + CASE WHEN ( casd_in_caused_by_chg_assignee.middle_name IS NULL OR casd_in_caused_by_chg_assignee.middle_name = ? ) THEN N'' ELSE N' ' END + isnull(casd_in_caused_by_chg_assignee.middle_name,N'') AS "assignee", casd_in_caused_by_chg_affected_contact.last_name + CASE WHEN ( casd_in_caused_by_chg_affected_contact.first_name IS NULL OR casd_in_caused_by_chg_affected_contact.first_name = ? ) AND ( casd_in_caused_by_chg_affected_contact.middle_name IS NULL OR casd_in_caused_by_chg_affected_contact.middle_name = ? ) THEN N'' ELSE N', ' END + isnull(casd_in_caused_by_chg_affected_contact.first_name,N'') + CASE WHEN ( casd_in_caused_by_chg_affected_contact.middle_name IS NULL OR casd_in_caused_by_chg_affected_contact.middle_name = ? ) THEN N'' ELSE N' ' END + isnull(casd_in_caused_by_chg_affected_contact.middle_name,N'') AS "end_user", casd_in_caused_by_chg_log_agent.last_name + CASE WHEN ( casd_in_caused_by_chg_log_agent.first_name IS NULL OR casd_in_caused_by_chg_log_agent.first_name = ? ) AND ( casd_in_caused_by_chg_log_agent.middle_name IS NULL OR casd_in_caused_by_chg_log_agent.middle_name = ? ) THEN N'' ELSE N', ' END + isnull(casd_in_caused_by_chg_log_agent.first_name,N'') + CASE WHEN ( casd_in_caused_by_chg_log_agent.middle_name IS NULL OR casd_in_caused_by_chg_log_agent.middle_name = ? ) THEN N'' ELSE N' ' END + isnull(casd_in_caused_by_chg_log_agent.middle_name,N'') AS "reporter", casd_in_caused_by_chg_requestor.last_name + CASE WHEN ( casd_in_caused_by_chg_requestor.first_name IS NULL OR casd_in_caused_by_chg_requestor.first_name = ? ) AND ( casd_in_caused_by_chg_requestor.middle_name IS NULL OR casd_in_caused_by_chg_requestor.middle_name = ? ) THEN N'' ELSE N', ' END + isnull(casd_in_caused_by_chg_requestor.first_name,N'') + CASE WHEN ( casd_in_caused_by_chg_requestor.middle_name IS NULL OR casd_in_caused_by_chg_requestor.middle_name = ? ) THEN N'' ELSE N' ' END + isnull(casd_in_caused_by_chg_requestor.middle_name,N'') AS "requestor", casd_in_caused_by_chg_cab.last_name + CASE WHEN ( casd_in_caused_by_chg_cab.first_name IS NULL OR casd_in_caused_by_chg_cab.first_name = ? ) AND ( casd_in_caused_by_chg_cab.middle_name IS NULL OR casd_in_caused_by_chg_cab.middle_name = ? ) THEN N'' ELSE N', ' END + isnull(casd_in_caused_by_chg_cab.first_name,N'') + CASE WHEN ( casd_in_caused_by_chg_cab.middle_name IS NULL OR casd_in_caused_by_chg_cab.middle_name = ? ) THEN N'' ELSE N' ' END + isnull(casd_in_caused_by_chg_cab.middle_name,N'') AS "cab" FROM call_req casd_in LEFT JOIN chg casd_in_caused_by_chg ON casd_in.caused_by_chg = casd_in_caused_by_chg.id LEFT JOIN ca_contact casd_in_caused_by_chg_assignee ON casd_in_caused_by_chg.assignee = casd_in_caused_by_chg_assignee.contact_uuid LEFT JOIN ca_contact casd_in_caused_by_chg_affected_contact ON casd_in_caused_by_chg.affected_contact = casd_in_caused_by_chg_affected_contact.contact_uuid LEFT JOIN ca_contact casd_in_caused_by_chg_log_agent ON casd_in_caused_by_chg.log_agent = casd_in_caused_by_chg_log_agent.contact_uuid LEFT JOIN ca_contact casd_in_caused_by_chg_requestor ON casd_in_caused_by_chg.requestor = casd_in_caused_by_chg_requestor.contact_uuid LEFT JOIN ca_contact casd_in_caused_by_chg_cab ON casd_in_caused_by_chg.cab = casd_in_caused_by_chg_cab.contact_uuid WHERE ( casd_in.type = ? )) Input (<string>|<string>|<string>|<string>|<string>|<string>|<string>|<string>|<string>|<string>|<string>|<string>|<string>|<string>|<string>|<string>I)

In Jasper:

Environment

CA Service Management 17.1 and higher.

Cause

Jasper validates the domain checking all related fields when editing domains to avoid inconsistencies.

Depending on the size of the database, it can result in slowness to perform this validation.

The combo names field is a kind of field which results in slowness during the validation.

Resolution

There are two possible approaches.

(1) Go to an OOTB Jasper environment and remove combo names fields following the steps below:

(it is an example with the "Change Management" domain)

Login to Jasperserver;
Click on View List under Adhoc Views;
Open the Adhoc view 'Change - Change Orders Details' and remove the fields which are ending with combo names and save the Adhoc view;
Now go back to Home of Jasperserver and Click on View List under Domains;
Right Click on the domain 'Change Management - Operational Reporting' and click Edit;
Go to the tab 'Joins'. Right-click on 'Caused_Chg_Combo' and click on Remove Derived Table;
Follow Step 6 for 'Child_Combo_Names' and 'Combo_Names' as well;
Now go to the Data Presentation tab. From 'JoinTree_1', delete fields like 'Affected End User Combo Name', 'Assignee Combo Name' etc., fields that contain combo names;
Save the domain once we finish deleting all the combo names;
Export the domain and import it in the environment where the issue occurs.

The save should be successful.

By following the above steps it is possible to remove the combo names from the Change Management domain and the long queries. It is necessary to make these changes in other domains as well to remove the long-running queries. Perform Step 3 if there are combo names fields in Adhoc views.

(2) Disable domain validation in Domain Validation Check Jasper:

To disable the validation edit the following configuration file:

IMPORTANT: This alternative has to be set carefully because it can cause side effects because validation will not happen if there is any mistake in the domain.

IMPORTANT: It is not recommended to change the OOTB domain (the best practice is to copy the OOTB domain with a different name).

Additional Information

TIBCO JasperReports® Server Administrator Guide

See Section 8.8.1