RemoveTrans repeatedly re-processes historical Removed loads – correct configuration?

Hello IFS Community,

I would appreciate help from experienced IFS users, consultants and developers in reviewing the following RemoveTrans behavior and determining the correct configuration.

The mechanism described below appears to behave less like ordinary housekeeping and more like a self-amplifying feedback loop: historical records can be repeatedly re-qualified, new technical records are created from already processed loads, and some of those newly created records can later contribute to subsequent executions.

In practical terms, the behavior resembles a mailbomb-like feedback loop or self-amplifying process — not because anything malicious is happening, but because one execution can create additional technical records that increase the amount of work performed by future executions.

The objective of this post is to understand whether this behavior is expected by design and what the correct configuration should be to prevent repeated processing without affecting legitimate External Files housekeeping.

What the API appears to do

Ext_File_Trans_API.Remove_Ready_Trans uses a cursor based on:

EXT_FILE_LOG
EXT_FILE_LOAD_TAB
EXT_FILE_TEMPLATE_DIR_TAB

Records become eligible according to a condition equivalent to:

TRUNC(log_date) + remove_days <= SYSDATE

This means that REMOVE_DAYS defines an age threshold for qualification.

It is not, by itself, a retention policy for EXT_FILE_LOG, and an old log record is not automatically deleted simply because the configured age threshold has been reached.

The important behavior appears when:

REMOVE_ALL_LOAD_STATES = TRUE

With this setting, Remove_Ready_Trans sets:

remove_ok_ = 'TRUE'

without restricting processing to the normal state filter.

As a result, loads already in:

state 8 = Removed

can also enter the processing path.

With:

REMOVE_ALL_LOAD_STATES = FALSE

the code applies:

state IN ('4','7')

and state 8 / Removed is excluded.

Behavior when REMOVE_COMPLETE = FALSE

For an External File configuration using:

REMOVE_COMPLETE = FALSE

the effective processing path appears to be:

Ext_File_Trans_API.Remove_Ready_Trans
        |
        v
Ext_File_Trans_API.Remove_File_Trans(load_file_id, '8')
        |
        v
DELETE FROM Ext_File_Trans_Tab
WHERE load_file_id = ...
        |
        v
Ext_File_Load_API.Update_State(load_file_id, '8')
        |
        v
LOAD row remains;
its state is set to 8 / Removed
        |
        v
Update_State prepares a new LOG record
with LOG_DATE = SYSDATE
        |
        v
Ext_File_Log_API.New__(..., 'DO')
        |
        v
a new EXT_FILE_LOG_TAB record is inserted

Remove_File_Trans deletes the source EXT_FILE_TRANS_TAB rows and then calls Update_State.

Update_State updates the load state and prepares a new log record containing, among other values:

STATE_DB = 8
LOG_DATE = SYSDATE

It then calls:

Ext_File_Log_API.New__(..., 'DO')

which reaches the insert path and creates another EXT_FILE_LOG_TAB record.

Why the number of technical records can increase

A second important part of the mechanism is the cursor itself.

The cursor operates on individual EXT_FILE_LOG rows and does not appear to use:

DISTINCT LOAD_FILE_ID

Therefore, if one historical load has several qualifying log entries, the same LOAD_FILE_ID can appear in several cursor rows.

Each qualifying cursor row is processed independently.

For each qualifying row, the process can effectively:

1. call Remove_File_Trans for the same historical LOAD_FILE_ID,
2. update the same load to state 8 / Removed,
3. create another EXT_FILE_LOG record with LOG_DATE = SYSDATE,
4. create another technical output record in the current RemoveTrans load.

Remove_Ready_Trans also executes:

Ext_File_Trans_API.Insert_Record(newrec_)

for every qualifying cursor row.

The resulting behavior can therefore be represented as a time-based cycle:

historical LOG
     |
     | reaches REMOVE_DAYS threshold
     v
qualifies for RemoveTrans
     |
     v
Removed load is accepted because
REMOVE_ALL_LOAD_STATES = TRUE
     |
     v
Update_State creates another LOG
with LOG_DATE = SYSDATE
     |
     v
older qualifying LOG remains
new LOG starts aging
     |
     | after REMOVE_DAYS
     v
new LOG can also become eligible
     |
     +----------> cycle continues

This is not direct recursion inside a single procedure call.

It is a feedback cycle between subsequent scheduler executions.

Because older qualifying log records can remain while new log records are created, the population of qualifying EXT_FILE_LOG rows can increase over time.

The absence of DISTINCT LOAD_FILE_ID can additionally mean that several log records belonging to the same historical load produce several iterations during one execution.

Why this can happen even after the source transactional data is gone

There can be technical result records referring to historical loads for which the source transactional row count is already zero.

In other words, the original EXT_FILE_TRANS_TAB payload for a historical load may already be absent, while a technical RemoveTrans result record can still be generated for that load.

This suggests that a large volume of generated records does not necessarily represent new business documents.

It can instead represent repeated housekeeping activity involving historical loads whose original transactional payload has already been removed.

Why cleanup alone does not remove the mechanism

Cleaning accumulated records can reduce the current database footprint, but it does not change the qualification logic.

If:

REMOVE_ALL_LOAD_STATES = TRUE

remains active, historical loads already in state 8 / Removed can continue to enter the same processing path.

Additionally, Update_State(8) creates another log record with:

LOG_DATE = SYSDATE

while older qualifying log records may remain.

After the configured REMOVE_DAYS period, the newly created log can itself become eligible for processing.

Cleanup can therefore remove accumulated effects while leaving the underlying mechanism capable of recreating them.

Effect of changing TRUE to FALSE

With:

REMOVE_ALL_LOAD_STATES = FALSE

the API applies:

state IN ('4','7')

and:

8 = Removed

is no longer accepted by this processing path.

Already-Removed historical loads therefore stop re-entering this specific flow, while housekeeping for the permitted states can continue.

A controlled comparison showed a substantial difference in the amount of technical output generated between the TRUE and FALSE configurations.

Repeated executions with FALSE also showed substantially reduced processing time and technical output.

Separate path: REMOVE_COMPLETE = TRUE

There is a separate processing path for configurations using:

REMOVE_COMPLETE = TRUE

In that case Remove_Ready_Trans calls:

Ext_File_Load_API.Delete_File_Load

which can also invoke deletion of the associated TRANS and LOG records.

At large scale, this can result in very large DELETE operations and substantial UNDO/REDO consumption.

Errors such as:

ORA-30036: unable to extend segment in undo tablespace

can therefore occur as a consequence of the large delete workload.

Increasing UNDO can provide additional capacity for such operations, but it does not change the re-qualification mechanism described above.

Important operational clarification

Changing:

REMOVE_ALL_LOAD_STATES = TRUE

to:

REMOVE_ALL_LOAD_STATES = FALSE

does not itself execute a DELETE operation.

This parameter controls which load states are eligible for the RemoveTrans processing path.

With FALSE, already-processed loads in state 8 / Removed are excluded from this path, while housekeeping for the permitted states can still continue.

Actual deletion behavior occurs later and depends on the applicable processing branch and the REMOVE_COMPLETE configuration.

Therefore, based on the API logic, changing REMOVE_ALL_LOAD_STATES from TRUE to FALSE appears to restrict repeated qualification of historical Removed loads rather than directly remove business documents.

Question to IFS Community / IFS

Is the following combined behavior expected by design?

1. Remove_Ready_Trans uses EXT_FILE_LOG rows as cursor input.
2. There is no DISTINCT by LOAD_FILE_ID.
3. REMOVE_ALL_LOAD_STATES=TRUE accepts state 8 / Removed.
4. With REMOVE_COMPLETE=FALSE, TRANS is deleted but LOAD and LOG remain.
5. Update_State(8) creates a new EXT_FILE_LOG record with LOG_DATE=SYSDATE.
6. RemoveTrans creates one technical output record for every qualifying cursor row.
7. Older qualifying LOG records remain.
8. Newly created LOG records can become eligible again after REMOVE_DAYS.

This combination appears capable of repeatedly processing the same historical loads and increasing the number of technical records across successive scheduled executions.

Is REMOVE_ALL_LOAD_STATES=FALSE the recommended configuration when normal External Files housekeeping should remain active, but historical loads already in state Removed should no longer repeatedly enter RemoveTrans?

If not, what is the officially recommended configuration, correction or patch for preventing this self-amplifying processing cycle?

I checked the underlying API sequence against the supplied technical material: the state filtering, Remove_File_Trans → Update_State, creation of a new log with LOG_DATE=SYSDATE, lack of DISTINCT LOAD_FILE_ID, and the time-cycle are all supported by the captured source.