What's your operating system?
If it's any Linux or Unix, you can have the onmode -ky or onmode -yuk in the shutdown routine. I would put it in the transition from run level 4 (or whatever your fully operational state is) to run level 3 (or whatever your next lower state is). Look for configuration options in places like /etc/init.d or /etc/rc.d.
If it's Windows, check the configuration on the service, it should also shut down the server cleanly before rebooting the computer.
--EEM
Original Message:
Sent: 6/5/2024 2:46:00 AM
From: Alexander Ivanov
Subject: RE: Inspect transactions log
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 Ivanov
------------------------------
Original Message:
Sent: Tue June 04, 2024 11:01 AM
From: Art Kagel
Subject: Inspect transactions log
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
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
Original Message:
Sent: Tue June 04, 2024 10:40 AM
From: Alexander Ivanov
Subject: Inspect transactions log
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 Ivanov
Original Message:
Sent: Tue June 04, 2024 10:02 AM
From: Art Kagel
Subject: Inspect transactions log
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.
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
Original Message:
Sent: Tue June 04, 2024 09:14 AM
From: Alexander Ivanov
Subject: Inspect transactions log
Is there a way to permanently log a SQL trace to a text file?
------------------------------
Alexander Ivanov
Original Message:
Sent: Tue June 04, 2024 06:33 AM
From: Art Kagel
Subject: Inspect transactions log
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
------------------------------
Art S. Kagel, President and Principal Consultant
ASK Database Management Corp.
www.askdbmgt.com
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
------------------------------