DB2 Support

JoeMcK · May 22, 2018, 7:19am

Hi,

Is there any way to get a connection from Metabase to an AS400 running DB2? We have ODBC connections to it from windows and linux, but looking at how we can use Metabase.

Cheers Joe.

jornh · May 22, 2018, 10:56am

There’s an ongoing effort. It looks like you currently have to roll up your sleeves and compile some Clojure code:

AndrewMBaines · May 22, 2018, 3:04pm

@jornh - do you know any reason why there isn’t just a generic ODBC and/or JDBC driver? Would be much simpler than having native connections for each database.

jornh · May 22, 2018, 7:22pm

First off Metabase Clojure is on top of Java. So we’re in JDBC not ODBC land.

Second what you’re hinting at has already seen some speculations from the core team it seems:

As I understand it “driver” is a bit of a misnomer. A lot of the Metabase “drivers” actually build on top of JDBC drivers (for databases/sources that has such a thing). So for most it’s more of an adaptation layer that mainly iron out SQL differences and adapt it all to the MBQL abstraction which the Metabase query builder UI is built on. Even most of the SQL drivers inherit base functionality from a base implementation:

github.com

metabase/metabase/blob/master/src/metabase/driver/generic_sql.clj

(ns metabase.driver.generic-sql
  "Shared code for drivers for SQL databases using their respective JDBC drivers under the hood."
  (:require [clojure
             [set :as set]
             [string :as str]]
            [clojure.java.jdbc :as jdbc]
            [clojure.math.numeric-tower :as math]
            [clojure.tools.logging :as log]
            [honeysql
             [core :as hsql]
             [format :as hformat]]
            [metabase
             [db :as db]
             [driver :as driver]
             [util :as u]]
            [metabase.models
             [field :as field]
             [table :as table]]
            metabase.query-processor.interface
            [metabase.util

This file has been truncated. show original

It’s all very well covered in this wiki page: https://github.com/metabase/metabase/wiki/Writing-A-Driver

I think the biggest “obstacle” towards just supporting everybody’s pet DB is that for each of them it also comes with a maintenance burden. That’s why the core developers are quite firm when it comes to having test coverage for each new “driver” - and yet still they have to somehow also provision and maintain the test infrastructure for this - potentially for a long time.

You know first hand that even a common and popular JDBC backed DB like mssql have generated quite a lot of

One IMO potential good way to reduce this burden would be getting in support for one or more Apache Calcite based JDBC drivers (Calcite, Dremio, Drill). With SparkSQL just landed in v0.29.x the road has been paved for this as it’s also Calcite based - Drill almost got in as part of that PR but stumbled). I think (hope?) that could potentially relieve some of the “driver” burden in Metabase land (ElasticSearch, Map-D, CSV/JSON/Excel based files and potentially also ClickHouse) and channel implementation effort for new exciting data sources to a common layer well below Metabase in Java land where implementation effort can be shared. The cost of this of course seen from and end user perspective would be that there’s a bit more moving parts involved in configuration - and upkeep …

Phew there, todays rant!

JoeMcK · May 23, 2018, 12:42am

I’ll try to look at the DB2 add-in as mentioned in https://github.com/metabase/metabase/issues/1509

However we are working with the JAR file version at present for simplicity, as I’m the only linux admin here and hence hoping for quick wins, without heavy lifting.

Will this require a different installation setup model?

jornh · May 23, 2018, 3:27am

No, it still compiles down to the .jar