Escalation Emails are not getting triggered From Identity Governance
Article ID: 197721
Updated On:
Products
Issue/Introduction
Hi Team,
When we are trying to send reminder escalation emails from Identity Governance Using the "Escalation Email" option in the respective workflow, Emails are not getting triggered to the respective managers from the tool. This is happening in our PROD environment.
I have worked on this issue earlier. This issue was resolved by restarting the IG But restarting the services in our PROD environment requires a lot of approvals and we want the permanent solution for this instead of restarting the services.
I am attaching the server logs of our two nodes. And we tried to send emails today between 6.00 am - 7.00 am EST.
Environment
Component : Identity Governanace
Resolution
looking at your stand alone config
com.microsoft.sqlserver.jdbc.SQLServerDriver
com.microsoft.sqlserver.jdbc.SQLServerXADataSource
When using CA Identity Governance with Microsoft SQL, and making many updates at once, deadlock exceptions may be generated. To improve performance while making many updates, run the following query on the SQL database while the CA RCM server is down:
The name eurkify_sbd refers to the database name the product was using at the time the tech doc was written.
ALTER DATABASE eurekify_sdb
SET READ_COMMITTED_SNAPSHOT ON;
GO
ALTER DATABASE eurekify_sdb
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
You will have to update the above command with the database names of these Connections:
jdbc:sqlserver://192.0.2.255:1445;databaseName=ig_sdb
jdbc:sqlserver://192.0.2.255:1445;databaseName=ig_ticketdb
jdbc:sqlserver://192.0.2.255:1445;databaseName=ig_datawarehouse
To help answer these questions:
This message:
send reminder escalation emails from Identity Governance Using "Escalation Email" option in the respective workflow,
Tells me that you are submitting large amounts of data to both the IG databases and the workflow databases.
This message in the logs:
00:31:23,960 WARN [org.jboss.jca.adapters.jdbc.local.LocalManagedConnectionFactory] (ConnectionValidator) Destroying connection that is not valid, due to the following exception: ConnectionID:106 ClientConnectionId: ################: com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed.
Tells me that MSSQL is shutting down connections.
The restart of the app server rebuilds those closed connections.
One way to stop connection shut down issues:
SET ALLOW_SNAPSHOT_ISOLATION ON
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server
By default with no SET ALLOW_SNAPSHOT_ISOLATION ON you can end up stepping on other connections.
When using Microsoft SQL Server, enabling Read Committed Snapshot Isolation (RCSI) is one way to prevent reads (SELECT statements) from escalating into full table locks.
and before a deadlock, you can timeout, but with this, you do not time out.
I am asking you for this change for the following reasons:
1) you are using MSSQL with our product.
2) Many customers have been given this solution as it is in our techdoc knowledge base.
3) you have no errors but timeouts closed connections, and QUARTz table errors.
The times are in the WPDS connections and the QUARTS tables should be in the SDB
You will need this on all of the databases used in your environment as you are working with more than one database and the problem is with both QUARTz and WPDS.
Feedback
