cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
robarivas Member
Member

IBM DB2 vs ODBC

My IT department tried to set up a connection to IBM DB2 but could only get it to work through ODBC. This doesn't seem right. Also, the performance over ODBC is horrible. Trying to filter a multi-million row table takes forever (almost literally). Our DB2 database is on a mainframe (z/OS). Can anyone tell me what I should tell my IT department so that they can establish a proper connection to the IBM DB2 database? I assume that a proper connection exists (otherwise why would Microsoft list it as a valid connection option) and that such a proper connection would perform acceptably. My thought is that the ODBC route is a bad, although technically functional, approach.

11 REPLIES 11
arify Established Member
Established Member

Re: IBM DB2 vs ODBC

Have you tried the built-in DB2 connector with Microsoft implementation? (IBM DB2 dialog > Advanced Options)

 

From the issues we've seen in customer scenarios, DB2 ODBC driver has a lot of incompatible behaviors when running against a z/OS instance.

 

Also, if you can send the traces to us (pbidesfb@microsoft.com) (Enable tracing from Options > Diagnostics > Enable Tracing (Verbose) (also you can open the Traces folder from here), then reproduce the failure, then close the PBI Desktop. You can get the traces from Traces folder.) we can see which step is taking long & might have some tips.

 

For example, if you're not using relationship columns, you can turn off "Include relationship columns" in the advanced settings.

robarivas Member
Member

Re: IBM DB2 vs ODBC

Thank you for the reply. I have emailed the Trace info. Also, I have tried using Microsoft's built-in connector. However, I get the following error when I try that:

 

Unable to Connect

 

We encountered an error while trying to connect.

 

 

Details: "Value cannot be null.
Parameter name: sourceArray"

arify Established Member
Established Member

Re: IBM DB2 vs ODBC

That looks interesting. Can you please send us traces for that error too?

 

Thanks

robarivas Member
Member

Re: IBM DB2 vs ODBC

Ok, I just sent the additional trace files.

arify Established Member
Established Member

Re: IBM DB2 vs ODBC

Thanks, we found where that error is coming, and will fix that soon. About the performance, I didn't see the ODBC traces. Can you try that again, and make sure you pick Verbose level?

robarivas Member
Member

Re: IBM DB2 vs ODBC

Fantastic, thank you. Hopefully "soon" means really, really, really soon Smiley Happy 

 

How will I know when it's fixed?

 

I'm assuming this means once it's fixed I should be able to connect using the "IBM DB2 Database" option from the "Database" category of the "Get Data" dialog using the "Microsoft (requires .Net 4.5 or higher)" option under the "Advanced options" dropdown, correct?

 

I don't have the ODBC set-up yet on the machine I am using at the moment but I do have it on my machine at my home so I will generate the trace data on that tonight and email that you. (of course my hope is to eventually not have to use the ODBC approach)

arify Established Member
Established Member

Re: IBM DB2 vs ODBC

We don't really have an ETA yet, it seems like an external issue. But, my guess, it probably won't take more than a few releases.

 

You'll know if it's fixed if someone updates this thread, or just start trying every new release in 2017 🙂 If there are no other issues, you should be able to use that "Microsoft" option.

robarivas Member
Member

Re: IBM DB2 vs ODBC

I've just sent another email with the trace files related to poor ODBC performance. In Power BI Desktop I pulled 3 tables from DB2 and tried filtering one of the tables to show only rows with a transaction posting date between 1/1/2016 and 1/31/2016. After waiting for a very long time I just gave up and closed the program.

arify Established Member
Established Member

Re: IBM DB2 vs ODBC

Oh, I just learned that this (Value cannot be null. error) was a known issue and it was already fixed 🙂 It'll be out in (end of) October release.

 

I'll look into the traces about the performance, my guess is the driver reports us that its unable to compare dates, so we have to pull all the data.. I'll let you know once I take a look at them.

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 202 members 2,109 guests
Please welcome our newest community members: