Manage Database Drivers

Manage Database Drivers


When using the Database Query Component Matillion ETL requires a Type 4 JDBC Driver (Type 3 drivers should also work but these remain untested)  to be loaded. Depending on the platform, Matillion ETL is delivered with some Type 4 Drivers but due to licensing restrictions others will need to be uploaded manually via the Database Driver Management screen.

Currently Included Drivers

Database Driver Included Version Download/Information
Microsoft SQL Server  JTDS 1.3.1
Sybase ASE JTDS 1.3.1
IBM DB2 for i JTOpen 6.0
IBM DB2 No - manual upload Match your DB2 version

Use the db2jcc4.jar
PostgresSQL Yes 9.4-1206-jdbc41
MySQL No - manual upload v8 Recommended (5.1.47 supported)
The file name required is normally mysql-connector-java-X.X.XX.jar where X.X.XX is the version number.
Oracle No - manual upload We would strongly recommend using
the V12 or newer driver
The file name required is normally 'ojdbc7.jar'
Teradata No - manual upload Any - latest recommended

Uploading a Driver via the UI

This will associate the driver you are uploading with the listed drivers and ensure it is loaded by Matillion ETL when you attempt a database connection.

From the Admin Menu → Manage Database Drivers screen.

 - Select the new group 
 - Click Browse and select the jar file for the browser.

Note: Sometimes a driver is not contained as a single JAR file but has some dependencies. If you can add these dependant jar files as additional uploads and they will be added to the driver classpath.

Note 2: Currently, users can only add drivers specified in the file. If your dropdown menu is empty or missing the driver you want to add, see the "I have a type 4 JDBC Driver for a database that is not listed here, can I connect?" section below.

Click Test. This will test that the driver has been successfully loaded in the class-path and retrieve its reported version. Note at this point we are not testing a database connection.
Finally click OK to save any change made.


Where are driver files stored? 

When a driver is uploaded via the web interface it is placed and loaded from the following location in the Instance file system

/usr/share/tomcat8/Drivers/<Driver Group Name>/
/etc/tomcat8/Drivers/<Driver Group Name>/

Where <Driver Group Name> is the name of the Driver group in the Matillion Web Interface. E.g. Teradata.

I have a type 4 JDBC Driver for a database that is not listed here, can I connect?

Yes, some configuration file changes are needed to support this. The drivers supported in Matillion are configurable in the following file.


In here you will see a JSON file with the repeating elements, a set for each driver.

To add an entry for IQ copy an existing block such as the ASE example above and then edit it. BE CAREFUL with the commas between the blocks. if the JSON syntax is wrong Matillion ETL wont start.

Each entry is defined by the following entries:

  • name: This can be anything and will just show up in the Database Query Component with this name.
  • driver: The base class name for the JDBC Driver.
  • url: An example URL used to prompt the user to enter the correct JDBC URL. Can be left blank or preferably with a URL such as jdbc:sybase:Tds:<serverName>:2638.
  • fetchsize: The number of rows fetched at once, if supported by the database driver.
  • limit: When Matillion gets metadata about the query, but “prepmeta” is false, it needs to fetch one row of data to determine this metadata. This is done using a limit, but different database support different ways of supporting that:
    • top-n: SELECT TOP 1 * FROM (query)
    • fetch-first-n: query FETCH FIRST 1 ROWS ONLY
    • limit-outer: SELECT * FROM (query) LIMIT 1
    • limit-inline: query LIMIT 1
    • rownum: SELECT * FROM (query) WHERE ROWNUM <=1
    • none: query
  • prepmeta: (True or False) Whether the metadata for the query can be retrieved using a prepared statement. The default is true. If a driver is giving strange errors, set to false and choose an appropriate limit above
  • allowUpload – (True or False) Enables the ability to upload your own driver JAR via the UI. Recommended value is true.
  • defaultProperties – Any documented driver properties to set by default. These can be overridden these in the UI.

For example:
"name" : "Sybase IQ",
"driver" : "com.sybase.jdbc4.jdbc.SybDriver",
"url" : "jdbc:sybase:Tds:",
"fetchSize" : "500",
"limit" : "top-n",
"prepmeta" : true,
"allowUpload" : true,
"defaultProperties" : {"loginTimeout": "20"}

Another example:

"name" : "Amazon Athena",
"driver" : "com.simba.athena.jdbc.Driver",
"url" : "jdbc:awsathena://athena.<REGION>",
"fetchSize" : "500",
"limit" : "limit-outer",
"prepmeta" : false,
"allowUpload" : true,
"defaultProperties" : {"loginTimeout": "20"}

How do I avoid the "No suitable driver found" error when attempting to add a Snowflake driver?

In addition to the previous question, the entry in the file for Snowflake may be incorrect and require minor edits. To avoid a "No suitable driver found" error, the entry should take the form:

{"name" : "SnowCDW",
"driver" : "com.snowflake.client.jdbc.SnowflakeDriver",
"url" : "jdbc:snowflake://host/?warehouse=whs",
"fetchSize" : "500",
"limit" : "limit-inline",
"prepmeta" : false,
"allowUpload" : true

Please do not hesistate to Contact Support if you are experiencing difficulties with database drivers.