Cannot Connect to AS400 with Power BI

[dyee4613v1]

Hi.  I'm trying to connect to AS400 database using an ODBC driver.  I tried this with both the ODBC driver and the IBM DB2 Database Drivers with little success. 

The ODBC Driver does connect but I can only see system-based tables.  I assumed it was an issue with my default SQL Schema or Library.  However, we tried a bunch of different options and none were successful.  Note: I did try an old user's login and this did show me tables specific to him but it didn't show anything I needed.  

The driver is ISeries Access ODBC Driver.  I also tried using IBM DB2 Database but that didn't work either.  Anyone have any tips/tricks I can try?  Thank you!

[dyee4613v1]

Hi.  I realized I never responded to this.  The issue was the library schema.  We thought you could just do it like with SQL where you do select * or something like that.  It didn't work.  You had to point to a specific library.  We tried a specific library and then suddenly we had access to everything.  

[v-kathullac]

Hi @dyee4613v1  ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @dyee4613v1  ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya.

[v-kathullac]

Hi @dyee4613v1 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya

[VahidDM · Accepted solution]

Hi @dyee4613v1 

Ensure your connection string and DSN are set up to use the correct default schema (library). In your ODBC settings, add the parameter DefaultLibrary (or similar, depending on your driver version) so that your login defaults to the library containing your tables. Also, verify that you’re using the latest ISeries Access ODBC driver (or IBM i Access Client Solutions ODBC driver) and that your credentials have permissions for the target tables. If necessary, use Power Query’s Advanced Editor to further refine the connection string.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube