iXp GUI not working after MS SQL Server DB migration

Document ID : KB000072552
Last Modified Date : 05/03/2018
Show Technical Document Details
Issue:
The CA iXp GUI does not show any jobs under instance after we migrated our CA WAAE database (AEDB) to another MS SQL Server DB Named Instance.
We did update the JDBC connection string in the iXp Admin Tool -> Instance tab -> Database option.
Environment:
CA Workload Automation iXp 11.3.5 on supported platforms.
CA Workload Automation AE 11.3+ on supported platforms.
Microsoft SQL Server Database.
Cause:
The jTDS JDBC driver connection string for MS SQL Server Named Instance is incorrect. The following connection string is defined in the CA iXp Admin Tool Instance configuration:
 
jdbc:jtds:sqlserver://MSSQL06\Corp:51555/AEDB

where:
MSSQL06 is the MS SQL Server hostname.
Corp is the MS SQL Server Named Instance.
51555 is the port MS SQL Server Named instance is listening on.
AEDB is the CA Workload Automation AE database.

The following error was logged in the ixpDaemon.log:

03-04 17:56:37:   db-A11:unable to determine IP address:MSSQL06\Corp:java.net.UnknownHostException:MSSQL06\Corp
03-04 17:56:37:   db-A11:connecting:jdbc:jtds:sqlserver://MSSQL06\Corp:51555/AEDB
03-04 17:56:37:   db-A11:java.sql.SQLException: Unknown server host name 'MSSQL06\Corp'.
...
...
03-04 17:57:08:   db-A11:com.pgti.ixp.core.exception.IxpException:Unknown server host name 'MSSQL06\Corp'.
Resolution:
  • Update the JDBC Connection String in the iXp Admin Tool -> Instance tab -> Database option.
from:
jdbc:jtds:sqlserver://MSSQL06\Corp:51555/AEDB
to:
jdbc:jtds:sqlserver://MSSQL06:51555/AEDB;instance=Corp
  • Restart the Apache Tomcat service hosting the CA Workload Automation iXp Web application.
Additional Information:
Excerpt from http://jtds.sourceforge.net/faq.html#instanceName

Where does one place an instance name in the connect string? Connecting with "jdbc:jtds:sqlserver://host\instance:port/database" gives an SQLException with the message "Logon failed".

You will have to use the instance property (either append it to the URL or place it into the Properties you supply to getConnection) instead of doing it the Microsoft way. Sorry, but jTDS (and its ancestor FreeTDS) existed a long time before named instances so the URL could not be changed (and using it this way confuses the URL parser).

CA iXp DocOps:
https://docops.ca.com/ca-workload-automation-ixp/11-3-5/en/administrating/configure-ca-workload-automation-ae-instance#ConfigureCAWorkloadAutomationAEInstance-DatabaseOptions