No Data in Atlas and Vocab Version missing

[Chris_Knoll]

No suitable driver found for jdbc:oracle:thin:cdm/TsZE5nuVc6@129.106.31.193:1521/truven

That doesn’t look like the JDBC url format, but, if the issue is ‘no suitable driver’ that means that the driver to handle the jdbc ins’t loaded. You should check your logs for messages related to ‘failed to load driver…’

One basic question:
The connection that uses the url in webapi.source table is between the Webserver (the one on which Atlas is installed) and Oracle DB (CDM/VOCAB/RESULTS)
or Between the WebAPI Database (postgresql in our case) and the Oracle DB ((CDM/VOCAB/RESULTS)

WebAPI is the only thing that connects to the database, so I’m not sure what yo umean when you distingish 'between the webserver and oracle db or between webapi database and oracle db).

The WebAPI is a java program that will make a connection to your postgresql database when it needs to read things like cohort definitions. This connection setting is defined in your settings.xml file and populated in the application during the .WAR file build (with the mvn command). WebAPI will connect to your CDM database using the values in the source database when trying to do cohort generation or vocabulary searches. But in both cases the WebAPI Java program makes the connection to either the postgresql or oracle.

In your case, I think the problem is that the oracle driver is not being installed in the WAR file, but I can’t be sure unless you can find the error of ‘Failed to load driver for Oracle’ in the log. If you do find that message, that means we have to configure the WebAPI WAR build to include the oracle driver.

@gregk, you have an environment which hosts the WebAPI database on Postgresql, but has SOURCE records that point to a Oracle CDM instance, right? Can you provide any special instructions to get the oracle driver loaded in the WAR file when people have their WebAPI database in PostgresSQL, but have other sources configured for other DBMS like oracle?

[smanikonda (Sarath Manikonda)]

’ jdbc:oracle:thin:@//server:1521/sid’

@Chris_Knoll

The latest url is: ’ jdbc:oracle:thin:@//server:1521/sid’
and it is inline with the documentation you provided and also I looked it up from other sources also.

Other than this error (which appears each time I access anything in Atlas), I don’t see anything else in the log file:
2019-04-24 10:10:32.658 INFO http-nio-8080-exec-1 com.odysseusinc.logging.LoggingService - - Could not get JDBC Connection; nested exception is java.sql.SQLException: No suitable driver found for jdbc:oracle:thin:@//server:1521/sid

@gregk
appreciate if you can provide any inputs to help me resolve the issue.

[anthonysena]

@smanikonda - as @Chris_Knoll’s noted earlier, when WebAPI starts up, it has some information on which database drivers it is able to load. Here is how it looks on my side:

2019-04-24 12:17:59.372 INFO localhost-startStop-1 org.springframework.jdbc.datasource.DriverManagerDataSource -  - Loaded JDBC driver: org.postgresql.Driver
2019-04-24 12:17:59.376 INFO localhost-startStop-1 org.ohdsi.webapi.DataAccessConfig -  - driver loaded: org.postgresql.Driver
2019-04-24 12:17:59.377 INFO localhost-startStop-1 org.ohdsi.webapi.DataAccessConfig -  - driver loaded: com.microsoft.sqlserver.jdbc.SQLServerDriver
2019-04-24 12:17:59.378 INFO localhost-startStop-1 org.ohdsi.webapi.DataAccessConfig -  - error loading oracle.jdbc.driver.OracleDriver driver. oracle.jdbc.driver.OracleDriver
2019-04-24 12:17:59.378 INFO localhost-startStop-1 org.ohdsi.webapi.DataAccessConfig -  - driver loaded: com.amazon.redshift.jdbc.Driver
2019-04-24 12:17:59.379 INFO localhost-startStop-1 org.ohdsi.webapi.DataAccessConfig -  - error loading com.cloudera.impala.jdbc41.Driver driver. com.cloudera.impala.jdbc41.Driver
2019-04-24 12:17:59.380 INFO localhost-startStop-1 org.ohdsi.webapi.DataAccessConfig -  - error loading net.starschema.clouddb.jdbc.BQDriver driver. net.starschema.clouddb.jdbc.BQDriver
2019-04-24 12:17:59.381 INFO localhost-startStop-1 org.ohdsi.webapi.DataAccessConfig -  - error loading org.netezza.Driver driver. org.netezza.Driver
2019-04-24 12:17:59.381 INFO localhost-startStop-1 org.ohdsi.webapi.DataAccessConfig -  - driver loaded: com.simba.googlebigquery.jdbc42.Driver

(this is usually toward the top of the log file)

As shown above, in my local environment I have drivers for PostgreSQL, SQL Server, RedShift and Google BigQuery properly loaded while Oracle is not loaded since I do not have those drivers installed.

I’m not sure if this already came up in this discussion or elsewhere but there is a note in the WebAPI setup guide that notes to install the Oracle driver via Maven:

OHDSI/WebAPI - Oracle Specific Configuration

The instructions note that this is required if you host your WebAPI DB on Oracle but perhaps its worth trying in your case? I’m not sure if you’ve already tried that or not but wanted to point it out just in case.

[smanikonda (Sarath Manikonda)]

@anthonysena

We are not having a separate webapi.log, all our log data is in the catalina.ot file. I restarted the Tomcat and it looks like there are some issues where the jar files are not found. But they do not seem to be related to Oracle JDBC.

The last error (highlighted) in the screenshot is what I am getting when I access Atlas.
Any inputs that can give us some direction would be great.

Thanks and Regards
Sarath Manikonda