Detect if we are inside a transaction

We have a library function that needs to run inside a transaction, so we would like to check if we are inside a transaction, and if not, start a transaction, do the work and commit. If there is a transaction already, we don't do anything. Currently we do a "begin work" and check if that gives -535, but I wonder if there is a more elegant solution. I searched and came across some old posts on the subject from the newsgroup, for instance one answer which I distilled into this: select sysmaster:bitval(txptab.flags,"0x2") from sysmaster:systxptab txptab, sysmaster:sysrstcb rstcb where rstcb.address = txptab.owner and sid=DBINFO("sessionid"); But for the current session this always gives 1, I suspect that is because of the implicit transaction that is started for each statement. It works if I check other sessions, but that can't be used for my case. I'm on 14.10.FC5, if that makes a difference. ------------------------------ Øyvind Gjerstad Developer/Architect PostNord AS ------------------------------ #Informix

Hi Øyvind, looks like what you're after isn't really exposed anywhere through SMI (nor onstat): the simple fact whether an explicit BEGIN WORK had been issued in a given session. What does exist is mi_transaction_state(), so following UDR created on top of that might do the trick: create function txstate() returns int external name '(mi_transaction_state)' language C;With this you'd get: for an implicit transaction: select txstate() txstate; txstate 2 for an explicit transaction: begin work; select txstate() txstate; txstate 1 and for 'not in transaction': execute function txstate();(expression) 0 HTH, Andreas ------------------------------ Andreas Legner ------------------------------

The solution I use is for every app to use library functions to BEGIN WORK and to COMMIT or ROLLBACK WORK that check a pair of global variables to see if the database supports transactions and whether it is in ANSI mode or not (they are set after connecting to the database). That BEGIN function sets a global flag indicating that a transaction has begun. The COMMIT and ROLLBACK functions check it to see if a transaction is active. You can do the same. Another option is for the function to open or maintain a second connection, switch connections, use that second session to check the transaction status of the main session using the query you posted, then switch back to the main session to do what's needed. That will only work if at least one of the sessions is not a shared memory connection type (can't have two shared memory connections in the same process). Managing multiple connections, at least in ESQL/C, is not difficult code. Dostats, for example, uses two connections when you ask it to process all user databases, one to get the list of databases that also manages a second connection to whichever database it is currently processing to do the actual work. My dbcopy utility uses two connections, one to the source database and a second to the target database to save the overhead of connecting to the target through the source server as a remote connection. If the databases are in separate instances this is much faster. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Thank you, Andreas, that worked! ------------------------------ Øyvind Gjerstad Developer/Architect PostNord AS ------------------------------

Hi Andreas, nice and smart solution! Thank you very much. Henri ------------------------------ Henri Cujass leolo IT, CTO Germany IBM Champion 2021 ------------------------------