ODBC Data Connection - Database isn't identified

[jbchurchill]

We recently successfully established an ODBC data connection to an AS/400 Mainframe using the IBM iAccess for Windows ODBC data source. When I go to use the connection and select the database there is nothing to select. Can someone explain what went wrong? We tried naming the connection to be the same name as the database but that did not work any better.

No Database

ODBC setup

[Jay_Brown]

Hello @jbchurchill , thanks for posting!

Can you test the actual data retrieval outside of Qlik?
https://community.qlik.com/t5/Knowledge/How-to-fetch-data-via-ODBC-DSN-without-using-Qlik-Products/t...
https://community.qlik.com/t5/Knowledge/How-to-fetch-data-with-ODBC-DSN-connections-using-Powershell...

Sometimes we see scenarios where the Test Connection function works but the user does not have access to the actual data.  This would be a good test to see if the same account can retrieve the data from that server.

[Anonymous]

This VBS script from your first link, indicates that it connects successfully. 

 

[NadiaB]

Hi @jbchurchill 

Are you saying that you created the connection in QS (successfully connects), but when you try to preview or load it fails ? Is that correct, or are you saying that you cannot create the connection at all? 

Has this worked before or is this the first time you attempt to create a connection to this data source?

Could it be possible to attempt the connection with a different driver?

What is the driver involved and its version?

Kind Regards. 

[Anonymous]

It successfully connects as shown by this screenshot using a vbs script that Jay_Brown pointed to (in one of those links) but using the connection in Qlik Sense brings up the dialog where I would typically browse for data but there are no tables showing. The user we are authenticating with is expected to have permissions for all the tables in the database but we just don't see anything. See the following screenshot to see what I see at that point (in the data load editor).  It was the ODBC client that IBM recommended for this.

 

[jbchurchill]

So I tested this using Excel and we still have a problem. The error message in Excel shows more detail but I'm not sure yet how to interpret the message so I'm asking for advice here. The first message is when connecting to the database using and the second is when formulating a specific query.

We have reviewed this article which indicates it is most likely a network issue.

https://www.ibm.com/support/pages/communication-link-failure-when-checking-out-schema

Screenshots showing Excel ODBC connection attempt and the SQL Query window (we did a SELECT * FROM <a known table>).

[Jay_Brown]

Hello @jbchurchill , We don't have records on that error, but you might be able to get more information about specifically where this is failing by enabling the ODBC trace and increasing the logging level.  Sometimes this can be done via the ODBC Administrator console, other times it must be done in the registry.

If you are able to enable that and increase the loglevel it may give you enough information to determine which part of this is failing.

[jbchurchill]

Thanks for the reply - I was able use the trace to generate a log file (760 lines long with lots of hex codes and lines that indicate some form of success). One such line is the following.
EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)

There are also lines that look like errors.
EXIT SQLGetDiagRecW with return code 100 (SQL_NO_DATA_FOUND)

One of the later "Error" lines looks exactly like the error I get from the popup.
DIAG [08S01] [IBM][System i Access ODBC Driver]Communication link failure. comm rc=10038 - CWBCO1003 - Sockets error, function returned 10038, 167.102.227.49 (10038)

I can't say that I understand much that the log would be telling me. If I had some idea of what to look for, that might help but I feel like I'm not quite getting there.