Forum Discussion
Cannot connect to AS400 via IBM DB2 database
Hello friends
Keep getting error while trying to connect to AS400
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
24 Replies
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 haven't been able to connect to DB2 either. I've been trying to work with Microsoft on the issue. Hopefully something comes of it. My DBA's just want to force me to use ODBC but ODBC performance seems inacceptably slow for me. The DBA claims that Microsoft told them the built-in connector to DB2 won't work, which seems implausible, and because ODBC "works" the DBA doesn't want to revisit the issue.
Anyway, in your instance you might try adding the port number to the end of your server name. I've heard 446 is a common port number, although as I said nothing's worked for me so far. In the server field type the following
whatever_your_server_is:446
Yes, I tried adding a port still error. Thank you
Same issue here and we got the same response from our PaaS supplier:
- on iSeries (AS/400) DB2 is on a different port
- use ODBC
But that is indeed slower, plus it requires additional software. That by itself can be overcome, if it weren't for the insane compatibility mess that IBM software is. You need that version of that software that is only compatible with this Windows version, but the data driver is a different version and that is not compatible with the plugin for Visual Studio, etc. etc, ... Combine that with endless vague support documents and you spend hours researching the stuff, without making any progress.
Microsoft could help us a lot by simply allowing a different port in their own DB2 connection driver. Then I could toss all of this iSeries access mess out the door. :-)
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 ZhangSame issue here. Our PaaS supplier gave the same answers:
- on iSeries / AS/400 DB2 uses a different port
- use ODBC: that will work.
But that is indeed a sub-optimal solution.
It would be nice if Microsoft could adapt the DB2 driver in such a way that a connection is possible.
I was able to make it work by doing the following in Power BI
Get data
Ibm db2 database
Connect
Server: servername:446
Database: databasename (in our case it's the system name)
Next
Select Database tab
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
hello all,
I tried to do the same but received the following error :
Microsoft DB2 Client : The Package correspoding to an SQL statement execution request was not found. SQLSTATE = 51002, SQLCODE = -805
however I am able to extract data in my excel from IBM i.
Any suggestions would be much appreciated.
just downloaded the most current BI desktop and most of my database options are gone, including DB2... How do i get them back? (sorry to hijack thread)
I'm trying to connect to an AS400 as well (or to the DB2 for i database). I downloaded the IBM Data Server Driver Package (DS Driver) and search on the IBM website the right drive but I could manage to find anything and I'm still getting this error:
Does anyone else have the same problem?
Many thanks,
Ben
I discovered this old thread when I searched for the following connection error:
The package corresponding to an SQL statement execution request was not found. SQLSTATE=51002 SQLCODE=-805
After some research i finally found the solution for my Power BI Version (May 2019):
It is not necessary to install and use the IBM DB2 driver package on your machine or to edit the default connection settings (Just enter the correct Hostname including Port and Databasename).
The user you use to connect to your DB2 database simply has not the needed privileges to install a certain package on your database essential to connect. It seems that Power BI automatically tries to install this package when you click "connect" for the first time.
When you login with an account that has admin privileges (or has the right to Install / Bind new packages) this package gets created in your DB2:
After installing this package every other database user can access this DB2 Data Source via Power BI!
This Link helped me:
https://docs.microsoft.com/bs-latn-ba/azure/data-factory/connector-db2
I hope this helps other people searching for the same error :)!
