With regards to SQL deadlock, SQL DBA was able to pin point what is causing it. We need your help on how we can stop this.
Release : 12.2
Component : CA WORKLOAD AUTOMATION DE (DSERIES)
Following recommendations suggested from dSeries team after meeting with SQL Server DBA.
1) Stop dSeries Server
2) Dropping the Foreign key in SQLServer
ALTER TABLE ESP_PUBSUB DROP CONSTRAINT FK_PUB
ESP_PUBSUB having two foreign keys, with the other key FK_SUB all the operation will be done. So No code change is required.
3) Keep the existing Index as it is, which are created by dSeries installer.
4) Once these changes are done on DB, start the dSeries Server and continuously monitor for 2-3 days, whether jobs are getting triggered and completed as expected.
5) Try to revisit the production Applications completion criteria, try to scatter at different intervals, not at the same time.
6) Broadcom dSeries Dev team tested on Microsoft SQL Server 2017(RTM)-14.0.1000.169 (X64).
7) Regular Re-Indexing on below tables for better performance. Schedule a SQLServer Job to re-index the following table based on workload.