| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
| Is WebSphere MQ 7.0 compatible with SQL Server 2008 |
« View previous topic :: View next topic » |
| Author |
Message
|
| paragupt |
 Posted: Wed Sep 01, 2010 10:56 am Post subject: Is WebSphere MQ 7.0 compatible with SQL Server 2008 |
 |
|
Newbie
Joined: 13 Jun 2007
Posts: 9
|
Hello All,
Is WebSphere MQ 7.0 compatible with SQL Server 2008 for a 2-phase commit? I have seen infocentre but no where it is mentioning anything related to SQL Server. Also if it is compatible, can you please send me a sample code for 2-phase commit for the same and also about how to do a XA configuration in MQ for SQL Server 2008.
Thanks a Lot!!! |
|
| Back to top |
|
 |
| Vitor |
| Posted: Wed Sep 01, 2010 11:04 am Post subject: Re: Is WebSphere MQ 7.0 compatible with SQL Server 2008 |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| paragupt wrote: |
| Is WebSphere MQ 7.0 compatible with SQL Server 2008 for a 2-phase commit? I have seen infocentre but no where it is mentioning anything related to SQL Server. |
Why would WMQv7 care if it's SQL Server 2008 that's part of an XA transaction as opposed to any other XA compliant piece of software?
| paragupt wrote: |
| if it is compatible, can you please send me a sample code for 2-phase commit for the same |
Same as the samples for any other 2-phase commit. Available in the usual places, including your installation.
(If they're not there, speak to whoever holds your installation media)
| paragupt wrote: |
| how to do a XA configuration in MQ for SQL Server 2008. |
Same as configuring WMQ to participate in any XA transaction.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| paragupt |
| Posted: Wed Sep 01, 2010 11:29 am Post subject: |
|
|
Newbie
Joined: 13 Jun 2007
Posts: 9
|
| Thanks a lot! Will try to do XA coding for SQL Server and if I face any problems then will post another message here. |
|
| Back to top |
|
|
| mdfaraz |
| Posted: Thu Sep 02, 2010 1:26 pm Post subject: |
|
|
Novice
Joined: 31 Mar 2010
Posts: 19
Location: Houston, TX
|
@Vitor,
I too am struggling to implement 2 way commit. I want to know what exactly I can achieve by using 2 way commit supported by MQ.
I have a VB.NET program and SQL server DB.
I do the following operations
MQ Read
DB tran1begin
DB tran1commit
DB tran2begin
DBtran2commit
MQ commit
In assence I want these steps to be a unit of work. So if an MQ error happens, the DB tran1 and tran2 should roll back. and if a DB error occurs, MQ tran should roll back WITHOUT THE APP GIVING AN EXPLICT ROLLBACK.
I cam across the IBM redbook for .NET(http://www.redbooks.ibm.com/redbooks/pdfs/sg247012.pdf) page 309 onwards, that describe using Microsoft DTC to achieve unit of work.
My understanding is, at any point, a loss to MQ connection/call to qmgr.BackOut, will cause all DB operations to roll back.
Please correct me as I feel I miss some thing in my understanding. mainly, how a Transaction Coordinator can roll back changes on DB.
Thanks |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Sep 02, 2010 2:28 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mdfaraz wrote: |
| I want to know what exactly I can achieve by using 2 way commit supported by MQ. |
Exactly what you're describing below.
| mdfaraz wrote: |
| Please correct me as I feel I miss some thing in my understanding. |
Nope, I think you've got it.
| mdfaraz wrote: |
| mainly, how a Transaction Coordinator can roll back changes on DB. |
Because that's what it does.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mdfaraz |
| Posted: Thu Sep 02, 2010 2:57 pm Post subject: Can Committed DB transaction be rolled back |
|
|
Novice
Joined: 31 Mar 2010
Posts: 19
Location: Houston, TX
|
The thing that is kind of doubtful for me is how can a committed transaction be rolled back. As in my scenario, if I commit DB tran1, then while performing DB tran2, if I lose connectivity to MQ, how will the commited tran1 roll back?
I am assuming its the 2 phase commit does this, but that would also mean that inspite of giving tran1.commit, my record in tran1 is still locked until mq commit occurs. |
|
| Back to top |
|
|
| bruce2359 |
| Posted: Thu Sep 02, 2010 3:15 pm Post subject: |
|
|
Poobah
Joined: 05 Jan 2008
Posts: 9469
Location: US: west coast, almost. Otherwise, enroute.
|
A unit of work (UofW) begins at a consistent state, and ends at a consistent state.
WMQ apps can coordinate a local unit of work (UofW), in which only WMQ resources are being changed. The app uses MQCMIT to commit, or MQBACK to backout these changes.
Alternatively, WMQ apps can be the coordinator of a global unit of work (UofW), a UofW that encompasses both WMQ and DB resource changes. Midrange WMQ apps use the MQBEGIN call to state that this app will be the coordinator, and will issue an MQCMIT to commit changes, or MQBACK to back out changes.
Another possibility is the use of an XA-compliant transaction manager (coordinator) for XA-compliant resource managers (DBs, WMQ). XA-compliancy is well-documented. Search Google.
In XA, a transaction manager (coordinator) takes on responsibility for ensuring that all the resource changes in the UofW are either successfully hardened (committed) OR backed out.
The application issues the MQCMIT/MQBACK (or vendor-supplied equivalent) to direct the transaction manager to then direct the resource managers to commit resource changes. If any of the resource managers cannot successfully commit its changes, then the transaction manager will direct all resource managers to back out all resource changes.
| Quote: |
| The thing that is kind of doubtful for me is how can a committed transaction be rolled back. |
A committed transaction (UofW) cannot be rolled back. A partially completed transaction (UofW) is not committed, and must be rolled back to ensure that all resources are in the consistent state that they were in at the beginning of the UofW.
Using your scenario, the XA-compliant transaction manager will recognize that some portion of the UofW did not complete, and will cause all resource changes to back out.
_________________
I like deadlines. I like to wave as they pass by.
ב''ה
Lex Orandi, Lex Credendi, Lex Vivendi. As we Worship, So we Believe, So we Live. |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Sep 02, 2010 4:45 pm Post subject: Re: Can Committed DB transaction be rolled back |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mdfaraz wrote: |
| As in my scenario, if I commit DB tran1, then while performing DB tran2, if I lose connectivity to MQ, how will the commited tran1 roll back? |
That's the point you're missing. If you issue a commit in this scenario you will commit everything. So to achieve the effect you want you need to perform DB tran 1 but not issue a commit.
| mdfaraz wrote: |
| I am assuming its the 2 phase commit does this, but that would also mean that inspite of giving tran1.commit, my record in tran1 is still locked until mq commit occurs. |
Yes. That transaction is either in the same unit of work & uncommitted, or it's committed & unlocked.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| bruce2359 |
| Posted: Thu Sep 02, 2010 5:05 pm Post subject: |
|
|
Poobah
Joined: 05 Jan 2008
Posts: 9469
Location: US: west coast, almost. Otherwise, enroute.
|
| Quote: |
| As in my scenario, if I commit DB tran1, then while performing DB tran2, if I lose connectivity to MQ, how will the commited tran1 roll back? |
This implies that you have multiple UofWs - not one big UofW.
A short definition of a Unit of Work: all the activities that comprise a single transaction, and are treated as a single transaction for the purposes of commit and/or back out of all the activities. A UofW begins at a consistent state (all resources are in synchronization). A UofW ends in a consistent state (once again, all resources are in synchronization. Please research Google for unit of work and xa-compliant.
As an example, lets define a business transaction includes these four related activities. Your application does:
1) an MQGET of a message,
2) a DB2 insert,
3) a DB2 update,
4) an MQPUT of a message
The driving application - the one that issues the MQI and SQL commands - decides that the business transaction has succeeded, and issues the MQCMIT. Then the transaction manager will direct WMQ and DB2 to commit all of these resource updates.
Alternatively, if the driving application decides that one or more of the activities have not succeeded (or for any other reason), then the app will issue the MQBACK.
At the completion of both MQCMIT and MQBACK, the DB2 and MWQ resources are once again in a consistent state.
Notice that I did not refer to 1) an MQGET as a transaction. It is not a transaction; rather, it is an activity that comprises the complete business transaction.
If your application is coded to commit following the MQGET, and commit following the DB2 insert, and commit following the DB2 update, and commit following the MQPUT, then you have four transactions. And, more importantly, as your scenario suggests, you cannot roll back a committed transaction.
_________________
I like deadlines. I like to wave as they pass by.
ב''ה
Lex Orandi, Lex Credendi, Lex Vivendi. As we Worship, So we Believe, So we Live. |
|
| Back to top |
|
|
| mdfaraz |
| Posted: Fri Sep 03, 2010 7:01 am Post subject: Some sample in .NET?? |
|
|
Novice
Joined: 31 Mar 2010
Posts: 19
Location: Houston, TX
|
Thanks everyone for your replies, you have cleared most of my doubts. So I believe I should not commit my transactions explicitly but let the queue manager(the coordinator of resource managers in my case) attempt to commit which will either all succeed or all fail.
I would love to see samples in .net on how to achieve atleast a simple scenario of UOW
Read MQ message
Update DB
Put MQ Message
Commit
In a Java sample(mqdb2log.java) I saw something like
COM.ibm.db2.jdbc.DB2XADataSource myDataSource =
new COM.ibm.db2.jdbc.DB2XADataSource();
myDataSource.setDatabaseName(dbName);
jdbcConn = qMgr.getJDBCConnection(myDataSource);
qMgr.begin()
myQueue.put(myMessage, pmo)
stmt.executeUpdate("update a set b=c")
if execute successful
qMgr.commit()
else
qMgr.backout();
In this code, I dont see an explict DB commit or rollback so I assume the qmgr.commit/backout will commit/rollback the change made by the update query.
Looks pretty straightforward for Java/DB2, but in .NET I wasn't able to do something similar as my dll amqmdnet.dll, which has the QueueManager class, does not expose any getJDBCConnection function and my qmgr.begin fails with 'MQRC_HCONN_ERROR'
The .NET Application programming red book has long descriptions on how this is done which is vastly different from the Java/DB2 sample. I will try to do this but would love to get some sample or link to the "Programming\MTS" in Websphere contact admin center(which the redbook tells to look for, but I dont find it anywhere )
Any help will be greatly appreciated as I mis-estimated my effort to the management after looking at the Java example and expecting pretty similar coding on .NET end
|
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Sep 03, 2010 7:10 am Post subject: Re: Some sample in .NET?? |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mdfaraz wrote: |
| In this code, I dont see an explict DB commit or rollback so I assume the qmgr.commit/backout will commit/rollback the change made by the update query. |
This is why you've gone to the trouble of using a transaction coordinator. So that the transaction (committing or rolling back) is coordinated with the various parties involved.
| mdfaraz wrote: |
| Looks pretty straightforward for Java/DB2, but in .NET I wasn't able to do something similar as my dll amqmdnet.dll, which has the QueueManager class, does not expose any getJDBCConnection function and my qmgr.begin fails with 'MQRC_HCONN_ERROR' |
Why would a queue manager class have any kind of JDBC support? That's nothing to do with the queue manager.
The connection error also has nothing to do with database connections, transactions, or anything else in this thread. It has, however, been discussed at length in many other threads (it's probably the most common reason code an application will receive).
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mdfaraz |
| Posted: Fri Sep 03, 2010 7:19 am Post subject: jdbc |
|
|
Novice
Joined: 31 Mar 2010
Posts: 19
Location: Houston, TX
|
Thanks Vitor,
but what do u make of the qmgr.getJDBCConnection(myDataSource) statement in the java code. Obviously its a method of Queue Manager which is probably available in Java dlls of mq but not .net equivalents.
Anyways, I am not greatly concerned with that, that example is on IBM's website and also talks about XA switch files (again thats provided for Sybase/Oracle/DB2 but not SQL server) - See http://www.ibm.com/developerworks/websphere/library/techarticles/0601_ritchie/0601_ritchie.html
Where do I look for a .NET sample similar to the one I have for Java?
Appreciate your time!! |
|
| Back to top |
|
|
| bruce2359 |
| Posted: Fri Sep 03, 2010 7:21 am Post subject: |
|
|
Poobah
Joined: 05 Jan 2008
Posts: 9469
Location: US: west coast, almost. Otherwise, enroute.
|
| Quote: |
| In this code, I dont see an explict DB commit or rollback so I assume the qmgr.commit/backout will commit/rollback the change made by the update query |
No, you don't see any explicit DB commit. Why? In an XA-compliant transaction, the resource-manager (DB, WMQ) commit/backout code is part of the XA-compliant transaction manager code - not the end-user application.
All you app needs to do is issue the language-appropriate commit/backout; and then the transaction manager drives each of the resource-managers (DB, WMQ, etc.) to do their individual commit/backout.
_________________
I like deadlines. I like to wave as they pass by.
ב''ה
Lex Orandi, Lex Credendi, Lex Vivendi. As we Worship, So we Believe, So we Live. |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Sep 03, 2010 7:23 am Post subject: Re: jdbc |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mdfaraz wrote: |
| what do u make of the qmgr.getJDBCConnection(myDataSource) statement in the java code. |
Nothing. My Java skills are non-existent. Someone else will be along with an answer in a moment.
| mdfaraz wrote: |
| Where do I look for a .NET sample similar to the one I have for Java? |
Same place you look for Java samples.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Sep 03, 2010 11:12 am Post subject: Re: jdbc |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| mdfaraz wrote: |
| what do u make of the qmgr.getJDBCConnection(myDataSource) statement in the java code. |
Here the qmgr will act as transaction manager and as such needs to know about the XA resource, hence this statement.
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
|
  |
Goto page 1, 2 Next |
Page 1 of 2 |
|
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
|
|
|
|
