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

[skaps]

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.

[DiganP · Accepted solution]

@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 · Accepted solution]

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.

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

[CleverUserNamer]

@DiganP thank you for the fix! Can you or someone else walk me through how to apply that code to the driver or to the db2cli.ini file (such as where these files might be, how to  edit/add to them, etc). 

thanks! 

IGNOREWARNINGS=1

IGNOREWARNLIST = "'01003'"

[vn503ce]

I am facing the same issue, but to no avail. I inserted the code, but it didn't help. 

[vn503ce]

Is there any way of fixing this issue. This seems to be with 11.7 version. Below is the error that I am seeing - 

Info: Input Data (2): ODBC Driver version: 03.51
Error: Input Data (2): Error SQLExtendedFetch: [IBM][CLI Driver] CLI0165E  Error in row. SQLSTATE=01S01[IBM][CLI Driver] CLI0002W  Data truncated. SQLSTATE=01004[IBM][CLI Driver] CLI0165E  Error in row. SQLSTATE=01S01[IBM][CLI Driver] CLI0002W  Data truncated. SQLSTATE=01004[IBM][CLI Driver] CLI0165E  Error in row. SQLSTATE=01S01[IBM][CLI Driver] CLI0002W  Data truncated. SQLSTATE=01004
End: Designer x64: Finished running CANADA_COREHR_PDD.yxmd in 13.9 seconds with 1 error

[jakub-mg]

Hello,

A workaround I found is to identify which column was causing the error and wrap it in a trim function.

It really feels like a hard error for this is excessive. Visibility is great but why not just throw a warning that truncation occurs and move on.

[vn503ce]

Thanks Jakub-mg. RTRIM function to trim down the spaces in char type columns worked. Kudos.