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 IndexMultiphase CommitIs WebSphere MQ 7.0 compatible with SQL Server 2008

Post new topicReply to topic Goto page 1, 2  Next
Is WebSphere MQ 7.0 compatible with SQL Server 2008 View previous topic :: View next topic
Author Message
paragupt
PostPosted: Wed Sep 01, 2010 10:56 am Post subject: Is WebSphere MQ 7.0 compatible with SQL Server 2008 Reply with quote

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
View user's profile Send private message Send e-mail Yahoo Messenger
Vitor
PostPosted: Wed Sep 01, 2010 11:04 am Post subject: Re: Is WebSphere MQ 7.0 compatible with SQL Server 2008 Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 25680
Location: Ohio, 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
View user's profile Send private message
paragupt
PostPosted: Wed Sep 01, 2010 11:29 am Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Yahoo Messenger
mdfaraz
PostPosted: Thu Sep 02, 2010 1:26 pm Post subject: Reply with quote

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
View user's profile Send private message
Vitor
PostPosted: Thu Sep 02, 2010 2:28 pm Post subject: Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 25680
Location: Ohio, 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
View user's profile Send private message
mdfaraz
PostPosted: Thu Sep 02, 2010 2:57 pm Post subject: Can Committed DB transaction be rolled back Reply with quote

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
View user's profile Send private message
bruce2359
PostPosted: Thu Sep 02, 2010 3:15 pm Post subject: Reply with quote

Poobah

Joined: 05 Jan 2008
Posts: 8388
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.
_________________
There are two types of people in this world:
1) Those that can extrapolate from incomplete data
Back to top
View user's profile Send private message
Vitor
PostPosted: Thu Sep 02, 2010 4:45 pm Post subject: Re: Can Committed DB transaction be rolled back Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 25680
Location: Ohio, 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
View user's profile Send private message
bruce2359
PostPosted: Thu Sep 02, 2010 5:05 pm Post subject: Reply with quote

Poobah

Joined: 05 Jan 2008
Posts: 8388
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.
_________________
There are two types of people in this world:
1) Those that can extrapolate from incomplete data
Back to top
View user's profile Send private message
mdfaraz
PostPosted: Fri Sep 03, 2010 7:01 am Post subject: Some sample in .NET?? Reply with quote

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

Code:
 :roll:
Back to top
View user's profile Send private message
Vitor
PostPosted: Fri Sep 03, 2010 7:10 am Post subject: Re: Some sample in .NET?? Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 25680
Location: Ohio, 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
View user's profile Send private message
mdfaraz
PostPosted: Fri Sep 03, 2010 7:19 am Post subject: jdbc Reply with quote

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
View user's profile Send private message
bruce2359
PostPosted: Fri Sep 03, 2010 7:21 am Post subject: Reply with quote

Poobah

Joined: 05 Jan 2008
Posts: 8388
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.
_________________
There are two types of people in this world:
1) Those that can extrapolate from incomplete data
Back to top
View user's profile Send private message
Vitor
PostPosted: Fri Sep 03, 2010 7:23 am Post subject: Re: jdbc Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 25680
Location: Ohio, 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
View user's profile Send private message
fjb_saper
PostPosted: Fri Sep 03, 2010 11:12 am Post subject: Re: jdbc Reply with quote

Grand Poobah

Joined: 18 Nov 2003
Posts: 19991
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
View user's profile Send private message Send e-mail
Display posts from previous:
Post new topicReply to topic Goto page 1, 2  Next Page 1 of 2

MQSeries.net Forum IndexMultiphase CommitIs WebSphere MQ 7.0 compatible with SQL Server 2008
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.