Informix

Hi. We use INFROMIX 14. Yesterday we discovered that there was no record in the database for one operation for May 27, in the software logs this operation was completed successfully, but in the database it was completely missing, as if it never happened. Is there a way to see if there were any transaction rollbacks for a specific time?And how to enable logging of transaction rollbacks?

Hi Alexander

trivial, but first check that the database is not in "with no log" mode: if it is "with no log", you won't see any transaction

Second, if you db is logged, use the onlog utility  (The onlog utility

)

Reading the output is not plain simple, but with some tricks you can fully understand all the transactions, row by row

Hope this helps

Eric

------------------------------
[eric] [Vercelletto] []
[Founder]
[kandooerp.org]
[Pont l'Abbé] [France]
[+33 626 52 50 68]

Disclaimer: My own opinions are my own opinions and do not reflect on 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.

Original Message:
Sent: Tue June 04, 2024 05:17 AM
From: Alexander Ivanov
Subject: Inspect transactions log

Hi. We use INFROMIX 14. Yesterday we discovered that there was no record in the database for one operation for May 27, in the software logs this operation was completed successfully, but in the database it was completely missing, as if it never happened. Is there a way to see if there were any transaction rollbacks for a specific time?And how to enable logging of transaction rollbacks?

------------------------------
Alexander Ivanov
------------------------------

Thank you. I was able to read the log file and found no transactions at this time.
Immediately after this operation the server was rebooted and in the logs I see17:20:18 Physical Recovery Started at Page (2:469382).17:20:19 Physical Recovery Complete: 976 Pages Examined, 244 Pages Restored.17:20:19 Logical Recovery Started.17:20:19 5 recovery worker threads will be started.17:20:22 Logical Recovery has reached the transaction cleanup phase.17:20:22 Logical Recovery Complete.	  0 Committed, 0 Rolled Back, 0 Open, 0 Bad LocksI'm interested in Physical Recovery Complete: 976 Pages can this overwrite data?

------------------------------
Alexander Ivanov

Original Message:
Sent: Tue June 04, 2024 05:29 AM
From: Eric Vercelletto
Subject: Inspect transactions log

Hi Alexander

trivial, but first check that the database is not in "with no log" mode: if it is "with no log", you won't see any transaction

Second, if you db is logged, use the onlog utility  (The onlog utility

)

Reading the output is not plain simple, but with some tricks you can fully understand all the transactions, row by row

Hope this helps

Eric

------------------------------
[eric] [Vercelletto] []
[Founder]
[kandooerp.org]
[Pont l'Abbé] [France]
[+33 626 52 50 68]

Disclaimer: My own opinions are my own opinions and do not reflect on 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.

Original Message:
Sent: Tue June 04, 2024 05:17 AM
From: Alexander Ivanov
Subject: Inspect transactions log

Hi. We use INFROMIX 14. Yesterday we discovered that there was no record in the database for one operation for May 27, in the software logs this operation was completed successfully, but in the database it was completely missing, as if it never happened. Is there a way to see if there were any transaction rollbacks for a specific time?And how to enable logging of transaction rollbacks?

------------------------------
Alexander Ivanov
------------------------------

Thank you. I was able to read the log file and found no transactions at this time.
Immediately after this operation the server was rebooted and in the logs I see17:20:18 Physical Recovery Started at Page (2:469382).17:20:19 Physical Recovery Complete: 976 Pages Examined, 244 Pages Restored.17:20:19 Logical Recovery Started.17:20:19 5 recovery worker threads will be started.17:20:22 Logical Recovery has reached the transaction cleanup phase.17:20:22 Logical Recovery Complete.	  0 Committed, 0 Rolled Back, 0 Open, 0 Bad LocksI'm interested in Physical Recovery Complete: 976 Pages can this overwrite data?

------------------------------
Alexander Ivanov

Original Message:
Sent: Tue June 04, 2024 05:29 AM
From: Eric Vercelletto
Subject: Inspect transactions log

Hi Alexander

trivial, but first check that the database is not in "with no log" mode: if it is "with no log", you won't see any transaction

Second, if you db is logged, use the onlog utility  (The onlog utility

)

Reading the output is not plain simple, but with some tricks you can fully understand all the transactions, row by row

Hope this helps

Eric

------------------------------
[eric] [Vercelletto] []
[Founder]
[kandooerp.org]
[Pont l'Abbé] [France]
[+33 626 52 50 68]

Disclaimer: My own opinions are my own opinions and do not reflect on 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.

Original Message:
Sent: Tue June 04, 2024 05:17 AM
From: Alexander Ivanov
Subject: Inspect transactions log

Hi. We use INFROMIX 14. Yesterday we discovered that there was no record in the database for one operation for May 27, in the software logs this operation was completed successfully, but in the database it was completely missing, as if it never happened. Is there a way to see if there were any transaction rollbacks for a specific time?And how to enable logging of transaction rollbacks?

------------------------------
Alexander Ivanov
------------------------------

Hi. We use INFROMIX 14. Yesterday we discovered that there was no record in the database for one operation for May 27, in the software logs this operation was completed successfully, but in the database it was completely missing, as if it never happened. Is there a way to see if there were any transaction rollbacks for a specific time?And how to enable logging of transaction rollbacks?

Alexander:

Just to wrap up Eric's post, rollbacks are a;ways logged as long as the database is not an UNLOGGED database.

That said, once the transaction is COMMITed there is no way to roll it back. Is it possible that the application has a code path that might not actually issue a COMMIT WORK statement and it only "thought" that the transaction was completed successfully? 

Art

Hi. We use INFROMIX 14. Yesterday we discovered that there was no record in the database for one operation for May 27, in the software logs this operation was completed successfully, but in the database it was completely missing, as if it never happened. Is there a way to see if there were any transaction rollbacks for a specific time?And how to enable logging of transaction rollbacks?

Alexander:

Just to wrap up Eric's post, rollbacks are a;ways logged as long as the database is not an UNLOGGED database.

That said, once the transaction is COMMITed there is no way to roll it back. Is it possible that the application has a code path that might not actually issue a COMMIT WORK statement and it only "thought" that the transaction was completed successfully? 

Art

Hi. We use INFROMIX 14. Yesterday we discovered that there was no record in the database for one operation for May 27, in the software logs this operation was completed successfully, but in the database it was completely missing, as if it never happened. Is there a way to see if there were any transaction rollbacks for a specific time?And how to enable logging of transaction rollbacks?

Is there a way to permanently log a SQL trace to a text file?

Alexander:

Just to wrap up Eric's post, rollbacks are a;ways logged as long as the database is not an UNLOGGED database.

That said, once the transaction is COMMITed there is no way to roll it back. Is it possible that the application has a code path that might not actually issue a COMMIT WORK statement and it only "thought" that the transaction was completed successfully? 

Art

Hi. We use INFROMIX 14. Yesterday we discovered that there was no record in the database for one operation for May 27, in the software logs this operation was completed successfully, but in the database it was completely missing, as if it never happened. Is there a way to see if there were any transaction rollbacks for a specific time?And how to enable logging of transaction rollbacks?

Alexander:

Depends on what you mean be "an SQL trace"? 

If you turn one SQLTRACE, you could poll the sysmaster tables related to SQLTRACE, those with names that match "sqltrace*" and write the data to disk. I do that for the Level 2 and Level 3 Informix Health Checks that I perform for clients. However, most often too many queries hit the server too fast to ever capture everything. Also, if a transaction involves multiple statements, it is even less likely that you would capture enough of the stream of statements in the transaction to be able to tell what had happened to your missing data.

If SQLTRACE is not enabled, then there really is no way to capture running SQL. There are sysmaster tables that contain currently running SQL, but there is no history there. Only live queries are available (OK in some cases the previous statement for particular sessions).

Let me ask you back: Is the database in question a BUFFERED LOG database? If so, you just need to change the database's logging mode to UNBUFFERED LOG using ontape, ondblog, or the SQL API function "alter logmode". I have never seen a single committed transaction lost with UNBUFFERED LOG set.

Also, as pointed out, make sure that your system's shutdown scripting performs a normal shutdown of the Informix instance(s) on the host before stopping the OS for a restart or power down.

Is there a way to permanently log a SQL trace to a text file?

Alexander:

Just to wrap up Eric's post, rollbacks are a;ways logged as long as the database is not an UNLOGGED database.

That said, once the transaction is COMMITed there is no way to roll it back. Is it possible that the application has a code path that might not actually issue a COMMIT WORK statement and it only "thought" that the transaction was completed successfully? 

Art

Hi. We use INFROMIX 14. Yesterday we discovered that there was no record in the database for one operation for May 27, in the software logs this operation was completed successfully, but in the database it was completely missing, as if it never happened. Is there a way to see if there were any transaction rollbacks for a specific time?And how to enable logging of transaction rollbacks?

DB in Buffered LOG mode. This is the first time I've ever seen a transaction disappear. However, judging by the logs, the software was completed correctly, and then they simply rebooted the server OS without turning off Informix. Could this be causing the problem? I looked through the logical log and did not find the transaction data.

Alexander:

Depends on what you mean be "an SQL trace"? 

If you turn one SQLTRACE, you could poll the sysmaster tables related to SQLTRACE, those with names that match "sqltrace*" and write the data to disk. I do that for the Level 2 and Level 3 Informix Health Checks that I perform for clients. However, most often too many queries hit the server too fast to ever capture everything. Also, if a transaction involves multiple statements, it is even less likely that you would capture enough of the stream of statements in the transaction to be able to tell what had happened to your missing data.

If SQLTRACE is not enabled, then there really is no way to capture running SQL. There are sysmaster tables that contain currently running SQL, but there is no history there. Only live queries are available (OK in some cases the previous statement for particular sessions).

Let me ask you back: Is the database in question a BUFFERED LOG database? If so, you just need to change the database's logging mode to UNBUFFERED LOG using ontape, ondblog, or the SQL API function "alter logmode". I have never seen a single committed transaction lost with UNBUFFERED LOG set.

Also, as pointed out, make sure that your system's shutdown scripting performs a normal shutdown of the Informix instance(s) on the host before stopping the OS for a restart or power down.

Is there a way to permanently log a SQL trace to a text file?

Alexander:

Just to wrap up Eric's post, rollbacks are a;ways logged as long as the database is not an UNLOGGED database.

That said, once the transaction is COMMITed there is no way to roll it back. Is it possible that the application has a code path that might not actually issue a COMMIT WORK statement and it only "thought" that the transaction was completed successfully? 

Art

Hi. We use INFROMIX 14. Yesterday we discovered that there was no record in the database for one operation for May 27, in the software logs this operation was completed successfully, but in the database it was completely missing, as if it never happened. Is there a way to see if there were any transaction rollbacks for a specific time?And how to enable logging of transaction rollbacks?

DB in Buffered LOG mode. This is the first time I've ever seen a transaction disappear. However, judging by the logs, the software was completed correctly, and then they simply rebooted the server OS without turning off Informix. Could this be causing the problem? I looked through the logical log and did not find the transaction data.

Alexander:

Depends on what you mean be "an SQL trace"? 

If you turn one SQLTRACE, you could poll the sysmaster tables related to SQLTRACE, those with names that match "sqltrace*" and write the data to disk. I do that for the Level 2 and Level 3 Informix Health Checks that I perform for clients. However, most often too many queries hit the server too fast to ever capture everything. Also, if a transaction involves multiple statements, it is even less likely that you would capture enough of the stream of statements in the transaction to be able to tell what had happened to your missing data.

If SQLTRACE is not enabled, then there really is no way to capture running SQL. There are sysmaster tables that contain currently running SQL, but there is no history there. Only live queries are available (OK in some cases the previous statement for particular sessions).

Let me ask you back: Is the database in question a BUFFERED LOG database? If so, you just need to change the database's logging mode to UNBUFFERED LOG using ontape, ondblog, or the SQL API function "alter logmode". I have never seen a single committed transaction lost with UNBUFFERED LOG set.

Also, as pointed out, make sure that your system's shutdown scripting performs a normal shutdown of the Informix instance(s) on the host before stopping the OS for a restart or power down.

Is there a way to permanently log a SQL trace to a text file?

Alexander:

Just to wrap up Eric's post, rollbacks are a;ways logged as long as the database is not an UNLOGGED database.

That said, once the transaction is COMMITed there is no way to roll it back. Is it possible that the application has a code path that might not actually issue a COMMIT WORK statement and it only "thought" that the transaction was completed successfully? 

Art

Hi. We use INFROMIX 14. Yesterday we discovered that there was no record in the database for one operation for May 27, in the software logs this operation was completed successfully, but in the database it was completely missing, as if it never happened. Is there a way to see if there were any transaction rollbacks for a specific time?And how to enable logging of transaction rollbacks?

Alexander:

Yea, what Andreas said! BUFFERED LOG is indeed the actual reason why the transaction was lost but the proximate cause was the uncontrolled crashing of the server when the OS was shutdown out from under the Informix instance so that it did not have a chance to flush that last logical log buffer containing the transaction operations and COMMIT record nor the updated data and index pages still dirty in the buffer pool. Either having been flushed would have prevented the data loss, and if the database had been UNBUFFERED LOG the when your application submitted the COMMIT WORK statement and it was written to the logical log buffer, that write would have forced that log buffer to flush to disk saving the transaction and making it available for fast recovery to rerun on startup.

Again though, make sure that the system shutdown scripts ALWAYS shutdown the database (onmode -ky) before shutting down the OS. That also would have prevented the data loss. Doing both UNBUFFERED LOG and correctly shutting down the Informix instance is the kind of paranoia that is the reason we DBAs get the big bucks!

Art

DB in Buffered LOG mode. This is the first time I've ever seen a transaction disappear. However, judging by the logs, the software was completed correctly, and then they simply rebooted the server OS without turning off Informix. Could this be causing the problem? I looked through the logical log and did not find the transaction data.

Alexander:

Depends on what you mean be "an SQL trace"? 

If you turn one SQLTRACE, you could poll the sysmaster tables related to SQLTRACE, those with names that match "sqltrace*" and write the data to disk. I do that for the Level 2 and Level 3 Informix Health Checks that I perform for clients. However, most often too many queries hit the server too fast to ever capture everything. Also, if a transaction involves multiple statements, it is even less likely that you would capture enough of the stream of statements in the transaction to be able to tell what had happened to your missing data.

If SQLTRACE is not enabled, then there really is no way to capture running SQL. There are sysmaster tables that contain currently running SQL, but there is no history there. Only live queries are available (OK in some cases the previous statement for particular sessions).

Let me ask you back: Is the database in question a BUFFERED LOG database? If so, you just need to change the database's logging mode to UNBUFFERED LOG using ontape, ondblog, or the SQL API function "alter logmode". I have never seen a single committed transaction lost with UNBUFFERED LOG set.

Also, as pointed out, make sure that your system's shutdown scripting performs a normal shutdown of the Informix instance(s) on the host before stopping the OS for a restart or power down.

Is there a way to permanently log a SQL trace to a text file?

Alexander:

Just to wrap up Eric's post, rollbacks are a;ways logged as long as the database is not an UNLOGGED database.

That said, once the transaction is COMMITed there is no way to roll it back. Is it possible that the application has a code path that might not actually issue a COMMIT WORK statement and it only "thought" that the transaction was completed successfully? 

Art

Hi. We use INFROMIX 14. Yesterday we discovered that there was no record in the database for one operation for May 27, in the software logs this operation was completed successfully, but in the database it was completely missing, as if it never happened. Is there a way to see if there were any transaction rollbacks for a specific time?And how to enable logging of transaction rollbacks?

Alexander:

Yea, what Andreas said! BUFFERED LOG is indeed the actual reason why the transaction was lost but the proximate cause was the uncontrolled crashing of the server when the OS was shutdown out from under the Informix instance so that it did not have a chance to flush that last logical log buffer containing the transaction operations and COMMIT record nor the updated data and index pages still dirty in the buffer pool. Either having been flushed would have prevented the data loss, and if the database had been UNBUFFERED LOG the when your application submitted the COMMIT WORK statement and it was written to the logical log buffer, that write would have forced that log buffer to flush to disk saving the transaction and making it available for fast recovery to rerun on startup.

Again though, make sure that the system shutdown scripts ALWAYS shutdown the database (onmode -ky) before shutting down the OS. That also would have prevented the data loss. Doing both UNBUFFERED LOG and correctly shutting down the Informix instance is the kind of paranoia that is the reason we DBAs get the big bucks!

Art

DB in Buffered LOG mode. This is the first time I've ever seen a transaction disappear. However, judging by the logs, the software was completed correctly, and then they simply rebooted the server OS without turning off Informix. Could this be causing the problem? I looked through the logical log and did not find the transaction data.

Alexander:

Depends on what you mean be "an SQL trace"? 

If you turn one SQLTRACE, you could poll the sysmaster tables related to SQLTRACE, those with names that match "sqltrace*" and write the data to disk. I do that for the Level 2 and Level 3 Informix Health Checks that I perform for clients. However, most often too many queries hit the server too fast to ever capture everything. Also, if a transaction involves multiple statements, it is even less likely that you would capture enough of the stream of statements in the transaction to be able to tell what had happened to your missing data.

If SQLTRACE is not enabled, then there really is no way to capture running SQL. There are sysmaster tables that contain currently running SQL, but there is no history there. Only live queries are available (OK in some cases the previous statement for particular sessions).

Let me ask you back: Is the database in question a BUFFERED LOG database? If so, you just need to change the database's logging mode to UNBUFFERED LOG using ontape, ondblog, or the SQL API function "alter logmode". I have never seen a single committed transaction lost with UNBUFFERED LOG set.

Also, as pointed out, make sure that your system's shutdown scripting performs a normal shutdown of the Informix instance(s) on the host before stopping the OS for a restart or power down.

Is there a way to permanently log a SQL trace to a text file?

Alexander:

Just to wrap up Eric's post, rollbacks are a;ways logged as long as the database is not an UNLOGGED database.

That said, once the transaction is COMMITed there is no way to roll it back. Is it possible that the application has a code path that might not actually issue a COMMIT WORK statement and it only "thought" that the transaction was completed successfully? 

Art

Hi. We use INFROMIX 14. Yesterday we discovered that there was no record in the database for one operation for May 27, in the software logs this operation was completed successfully, but in the database it was completely missing, as if it never happened. Is there a way to see if there were any transaction rollbacks for a specific time?And how to enable logging of transaction rollbacks?

Thanks everyone. I figured out what my problem is and will try changing the logging mode. And I'll remind local system administrators how to properly close Informix.

Alexander:

Yea, what Andreas said! BUFFERED LOG is indeed the actual reason why the transaction was lost but the proximate cause was the uncontrolled crashing of the server when the OS was shutdown out from under the Informix instance so that it did not have a chance to flush that last logical log buffer containing the transaction operations and COMMIT record nor the updated data and index pages still dirty in the buffer pool. Either having been flushed would have prevented the data loss, and if the database had been UNBUFFERED LOG the when your application submitted the COMMIT WORK statement and it was written to the logical log buffer, that write would have forced that log buffer to flush to disk saving the transaction and making it available for fast recovery to rerun on startup.

Again though, make sure that the system shutdown scripts ALWAYS shutdown the database (onmode -ky) before shutting down the OS. That also would have prevented the data loss. Doing both UNBUFFERED LOG and correctly shutting down the Informix instance is the kind of paranoia that is the reason we DBAs get the big bucks!

Art

DB in Buffered LOG mode. This is the first time I've ever seen a transaction disappear. However, judging by the logs, the software was completed correctly, and then they simply rebooted the server OS without turning off Informix. Could this be causing the problem? I looked through the logical log and did not find the transaction data.

Alexander:

Depends on what you mean be "an SQL trace"? 

If you turn one SQLTRACE, you could poll the sysmaster tables related to SQLTRACE, those with names that match "sqltrace*" and write the data to disk. I do that for the Level 2 and Level 3 Informix Health Checks that I perform for clients. However, most often too many queries hit the server too fast to ever capture everything. Also, if a transaction involves multiple statements, it is even less likely that you would capture enough of the stream of statements in the transaction to be able to tell what had happened to your missing data.

If SQLTRACE is not enabled, then there really is no way to capture running SQL. There are sysmaster tables that contain currently running SQL, but there is no history there. Only live queries are available (OK in some cases the previous statement for particular sessions).

Let me ask you back: Is the database in question a BUFFERED LOG database? If so, you just need to change the database's logging mode to UNBUFFERED LOG using ontape, ondblog, or the SQL API function "alter logmode". I have never seen a single committed transaction lost with UNBUFFERED LOG set.

Also, as pointed out, make sure that your system's shutdown scripting performs a normal shutdown of the Informix instance(s) on the host before stopping the OS for a restart or power down.

Is there a way to permanently log a SQL trace to a text file?

Alexander:

Just to wrap up Eric's post, rollbacks are a;ways logged as long as the database is not an UNLOGGED database.

That said, once the transaction is COMMITed there is no way to roll it back. Is it possible that the application has a code path that might not actually issue a COMMIT WORK statement and it only "thought" that the transaction was completed successfully? 

Art

Hi. We use INFROMIX 14. Yesterday we discovered that there was no record in the database for one operation for May 27, in the software logs this operation was completed successfully, but in the database it was completely missing, as if it never happened. Is there a way to see if there were any transaction rollbacks for a specific time?And how to enable logging of transaction rollbacks?