14.10.FC7 on Linux 8.6
We have a small Innovator Edition instance running an unlogged database. I know, but it's a legacy system that is accessed by an outside client, and they don't want to change their application to support logging. Their application is written using Web Methods.
The client retrieves data from a view (outbound_invoices). Once all the data has been retrieved, they INSERT a row to another view (trans_history), listing the batch number and number of rows retrieved from the outbound_invoices view. There is an INSTEAD OF trigger on the trans_history view. This trigger fires a stored procedure that counts the number of rows that it finds with that batch number in the table on which outbound_invoices view is built. If that matches the value inserted into the trans_history view, the procedure then deletes all of the rows for that batch from the table.
The problem that we are seeing is that when there are 2500+ rows in a batch, only some of the rows are deleted. Smaller batches seem to work without any problem. And of course it is not consistent. We recently had a batch with 3480 rows that only deleted some of the rows, but a later batch with 5215 rows successfully deleted all rows.
Nothing is showing up in the Informix message log.
We're trying to figure out why the larger batches are only deleting a portion of the data. One theory is that the client's application is terminating the connection before the trigger completes, causing the stored procedure to stop prematurely. But that seems odd, as the INSERT into the trans_history view should not return a SQLCODE to the calling program until after the trigger completes, right? Is there some way that the Web Methods application could close the connection without waiting for the INSERT statement to return a SQLCODE? Any other ideas on what might be happening? Or suggestions on what to look for?
Thanks.
------------------------------
mark collins
------------------------------