Adding new JDBC drivers

BMC Atrium Discovery ships with the JTDS driver which can connect to MS SQL Server and Sybase. To connect to other databases you must download and install JDBC drivers for the databases that you want to use.

If you wish to connect to a database from the list below, then all you need to do is download the driver from the appropriate web site (the URL is available from Uploading new JDBC drivers) and upload it using the Uploading new JDBC drivers page. However, before you can use a new driver that is not in the list below, you must provide a properties file for it. The properties file determines how the JDBC URL is constructed. The following properties files are shipped with BMC Atrium Discovery and are located in the $TIDEWAY/data/installed/jdbcdrivers directory:

  • Informix
  • Ingres
  • Microsoft SQL Server
  • MySQL
  • Oracle
  • PostgreSQL
  • Sybase
  • JTDS

Additional driver support

While it is possible to add new JDBC drivers not specified on the preceding list, these will not have been tested by BMC, and as such no guarantees can be made that they will work with BMC Atrium Discovery as expected. For further information, please contact BMC Support.

The $TIDEWAY/data/custom/jdbcdrivers and $TIDEWAY/data/installed/jdbcdrivers directories are both checked for properties files.
When you save a driver to the $TIDEWAY/data/custom/jdbcdrivers directory, you can create a new properties file in the same directory, or you can edit one of the supplied properties files in the $TIDEWAY/data/installed/jdbcdrivers directory.

Writing a properties file

  1. Create a text file called name.properties where name must be the same as the name of the driver that the properties file relates to. For example, mysql-connector-java-5.0.7.properties is the properties file for mysql-connector-java-5.0.7.jar.
  2. The following table describes the entries that you must put in the properties file and also the additional optional entries:

Mandatory Entries

Entry Name

Description

driver.name

The name of the driver. This is the field that is displayed throughout the BMC Atrium Discovery UI to identify this driver.

driver.class

The main class that will be loaded from the relevant jar file in custom/jdbcdrivers. The user will need to get this from the documentation provided by the database/JDBC vendor. For example, driver.class=com.mysql.jdbc.Driver)

driver.url

The format of the connection URL that this driver uses. There is no standard URL for the JDBC URL so every driver has its own format. BMC Atrium Discovery passes the assembled URL to each registered driver and the driver responds whether it recognises the URL or not. The URL is made up of several parts that are dealt with in the next section.

Optional Entries

Entry Name

Description

driver.
default=true

Specifies that this driver is the default driver and will be displayed first in lists when creating objects that use JDBC drivers, for example, SQLIntegrationConnections. If no default is specified across all the drivers then the order in which they are displayed is random. If more than one driver is specified as the default then the last driver to be loaded will be marked as the default.

driver.
exceptionProcessor

Specifies a Java class that can format SQL exceptions (java.sql.
SQLException
) that bubble up from the JDBC driver. This class must implement the ExceptionProcessorIF interface. When an SQL exception occurs, the specified class will be called and then the result of that call will be published to the relevant source, which may be a log file or to the UI. For example, driver.exceptionProcessor=
com.tideway.integrations.service.servants.MySqlExceptionProcessor

translation.
variablename

Translates a variable that is specified in the JDBC URL to a user visible label. See the table below for a list of variable names. (e.g. translation.extra_parameters=Additional Parameters)

validationregex.
variablename

Used only with Integration Points and Software Credential Groups. Validates a given variable specified in the JDBC URL. When the user tries to save a connection, each of the properties is checked to see if it has an associated validation regex. If so, and if the Match on this checkbox is NOT selected, or the connection is static, then the value entered by the user is matched against the value specified here. If they do not match, the user is prompted to re-enter the value. Endpoint is always validated, for static credentials, to ensure that it is a valid IPv4 address.

The following table shows the parameters used by each driver:

Field

Informix

MySQL

Postgres

Oracle (service)

Oracle (SID)

Ingres

Sybase

MS SQL Server

JTDS

servertype

Req

endpoint

Req

Req

Req

Req

Req

Req

Req

Req

Req

port

Opt

Opt

Opt

Opt

Opt

Opt

Opt

Opt

Opt

database

Req

Opt

Req

Req

Opt

Opt

INFORMIXSERVER

Req

extra_parameters

Opt

Opt

Opt

Opt

Opt

Opt

instance_name

Opt

service

Req

sid

Req

When you have added a new driver and written a properties file for it, you must restart the Tideway services. To do this, enter the following command:

$ sudo /sbin/service tideway restart

Multiple drivers using the same JAR (Oracle)

There are two methods of connecting to Oracle, OracleService and OracleSID. These methods use the same driver JAR file, but need two properties files to build the different connection URLs used. Rather than copying and renaming the JAR file, a better method is to use symbolic links.

The driver file is called ojdbc14.jar for Oracle to 10g. For Oracle 11g the driver file is called ojdbc15.jar which requires JRE 1.5 or later.

cd ~/data/installed/jdbcdrivers
ln -s ojdbc14.jar oracleService.jar
ln -s ojdbc14.jar oracleSID.jar

This creates a symbolic link called oracleService.jar for the oracleService.properties properties file and one called oracleSID.jar for the oracleSID.properties properties file.

Was this page helpful? Yes No Submitting... Thank you

Comments

  1. Blaine Simpson

    This comment is for people modifying the driver .properties files described above.

    You can simplify values in your properties values by not backslash-escaping colons. Java's Properties.save() writes the backslashes, but whereas this escaping is required inside of names, it is completely useless inside of values.

    It's unfortunate that ADDM requires the endpoint value to be a dotted-quad address, because there are important use cases where the IP addresses for DB services should not be hard-coded into apps. ADDM accepts dependency upon host name resolution elsewhere but forces you to use numerical IP addresses here. There are also excellent integration opportunities using no host specifier at all. One could use HyperSQL or Derby and locate the database files under ~/data/customer, getting performance about 1000x better than using a network JDBC connection.

    Work-around to bypass requirement for numerical endpoint parameter

    %endpoint% must appear in your URL, and you can not modify the regex, but JDBC connect parameters with unknown keys are usually or always ignored.
    Therefore, you can include %endpoint% as a bogus connection parameter value like so:

    driver.url=jdbc:hsqldb:%catalogType%:%hostOrDir%[:%port%][/%catalogName%];bogus=%endpoint%[;%LIST;extra_parameters%]
    

    Unless you are the sole and permanent administrator, you ought to give some visual help like:

    translation.endpoint=Just enter 0.0.0.0.  (This is a Workaround)
    

    Create any new parameters (like catalogType, etc. above) to format the URL as appropriate for your database and your usage.

    Feb 20, 2012 10:58
  2. Blaine Simpson

    You don't need to restart all of the tideway services when modifying the JDBC drivers. It's an iterative process to get the jar files and .properties files right, so you can save yourself a lot of time by restarting only the sql_provider and tomcat services like so:

    sudo /sbin/service tideway restart sql_provider
    
    Feb 20, 2012 11:02