Module 05

Navigational DML & currency

This is the core of the database and the longest module in the course. You are not writing queries. You are driving a cursor through a graph by hand — and the database remembers where that cursor has been in four separate ways at once. Get that wrong and nothing fails; you just quietly read the wrong record.

~70 minLive interpreter12-question checkpoint
What this module is really about

How programs actually read and change this data. There is no query language here. Instead the program says: go to this record, now follow that relationship, now read the next one. You write the route yourself.

The hard part is that the database keeps track of where you are — and it keeps four separate position markers at the same time. Moving one moves others. Almost every bug in this kind of code, and a good share of the odd-looking data you will meet when copying it out, comes from that one fact.

New words on this page, one line each
currency
The database’s memory of where your program is positioned. Four markers, tracked at once.
run unit
One program’s session with the database. It owns the position markers and the locks.
READY
Open a storage area, saying whether you intend to read or change it, and how much you will share it.
FIND / GET / OBTAIN
Move to a record; copy it into program memory; do both at once.
STORE / MODIFY / ERASE
Insert, update, delete. STORE attaches the new record to whatever parents you are currently sitting on.
CONNECT / DISCONNECT
Attach or detach a record from a relationship by hand.
error-status
A four-character code set after every single command. Checking it is the only error mechanism there is.
0307
“End of set” — you have walked off the end of a chain and arrived back at the parent. Normal, not an error.
0326
“Record not found” — no record matched the key you supplied. Also normal.

How this differs from writing a query

Picture it

You are standing in the warehouse with a card file open and a finger holding your place.

Except you have four fingers in four different places at once: one on the last card you touched overall, one per type of card, one per relationship, and one per room. Pick up any card and several of those fingers move at the same time.

That is currency. It is the single most important idea in the module, and the simulator below makes it obvious far faster than reading about it will.

A relational query says what you want and lets the engine decide how to get it. Navigational DML says how: position here, follow that pointer, read this record, follow the next pointer. There is no optimiser to second-guess you and no plan to explain. The program is the plan.

The position is called currency, and IDMS maintains four kinds simultaneously.

The four currencies — every retrieval verb updates several of them at once
CurrencyHow manyWhat it holds
Current of run unitOneThe db-key of the most recently accessed record occurrence, whatever its type. This is what a bare GET, MODIFY or ERASE acts on.
Current of record typeOne per record type in the subschemaThe last occurrence of that record type accessed.
Current of set typeOne per set typeThe last occurrence accessed in that set — which may be the owner or a member.
Current of areaOne per areaThe last occurrence accessed in that area, used by area sweeps.
The single most important sentence in this course Accessing any record updates the currency of every set that record participates in. Retrieving an EMPLOYEE while walking DEPT-EMPLOYEE also moves the currency of OFFICE-EMPLOYEE, EMP-EMPOSITION, EMP-COVERAGE and EMP-EXPERTISE. Interleave two set walks over the same record type without saving db-keys and you will corrupt both — with no error, ever.

Opening data, and saying how much you will share it

If you know transaction isolation levels

Same trade-off, declared up front and for a whole storage area rather than per statement. You state once, when you open the area, whether you intend to read or write and whether others may do the same while you hold it.

The strictest option locks everyone else out of the entire area for the duration. A long overnight job that chooses it is how a Monday morning goes wrong.

Before touching an area you must READY it. The usage mode is a lock declaration for the whole area and it is chosen once, for the life of the run unit.

Usage modeThis run unit mayConcurrently, others may
RETRIEVALReadRead and update
PROTECTED RETRIEVALReadRead only — updates are blocked
EXCLUSIVE RETRIEVALReadNothing — the area is yours
UPDATERead and updateRead and update
PROTECTED UPDATERead and updateRead only
EXCLUSIVE UPDATERead and updateNothing

RETRIEVAL is the default and permits other run units to be changing the data underneath you. That is usually correct for online enquiry and usually wrong for a report that must be internally consistent. PROTECTED and EXCLUSIVE buy consistency at the cost of concurrency, and a long-running batch job holding EXCLUSIVE UPDATE on a busy area is how a mainframe morning goes badly.

The three reading commands

The difference in one line

FIND moves you there. GET copies the record into your program’s memory. OBTAIN does both, and is what almost all real code uses.

The reason to care: after a bare FIND, your program’s memory still holds the previous record. Update at that moment and you write the wrong data over the right record, with no error.

Three verbs, and the difference between them is exactly one thing: whether the data is moved into your program's work area.

FIND

Locates a record and sets currency. Does not move data. Use it when you only need position — to test for existence, to establish an owner for a subsequent STORE, or to walk past records you do not need.

GET

Moves the current of run unit into the record's work area. No navigation. Use it after a FIND once you have decided you want this one.

OBTAIN

Exactly FIND + GET. Almost all application code uses OBTAIN; FIND alone is the optimisation you reach for when scanning many records to select few.

Every way to move to a record

every navigation you will meet in production code
*> 1. by CALC key — one I/O, needs the key moved to the work area first
    MOVE WS-EMP-ID TO EMP-ID-0415.
    OBTAIN CALC EMPLOYEE.

*> 2. by saved db-key — one I/O, no navigation at all
    OBTAIN EMPLOYEE DB-KEY IS WS-SAVED-DBKEY.

*> 3. positional within a set
    OBTAIN FIRST  EMPOSITION WITHIN EMP-EMPOSITION.
    OBTAIN NEXT   EMPOSITION WITHIN EMP-EMPOSITION.
    OBTAIN PRIOR  EMPOSITION WITHIN EMP-EMPOSITION.
    OBTAIN LAST   EMPOSITION WITHIN EMP-EMPOSITION.
    OBTAIN OWNER            WITHIN EMP-EMPOSITION.

*> 4. the nth member of a set
    OBTAIN 3 EMPOSITION WITHIN EMP-EMPOSITION.

*> 5. by sort key within a SORTED set — positions without walking
    MOVE '20180101' TO START-DATE-0420.
    FIND EMPOSITION WITHIN EMP-EMPOSITION USING START-DATE-0420.

*> 6. sweeping an area in db-key sequence
    OBTAIN FIRST EMPLOYEE WITHIN EMP-DEMO-REGION.
    OBTAIN NEXT  EMPLOYEE WITHIN EMP-DEMO-REGION.

*> 7. re-reading where you already are
    OBTAIN CURRENT EMPLOYEE.
    OBTAIN CURRENT WITHIN DEPT-EMPLOYEE.
Format 2 is the one to remember ACCEPT DB-KEY saves the current record's address into a program variable; OBTAIN … DB-KEY IS returns to it in a single I/O with no navigation. Every efficient IDMS program is built out of this pair: navigate once, save the addresses you will need, then jump directly. It is also the technique that makes interleaved set walks safe.

Lab — the DML interpreter

This is a working interpreter over the sample database, with real currency semantics and IDMS error-status codes. Start with the quick buttons, then type your own. Watch the currency panel after every verb.

Guided walkthrough — do these in order
  1. BIND RUN-UNIT then READY ALL USAGE RETRIEVAL.
  2. MOVE '3200' TO DEPT-ID-0410 then OBTAIN CALC DEPARTMENT. Note that currency of DEPT-EMPLOYEE is now the owner.
  3. OBTAIN FIRST WITHIN DEPT-EMPLOYEE, then OBTAIN NEXT WITHIN DEPT-EMPLOYEE repeatedly until you get 0307. Where is currency now?
  4. Go back to the department and get an employee again. Now issue OBTAIN OWNER WITHIN OFFICE-EMPLOYEE. You have jumped sideways across the network.
  5. Now issue OBTAIN NEXT WITHIN DEPT-EMPLOYEE. Read the result carefully — this is the interleaving trap.
  6. Fix it: re-establish the employee with OBTAIN DB-KEY IS <page:line> using the db-key from step 4, then continue the walk.
  7. Try READY EMP-DEMO-REGION USAGE RETRIEVAL then STORE EMPLOYEE. Read the status code.
  8. RESET, then READY ALL USAGE UPDATE, move an employee id and name, and STORE EMPLOYEE. Which sets were connected automatically, and which were not?
  9. Find a department, then ERASE DEPARTMENT with no option. Read the status. Then try ERASE DEPARTMENT PERMANENT.

The commands that change data

STORE

STORE takes the contents of the record's work area, places the occurrence according to its location mode, and connects it to every set in which it is an AUTOMATIC member — using the current owner of each of those sets. This is the part that catches people: the owner is not named in the statement. It comes from currency.

a correct STORE sequence
*> establish the owners FIRST — currency is the only thing STORE looks at
    MOVE WS-DEPT-ID TO DEPT-ID-0410.
    OBTAIN CALC DEPARTMENT.            *> currency of DEPT-EMPLOYEE = this department
    MOVE WS-OFFICE   TO OFFICE-CODE-0450.
    OBTAIN CALC OFFICE.                *> currency of OFFICE-EMPLOYEE = this office

*> now fill the work area and store
    MOVE WS-EMP-ID    TO EMP-ID-0415.
    MOVE WS-FIRST     TO EMP-FIRST-NAME-0415.
    MOVE WS-LAST      TO EMP-LAST-NAME-0415.
    STORE EMPLOYEE.                    *> connected to BOTH sets automatically

    IF ERROR-STATUS NOT = '0000'
        PERFORM DB-ERROR-ROUTINE.

Get the order wrong — fill the work area, then OBTAIN the department — and the OBTAIN overwrites nothing but currency is correct, so it still works. Omit the OBTAIN entirely and the record is connected to whatever department the previous iteration of your loop happened to leave current. That bug ships.

MODIFY

MODIFY replaces the stored occurrence with the work-area contents. Two subtleties matter:

  • The record must have been retrieved with GET or OBTAIN in this run unit. Modifying after a bare FIND writes whatever junk is in the work area over the record.
  • If you change a field that is the sort key of a sorted set, IDMS must physically disconnect and reconnect the record to keep the chain ordered. If you change a CALC key where duplicates are not allowed, this may be rejected outright — the usual pattern is ERASE plus STORE.

ERASE — four verbs wearing one name

Delete is four different commands wearing one name

The plain form refuses if the record still has children. The other three delete children too — and they differ in how they treat children that are also attached to some other parent.

This matters more than it looks. One delete command can remove thousands of records across several storage areas. If you are copying this data somewhere, a single source operation can arrive as an enormous burst of deletions that all have to be applied together.

StatementEffect on the recordEffect on members of sets it owns
ERASE recordErased — but only if it owns no non-empty setNone; the statement is rejected if any member exists
ERASE record PERMANENTErasedMANDATORY members erased recursively; OPTIONAL members disconnected and left as orphans
ERASE record SELECTIVEErasedMANDATORY members erased recursively; OPTIONAL members erased only if they belong to no other set, otherwise disconnected
ERASE record ALLErasedAll members erased recursively, mandatory or not, regardless of other memberships
One verb, an unbounded number of deletions ERASE … ALL on a record at the top of a deep structure can cascade through thousands of occurrences across several areas, in one statement, in one transaction. For replication this means a single source operation can generate an enormous burst of delete images that must be applied atomically. Ask the application team which ERASE forms are actually used before you size anything.

After any ERASE, currency of run unit is null. IDMS deliberately leaves you nowhere, because the obvious next position — the erased record — no longer exists. Programs that assume they can continue a walk after an erase are broken; the correct pattern is to save the next member's db-key before erasing.

CONNECT and DISCONNECT

moving an employee between departments
*> DEPT-EMPLOYEE is OPTIONAL AUTOMATIC, so this is legal
    MOVE WS-EMP-ID  TO EMP-ID-0415.
    OBTAIN CALC EMPLOYEE.
    DISCONNECT EMPLOYEE FROM DEPT-EMPLOYEE.

    MOVE WS-NEW-DEPT TO DEPT-ID-0410.
    OBTAIN CALC DEPARTMENT.        *> new owner becomes current of set

    OBTAIN EMPLOYEE DB-KEY IS WS-EMP-DBKEY.   *> re-establish the employee
    CONNECT EMPLOYEE TO DEPT-EMPLOYEE.

Note line 4 of that fragment: after obtaining the new department, the current of run unit is the department, not the employee. CONNECT acts on the current of run unit. Without the OBTAIN … DB-KEY you would be trying to connect a department to a set it does not belong to. This is the single most common IDMS bug, and the interpreter above will happily reproduce it for you.

How errors are reported

There are no exceptions here

Nothing throws. After every single command the database sets a four-character code, and the program is expected to look at it.

Two of those codes are not errors at all and appear constantly: 0307 means you reached the end of a chain, and 0326 means nothing matched your key. They are how loops terminate and how “not found” is reported.

Every DML call sets ERROR-STATUS in the IDMS communication block: four characters, a two-digit major code identifying the function and a two-digit minor code identifying the condition. Checking it after every call is not optional style; it is the only error mechanism there is.

The conditions you will meet constantly. Minor codes are stable; confirm the exact major-code pairing for your release in the CA IDMS Messages and Codes manual.
StatusMeaningUsually caused by
0000Success—
0307End of set, area or indexThe normal loop terminator. Not an error.
0326Record not foundNo occurrence matches the CALC key supplied
xx05Record, set or area not in the subschemaModelling against the wrong subschema, or a typo
xx06Currency not establishedNavigating a set you have not positioned in — the classic currency bug
xx66Area not readied in the required usage modeAttempting an update after READY … RETRIEVAL
xx29Owner of a non-empty setPlain ERASE on a record that still owns members
xx12Duplicate key not allowedSTORE violating DUPLICATES ARE NOT ALLOWED
3xxxDeadlock or timeout familyLock contention — see Module 08
the canonical COBOL loop
       MOVE WS-DEPT-ID TO DEPT-ID-0410.
       OBTAIN CALC DEPARTMENT.
       IF DB-REC-NOT-FOUND
           PERFORM NO-SUCH-DEPARTMENT
       ELSE
           PERFORM WALK-EMPLOYEES.

       WALK-EMPLOYEES.
           OBTAIN FIRST EMPLOYEE WITHIN DEPT-EMPLOYEE.
           PERFORM UNTIL DB-END-OF-SET
               ACCEPT WS-EMP-DBKEY FROM CURRENCY
               PERFORM PROCESS-ONE-EMPLOYEE
               OBTAIN EMPLOYEE DB-KEY IS WS-EMP-DBKEY
               OBTAIN NEXT EMPLOYEE WITHIN DEPT-EMPLOYEE
           END-PERFORM.

DB-END-OF-SET and DB-REC-NOT-FOUND are condition names generated into the communication block copybook — they test ERROR-STATUS for 0307 and 0326 respectively. The ACCEPT/re-OBTAIN pair inside the loop is the defensive idiom: PROCESS-ONE-EMPLOYEE may navigate anywhere it likes, and the walk still resumes from the right place.

Five ways this goes wrong, and the fix for each

1. Interleaved set walks

Walking DEPT-EMPLOYEE and, per employee, walking EMP-EMPOSITION. The inner walk moves currency of DEPT-EMPLOYEE too, because EMPOSITION retrieval does not — but the OBTAIN OWNER at the end does.

Fix: ACCEPT the outer db-key before the inner loop and re-OBTAIN it afterwards.

2. Continuing after ERASE

Currency of run unit is null after an erase, so the next OBTAIN NEXT returns a currency error or, worse, resumes from a stale set currency.

Fix: save the next member's db-key before erasing, then re-position.

3. MODIFY after FIND

FIND does not populate the work area. A subsequent MODIFY writes whatever was in it — often the previous record.

Fix: use OBTAIN, or issue an explicit GET before modifying.

4. STORE with stale owner currency

An AUTOMATIC member connects to whatever owner is current. In a loop, that is frequently the previous iteration's owner.

Fix: establish every owner immediately before the STORE, every time.

5. Assuming 0307 means "empty"

0307 from OBTAIN NEXT means "you have reached the owner again", not "there was nothing". A set with three members returns it on the fourth call.

Fix: use IF set-name IS EMPTY to test emptiness — it costs no I/O.

6. Long-held run units

Not a correctness bug but the operational one: a run unit that never commits holds locks across everything it touched.

Fix: COMMIT periodically in long batch, and be aware it releases currency unless you use COMMIT ALL.

Saving, undoing, and locking a record on purpose

If you know SQL transactions

COMMIT and ROLLBACK mean what you expect. The one with no SQL equivalent is KEEP EXCLUSIVE, which is this database’s SELECT ... FOR UPDATE: it locks the record you are sitting on before you modify it, so another program cannot slip in between your read and your write.

StatementDoesCurrency afterwards
COMMITWrites a checkpoint; updates become durable; locks releasedReleased
COMMIT ALLSame, but currencies are retained so a long walk can continueRetained
ROLLBACKBacks out to the last checkpoint using before-imagesReleased
ROLLBACK CONTINUEBacks out but leaves the run unit activeReleased
FINISHEnds the run unit, commits, releases areas and locksGone
KEEPPlaces an explicit shared lock on the current recordUnchanged
KEEP EXCLUSIVEExplicit exclusive lock — the read-then-update patternUnchanged

KEEP EXCLUSIVE is how an IDMS program does optimistic-turned-pessimistic locking: retrieve, decide you will update, take the lock before anyone else can, then modify. Without it, two run units can read the same record and both write, and the second write silently wins.

A complete program

give everyone in a department a rise, safely
       IDENTIFICATION DIVISION.
       PROGRAM-ID. EMPRAISE.
       ENVIRONMENT DIVISION.
       IDMS-CONTROL SECTION.
       PROTOCOL. MODE IS BATCH DEBUG
           IDMS-RECORDS MANUAL.
       DATA DIVISION.
       SCHEMA SECTION.
       DB EMPSS01 WITHIN EMPSCHM VERSION 100.
       WORKING-STORAGE SECTION.
       COPY IDMS SUBSCHEMA-CONTROL.
       COPY IDMS SUBSCHEMA-NAMES.
       COPY IDMS RECORD DEPARTMENT.
       COPY IDMS RECORD EMPLOYEE.
       COPY IDMS RECORD EMPOSITION.
       01  WS-EMP-DBKEY     PIC S9(8) COMP.
       01  WS-COUNT         PIC 9(5) VALUE ZERO.
       01  WS-RISE          PIC S9(3)V99 COMP-3 VALUE 1.04.

       PROCEDURE DIVISION.
       MAIN-LINE.
           BIND RUN-UNIT.
           BIND DEPARTMENT. BIND EMPLOYEE. BIND EMPOSITION.
           READY ORG-DEMO-REGION USAGE RETRIEVAL.
           READY EMP-DEMO-REGION USAGE UPDATE.

           MOVE '3200' TO DEPT-ID-0410.
           OBTAIN CALC DEPARTMENT.
           IF DB-REC-NOT-FOUND
               DISPLAY 'NO SUCH DEPARTMENT'
               PERFORM CLOSE-DOWN
           END-IF.

           OBTAIN FIRST EMPLOYEE WITHIN DEPT-EMPLOYEE.
           PERFORM UNTIL DB-END-OF-SET
               ACCEPT WS-EMP-DBKEY FROM CURRENCY
               PERFORM RAISE-ONE
               OBTAIN EMPLOYEE DB-KEY IS WS-EMP-DBKEY
               OBTAIN NEXT EMPLOYEE WITHIN DEPT-EMPLOYEE
           END-PERFORM.

           PERFORM CLOSE-DOWN.

       RAISE-ONE.
           IF EMP-EMPOSITION IS EMPTY
               GO TO RAISE-EXIT
           END-IF.
           OBTAIN FIRST EMPOSITION WITHIN EMP-EMPOSITION.
           KEEP EXCLUSIVE CURRENT.
           COMPUTE SALARY-AMOUNT-0420 =
                   SALARY-AMOUNT-0420 * WS-RISE.
           MODIFY EMPOSITION.
           IF ERROR-STATUS NOT = '0000'
               DISPLAY 'MODIFY FAILED ' ERROR-STATUS
               ROLLBACK
               PERFORM CLOSE-DOWN
           END-IF.
           ADD 1 TO WS-COUNT.
           IF WS-COUNT / 100 * 100 = WS-COUNT
               COMMIT ALL
           END-IF.
       RAISE-EXIT.
           EXIT.

       CLOSE-DOWN.
           FINISH.
           DISPLAY 'UPDATED ' WS-COUNT ' POSITIONS'.
           GOBACK.

Read that program looking for the five things it does deliberately:

  1. Areas readied in the weakest usage mode that works — ORG is retrieval only.
  2. ACCEPT/re-OBTAIN around the inner work, so the outer walk survives.
  3. IF … IS EMPTY instead of relying on a 0307 from the first OBTAIN.
  4. KEEP EXCLUSIVE before the read-modify-write.
  5. COMMIT ALL every hundred rows, so locks and journal footprint stay bounded while currency survives.

What this means for replication

  • The journal shows physical effects, not verbs. One MODIFY of a sort key can produce a disconnect, a reconnect and index-block updates. Your capture layer must distinguish user data changes from structural maintenance, or the target sees churn that never happened logically.
  • Transaction boundaries come from COMMIT and FINISH. A batch job that commits every 100 rows gives you 100-row transactions; one that never commits until the end gives you a single enormous one that the apply side must handle atomically.
  • ERASE … ALL is a fan-out. Size your apply pipeline for the worst cascade in the application, not the average change.
  • Local-mode and KEEP-free readers can see torn state. If you build a validation extract, ready areas PROTECTED RETRIEVAL or reconcile against a journal position; a plain RETRIEVAL sweep of a busy area is not a snapshot of anything.
  • Currency bugs in the source produce real data. A record connected to the wrong owner by a stale-currency bug is, as far as the database is concerned, correct. Reconciliation will show it as a difference from whatever the business expects; the source is where the bug lives.

Checkpoint