All:

While tracking down another issue, I noticed several sessions reporting error -255 (Not In Transaction). These seemed out of place, because the sessions I saw showing that error were not doing COMMIT or ROLLBACK, which as far as I can tell should be the only statements that would generate that error.

I put a trap on -255 with onmode -I and got _hundreds_ of hits. I saw INSERT statements, UPDATE statements and even SELECT statements. Never a COMMIT or ROLLBACK.

The tables in question don't have triggers on them, so there wouldn't be a COMMIT/ROLLBACK in there.So I'm stumped. I'm left to think that perhaps the engine is reporting the wrong error.

Has anyone else seen this?

We're on 14.10.FC7W1 on CentOS 7. I'm seeing the errors coming from both JDBC clients and legacy ODBC clients (the latter running a VERY old 32-bit SDK) alike.

I'm gathering information to open a ticket but wanted to check to see if anyone else had encountered this.

TIA:

- TJG

------------------------------
TOM GIRSCH
------------------------------

#Informix

Agreed. You should only see a -255 error for the following statements:

• COMMIT WORK
• ROLLBACK WORK
• SAVEPOINT
• RELEASE SAVEPOINT
• ROLLBACK WORK TO SAVEPOINT

I cannot think of any others.

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

Original Message:
Sent: 6/16/2022 10:32:00 AM
From: Marcus Haarmann
Subject: RE: Odd -255 Errors

Do you have logging enabled on the db? Maybe running in ANSI Mode?

Marcus 

Original Message:
Sent: 6/16/2022 10:55:00 AM
From: Marcus Haarmann
Subject: RE: Odd -255 Errors

From what you are telling us, the application side is working correctly ?

So the sqlerror would be somewhere in the background, maybe not respected

or not even forwarded to the application.

Did you try to switch on sqlexplain for a specific session

onmode -Y <sessionId> 2 (or 1)

Check the resulting sqlexplain out file what really happens. Any query done 

by a trigger or whatever reason should be listed there.

Maybe this can give you some hints.

MARCUS HAARMANN

Original Message:
Sent: 6/16/2022 10:32:00 AM
From: Marcus Haarmann
Subject: RE: Odd -255 Errors

Do you have logging enabled on the db? Maybe running in ANSI Mode?

Marcus 

This might well be some internal 'commit' being executed 'just in case', with its success, or any error, being ignored on purpose, so nothing related to the success of an actual statement, and nothing reported back to the client.
Whether such error code should make it into sql traces, or if this could be avoided in all cases, might be an interesting question, but as long as such error doesn't reach the application, I'd not consider it as reported.  Also in onstat (-g ses <sid>) it's not uncommon to see SQL/ISAM codes that nobody else would notice, and there's a lot of such just-in-case operations as well as deliberate testing for certain conditions, by means of provoking an error, internally that also would never reach any surface ... unless someone starts trapping these errors, getting a lot of false positives and ending up more confused than (s)he was.

------------------------------
Andreas Legner
------------------------------

I have encountered the same issue with the DBI perl driver a couple of years back. When it established a connection, it would do a query to retrieve some server info and at the end, it would always issue a "ROLLBACK WORK" ( I was told it was because of ANSI databases ). If it got a -255, then it was not an ANSI database, and would proceed in accordance. The application never saw the -255 error.

Luis Marques

------------------------------
Luis Marques
------------------------------

Hmm, Luis may have it. You might just be seeing code that is using a COMMIT or ROLLBACK to determine whether or not the database is in ANSI mode or non-logged. In a non-logged database you would get -256 instead of -255 and no error in ANSI mode database. Better to just check the sqlca.sqlerrd fields after connecting, but it works.

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

Original Message:
Sent: Fri June 17, 2022 04:43 AM
From: Luis Marques
Subject: Odd -255 Errors

I have encountered the same issue with the DBI perl driver a couple of years back. When it established a connection, it would do a query to retrieve some server info and at the end, it would always issue a "ROLLBACK WORK" ( I was told it was because of ANSI databases ). If it got a -255, then it was not an ANSI database, and would proceed in accordance. The application never saw the -255 error.

Luis Marques

------------------------------
Luis Marques
------------------------------

Original Message:
Sent: 6/17/2022 7:06:00 AM
From: Art Kagel
Subject: RE: Odd -255 Errors

Hmm, Luis may have it. You might just be seeing code that is using a COMMIT or ROLLBACK to determine whether or not the database is in ANSI mode or non-logged. In a non-logged database you would get -256 instead of -255 and no error in ANSI mode database. Better to just check the sqlca.sqlerrd fields after connecting, but it works.

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

OH SURE! Make a reasonable deduction and burst all our bubbles! 

B^}

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------

Original Message:
Sent: Fri June 17, 2022 08:54 AM
From: TOM GIRSCH
Subject: Odd -255 Errors

But in that case, wouldn't "ROLLBACK WORK" show as the current statement?

Original Message:
Sent: 6/17/2022 7:06:00 AM
From: Art Kagel
Subject: RE: Odd -255 Errors

Hmm, Luis may have it. You might just be seeing code that is using a COMMIT or ROLLBACK to determine whether or not the database is in ANSI mode or non-logged. In a non-logged database you would get -256 instead of -255 and no error in ANSI mode database. Better to just check the sqlca.sqlerrd fields after connecting, but it works.

Art

------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
------------------------------


Original Message:
Sent: Fri June 17, 2022 04:43 AM
From: Luis Marques
Subject: Odd -255 Errors

I have encountered the same issue with the DBI perl driver a couple of years back. When it established a connection, it would do a query to retrieve some server info and at the end, it would always issue a "ROLLBACK WORK" ( I was told it was because of ANSI databases ). If it got a -255, then it was not an ANSI database, and would proceed in accordance. The application never saw the -255 error.

Luis Marques

------------------------------
Luis Marques

I found to run the dbexport dbname also show the "-255 - Not in transaction." error message when the database with log mode. My Informix version is 14.10.FC7W1 on AIX 7.2

I also test the dbexport on 14.10.FC6 and 14.10.FC8 on AIX 7.2. It is not show the "-255 - Not in transaction." error message.

------------------------------
Philip Yeh
------------------------------

As far as I know, the problem only happens in 14.10.FC7W1, it does not reproduce in 14.10.FC8 and earlier versions.

Most likely a cosmetic error, should not affect the dbexport output.

IT40507: DBEXPORT ENDS WITH -255 WHEN EXPORTING DATABASE WITH LOGGING

------------------------------
KHON CHIEK TAN
------------------------------