inconsistent operation of trigger in unlogged database

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 ------------------------------

Ooh nasty to debug. Put some debugging in the SP? - Clive Eisen

We've done that, and are waiting to see if the problem occurs again. But there's not a lot of opportunity in the logic for debugging. The procedure basically does: CREATE PROCEDURE cleanout(i_batch_no integer, i_number_of_rows integer)DEFINE p_row_count integer; SELECT count(*) INTO p_row_count FROM tbl_outbound_invoices WHERE batch_no = i_batch_no;IF p_row_count = i_number_of_rows THEN DELETE FROM tbl_outbound_invoices WHERE batch_no = i_batch_no;END IF; END PROCEDURE; The debugging that we've added is an ON EXCEPTION at the top block and a TRACE "Deleted " || dbinfo('sqlca.sqlerrd2') || " rows from table" statement after the DELETE. The ON EXCEPTION will output sqlcode, isam code, and err text via TRACE statement. ------------------------------ mark collins ------------------------------

Mark: There is a way to mark a connection with a callback function in the application's code space with a timeout setting to execute the callback if a query runs longer than the timeout. It is possible that the "Web Methods" is setting a callback that is going off before the deletes complete on some larger data sets but not others and the application is closing the connection inside the callback function. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Art, Thanks for that info. I do not know whether we'll ever be able to confirm whether the client has done that or not, but it is good to know that it is possible to do that. It looks like we may need to revamp our process to do things differently. Perhaps rewrite the trigger to write to a table of tasks scheduled to be done later or something. ------------------------------ mark collins ------------------------------

Mark: What you could do is capture the changes to that original table using CDC or Smart Triggers to an external program that will handle inserting the status record and deleting the associated detail input rows from a separate session you can write in C or Java. Sounds complex, but not hard. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Art, Interesting options. I'll add those to the list for discussion. We're first reaching out to the client in the hope that we might at least confirm whether they're doing the callback that you mentioned. ------------------------------ mark collins ------------------------------

The client may not know about the callback if it is built into the "web method" template. FWIW. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Would require logging? ------------------------------ Andreas Legner ------------------------------

AARRGGHH!! Yes, you are correct both methods, C & Smart Triggers as well as Post Commit Triggers would require logging enabled on the database. Sorry about that. Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------