autonomous transaction

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?

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

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.

On Thu, May 12, 2011 at 11:51, 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" ... > I4GL since 7.30 (IIRC) has supported the CONNECT statement, including WITH CONCURRENT TRANSACTIONS. > ________________________________ > 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. [...] -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --90e6ba6e8a4046505304a318e4af

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

I always forget that. Cesar, Jonathan is of course right, you can do this directly in 4GL code now. 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 3:06 PM, Jonathan Leffler < jonathan.leffler@gmail.com> wrote: > On Thu, May 12, 2011 at 11:51, 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" ... > > > > I4GL since 7.30 (IIRC) has supported the CONNECT statement, including WITH > CONCURRENT TRANSACTIONS. > > > ________________________________ > > 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. [...] > > -- > Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> > Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org > "Blessed are we who can laugh at ourselves, for we shall never cease to be > amused." > > --90e6ba6e8a4046505304a318e4af > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --bcaec54865d29ac0c404a31933fa

For completeness - this is implemented in Aubit4GL is a neater way - you can prefix SQL statements with the connection : USE SESSION connectionname FOR .... eg.. USE SESSION s_sess1 FOR SELECT * INTO lv_rec.* FROM sometab where pk=1 USE SESSION s_sess2 FOR INSERT INTO newtab VALUES(lv_rec.*) and it does the switching automatically... On 12 May 2011 20:27, Art Kagel <art.kagel@gmail.com> wrote: > 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 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151747af3e0b6f7304a323ccf8

On 12/05/11 12:37, Cesar Inacio Martins 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. > > My scripting tool SQSL (see sig) can do exactly that, even against different database engines, for instance CONNECT TO "ifmx_pri" SOURCE ifmx; CONNECT TO "db2inst1" SOURCE db2cli; SELECT * FROM tab1 CONNECTION "ifmx_pri"; { retrieve from other connection } SELECT * FROM tab2; { retrieve from current connection db2inst1 } you can of course switch current connection with the SET CONNECTION and you can even SELECT col1, col2 FROM tab2 CONNECTION "db2inst1" INSERT INTO tab1 VALUES(?,?) CONNECTION "ifmx_pri" This you can do as either a stand alone tool, or you can link the interpreter in 4gl, Aubit-4gl or any esql/c application. If that sounds interesting, you can download SQSL from my web site, below. PS: Art - whenever you think the answer is going to be "this can only be done by a host language", then bear in mind that SQSL does it as a scripting language... -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

Marco, you say: PS: Art - whenever you think the answer is going to be "this can only be done by a host language", then bear in mind that SQSL does it as a scripting language... I also said that "most SQL processing tools don't do it", obviously SQSL does. ;-) 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 Fri, May 13, 2011 at 4:39 AM, Marco Greco <marco@4glworks.com> wrote: > On 12/05/11 12:37, Cesar Inacio Martins 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. > > > > > > My scripting tool SQSL (see sig) can do exactly that, even against > different > database engines, for instance > > CONNECT TO "ifmx_pri" SOURCE ifmx; > CONNECT TO "db2inst1" SOURCE db2cli; > SELECT * FROM tab1 CONNECTION "ifmx_pri"; { retrieve from other connection> } > SELECT * FROM tab2; { retrieve from current connection db2inst1 }> > you can of course switch current connection with the SET CONNECTION and you > can even > > SELECT col1, col2 FROM tab2 CONNECTION "db2inst1" INSERT INTO tab1 > VALUES(?,> ?) CONNECTION "ifmx_pri" > > This you can do as either a stand alone tool, or you can link the > interpreter > in 4gl, Aubit-4gl or any esql/c application. > If that sounds interesting, you can download SQSL from my web site, below. > > PS: Art - whenever you think the answer is going to be "this can only be > done > by a host language", then bear in mind that SQSL does it as a scripting > language... > -- > Ciao, > Marco > > ______________________________________________________________________________ > Marco Greco /UK /IBM Standard disclaimers apply! > > Structured Query Scripting Language http://www.4glworks.com/sqsl.htm > 4glworks http://www.4glworks.com > Informix on Linux http://www.4glworks.com/ifmxlinux.htm > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf307f34200ab08904a32573f1