Forum Discussion
Cannot connect to AS400 via IBM DB2 database
Hi Urid1969,
Firstly, please check the IBM AS400 server status and port status as per the below steps.
a. Verify that if the IBM server is up and running following the troubleshoot steps in this article.
b. Check the port status by using “netstat” in your IBM server and verify the port is in state Listen.Secondly, choose each driver in the following screenshot when connecting to IBM database and check if it is successful. When you choose IBM, please ensure that you have IBM DB2 Data Server Driver installed on your computer (minimum requirement is the IBM Data Server Driver Package (DS Driver)). Select the driver that matches your Power BI Desktop installation (32-bit or 64-bit).
Thirdly, check if you are able to connect to IBM AS400 database from other application such as Excel.
Thanks,
Lydia Zhang
I got it to work yesterday.
Get data
Ibm db2 database
Connect
Server: servername:446
Database: databasename
Next
Select Database tab on left
User name (try your own but I used qsecofr)
Password (try your own but I used the qsecofr password)
I changed the “select which level to apply to” to “servername:446;databasename
Hit connect
Hit ok for no encryption support
And I got data
Great! Thanks, I just tried it myself as well and it works indeed! Thank you so much for sharing this!
Hi Andy,
I got data, but most of the fields came over as binary when I attempted to set it up in an on-premises gateway.
Works fine for my personal gateway, but the refreshes won't work when I am offline.
Has anyone else experienced this?
Thank you. It worked for me as well. Just a curious ask:- is 446 a default port number so that anyone can use? Since, I tried with server port numbers but they didn't work. Why I dont know.
Anonymous
446 is simply the default DRDA port. DB2 for DB2/400 typically use the DRDA standard port number, 446, whereas DB2/LUW typically uses 50000 as the port number. Refer to IBM DB2 Admins and Installation guides changing these port numbers for the DRDA Service.
If this port works for you everything should be fine.
I ended up creating ODBC connections and connecting via ODBC to all of my iSeries files. Not sure what the benefit is for having it either way, is the direct connection faster?
The ODBC connection is standard connection to any external database/query engine. If the direct connection you meant refers to direct query, then, direct query is very useful for dashboarrds where the data volume is huge. It helps you to access data right from the tables itself without having it extracted and stored in the Power BI desktop.
If you are talking about direct IBM DB2 database connection instead of going through ODBC, I suppose, making odbc connection is easier as I faced lot of issues in giving the right port number for direct ibm db2. I don't think it is faster or slower in either case.
My guess is that you want to know what is the best or recommended connection in Power BI for DB2 generally.
I know that ODBC connections can potentially be slow for sometimes for large database management systems and i have read that people complain about slow speed connecting to DB2 via ODBC in Power BI (Look at post #2 in this thread).
I would recommend you using either the Microsoft or the IBM Driver instead. I had less struggle with using the this Microsoft Driver, so I am using this one. In terms of performance the Microsoft Driver seems to retrieve Data slightly slower. I tested an import with 3 Million Rows of Data from a large DB2 table. The Microsoft driver needed 2 Mins 50 Sec and the DB2 driver 2 Mins 25 Sec.
Please let me know why my IBM driver is not working. I have installed the IBM iAccess v1ri directly from the ibm website. The Microsoft driver as well as the odbc connection is working.
