topic Re: tDB2Output autocommit in Talend Studio

tDB2Output autocommit

Anonymous — Sat, 16 Nov 2024 13:39:08 GMT

Hello,
I have a big parent job that initiates a DB2 connection for the output, with the option 'Use or register a shared DB Connection'.
The child jobs use the same connection (also checked the 'Use or....' option), and autocommit == false (unchecked).
The aim is to have a big transaction.
Each child job uses that connection with a tDB2_Output.
Even if I get an error, all the data that has been transferred by the child jobs that already run, is committed... (there is a big tDB2_rollback if any error, in the parent job).
Cause : each tDB2_Output closes its preparedStatement at the end of the job. This causes a flush (commit)....
Bug or per se?
Cheers,

==> the answer (for JIRA?) : in the tDB2Output_begin.javajet :

conn_<%=cid%>.setAutoCommit(false);

==> Java documentation : when autocommit() is called, an automatic commit is made before changing this 'flag'
==> I change the javaet on my machine to first check : if already false, I do nothing...
Please raise a JIRA issue

Re: tDB2Output autocommit

Anonymous — Wed, 09 Dec 2009 12:20:35 GMT

In fact, you don't find this in the doc, but you need to watch the J2SE code (5 & 6) of the Connection object
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Connection.html

Re: tDB2Output autocommit

Anonymous — Wed, 09 Dec 2009 12:29:55 GMT

Here I am again...
The code that sets the autocommit is in the section
/**
* start
*/
...of the generated code.
Where is it stated?
Appearently, this is a common feature for connections, but I don't find chere to change this.... (not in the connection javajet...).
Please, tell me

Re: tDB2Output autocommit

Anonymous — Wed, 09 Dec 2009 13:20:30 GMT

( deleted -- see next post)

Re: tDB2Output autocommit

Anonymous — Thu, 10 Dec 2009 09:36:46 GMT

As usual, I answer my own question. This should becomea JIRA issue as it affects (I think) every type of DB connection :
...\TOS-All-r33000-V3.2.2\workspace\.JETEmitters\src\org\talend\designer\codegen\translators\databases\db2\TDB2ConnectionBeginJava.java
line 250

    stringBuffer.append(TEXT_39);
    stringBuffer.append(cid);
    stringBuffer.append(TEXT_40);
    stringBuffer.append(setAutoCommit);
    stringBuffer.append(TEXT_41);

has to be replaced to chan ge the flag only if needed

//GDU : no setAutocommit(xx)    if not needed (already xx)
stringBuffer.append(NL);
stringBuffer.append("//no setAutocommit if not needed (e.g. : already false + setAutocommit(false) ==> commits the changes and re-set the flag (J2SE)");
stringBuffer.append(NL);
stringBuffer.append("if (conn_" + cid + ".getAutoCommit() != " + (setAutoCommit?"true":"false") + "){" + NL);
stringBuffer.append(TEXT_39);
stringBuffer.append(cid);
stringBuffer.append(TEXT_40);
stringBuffer.append(setAutoCommit);
stringBuffer.append(TEXT_41);
stringBuffer.append("\t}");
stringBuffer.append(NL);
//end of changes

The transaction then works like acharm accross 25 child jobs

Re: tDB2Output autocommit

Anonymous — Thu, 10 Dec 2009 09:57:58 GMT

see bug http://www.talendforge.org/bugs/view.php?id=10571