JDBC Receiver structure for DB2/AS400

[yeshuaq]

Hi,

Can you please suggest me to define the JDBC receiver structure for DB2/AS400.

i had created the data type as shown below and while i tested it shows error as given below

Error when executing statement for table/stored proc. TESTLBRY.FILE123 (structure StatementName): com.ibm.as400.access.AS400JDBCSQLSyntaxErrorException: [SQL0104] Token ) was not valid. Valid tokens: DAY PATH YEAR LABEL MONTH OPTION RESULT DEFAULT CONNECTION.

and in logs SQL query shows as: INSERT INTO TESTLBRY.FILE123 () VALUES ()

[peter_karg]

We didn't use the update function, but I used some blog's and there is a good one which may help you also. https://blogs.sap.com/2017/11/10/sap-pi-proxy-to-jdbc-scenario/

hope this is helpful for you.

Regards Peter

[peter_karg]

Hi yeshua,
up to now I had only an insert into our AS/400 DB2 without a key. And the XML from mapping test looks like this:

This is working without problems.
Regards
Peter

[yeshuaq]

Hi Peter,

Thank you for response and i had compared the my mapping receiver structure with the data provided by you and i hope the target structure defined in my mapping is fine.

now issue is while process data with ACTION as 'INSERT', i am able to load data to DB table.

but when i send data using action as UPDATE_INSERT, the Query in receiver channel shows as

update TESTLBRY.FILE123 SET Field11=100, Filed12=a, Field13=b, Field14=c, Field15=0 WHERE (Field11=101 and Filed12=aa and Field13=bb and Field14=cc and Field15=0) OR (Field11=102 and Filed12=aaa and Field13=bbb and Field14=ccc and Field15=0) OR (Field11=103 and Filed12=aaaa and Field13=bbbb and Field14=cccc and Field15=0) OR (Field11=104 and Filed12=axc and Field13=bxc and Field14=cxc and Field15=0) OR (Field15=1) OR (Field15=1) OR (Field15=1) OR (Field15=1) OR (Field15=1)

So if you notice above query generated in run time, it shows as update query and first record shows after SET and 2nd record to 5th record are taken as WHERE condition and also key fields in to where condition.

i think the correct query should shows as all the 5 records to update and key fields should take as WHERE condition.

Please suggest how to correct it

Thank you. YQ