Placement of ET logic in Natural batch program

, ,
1

In our shop, we’ve been told for years that placement of ET logic in a Natural batch program is crucial in order to avoid database timeouts. Is this correct? For example:


READ VIEW-1 BY FIELD-1
  ACCEPT IF FIELD-2 = 'XY'
  GETR. GET VIEW-1 *ISN
  MOVE 'AB' TO FIELD-2
  UPDATE (GETR.)
  ADD 1 TO #ET-CNT
  IF #ET-CNT > 99
    END TRANSACTION
    RESET #ET-CNT
  END-IF
END-READ
END TRANSACTION
END

We’ve been told that, depending on the number of records in the file and the number of records with FIELD-2 equal to ‘XY’, the code written above could result in a database timeout.

We’ve been told that a better solution would be:


READ VIEW-1 BY FIELD-1
  ADD 1 TO #ET-CNT		/* END TRANSACTION AFTER EVERY 100 RECORDS READ
  IF #ET-CNT > 99
    END TRANSACTION
    RESET #ET-CNT
  END-IF
  ACCEPT IF FIELD-2 = 'XY'
  GETR. GET VIEW-1 *ISN
  MOVE 'AB' TO FIELD-2
  UPDATE (GETR.)
END-READ
END TRANSACTION
END
2

I always thought ET’s were “expensive”, you should be able to process quite a few hundred thousand records if your TT parameter was 5 minutes. If you were issuing an ET every 100 records you would probably be doing hundreds unnecessarily

3

Instead of basing the timing of the ET on records read or updated, why not base the timing on elapsed wall clock time (which is what the timeout is based on). For example

MOVE FALSE to #flag /* actually not necessary, false is default
SETA. SETTIME
READ VIEW-1 BY FIELD-1
** ADD 1 TO #ET-CNT /* END TRANSACTION AFTER EVERY 100 RECORDS READ
** IF #ET-CNT > 99
IF *TIMD (SETR.) > value a bit less than timeout (accomodate update)
OR ( TIMD (SETA.) > value a bit less than timeout (accomodate update)
AND #flag = false)
END TRANSACTION
SETR. SETTIME / resets timer
move true to #flag
** RESET #ET-CNT
END-IF
ACCEPT IF FIELD-2 = ‘XY’
GETR. GET VIEW-1 *ISN
MOVE ‘AB’ TO FIELD-2
UPDATE (GETR.)
END-READ
END TRANSACTION
END

4

Well, with respect to what Steve recommends, I would not follow such an advice :slight_smile:
I think when you UPDATE in batch in most cases your “processing” (especially like "move ‘AB’ to field-2) does NOT take any time; as such, most likely you would end up with “ADABAS response code NNNN” (don`t remember exactly) because “you are attempting to place more records in hold status than permitted” MUCH more likely that because of your “TIMEOUT exceeded or something else”.
Just my 2 cents :slight_smile:
Best regards,
NK

5

Hi Nikolay;

Dan is employing “GET logic” so he is only holding records that will actually be updated.

Your comment would be valid if Dan was updating most of the records read; but, if that were the case, he probably would not be using GET logic.

steve

6

Thanks for your replies, everyone. It sounds as though you are confirming what our shop has said all along; namely, that the placement of the ET logic after the initial READ rather than after the UPDATE statement is crucial to avoid a database timeout (NAT3009).

7

Typically 3009 ABENDs are the result of trying to hold too many records or from holding records for too long.

“the placement of the ET logic after the initial READ rather than after the UPDATE statement is crucial to avoid a database timeout”

It’s not the physical placement that’s crucial. Your code sample works but is unorthodox. It’s structure is necessary because of the ACCEPT statement. Your example would be more mainstream if you replaced the ACCEPT with an IF and moved the END TRANSACTION to the end of the READ loop. By unorthodox I mean that an ET is used to define a unit of work, and your unit of work ends with the previous record, but is triggered by the current record.

The ideal is to hold a record only for as long as necessary. Issuing an ET based on a timer “a little bit less than timeout” maximizes the duration. This technique should be used only in addition to the record counting technique, not as a replacement.

In support of Nikolay’s post, the original code sample appears to target Adabas timeouts, but I have worked with developers who consistently use GET for each record to be updated, even when the entire file is modified. I don’t know whether this stems from poor training or laziness.

ETs aren’t as expensive as they were before Asynchronous Buffer Flushes, but we still like to limit them. But why turn around and reduce the savings by issuing a GET for each updated record? The desire to limit the negative impact on other users (from holding records you don’t need) may be realized just as effectively by reducing the hold duration (via smaller ET thresholds - even at the expense of more ETs).

First an example with GET, then without:

R.
READ file ...
  ADD 1 TO record-count
  IF  selection criteria       /* replaces ACCEPT
    THEN
      G.
      GET file *ISN (R.)
      ASSIGN file.field = ...
      UPDATE (G.)
      ADD 1 TO ET-count
  END-IF
  IF   ET-count       > 50
   OR record-count > 1000     /* <---
    THEN
      END TRANSACTION
      RESET ET-count
              record-count
  END-IF
R.
READ file ...
  ADD 1 TO record-count
  IF  selection criteria       /* replaces ACCEPT
    THEN
      ASSIGN file.field = ...
      UPDATE (R.)
      ADD 1 TO ET-count
  END-IF
  IF   ET-count      > 50
   OR record-count > 100     /* <---
    THEN
      END TRANSACTION
      RESET ET-count
              record-count
  END-IF