Data Stream In - AS400 error - DB2 for i5/OS]SQL7008

[kv231]

I am trying to read the data into a temporary file however when it tries to write to the file I get

the following error message:

Error: Data Stream In (46): DataWrap2ODBC::SendBatch: [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL7008 - KTEMPK1A1 in QGPL not valid for operation. Insert into KTempK1a1(DUDNM1,DUDNM2) Values (?,?)

Can anyone help?

would really appreciate any help or pointer.

I know that there are no permission issues as the file is being created.

[gtorres8]

Hi @kv231 ,

The error SQL7008 points to update/insert errors in iSeries. Per the following articles, the cause of the error is that journaling is not enabled for the iSeries table. And/or an autoCommit not being enabled. The referenced articles have several other options to address the error. I hope one of them works for you.

References:

Article 1

Article 2

[kv231]

Thank you for assistance.

I now have an issue where  I get the following message:

Warning: Data Stream In (46): Executing PreSQL: "DROP TABLE "DWLIB"."DRDLD1";" (error ignored) : [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token ; was not valid. Valid tokens: <END-OF-STATEMENT>.

So I am dropping the file and recreating it.  It looks like the ODBC Drops command does not seem to work.

Kalpana

[gtorres8]

Hi @kv231,

Sorry to hear that. What version of the DB2 ODBC drivers are you currently using? What happens if you use the temporary table option in the Data Stream In tool; does it work without errors?

[JonPoole83]

I would be interested to know if you were able to resolve your issue because I am running into the same thing.

[gtorres8]

Hi @JonPoole83 ,

This is a limitation of what is supported in Alteryx Designer. Per the IBM DB2 data source help documentation, we have not tested or certified DB2 z/OS or iSeries and we cannot guarantee proper functionality in Designer.

I recommend creating an idea on Community to have this tested and supported.

[kv231]

unfortunately  I didn't get this resolved.  If it is resolved it would remove a manual process out business are doing currently.

[Nonsensely]

We tried everything we could to get the Alteryx DB2 connection going, but constant fail. 

We created a 64-bit ODBC connection using iAccess drivers and connect via Alteryx Generic ODBC.  Had trouble writing to iSeries fromt these connections for over a year, and FINALLY found an old post yesterday that said to add:  'transaction isolation=none' to the connection string, and VOILA! It works.  TOTAL game changer for our site.