IDS Forum

Re: Issue in transactions with informix using DRDA

Posted By: Sathyanesh Krishnan
Date: Tuesday, 8 November 2016, at 7:07 p.m.

In Response To: Issue in transactions with informix using DRDA (ROGER VILCA)

Hi Roger,

In case of distributed transaction, application don't need to
explicitly start a transaction. The Ambient Transaction will be
initiated by the Distributed Transaction Manager based on the
transaction scope criteria what application has specified.

That means you may put all heterogeneous database operations
wrapped in a TransactionScope block as shown in the pseudo code.

using(TransactionScope scope =3D new TransactionScope())
{

// Perform all DB Operation related to this Transaction here

// Eg:

// Withdraw $100 from oracle=5Fdb

// Deposit $100 to Informix=5Fdb

scope.Complete();

// If there is any unhandled exception by now

// then the Transaction Manager will initiate rollback for all DBs.

// If there is no unhandled exception within this block then

// the Transaction Manager will proceed for phase-1 of the P2C.

// If Phase-1 got OK from all participant servers, then the

// Transaction Manager will initiate phase-2 with commit

// else it will be a rollback for all DBs
}

FYI:
You may specify appropriate TransactionScopeOption based on
your application need.
For example:
using (TransactionScope scope =3D new TransactionScope(
TransactionScopeOption.Required, timeout))
{}

Please see MSDN for more information related to TransactionScope.

Regards,
Satyan

Advisory Software Engineer
R&D - IBM Information Management Division
11200 Lakeview, Lenexa, KS 66219
Tel: 913 599 8792 (T/L: 337-8792)

From: "ROGER VILCA" <rvilca@luzdelsur.com.pe>
To: ids@iiug.org
Date: 11/08/2016 05:20 PM
Subject: Issue in transactions with informix using DRDA [38091]
Sent by: ids-bounces@iiug.org

Hi,
My environment:
Informix 11.70 FC8W1 in HPUX 11iv3 64 bits.

Anybody knows how to use a distributed transaction in Informix with DRDA.

For example:

Instance 1, database db1:

create table "informix".rviweb
(

a integer
);

Instance 2, database db2:

create table "informix".rvidbtecnica
(

a integer
);

We use synonyms on remote instances:

Instance 1, database db1:
create synonym "informix".rvidbtecnica for
db2@instance2:"informix".rvidbtecnica;

code fragment (VB):

Public Sub RunSqlTransaction(ByVal myConnString As String)

Dim myConnection As New IfxConnection(myConnString) -- Execute in db1

myConnection.Open()

Dim myCommand As IfxCommand =3D myConnection.CreateCommand()

Dim myTrans As IfxTransaction

myTrans =3D myConnection.BeginTransaction()

myCommand.Connection =3D myConnection

myCommand.Transaction =3D myTrans

myCommand.CommandText =3D "Insert into rviweb values (1)"

myCommand.ExecuteNonQuery()

myCommand.CommandText =3D "Insert into rvidbtecnica values (1)"

myCommand.ExecuteNonQuery()

myTrans.Rollback()

End Sub

The problem is that the rollback is only for the first query, but the
second
query is committed.

Do you provide any clues about this behaviour with informix with DRDA and
IBM
Data Server Provider for .NET ?

Thanks in advance,
Roger

***************************************************************************=
****

Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• Issue in transactions with informix using DRDA
  ROGER VILCA -- Tuesday, 8 November 2016, at 6:19 p.m.
  • Re: Issue in transactions with informix using DRDA
    Marcus Haarmann -- Tuesday, 8 November 2016, at 6:45 p.m.
    • Re: Issue in transactions with informix using DRDA
      ROGER VILCA -- Tuesday, 8 November 2016, at 6:53 p.m.
      • Re: Issue in transactions with informix using DRDA
        Art Kagel -- Tuesday, 8 November 2016, at 7:05 p.m.
        • Re: Issue in transactions with informix using DRDA
          ROGER VILCA -- Tuesday, 8 November 2016, at 7:16 p.m.
          • Re: Issue in transactions with informix using DRDA
            Art Kagel -- Tuesday, 8 November 2016, at 7:37 p.m.
            • Re: Issue in transactions with informix using DRDA
              ROGER VILCA -- Wednesday, 9 November 2016, at 11:39 a.m.
              • Re: Issue in transactions with informix using DRDA
                Sathyanesh Krishnan -- Wednesday, 9 November 2016, at 2:16 p.m.
                • Re: Issue in transactions with informix using DRDA
                  ROGER VILCA -- Wednesday, 9 November 2016, at 2:42 p.m.
                  • Re: Issue in transactions with informix using DRDA
                    Sathyanesh Krishnan -- Friday, 11 November 2016, at 1:10 p.m.
        • Re: Issue in transactions with informix using DRDA
          Marcus Haarmann -- Friday, 11 November 2016, at 9:49 a.m.
          • Re: Issue in transactions with informix using DRDA
            Art Kagel -- Friday, 11 November 2016, at 11:21 a.m.
          • Re: Issue in transactions with informix using DRDA
            ROGER VILCA -- Monday, 14 November 2016, at 11:52 a.m.
            • Re: Issue in transactions with informix using DRDA
              Sathyanesh Krishnan -- Monday, 14 November 2016, at 12:10 p.m.
            • Re: Issue in transactions with informix using DRDA
              Sathyanesh Krishnan -- Monday, 14 November 2016, at 12:19 p.m.
  • Re: Issue in transactions with informix using DRDA
    Sathyanesh Krishnan -- Tuesday, 8 November 2016, at 7:07 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.