Unable to Connect to DB2 iSeries – Error in "SET TRANSACTION ISOLATION LEVEL"

[EY_]

Dear Qlik Support,

My customer is experiencing an issue when connecting to a DB2 iSeries data source using the IBM i Access ODBC Driver (version: IBMiAccess_v1r1_WindowsAP_English). The following error message is displayed:

SYS-E-HTTPFAIL, Cannot connect to DB2 i Server.
SYS,GENERAL_EXCEPTION,Cannot connect to DB2 i Server,Error in SET TRANSACTION ISOLATION LEVEL READ COMMITTED
RetCode: SQL_ERROR
SqlState: HY000
NativeError: 19792
Message: [IBM][System i Access ODBC Driver][DB2 for i5/OS] - Error message text unavailable. Message can not be translated successfully.

RetCode: SQL_ERROR
SqlState: 42000
NativeError: -104
Message: [IBM][System i Access ODBC Driver][DB2 for i5/OS] - Error message text unavailable. Message can not be translated successfully.

Failed (retcode -1) to execute statement: SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Could you please advise how we can adjust or troubleshoot this error?

[Dana_Baldwin]

Hi @EY_ 

Please advise regarding the full version number of Qlik Replicate, DB2i and driver that you are using. So far I'm not able to find information on native error 19792 or -104.

Is this a new endpoint connection?

Has the firewall been opened correctly? You may need to involve the DB2 DBA to know which ports need to be opened.

Are you running the task when this happens or just testing the connection in the endpoint configuration? If running the task set logging for Source_Capture to Trace to see if you can get additional details about the error.

Thanks,

Dana

[EY_]

Hi @Dana_Baldwin

Thank you for your assistance.

Here is the requested information:

• Qlik Replicate version: 2024 May

• DB2i version: V7R5

• ODBC driver version: 13.64.28

• Is this a new endpoint connection? Yes

• Firewall: Confirmed to be opened correctly

• Current activity: We are just testing the connection in the endpoint configuration, not running the task yet.

[john_wang]

Hello @EY_ ,

It appears the issue is related to the IBM i Access ODBC Driver. Possible causes include:

• Missing or outdated message files on the Windows client

• Locale or language mismatch between the client and the IBM i system

• Corrupted or misconfigured IBM i Access ODBC driver installation

Could you please try creating a 64-bit ODBC DSN and check whether the connection works?

If you're running on Linux, please share the contents of the file "/etc/odbcinst.ini" so we can review the driver configuration.

thanks,

John.

[EY_]

Hi @john_wang ,

We have already adjusted the ODBC Driver settings and configured Qlik to use a DSN connection. However, the following error message still appears:

SYS-E-HTTPFAIL, Cannot connect to DB2 i Server.
SYS,GENERAL_EXCEPTION,Cannot connect to DB2 i Server, RetCode:
SQL_ERROR Sqlstate: HY000 NativeError: 79899
Message: [IBM][System i Access ODBC Driver][DB2 for i5/OS] - Error message text unavailable. Message cannot be translated successfully.

May I ask if there are any missing configurations in the ODBC Driver that could be causing this issue?

Looking forward to your reply.
Thank you.

[john_wang]

Hello @EY_ ,

It appears there may be an issue with the installation or configuration.

Could you please try testing the connectivity using a 64-bit ODBC DSN and check if a simple query can be executed successfully through the DSN?

thanks,

John.