Removing offline API Gateway Cluster from Enterprise Service Manager (ESM).

Document ID : KB000010883
Last Modified Date : 14/02/2018
Show Technical Document Details
Introduction:

If an API Gateway Cluster is offline, it is not possible to remove it from ESM through the UI. This situation could happen for reasons like a cluster being decommissioned or change in the clusters network properties. This demonstrates how to remove an offline clusters from the ESM database.

Environment:
- This have been tested on CA API Gateway 9.1 Virtual Appliance with Enterprise Service Manager 1.16.- Steps presented here are expected to work on CA API Gateway 8.x/9.x with corresponding ESM.- IT IS IMPERATIVE THAT ALL STEPS ARE FOLLOWED IN THE INSTRUCTIONS ESPECIALLY THE BACK-UP DB PRIOR TO RUNNING ANY DELETE QUERIES.
Instructions:
  • Connect to the API Gateway node hosting ESM database using SSH, login as ssgconfig and go to the privileged shell. 
  • Stop ESM service: 
    • # service ssem stop 
  • Install derby command line tool ij: 
  • Verify the derby IJ tool is working properly:
    • /opt/SecureSpan/JDK/bin/java org.apache.derby.tools.sysinfo 
  • Backup the original ESM DB:
    • # cd /opt/SecureSpan/EnterpriseManager/var/ 
    • # tar -cvf ~/emsdbbackup.tar db 
  • Execute ij: 
    • # /opt/SecureSpan/JDK/bin/java org.apache.derby.tools.ij 
  • Connect to the ESM database:
    • ij> connect 'jdbc:derby:/opt/SecureSpan/EnterpriseManager/var/db/emsdb'; 
  • Get a list of the clusters present in the ESM database. Here is a sample output (yours will vary based on host ID's and names): 
    • ij> select * from SSG_CLUSTER;
    • OBJECTID NAME 
    • ----------------------------------- 
    • 360449Internal Gateway Cluster 
    • 360450Internal Gateway Cluster 
    • 360451DMZ Proxy Cluster 
    • 9306122EDC Internal Gateway Cluster 
    • 9306123EDC DMZ Proxy Cluster 
    • 557056Internal Gateway 
    • 557057DMZ Proxy Cluster 
  • Let's say you want to remove the first two entries: 
  • * Below will remove objects for "360449Internal Gateway Cluster"
    • ij> delete FROM MIGRATION WHERE SOURCE_CLUSTER_OID = 360449 OR TARGET_CLUSTER_OID = 360449; 
    • ij> delete FROM MIGRATION_MAPPING WHERE SOURCE_CLUSTER_OID = 360449 OR TARGET_CLUSTER_OID = 360449; 
    • ij> delete FROM REPORT WHERE SSG_CLUSTER_OID = 360449; 
    • ij> delete FROM SSG_NODE WHERE SSG_CLUSTER_OID = 360449; 
    • ij> delete FROM SSG_CLUSTER WHERE OBJECTID = 360449; 
    • * Below will remove objects for "360450Internal Gateway Cluster"
    • ij> delete FROM MIGRATION WHERE SOURCE_CLUSTER_OID = 360450 OR TARGET_CLUSTER_OID = 360450; 
    • ij> delete FROM MIGRATION_MAPPING WHERE SOURCE_CLUSTER_OID = 360450 OR TARGET_CLUSTER_OID = 360450; 
    • ij> delete FROM REPORT WHERE SSG_CLUSTER_OID = 360450; 
    • ij> delete FROM SSG_NODE WHERE SSG_CLUSTER_OID = 360450; 
    • ij> delete FROM SSG_CLUSTER WHERE OBJECTID = 360450; 
  • Quit ij:
    • ij> exit;
  • Restart ESM service:
    • # service ssem start
  • Log back in to ESM and observe that desired clusters are removed from monitoring.
Additional Information:

For further assistance please contact CA Technical support.