Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Urid1969
Helper I
Helper I

Cannot connect to AS400 via IBM DB2 database

Hello friends

Keep getting error while trying to connect to AS400 

Untitled.png

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

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).
2.PNG

Thirdly, check if you are able to connect to IBM AS400 database from other application such as Excel.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

24 REPLIES 24
Jeick
Frequent Visitor

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:

New Package after Power BI Connect.PNG

 

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 :)! 

 

DaveKnittle
New Member

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: 

 

Capture.PNG

 

Does anyone else have the same problem?

 

Many thanks,

Ben

We added port 446 when entering the server name, for example HOSTNAME:446, then HOSTNAME for database.  Hope this helps.

 

@curtismob

curtismob
Helper IV
Helper IV

Thank you!!  This worked for me as well.

 

@curtismob

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.

 

 

 

AndyRhodes
New Member

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

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?

@Nonsensely 

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.

Anonymous
Not applicable

@Jeick,

 

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.

@Anonymous 

 

What kind of error do you get when you select the IBM driver?

 

I described the solution to all the errors I got when I tried using the IBM driver at the last post in this thread:

https://community.powerbi.com/t5/Power-Query/DB2-error-SQL0204N-is-an-undefined-name/m-p/859580#M28919

Anonymous
Not applicable

@Jeick ,

 

Below is the error I am getting:-

DirectQuery_IBM_Driver_Error.PNG

@Anonymous 

I've seen this error when no DB2 connect license file exists in your installpath. Or when there is a version missmatch between your license file and db2 client.

 

Links that may help:

https://www.ibm.com/support/pages/sql1598n-error-connecting-mainframe-db2-ibm-data-server-driver-even-though-db2-connect-license-file-exists-license-directory-install-path

 

https://www.ibm.com/developerworks/community/forums/html/topic?id=03c676c4-9ebc-4b82-a9f9-2bc4c362d08d

Anonymous
Not applicable

@Nonsensely 

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.

Anonymous
Not applicable

@AndyRhodes 

 

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.

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?

Great!  Thanks, I just tried it myself as well and it works indeed!  Thank you so much for sharing this!

AndyRhodes
New Member

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

wimbor
Regular Visitor

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. 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors