Delete SQL statement failing in Post Create SQL from IBM DB2 table in version 2021.1.4

[shubhadeeproy]

Hi,

After upgrading Alteryx Designer version to 2021.1.4, I am getting an error on the Delete statement in the Post Create SQL Statement section. I am using the output tool to write into a temporary table and using that temporary table to delete records from an actual production table. This process has been working perfectly fine for more than a year till I upgraded to the new designer version recently.

Here's the SQL statement.

delete from DVPCONT.ECAURPSR 
where (ARMGUI, ARSEVC, ARDMUI) in 
(Select ARMGUI, ARSEVC, ARDMUI From DVPCONT.TEMPECAURP 
 Where TASK='Change Action Delete')

Here's the error message:

Output Data (2)	Executing PostSQL: "delete from DVPCONT.ECAURPSR ¶where (ARMGUI, ARSEVC, ARDMUI) in ¶(Select ARMGUI, ARSEVC, ARDMUI From DVPCONT.TEMPECAURP ¶ Whe..." : [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token ; was not valid. Valid tokens: <END-OF-STATEMENT>.

Output Data (2)	Error running PostSQL on "DVPCONT.TEMPECAURP": [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token ; was not valid. Valid tokens: <END-OF-STATEMENT>.

There is nothing wrong with the statement and it works fine when run directly on the System i/iSeries server.

Here are the table structures.

ECAURPSR

TEMPECAURP 

This is the ODBC driver version I have.

Here is the ODBC setup.

I am also attaching the sample workflow.

Would appreciate any help.

[robwalks]

Hi @shubhadeeproy did you manage to find a solution to this issue? I'm experiencing the exact same problem.

[shubhadeeproy]

No @robwalks. I could not get any option to work and had to come up with a work around. I now create a temporary file on the iSeries server with records that need to be deleted and then delete the records by executing SQL delete command manually on the iSeries server by referencing that temporary file. What a pity!

[robwalks]

@shubhadeeproy as you say that is a pity and a workaround that's not viable for me as I don't have that level of access to the iSeries. 

I suspect this thread highlights the issue - there is a rogue semi-colon character being added into the SQL statement and its causing the iSeries to reject it:

https://community.alteryx.com/t5/Alteryx-Server-Ideas/iSeries-In-DB-SQL-Statement/idi-p/650541

[robwalks]

Is there any progress in fixing this bug please Alteryx?