DB2 connections throwing error on Alteryx 11.7 version and working fine on 10.5

HI, we have one workflow which connects to DB2 and the flow was running fine on our Alteryx 10.5 version server but after we upgraded the server to 11.7 it is throwing below error-

SQLSTATE=01004[IBM][CLI Driver] CLI0165E Error in row

can any ine please help what is causing this issue exactly and what will be the solution?

also i wanted to know the DB2 driver compatible version with Alteryx 11.7 version.

Thanks a lot in advance. its very urgent so requesting for quick help.

Database Connection

Accepted answers

DiganP

@skaps You can fix by inserting the following lines in the db2cli.ini file or the Advanced Settings tab within the driver to prevent that error from being reported back to Alteryx:

IGNOREWARNINGS=1

IGNOREWARNLIST = "'01003'"

The difference in behavior with 11.7 is due to greater database transparency. Previously, the user had no notification that there were modifications being made to the data. The database error was generated, but not reported through Alteryx. Now the error is reported.

skaps

hi @DiganP,

I also found out the same that the issue is actually with Alteryx version. In v10.5 invalid data got bypassed with a warning message for each row of data but in 11.7 Alteryx forced the flow to fail if any such invalid data come up.

I have not tried adding the code that you shared, I will try and will check. Thanks a lot for your help.

All comments

DiganP

@skaps You can fix by inserting the following lines in the db2cli.ini file or the Advanced Settings tab within the driver to prevent that error from being reported back to Alteryx:

IGNOREWARNINGS=1

IGNOREWARNLIST = "'01003'"

skaps

hi @DiganP,

I have not tried adding the code that you shared, I will try and will check. Thanks a lot for your help.

jakub-mg

@DIganP: the confusing part is that the same query executes fine using i.e. Teradata SQL Assistant (and I haven't got IGNOREWARNINGS set in my ini file). Which makes us think they are somehow related to how Alteryx 11.7 uses the driver.

We're actually seeing two errors in Alteryx for the same query: ToolId 103: Error SQLExtendedFetch: [IBM][CLI Driver] CLI0165E Error in row. SQLSTATE=01S01[IBM][CLI Driver] CLI0002W Data truncated. SQLSTATE=01004

I'm conscious of silencing these errors on the server as they're quite generic and might be required in various other context. Any further thoughts on this? Thanks Jakub

jakub-mg

@DiganP:

What's interesting here, is that the same query executes fine in i.e. SQL Assistant, no error is thrown by DB2 (and I haven't disabled any error messages locally). This makes us think it has something to do with how 11.7 works with the driver. The same workflow executes fine on 10.5.

We're actually getting two error codes back: [IBM][CLI Driver] CLI0165E Error in row. SQLSTATE=01S01[IBM][CLI Driver] CLI0002W Data truncated. SQLSTATE=01004. I'm reluctant to suppress them on the server because it feels like a sledge hammer approach, and potentially depriving end users of useful DB feedback for the sake of making one workflow execute.

Any further thoughts on this, or similar issue being seen by anyone?

Regards Jakub

*apologies - it seems I've managed to post twice - it's because the original post took a long time to appear...

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703