Here are the steps for adding new database connections to Analyze (for database types not provided by Infogix).
- ascertain the database vendor, type and version (Oracle, Teradata, Cloudera-Impala, etc.)
- ensure that you have username and password credentials that will allow you to access the database
- obtain the correct driver (JAR file) for the database type and version - usually from the vendor website
- place the driver into site-7731/lib/java/db/<dbtype> It's best to create a sub folder for each database type so that different vendors don't step on each other.
- Create a JDBC node
- enter your query into the SQL box
- enter you username/password into the node
- In the DbUrl field, enter the necessary URL to connect to the database. There are examples in the tooltip for this field
- In the DbDriver field, enter the name of the driver. There are examples in the tooltip for this field
- in DbDriverClasspath field, enter the full name of the folder where you placed the JAR file <installation dir>/site-7731/lib/java/db/<dbtype> Sometimes it is helpful to actually enter the name of the JAR file itself, but often it works just to name the folder
- Try to Connect.
- There (probably) will be error messages produced by the node. A few of them are noted below. It is crucial that you understand that EACH TIME you click on run, you might be presented with a different error message. There are at least 6 steps in the process of establishing a connection and running the query, and if you're only looking at the red X for failure, you will miss the fact that the error message changed. You've got to get past ALL of the different steps to have a successful query.
- "Unable to load database driver" - this could mean that you've got the DbUrl formatted incorrectly - refer to the vendor documentation - the DbUrl section that starts with jdbc:<dbtype>: does not refer to a driver that can be recognized. This could also mean that the DbDriver field is incorrect, or that the Classpath doesn't point to the driver you added
- "No suitable driver found" - the DbDriver field does not properly match the name inside the JAR file. Refer back to the vendor documentation, or inspect the JAR to ensure you've got the right name
- "Unable to load database driver:"
- "Unable to establish connection" - The database server name or port is inaccurate
- "Connection Refused" - more often than not, this is a firewall problem
- "Unable to establish SSL....." - you are trying to connect over a secure connection, and will likely have to supply additional options on the DbUrl - refer to the vendor documentation.
- "The UserId, Password or Account is invalid." - the credentials you supplied were not accepted by the database
- "Unable to authenticate" - your username and password were not recognized by the server
- "The property SqlQuery requires a value to be set" - you must enter some form of valid query. It is strongly advised that when first building a query, use something simple while you are testing the connection. Don't overcomplicate it until you get a connection working. A good first query is "SELECT 1 AS A FROM DUAL" - on most sql systems, this will return a single record without worrying about schema and table names.
NOTE - These messages will vary based upon the database vendor. The samples we supply here are only a guide as to what might be wrong.