AS400, Power Query, IBM DB2 Databases, and you! RRS feed

  • Question

  • Good Afternoon Everyone.

    I have an IBM AS/400 system that I'm trying to connect to with the functionality of power query. I have no problem doing ODBC connections to it naturally, but it's been well documented here and elsewhere that right now power query does not support generic data connections of that sort. That is not part of my question.

    I'm having a terrible time trying to connect power query to the DB2 database that the AS/400 system uses. I'd appreciate any tips or tricks anyone has for getting it to connect. I have not had any issues connecting power query to our SQL databases at all.

    As a final aside, does Microsoft have a consulting service available for this sort of thing? I'd gladly put in for an hour of tech time for someone savvy to get my power query to talk to this system.

    Tuesday, June 2, 2015 4:53 PM


All replies

  • It's been possible since (I think) February to use ODBC from Power Query, but you have to supply the SQL statement yourself.

    What kinds of problems have you seen with DB2 connectivity? Have you gotten past the point where you've installed the DB2 ADO.NET driver and PQ is able to find it?

    Alas, I'm painfully unfamiliar with the type of paid support options that are available. I would guess that you can open an incident using the usual support routes, though that doesn't sound like it would necessarily get you what you're looking for.

    Wednesday, June 3, 2015 1:22 PM
  • Thank you for letting me know that generic ODBC connections are now supported Curt. I'd prefer to have the user avoid having to supply the SQL query, one of the great features of power query is the ease of working with tables without any SQL knowledge.

    I am unable to tell if I have established a connection in the first place. I receive the error:

    DataSource.Error: IBM DB2: Unable to find a database provider with invariant name 'IBM.Data.DB2'.
    This error may have been the result of provider-specific client software being required but missing on this computer.  To download client software for this provider, visit the following site and choose the 64-bit (x64) version of, at minimum, 'IBM Data Server Driver Package (DS Driver)'

    However, I believe this error is the same even if you've entered a completely incorrect database.

    I have the x64 server driver package from IBM installed, which was my first attempt at troubleshooting.

    I'm taking my connection from an existing connection I have written in VBA, using this ODBC connection string. (Identifying details changed)

    Provider=IBMDA400;Data Source=(the ip address of the server);User Id=(Valid User ID);Password=(Valid Password)

    I then pass it the command

    Select * From (Database Name).(Table) for example

    I'm filling in the connection details from that above string, and using the database name in that sql statement as the database name in power query. I may be completely misunderstanding the connection process.

    • Edited by PowerActuary Friday, June 5, 2015 4:09 PM clarification
    Friday, June 5, 2015 4:08 PM
  • Progress is being made, of a sort.

    I am no longer getting the error that it's not able to find a database provider. I believe I am now connecting to the server. However, at this point:

    Encryption Support

    The database you are trying to connect to doesn't support encryption. To access this data source with using an unencrypted connection, click OK.

    Naturally, I click OK, but the message repeats after a few seconds. I have tried unchecking encryption in the data source settings, but that has no effect.

    Also, changing the database I'm connecting to has no effect on the error message, nor does changing the username and password to something invalid.

    Friday, June 5, 2015 4:51 PM
  • Further update on this.

    I have run the tracing function inside power query and found that I'm getting error 10061. Doing some searching, this is often caused by having an incorrect database name. Tracked that down, using the AS400 console command DSPRDBDIRE. I have confirmed that my database name is correct.

    IBM's support thread for this error is, if that helps anyone.

    At this point I'm completely stuck. I'm going to cross post with the IBM support forum for AS400, but hopefully someone on here has run into something similar and can bridge the gap.

    Friday, June 12, 2015 9:51 PM
  • I'm very interested in this issue, I thought that PQ import option "From IBM DB2" meant only DB2 LUW platform and not iSeries / System i / AS400...

    Can you confirm and/or share your solution or workaround?

    By the way, what did you changed to get to "Progress is being made, of a sort." ?

    Thanks in advance for any info


    Friday, June 19, 2015 8:37 AM
  • I'm also encountering the same error. PowerActuary were you able to find a solution?
    Wednesday, July 8, 2015 8:41 PM
  • The encryption prompt loop should be fixed in the latest version (2.24.4064.242).


    Thursday, July 16, 2015 11:23 PM