Topic
  • 2 replies
  • Latest Post - ‏2016-03-14T13:24:25Z by mkoni
rpeebles
rpeebles
1 Post

Pinned topic Connecting to IBM DB2i using MS PowerQuery

‏2014-08-26T20:39:55Z |

Has anyone out there done this? 

I have someone in our BI group attempting to access our IBM DB2i database using the MS Excel add-in Power Query, but to no avail.  They are able to connect with MS Query and Pivot tables, but Power Query will not connect. 

They have been working with MS support, but not answers yet from them.  Just wondering if anyone from the IBM world has ever made this connection successfully?

 

Thanks,

 

Richy Peebles

IBMi Admin/Analyst

 

  • jfmurr1
    jfmurr1
    1 Post

    Re: Connecting to IBM DB2i using MS PowerQuery

    ‏2016-02-15T17:46:04Z  

    I see this is a little old and no response from anyone.  Did you ever get this to work?  I've been able to connect PQ to a Z/OS DB2 host subsystem.  The key is supplying the "optional" SQL.  When I tried just putting in the server URL like "myserver.abc.xyz:3201" making sure I added the port and then just the subsystem name - when I tried to navigate and select a table and get data from it I get a strange SQL error.  When I also supply the "optional" SQL like "SELECT * FROM SCHEMA.MY_TABLE" it works.

  • mkoni
    mkoni
    39 Posts

    Re: Connecting to IBM DB2i using MS PowerQuery

    ‏2016-03-14T13:24:25Z  

    Hi,

    I found your question, but I don't know if you still wait for an answer.

    btw.

    to make a connection to iseries db2 using ms-powerquery. (ms-excel 2013)

    1. create a system-dsn odbc driver entry (notice the name of the system-dsn)

    2. notice yor rdb name of your iseries (wrkrdbdire)

     

    here is the connection string:     Provider=MSDASQL.1;Persist Security Info=True;Extended Properties="DSN=DSN_NAME;";Initial Catalog=CATALOG_NAME

    where DSN_NAME is the name of your odbc system-dsn and CATALOG_NAME is the name of your database on your iseries (wrkrdbdire)

     

    regards mike

    Updated on 2016-03-14T13:24:52Z at 2016-03-14T13:24:52Z by mkoni