IDS Forum

Re: autonomous transaction

Posted By: Art Kagel
Date: Thursday, 12 May 2011, at 3:27 p.m.

In Response To: Re: autonomous transaction (Cesar Inacio Martins)

In 4GL it's doable. You have to bend over a bit, because officially 4GL
doesn't support multiple connections, but you can do it by wrapping the
CONNECT TO ... AS <connection_name>; and SET CONNECTION TO <connection_name>
statements in 4GL callable C functions.

As long as you don't want to do this from within a single database
connection (like in a stored procedure) it's the same as for Oracle.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.

On Thu, May 12, 2011 at 2:51 PM, Cesar Inacio Martins <
cesar_inacio_martins@yahoo.com.br> wrote:

> Thanks for the detailed answer Art!
>
> In this case the language is IBM 4GL and the developer ask me if Informix
> have
> this resource because he is working in one specific situation where this
> can
> help a lot.
>
> So... we will try imagine a workaround over the application. well well.. if
> Informix have some resources like oracle/db2... world database would be
> "informix rules" ...
>
> ________________________________
> De: Art Kagel <art.kagel@gmail.com>
> Para: ids@iiug.org
> Enviadas: Quinta-feira, 12 de Maio de 2011 12:22
> Assunto: Re: autonomous transaction [23676]
>
> The Informix equivalent of an autonomous transaction can only be done from
> a
> host language program such as an ESQL/C, 4GL, Java/JDBC, C/ODBC,
> Perl/DBD-DBI application. You have open multiple separate named connections
> to the database, one for each transaction context you need, including in
> each connection the WITH CONCURRENT TRANSACTIONS clause. Then you can
> freely switch between the connections with the CONNECT TO <connection
> name>;
> statement, even in the middle of an active transaction, perform work
> including beginning and committing/rolling back independent transactions,
> and switch back to the other connection(s) to continue the interrupted
> transaction. This is easy and fast and my table copy utility, dbcopy, does
> this to keep separate read and insert connections to possibly separate
> server instances (though both could be connected to the same instance)
> which
> actually makes it perform faster than if it was fetching from one cursor
> and
> inserting into another on a single connection to the server(s). Note that
> you cannot have more than one shared memory type connection from a single
> process. To have multiple connections at most one connection can be shared
> memory and any other connections must use a stream-pipe or networked
> connection type. All connections can be a type other than shared memory,
> but at most one can be shared memory.
>
> There is nothing like the Oracle "PRAGMA AUTONOMOUS_TRANSACTION;"
> statement,
> so you cannot have multiple contexts within a stored procedure, nor between
> executions of separate stored procedures (nested or otherwise), nor do most
> SQL execution utilities manage the multiple connections required to take
> advantage of the technique described above (though you could certainly
> write
> one of your own using one of the host languages available). However, there
> are no restrictions as to what you can do within any of the several
> connection contexts while parallel transactions are in effect.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Thu, May 12, 2011 at 7:37 AM, Cesar Inacio Martins <
> cesar_inacio_martins@yahoo.com.br> wrote:
>
> > Hi,
> >
> > Someone implement some technique to work with Informix like autonomous
> > transaction from Oracle?
> > I believed this is possible only opening 2 connections , but there is no
> > way
> > to share the same context (cursors, temp, etc)
> > Anyway... there is something what approach this technique?
> >
> >
> >
> >
>
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --bcaec547ca0f5bff3604a315c3e9
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--20cf307cfbca49669004a3192d78

Messages In This Thread

• autonomous transaction
  Cesar Inacio Martins -- Thursday, 12 May 2011, at 7:37 a.m.
  • Re: autonomous transaction
    Art Kagel -- Thursday, 12 May 2011, at 11:22 a.m.
    • Re: autonomous transaction
      Cesar Inacio Martins -- Thursday, 12 May 2011, at 2:51 p.m.
      • Re: autonomous transaction
        Jonathan Leffler -- Thursday, 12 May 2011, at 3:06 p.m.
        • Re: autonomous transaction
          Art Kagel -- Thursday, 12 May 2011, at 3:29 p.m.
      • Re: autonomous transaction
        Art Kagel -- Thursday, 12 May 2011, at 3:27 p.m.
        • Re: autonomous transaction
          Mike Aubury -- Friday, 13 May 2011, at 4:07 a.m.
  • Re: autonomous transaction
    Marco Greco -- Friday, 13 May 2011, at 4:39 a.m.
    • Re: autonomous transaction
      Art Kagel -- Friday, 13 May 2011, at 6:05 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.