topic Re: Update Or Insert on AS400 in Talend Studio

Update Or Insert on AS400

Anonymous — Sat, 16 Nov 2024 13:47:29 GMT

Hello,
I have an Excel File with 3 columns. The first and second columns are the both the key, the third is a simple text.
I would like to insert or update on a table on AS400.
In order to do that, I put my Excel File like an Input Files. I define the first and the second columns like a key.
I link my ExcelFile to a tMap and the tMap with tAS40Output.
I map the column of excel files to the table of AS400.
The job run and generate an error when i try to insert Data
for exemple
1 a toto
2 b tata
2 c titi
when I look in my table I find
1 a toto
2 b tata
but not 2 c titi

I put on As400Output ,in settings Action on Date : "Update or Insert" or "Insert or Upadte"
I select two columns as a key and set them are updatable
Could you help me please? Have you got an idea to resolve my problem?
PS: If i use a simple key (one column) that's works perfectly.
Thanks

Re: Update Or Insert on AS400

Anonymous — Wed, 02 Sep 2009 12:29:19 GMT

provide detailed description of the error

Re: Update Or Insert on AS400

Anonymous — Wed, 02 Sep 2009 12:33:03 GMT

"Error"
Exception in component tAS400Output_1
java.lang.RuntimeException: Execution failure, there might be an error in your SQL syntax.
at transfert_exelversas400.testupdate_0_1.TestUpdate.tFileInputDelimited_2Process(TestUpdate.java:672)
at transfert_exelversas400.testupdate_0_1.TestUpdate.runJobInTOS(TestUpdate.java:879)
at transfert_exelversas400.testupdate_0_1.TestUpdate.main(TestUpdate.java:788)

Re: Update Or Insert on AS400

Anonymous — Wed, 02 Sep 2009 12:50:24 GMT

Check as400 schema and reset db types,
or look at the code with the red mark where the update string is built.

Re: Update Or Insert on AS400

Anonymous — Wed, 02 Sep 2009 12:59:50 GMT

I've checked it but still the error.
there no red mark where the update string is built
this is a part of the code genereted
String update_tAS400Output_1 = "UPDATE "
+ "testupdate"
+ " SET id = ?,nom = ?,prenom = ?,adresse = ? WHERE id = ? AND nom = ?";
java.sql.PreparedStatement pstmtUpdate_tAS400Output_1 = conn_tAS400Output_1
.prepareStatement(update_tAS400Output_1);
String insert_tAS400Output_1 = "INSERT INTO " + "testupdate"
+ " (id,nom,prenom,adresse) VALUES (?,?,?,?)";

Re: Update Or Insert on AS400

Anonymous — Wed, 02 Sep 2009 18:59:28 GMT

- Run with trace option enable to see if incoming data is somewhat strange
- try with different data input source
- debug the job

Re: Update Or Insert on AS400

Anonymous — Wed, 02 Sep 2009 19:51:08 GMT

Anyway, sometimes I've found myself in the same situation and it is a pity that the sqlexception is masked in the code when a useful hint can be given...
I see that in many sql related components talend upcast to Exception ( losing some methods )... but in the as400 case they just drop the exception throwing a runtimeexception instead.... there is some inconsistency in handling sql exception in all talend components...
anyway I've filed a bug/request, would be useful for all:
http://www.talendforge.org/bugs/view.php?id=8972

Re: Update Or Insert on AS400

Anonymous — Thu, 03 Sep 2009 10:44:58 GMT

Thanks a lot for your answer.
I tried all your propositions but the error still, I think there is some incompatibility with AS400 !!
I see the Talend does not manage well the AS400

Re: Update Or Insert on AS400

Anonymous — Thu, 03 Sep 2009 10:57:15 GMT

I use as400 also, and yes there are still some quirks...
sometimes I see that there are random problems if the file is being use is under journaling or not...
sometimes I had to force in the connection "additonal parameters" in talend this string
"prompt=false;transaction isolation=none"
to be able to work
For all, the additional JDBC parameters can be found at
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/index.htm?info/rzahh/javadoc/JDBCProperties.html

Would be nice to have exposed in the GUI such options during connection configuration.

Re: Update Or Insert on AS400

Anonymous — Thu, 03 Sep 2009 11:07:30 GMT

I have a question about the execution of Talend in AS400
When I run a job for loading a data in a table and I look in AS400 with "wrkactjob", Talend can't exeed the 20-23 % UC
Is it your case ? or do you have any idea ?
Thanks

Re: Update Or Insert on AS400

Anonymous — Thu, 03 Sep 2009 11:33:31 GMT

don't look at proc share the same you look at a winpc scheduler... as400 has to provide many guarantees and accounts for timeslices in different way to allow stable multiuser experience... and chiefly talend stuff is quite I/O bound...