ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Commit impossible via javaComputeNode

Post new topic  Reply to topic
 Commit impossible via javaComputeNode « View previous topic :: View next topic » 
Author Message
ventura
PostPosted: Wed Sep 14, 2016 6:09 am    Post subject: Commit impossible via javaComputeNode Reply with quote

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
View user's profile Send private message
mqjeff
PostPosted: Wed Sep 14, 2016 6:41 am    Post subject: Reply with quote

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
View user's profile Send private message
ventura
PostPosted: Wed Sep 14, 2016 7:08 am    Post subject: Reply with quote

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
View user's profile Send private message
martinb
PostPosted: Wed Sep 14, 2016 7:49 am    Post subject: Reply with quote

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
View user's profile Send private message
ventura
PostPosted: Thu Sep 15, 2016 4:41 am    Post subject: Reply with quote

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
View user's profile Send private message
ventura
PostPosted: Tue Sep 27, 2016 4:02 am    Post subject: Reply with quote

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
View user's profile Send private message
mqjeff
PostPosted: Tue Sep 27, 2016 9:02 am    Post subject: Reply with quote

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
View user's profile Send private message
leron8
PostPosted: Wed Oct 26, 2016 7:25 am    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Commit impossible via javaComputeNode
Jump to:  



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
Protected by Anti-Spam ACP
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.