How to learn the real reasons behind the Database status showing Unavailable in the CSA

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

When setting up the connections for the databases in the CSA (both PPM and DWH schemas) you may be informed after clicking Save that its status is Unavailable.

What does that mean exactly, and how is the status determined to be Available or Unavailable.

CSA Database - status Unavailable

At a high level, in order for the status to show Available after clicking on Save, then the CSA was able to make a connection to the database with the username and password provided.

If using a custom JDBC URL to make the connection, this information is also used.  If you are not using a custom JDBC URL, then the URL will be formed from the following fields depending on your chosen DBMS vendor:

Oracle will use your Hostname, Port, and Service ID fields.

Microsoft SQL Server will use your Hostname, Port, and Database Name fields.  If you are using a named-instance of SQL Server, then the Instance Name field can be used instead of the port.

If a connection does not succeed with this information, and presuming that an independent connection can be made to the database using the same details through another tool, then the following instructions can help narrow down the cause as to why.

Please note that the other fields on the Database pages are not used to determine the Status field, so details such as the tablespaces or whether you have any data or tables in the schema itself does not matter at this point in time.

Instructions:

1. Go to the CSA > Properties > Application tab

2. At the end of the JVM Parameters for the CSA service (not the App service), add a space (' ') and then these values.  Please note that in this article the parameters may wrap around onto the next line.  When entering into the JVM Parameters field, they should all be on the same line (there is only 1 in the application):
-Xdebug -Xnoagent -Djava.compiler=NONE -Xrunjdwp:transport=dt_socket,server=y,suspend=n,address=localhost:5005

3. Click Save.

4. Stop, deploy, and start the CSA service.

5. Navigate either to the DWH or Database properties page in the CSA where the DB was showing 'unavailable'.

6. At the putty terminal (unix) or command prompt (Windows) in the production server where the CSA was restarted, type:
jdb -connect com.sun.jdi.SocketAttach:hostname=localhost,port=5005

7. The following should probably appear with a prompt awaiting more information:
Initializing jdb ...
>

8. Type in the following command, making sure that the case sensitivity is precise:
stop at com.niku.nsa.util.NsaUtil:43

9. Go back to the CSA and click Save.  This will likely not complete, you have to go back to the jdb program in putty now.

10. If at this point you see a message saying Breakpoint hit on com.niku.nsa.util.NsaUtil.getConnectionStatus(), line=43, perform the next two steps otherwise skip them.

11. Type in the following commands, one at a time:
print e
print info
print dwhDatabaseId_
print db_.getUrl()

12. You should now see an Exception message displayed that either refers to a database or network issue to be addressed.  It will also include the JDBC url being used, the credentials, and also whether the database ID is correctly Datawarehouse or null or niku or something else.

13. Type in the commands:
clear com.niku.nsa.util.NsaUtil:43
resume
quit

14. If no longer needed, you can now remove the JVM parameters that were added in the CSA and then stop/deploy/start the CSA services again.

Additional Information:

This information contains steps that work on CA PPM versions 14.2 to at least 15.2.  The steps will need some modification to work on other versions.