When I launch a MTS transaction against an Informix 9.40UC2 or
10.0UC3, the transaction appears to be completed in the client side,
but in the server a global transaction with flags --X--G never ends,
blocking other operations over locked resources with errrors like:

Error -2147467259->[Informix][Informix ODBC Driver][Informix]Could not
do a physical-order read to fetch next row.

I have tried with Informix ODBC clients 2.90 and 2.80

¿Does anybody knows the right parameter configuration?

Thanks in advance

i may be off here ...
afaik MTS does not have xa in it.
so all is done in different sessions and if session a does an update
(no commit yet) and
session b wants to fetch that row it hits a lock.

may be there is something in the release notes of csdk or the engine
which says something about MTS and wether it works or not.
if i recall correctly sorry mate it wont work

Superboer.






Francisco schreef:

> When I launch a MTS transaction against an Informix 9.40UC2 or
> 10.0UC3, the transaction appears to be completed in the client side,
> but in the server a global transaction with flags --X--G never ends,
> blocking other operations over locked resources with errrors like:
>
> Error -2147467259->[Informix][Informix ODBC Driver][Informix]Could not
> do a physical-order read to fetch next row.
>
> I have tried with Informix ODBC clients 2.90 and 2.80
>
> ¿Does anybody knows the right parameter configuration?
>
> Thanks in advance

Hello,

DTC does use XA when cordinating transactions on Informix database
servers. Sometimes, you'll get multiple branches and the Microsoft
stuff expects locks to be shared amoung those branches -- referred to
as "tightly coupled". IDS does now have support for tightly coupled
transactions, and so any problems you encounter needs to be reported to
Informix Tech Support.

IDS is reported to have started supporting tightly coupled transactions
in 9.40.xC2, but things don't really work too good until you get to
9.40.xC6 and later engines (which includes all the 10.00 releases).
The IDS 7.31 engine does not support tightly coupled transactions, so
7.31 engine users need to plan to upgrade if they are going to support
a wide range of Windows applications using transactions.

Now that we've got what works and what doesn't out of the way...

First, you want to make sure that you have connection pooling enabled
for the Informix ODBC driver. You do this in the ODBC Data Source
Administrator, the "Connection Pooling" tab. Double click on the
Informix ODBC driver, and make sure "Pool Connections to this driver"
is chosen.

However, my guess is that you're running into ODBC bug 174816. This
bug was discovered last fall and has since been fixed, but not in time
to be included in any release of CSDK. Therefore, you're going to
want to contact Informix Tech Support and request a patch (you'll want
to get CSDK 2.90.TC4X9).

The way DTC works is that it releases control back to the application
before the transaction is completed. Under just the right
circumstances, the application could then progress and unenlist the
ODBC connection from DTC before the transaction was completed. When
this happened, the ODBC driver was no longer in the state to deliver XA
messages and failed to return an error on the COMMIT request. This
resulted in a prepared transaction remaining on the database server
that would only go away by running some other utility to get rid of it
(not even bouncing the server will get rid of the transaction).

If you get the patch and the problem doesn't go away, please call
Informix Tech Support. I've yet to figure out an easy way to diagnose
problems when DTC is involved!



Please note, OLEDB also has some outstanding problems -- if MDAC 2.8
SP1 is used. The IBM development group is currently working on those
problems, but no patch is yet available for OLEDB.

Reece



Superboer wrote:
> i may be off here ...
> afaik MTS does not have xa in it.
> so all is done in different sessions and if session a does an update
> (no commit yet) and
> session b wants to fetch that row it hits a lock.
>
> may be there is something in the release notes of csdk or the engine
> which says something about MTS and wether it works or not.
> if i recall correctly sorry mate it wont work
>
> Superboer.
>
>
>
>
>
>
> Francisco schreef:
>
> > When I launch a MTS transaction against an Informix 9.40UC2 or
> > 10.0UC3, the transaction appears to be completed in the client side,
> > but in the server a global transaction with flags --X--G never ends,
> > blocking other operations over locked resources with errrors like:
> >
> > Error -2147467259->[Informix][Informix ODBC Driver][Informix]Could not
> > do a physical-order read to fetch next row.
> >
> > I have tried with Informix ODBC clients 2.90 and 2.80
> >
> > ¿Does anybody knows the right parameter configuration?
> >
> > Thanks in advance

Hello,

I'll second what Reece has said here, he's right on the problem. Also,
maybe you should look at IDS defect number 172850 related to your
issue(s). Please take a look at your online log for reference to Long
Transactions due to XA or distributed transactions. Got burned a
couple of times by this one so we switched to using _Open_Query_
instead of Distributed Transactions.

Hope this helps...
Newel

Reece wrote:
> Hello,
>
> DTC does use XA when cordinating transactions on Informix database
> servers. Sometimes, you'll get multiple branches and the Microsoft
> stuff expects locks to be shared amoung those branches -- referred to
> as "tightly coupled". IDS does now have support for tightly coupled
> transactions, and so any problems you encounter needs to be reported to
> Informix Tech Support.
>
> IDS is reported to have started supporting tightly coupled transactions
> in 9.40.xC2, but things don't really work too good until you get to
> 9.40.xC6 and later engines (which includes all the 10.00 releases).
> The IDS 7.31 engine does not support tightly coupled transactions, so
> 7.31 engine users need to plan to upgrade if they are going to support
> a wide range of Windows applications using transactions.
>
> Now that we've got what works and what doesn't out of the way...
>
> First, you want to make sure that you have connection pooling enabled
> for the Informix ODBC driver. You do this in the ODBC Data Source
> Administrator, the "Connection Pooling" tab. Double click on the
> Informix ODBC driver, and make sure "Pool Connections to this driver"
> is chosen.
>
> However, my guess is that you're running into ODBC bug 174816. This
> bug was discovered last fall and has since been fixed, but not in time
> to be included in any release of CSDK. Therefore, you're going to
> want to contact Informix Tech Support and request a patch (you'll want
> to get CSDK 2.90.TC4X9).
>
> The way DTC works is that it releases control back to the application
> before the transaction is completed. Under just the right
> circumstances, the application could then progress and unenlist the
> ODBC connection from DTC before the transaction was completed. When
> this happened, the ODBC driver was no longer in the state to deliver XA
> messages and failed to return an error on the COMMIT request. This
> resulted in a prepared transaction remaining on the database server
> that would only go away by running some other utility to get rid of it
> (not even bouncing the server will get rid of the transaction).
>
> If you get the patch and the problem doesn't go away, please call
> Informix Tech Support. I've yet to figure out an easy way to diagnose
> problems when DTC is involved!
>
>
>
> Please note, OLEDB also has some outstanding problems -- if MDAC 2.8
> SP1 is used. The IBM development group is currently working on those
> problems, but no patch is yet available for OLEDB.
>
> Reece
>
>
>
> Superboer wrote:
> > i may be off here ...
> > afaik MTS does not have xa in it.
> > so all is done in different sessions and if session a does an update
> > (no commit yet) and
> > session b wants to fetch that row it hits a lock.
> >
> > may be there is something in the release notes of csdk or the engine
> > which says something about MTS and wether it works or not.
> > if i recall correctly sorry mate it wont work
> >
> > Superboer.
> >
> >
> >
> >
> >
> >
> > Francisco schreef:
> >
> > > When I launch a MTS transaction against an Informix 9.40UC2 or
> > > 10.0UC3, the transaction appears to be completed in the client side,
> > > but in the server a global transaction with flags --X--G never ends,
> > > blocking other operations over locked resources with errrors like:
> > >
> > > Error -2147467259->[Informix][Informix ODBC Driver][Informix]Could not
> > > do a physical-order read to fetch next row.
> > >
> > > I have tried with Informix ODBC clients 2.90 and 2.80
> > >
> > > ¿Does anybody knows the right parameter configuration?
> > >
> > > Thanks in advance