[Info-ingres] E_US1262 Your transaction has been externally aborted

[Roy Hann]

Paul White wrote:

> Hi Charaka,
>
> You have an Ingres question here.
>
> How big is your transaction log? I suggest 2GB is reasonable for a busy
> production site.
> The transaction log records a before and after image of every page affected
> in an update. 
> So you will need at least twice the table size if you are going to update
> all records.
>
> The wo_history_det table is 2683 bytes wide.  Each record uses 4K page for
> storage.
> 71795 records is at least 294,072,320 bytes.
> so you will need at least 588,144,640 bytes.
>
> Also take into account any indexes using the field as they will also be
> recorded in the transaction log.
>
> Options you might consider.
> • Increase size of your transaction log
> • Break the update into smaller batches say 10,000 records at a time
> • Use NOLOGGING (not for production)
> • Modify the table to use compression to fit more records on a page

I would add a suggestion to that list. 

I question whether is_updated is a fact about the work_order_no. To my
eye it looks like metadata; it is information about the row not the
fact represented by the row. If that is so, then it logically belongs
in a separate table.

work_order_no is declared a unique physical key, so I guess it has to
be a logical key. So create a second table called, say,
wo_history_det_processed likse this:

   CREATE TABLE wo_history_det_processed
   (
       work_order_no varchar(12) not null not default
   ) with noduplicates;

(Ideally you'd declare a UNIQUE or PRIMARY KEY constraint on
work_order_no in wo_history and set up a FOREIGN KEY constraint
referencing it in wo_history_det_processed.)

When a work order is processed insert its number into wo_history.

Assuming setting the flag to zero amounts to clearing it, your update
would be replaced by an unrestricted delete on the much smaller table
or even by a MODIFY...TO TRUNCATED, which ought to eliminate your log
full error.

Roy