|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
 |
|
Commit impossible via javaComputeNode |
« View previous topic :: View next topic » |
Author |
Message
|
ventura |
Posted: Wed Sep 14, 2016 6:09 am Post subject: Commit impossible via javaComputeNode |
|
|
Newbie
Joined: 14 Sep 2016 Posts: 4
|
Hello,
Via a JavaComputeNode, I would like to insert rows in a SybaseIQ DB.
However, the insert is never commit even if I can see that the prepareStatement.executeUpdate(); returns 1. And if I force the commit with a connection.commit(), the commit is performed, but I should not force it like this, isn't it?.
No sql exception is throw at this point (nor after in fact).
But I get another error (found in the stderr file of my execution group/integration server) :
Code: |
Exception in thread "Thread-49" 2016-09-14 15:05:17.602 73 <com.ibm.broker.plugin.MbRecoverableException class:com.ibm.broker.jdbctype4.localtrxn.JDBCType4SinglePhaseTrxnHandlerErrors@bc6ab915 method:JDBCType4SinglePhaseTrxnTable::commit source:BIPmsgs key:6268 >
2016-09-14 15:05:17.603 73 at com.ibm.broker.jdbctype4.localtrxn.JDBCType4SinglePhaseTrxnHandlerErrors.throwException(JDBCType4SinglePhaseTrxnHandlerErrors.java:216)
2016-09-14 15:05:17.603 73 at com.ibm.broker.jdbctype4.localtrxn.JDBCType4SinglePhaseTrxnTable.commit(JDBCType4SinglePhaseTrxnTable.java:1012)
2016-09-14 15:05:17.603 73 at com.ibm.broker.jdbctype4.localtrxn.JDBCType4SinglePhaseTrxnTable.accessThreadToTransactionTable(JDBCType4SinglePhaseTrxnTable.java:247)
2016-09-14 15:05:17.604 73 at com.ibm.broker.jdbctype4.localtrxn.JDBCType4SinglePhaseTrxnHandler.commitTrxnBranch(JDBCType4SinglePhaseTrxnHandler.java:570) |
When I debug the flow, once it finishes, I can see that JDBCType4SinglePhaseTrxnHandler.commitTrxnBranch() is prompted but as I don't have the source of this DBCType4SinglePhaseTrxnHandler, I cannot investigate.
I checked what is the BIPmsgs key:6268 in the ibm site and it seems to be a connection issue.
Here is the JDBCProviders I defined in broker :
Code: |
SybaseIQ_DWH
connectionUrlFormat='jdbc:sybase:Tds:[serverName]:[portNumber]/[databaseName]?user=[user]&password=[password]'
connectionUrlFormatAttr1=''
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='DEV_OPUS_EOD'
databaseSchemaNames='useProvidedSchemaNames'
databaseType='Sybase'
databaseVersion='default_Database_Version'
description='default_Description'
environmentParms='default_none'
jarsURL='/app/sybase/jConnect-7_0/classes'
jdbcProviderXASupport='false'
maxConnectionPoolSize='0'
portNumber='9950'
securityIdentity='SybaseIQ_DWH_Cred'
serverName='vmd-sybiq-04'
type4DatasourceClassName='com.sybase.jdbc4.jdbc.SybXADataSource'
type4DriverClassName='com.sybase.jdbc4.jdbc.SybDriver'
|
and the securityIdentity is defined like this :
Code: |
mqsisetdbparms $BROKERNAME -n jdbc::SybaseIQ_DWH_Cred -u user -p password |
I don't understand what is wrong. I found some similar post where the problem was a changed connectionUrlFormat but it should not be the case here (I didn't change it anyway). Maybe it is because I try to connect a Sybase IQ DB? But I use a similar url when I try to connect this DB via a tool like Squirrel and I have no problem to insert anything with it...
So, can you help me please?
Many thanks in advance,
E.V |
|
Back to top |
|
 |
mqjeff |
Posted: Wed Sep 14, 2016 6:41 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008 Posts: 17447
|
What's your *code* ?
That's where you're likely doing something wrong. _________________ chmod -R ugo-wx / |
|
Back to top |
|
 |
ventura |
Posted: Wed Sep 14, 2016 7:08 am Post subject: |
|
|
Newbie
Joined: 14 Sep 2016 Posts: 4
|
Hi,
Thanks for your quick reply.
Here is my code :
Code: |
...
Connection conn = null;
PreparedStatement prepareStatement = null;
try {
conn = getJDBCType4Connection("SybaseIQ_DWH", JDBC_TransactionType.MB_TRANSACTION_AUTO);
String updateFXRate = "INSERT INTO dbo.dlz_esb_fx_rate (type, currency_in, currency_out, scenario, bid, ask, date_from, date_to, updated_on)"
+ " VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
prepareStatement = conn.prepareStatement(updateFXRate);
prepareStatement.setString(1, type);
prepareStatement.setString(2, currency_in);
prepareStatement.setString(3, currency_out);
prepareStatement.setString(4, scenario);
prepareStatement.setDouble(5, bid);
prepareStatement.setDouble(6, ask);
prepareStatement.setTimestamp(7, new Timestamp(date_from.getTime()));
prepareStatement.setTimestamp(8, new Timestamp(date_to.getTime()));
prepareStatement.setTimestamp(9, new Timestamp(updated_on.getTime()));
int nbUpdate = prepareStatement.executeUpdate();
//conn.commit();
outMessage.getRootElement().getFirstElementByPath("XMLNSC").detach();
MbElement xmlRoot = outMessage.getRootElement().createElementAsLastChild(MbXMLNSC.PARSER_NAME);
MbElement response = xmlRoot.createElementAsLastChild(MbElement.TYPE_NAME, "insertFXRateResponse", null);
response.setNamespace(NAMESPACE_MED);
response.createElementAsLastChild(MbElement.TYPE_NAME, "status", "SUCCESS").setNamespace(NAMESPACE_MED);
} catch (SQLException e) {
e.printStackTrace();
throw e;
} finally {
try {
if (prepareStatement != null) {
prepareStatement.close();
}
} catch (SQLException e) {
e.printStackTrace();
throw e;
}
}... |
E.V |
|
Back to top |
|
 |
martinb |
Posted: Wed Sep 14, 2016 7:49 am Post subject: |
|
|
Master
Joined: 09 Nov 2006 Posts: 210 Location: UK
|
Given that IIB is managing the transaction for JDBC connections obtained via getJDBCType4Connection() you should not call "commit()" as noted in the topic "Interacting with databases by using the JavaCompute node"
The BIP6268 error has two inserts, the second providing the detail of the exception that lead to the connection issue. You should obtain the full details of this BIP msg - from the system event log, or enabling IIB trace etc.
Note that IIB is performing the commit as part of the overall message flow transaction, so the commit will only occur when your flow logic is successfully completed and control returns to the input node. What logic do you have after the Java Compute node in this case?.
You could consider testing with a simple In-JCN-Out flow to help isolate the issue.
HTH |
|
Back to top |
|
 |
ventura |
Posted: Thu Sep 15, 2016 4:41 am Post subject: |
|
|
Newbie
Joined: 14 Sep 2016 Posts: 4
|
Hello,
I checked the trace and I asked to the admin of the database to check the logs in their side. I will come back to you once I know more.
Here is the message I got :
2016-09-15 13:36:04.293030 19391 Error BIP2695E: Thread number '19391' with name ''dwh.reference.marketinfo.mediation.ProcessRequest(DWH.REF.MARKETINFO.MED.REQUEST)'' failed to commit a resource of type ''JDBC''. Other resources have been committed.
When committing a transaction a locally coordinated resource has failed to commit. The transaction state for this resource might not be consistent with the other resources in the transaction. Commits have been issued on other resources involved in this transaction.
Examine the transactional state of the failed resource and manually resolve the transaction. Review previous messages to find out why the error occurred.
2016-09-15 13:36:04.293055 19391 RecoverableException BIP6265E: A problem was encountered when committing a transaction with the JDBC Datasource ''SybaseIQ_DWH''.
The node was unable to commit a database transaction with DataSource ''SybaseIQ_DWH'', which represents a JDBC provider. This problem can be caused by network problems, or errors on the database server. The transaction might be left in an "in-doubt" state, which you might have to resolve manually.
Check the database logs to determine the reason for the problem, and refer to the documentation supplied by the database provider. Contact your IBM support center if you are unable to determine the cause of the problem.
E.V |
|
Back to top |
|
 |
ventura |
Posted: Tue Sep 27, 2016 4:02 am Post subject: |
|
|
Newbie
Joined: 14 Sep 2016 Posts: 4
|
Hello,
I partially solved the problem by adding "SET CHAINED OFF" in the end of my query.
However I continue to get the error
Code: |
...Exception in thread "Thread-49" 2016-09-14 15:05:17.602 73 <com.ibm.broker.plugin.MbRecoverableException class:com.ibm.broker.jdbctype4.localtrxn.JDBCType4SinglePhaseTrxnHandlerErrors@bc6ab915 method:JDBCType4SinglePhaseTrxnTable::commit source:BIPmsgs key:6268 >... |
I continue to investigate to see if it not linked to something in a library we use.
EV |
|
Back to top |
|
 |
mqjeff |
Posted: Tue Sep 27, 2016 9:02 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008 Posts: 17447
|
You shouldn't call commit against a broker type 4 jdbc connection.
You should review how an IIB flow commits transactions, and use those methods. _________________ chmod -R ugo-wx / |
|
Back to top |
|
 |
leron8 |
Posted: Wed Oct 26, 2016 7:25 am Post subject: |
|
|
 Novice
Joined: 25 Oct 2016 Posts: 20 Location: MX
|
You could try verifying the value set here
Code: |
conn.getAutoCommit(); |
It should be set to false and leaving this configurable service property the same: "jdbcProviderXASupport='false'" |
|
Back to top |
|
 |
|
|
 |
|
Page 1 of 1 |
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
|
|
|