Metabase: DB2 Support

[chadbean]

Would like to put in a request to see about creating a DB2 driver. Is that feasible? Any other interest?

It's not a super popular DB, but is still used heavily in some enterprise environments. If there is a starting place on creating a new driver, please let me know so I can try to help.

⬇️ Please click the 👍 reaction instead of leaving a +1 or 👍 comment

[tomferreira]

+1

[camsaul]

Hi @chadbean. We're prioritizing new drivers by demand, and it looks like Redshift, Oracle, ElasticSearch, and BigQuery all have it in spades :spades: So it might be a while before we get around to writing a DB2 driver.

Since you expressed interest, I slapped together a quick guide to writing a Metabase driver if you want to give it a go. Otherwise, any research you can help us with, such as:

• Figuring out the best JDBC driver for DB2
• Figuring out how to test against DB2 on CircleCI
• Figuring out what SQL dialect weirdness we're going to have to work around for DB2

Would be super-helpful.

[chadbean]

@camsaul this is awesome, thank you for the write up, I love the emoji. I'll look into this a bit more.

[chadbean]

I changed the title to specifically mention the IBM i, as the platform I would be most interested in and the JDBC drivers for IBM i and DB2 on other platforms seems to be different which would require some consideration on how to develop against that.

The best JDBC driver for IBM i and DB2 is: _com.ibm.as400.access.AS400JDBCDriver_ [1] available via the Toolbox for Java/JTopen at http://sourceforge.net/projects/jt400?cm_mc_uid=06592322392514441664836&cm_mc_sid_50200000=1448035762

Testing this against a DB2 instance on the IBM i might be a lost cause since it might be almost impossible to virtualize without a Power system with the IBM i software, unfortunately.

[1] http://www-03.ibm.com/systems/power/software/i/toolbox/faq/jdbc.html#faqA1

[chadbean]

After a little more research, it seems as though the IBM DB2 JDBC 4.x driver [1], can work with IBM i and other platforms, so this issue could apply outside of the IBM i.

[1] http://www-01.ibm.com/support/docview.wss?uid=swg21363866

[alexkyllo]

+1 for DB2 support. I want to use this at work, but my company's data is mostly in DB2 databases. Considering working on this myself as well.

[jayakumark]

+1 for db2

[lucascdsilva]

+1 for db2

[samant-rags]

+1 for db2 with Oracle done .. this is now the most wanted :)

[camsaul]

@samant-rags Vertica (#1337) is actually our current most-wanted driver 😉 We're working on shipping a driver for our next release thanks to lots of help from the community.

If you (or anyone else on this thread) is familiar with Clojure and feeling adventurous you can try doing the same as our Vertica users and submitting a PR for a driver (we have a guide to writing one here). Even making a bit of headway there would mean we can ship a DB2 driver a lot faster. If you want to give it a go, I'm here to answer any questions you might have along the way.

[jayakumark]

Now Vertica is done . whether DB2 comes next ?

[camsaul]

@jayakumark if you want to help write it, PRs are welcome!

By the way, if anyone else is interested in a DB2 driver, one way we prioritize issues is by sorting them by the number of 👍 reactions. So instead of leaving +1 comments, please just upvote by adding a reaction.

[wallce]

@camsaul can i attach db2 driver here?

[camsaul]

@wallce what do you mean by attaching it? If you want to write a DB2 driver a PR is welcome.

[wallce]

Hi, done my DB2 driver as attached.

On Wed, Sep 27, 2017 at 12:55 AM, Cam Saul [e-mail] wrote:

@wallce https://github.com/wallce what do you mean by attaching it? If
you want to write a DB2 driver a PR is welcome.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/metabase/metabase/issues/1509#issuecomment-332264326,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AeyzU7dVTga6ou6UDo54Tc88rezLc--Eks5smSx1gaJpZM4GliZE
.

[dludwig-jrt]

Hi @wallce, where did you attach your DB2 driver?

[wallce]

@dludwig-jrt
@camsaul

db2.zip

[dludwig-jrt]

Thank you.

[camsaul]

Hi @wallce, instead of attaching a zip file, could you please open a PR?

[wallce]

no idea how to create PR. can help. tq

On Thu, Sep 28, 2017 at 1:41 AM, Cam Saul [e-mail] wrote:

Hi @wallce https://github.com/wallce, instead of attaching a zip file,
could you please open a PR?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/metabase/metabase/issues/1509#issuecomment-332600024,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AeyzUypTipBkiuvY3m36d2h7WjvHPmUaks5smoivgaJpZM4GliZE
.

[lucascdsilva]

Hi @wallce, did you write tests? Already using in production?

[wallce]

Ya. Using but not production yet.

**fix to support oldest db2

db2.zip

On Sep 30, 2017, at 3:31 AM, Lucas Carvalho [e-mail] wrote:

Hi @wallce, did you write tests? Already using in production?

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or mute the thread.

[camsaul]

@wallce https://help.github.com/articles/creating-a-pull-request/

[camsaul]

Hey everyone, one way we prioritize issues is by sorting them by the number of 👍 reactions on each issue's description. So instead of leaving +1 or 👍 comments, please just upvote by adding a reaction to the issue description.

[stephenlamb]

Hi guys!

I'm testing Metabase as BI report tool for my company. I really need to connect to a DB2 as a prerequisite to continue tests. If not, I will be forced to use other solutions... And I really liked Metabase.

So, any news about this DB2 connector?

Thank You in advance!

[lucascdsilva]

Did you test the connector made by @wallce ?

[dludwig-jrt]

I had to modify the driver file originally posted by wallce since I am working on an older DB2 on an AS400. To compile the project, download the latest git source with the latest tag release and place the attached file in the project directory structure metabase-0.26.1.1/src/metabase/driver/. Note that you will need to place a copy of your DB2 driver in a plugins folder under the main project. See https://github.com/metabase/metabase/blob/master/docs/administration-guide/databases/oracle.md

db2.clj.zip

[camsaul]

Hi @wallce/@dludwig-jrt, instead of attaching ZIP files do you think you can just open PRs? That would make trying it out easier for everyone. They could just check out your branch instead of having to check out the main Metabase repo and then manually having to put the db2.clj file in the correct directory.

[stephenlamb]

Thank you guys! I will try tomorrow and respond you.

[dludwig-jrt]

See pull request: https://github.com/metabase/metabase/pull/6281

[stephenlamb]

Sorry guys. I got into other project and did not tryied until this afternoon. I follow the instructions, saved db2.cjl under driver folder, built the jar and... nothing appeared. What it could be?

[stephenlamb]

People,

Finally I was able to insert DB2 inside Metabase.

Using the first CLJ file that @dludwig-jrt posted with the right java driver for my DB2.

The issue now is I can connect, but metabase try to scan field values daily and "my" DB2 is a huge tremendous BIG database. The consequence is that server and metabase became so slow that I had to delete the DB2 connection. Is there a way to connect to just the table I need?

Thank you guys!

[kalampakas]

If anyone in this thread is still alive and cares - we wanted a db2 driver to a Linux IBM DB2 and we cloned the metabase repo from @dludwig-jrt and replaced the class from AS400 to the "normal" jdbc class , using the universal 4 driver , com.ibm.db2.jcc.DB2Driver , and set subprotocol to db2 - we can connect to the db but when doing simple view of the raw data in a table we get DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=.;ELECT FROM ...
Any idea why those chars show up instead of S ? Or what we might've done wrong ? Thanks for all the work on this, it should really be folded in to work like the rest
UPDATE: After a moment of epiphany went back and removed the apply-limit and apply-page overrides from the mixin cause our Linux DB2 is more sane than AS400 . Maybe some sort of selection between AS400 and Linux DB2 might be possible/beneficial BEFORE the pull ? We haven't noticed anything unusual in smoke testing that seems to break stuff but we can get data whereas before we couldn't. Thanks a bunch for this driver though! Thinking of trying to add ES - https://github.com/metabase/metabase/issues/1300
UPDATE #2: It also appears DATE is buggy kind of the same way when trying to eg. do a query between dates we get DATE;1, FROM as part of the error

[ericnzero]

@stephenlamb how do you find DB2? I have the same problem.
https://github.com/metabase/metabase/issues/1509#issuecomment-360859506

[jornh]

@jepaglinawan if your problem is DB2 does not show up in the database selector dropdown maybe the path advice in https://github.com/metabase/metabase/issues/3960#issuecomment-406684317 is helpful?

[ericnzero]

@jornh thank you for your help :)

[dludwig-jrt]

@camsaul, How do I contribute/submit the following AS400 DB2 driver to the metabase project? It is coded for v0.32 and higher.
db2.zip

[dludwig-jrt]

I also uploaded this driver to: https://github.com/dludwig-jrt/metabase-db2-driver

[alisonrafael]

I forked @dludwig-jrt project, changed the driver (com.ibm.db2.jcc.DB2Driver), implemented limit/offset, and fixed date filter problems. I hope it is helpful: https://github.com/alisonrafael/metabase-db2-driver

[dludwig-jrt]

Our version of the driver was coded for: v7r1 iSeries DB2. Previous versions required implementation of limit and offset, but this version does not.

@alisonrafael, what version of DB2 are you using?

[alisonrafael]

@dludwig-jrt DB2 V11.1 for Linux/UNIX/Windows (Driver version 4.25.13)
UPDATE: correcting, I'm using the DB2 v9.7.0.5 for Linux/UNIX/Windows

[jagDanJu]

Hey @alisonrafael
can you please update your driver to work with the current version of metabase?
I need downgrade metabase to 0.33.7.3, because it does not work with your driver.

following error occurs:

java.lang.IllegalArgumentException: No method in multimethod 'connection-details->spec' for dispatch value: :db2

Thanks!