| Author |
Message
|
| wyatt |
 Posted: Wed Jun 01, 2011 10:54 am Post subject: JDBC Type 4 Driver - DB Commit |
 |
|
Voyager
Joined: 28 Nov 2004
Posts: 76
|
Does the JDBC Type 4 driver allow the application to control when a DB commit statement is executed?
The DB connection seems to have a single parameter - "MB_TRANSACTION_AUTO"
Connection conn = mbNode.getJDBCType4Connection("DB2", JDBC_TransactionType.MB_TRANSACTION_AUTO);
Thanks |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Wed Jun 01, 2011 10:59 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
http://www-01.ibm.com/support/docview.wss?uid=swg27020522
| Quote: |
For non-XA transactions, you can create a JDBC type 4 connection from a broker to all the database servers that are supported.
For JDBC XA connections, the following restriction applies:
1.Only DB2 and Oracle are supported.
For JDBC type 4 connections to DB2, the following restriction applies:
1.You need either DB2 Version 9.1 with Fix Pack 3 or later, or DB2 9.5.
JDBC type 4 drivers are not supplied with WebSphere Message Broker; obtain these files from your database vendor. The following drivers are supported:
•IBM DB2 Driver for JDBC and SQLJ Version 9.1 and 9.5
•IBM solidDB JDBC Driver 2.0
•Microsoft SQL Server 2005 JDBC driver 1.1 for use with Microsoft SQL Server 2005
•Microsoft SQL Server 2005 JDBC driver 1.2 for use with Microsoft SQL Server 2008
•Oracle 10gR2 or 11gR1 JDBC Driver
•Sybase jConnect for JDBC 6.05
•IBM Informix JDBC 3.50.JC3 |
http://publib.boulder.ibm.com/infocenter/wmbhelp/v6r1m0/topic/com.ibm.etools.mft.doc/ac30494_.htm
| Quote: |
| •MB_TRANSACTION_AUTO defines the level of transaction coordination that is required by the node. Only this value is supported, and indicates that the coordination in the node is inherited from the coordination configured at message flow level. |
| Quote: |
| •Do not include code that performs a COMMIT or a ROLLBACK function. |
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Jun 01, 2011 11:17 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
This only assumes that you use the methods provided by Broker to get a JDBC connection.
If you otherwise use "normal" JDBC to create your own connection, then you can control the unit of work as you like - but only from within your own code.
So if you want Broker to control the transaction, ask Broker for the connection. If you want to control the transaction yourself, create the connection yourself. |
|
| Back to top |
|
|
| wyatt |
| Posted: Wed Jun 01, 2011 11:18 am Post subject: |
|
|
Voyager
Joined: 28 Nov 2004
Posts: 76
|
I just noticed the setAutoCommit() method; I think that will do the trick.
Thanks
e.g.
Connection conn = mbNode.getJDBCType4Connection("DB2", JDBC_TransactionType.MB_TRANSACTION_AUTO);
conn.setAutoCommit(true); |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Jun 01, 2011 12:24 pm Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
@mqjeff
It may be worthwhile to amend the documentation since the statement "Do not include code that performs COMMIT..." is especially clear. You have indicated that there are really two schemes: one for auto-handle by WMB and one for manually handle by other code.
An area of improvement in the product would be a maturation of the compensating transaction scheme. Currently, no capabilities exist for JCNs to automatically have compensating transactions applied when downstream nodes throw an exception that requires a rollback.
We also need clarity in the documentation on when it is appropriate to apply compensating transactions since some exceptions do not necessarily imply a rollback.
Also a great topic for an updated developerWorks article on XA with WMB.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Jun 01, 2011 12:31 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
The best way to get the documentation amended is to find the relevant page in the InfoCenter and then clickety-clicky the little button at the bottom that says "feedback".
The main difficulty with having a JCN notified of the need for a compensating transaction is that the node downstream that throws the exception may not be executing with the JVM at all - it may be executing inside the C++ runtime instead.
This is a similar issue to trying to use an ESQL DECLARE HANDLER to catch exceptions thrown in a downstream JCN. Although that may actually be more useful than trying to catch exceptions from the propagate() method.
If I had to design a system that would ensure that a JCN would be notified of the need for a compensating transaction... I believe I would handle the jdbc connection inside a Java singleton or other kind of persistent object and then wire another JCN off the Catch terminal of the input node that started the flow.
Have you considered using a singleton for this? |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Wed Jun 01, 2011 12:33 pm Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Jun 01, 2011 12:37 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| lancelotlinc wrote: |
| Very excellent choice, sir. Singletons would provide the needful. |
I did something similar when trying to access Oracle from Broker v6.0 running on VSE. Couldn't use ODBC... |
|
| Back to top |
|
|
|
|
