Using Data Areas in RPG to derive a primary key value

To derive a primary key value in such a way that it is truly unique, Data Area objects can be used programmatically from within (ILE RPG).

Basics

The integration of Data Area Objects into ILE RPG allow for the following operation (somewhat simplified):

• read and lock data area
• increment read value
• write new record to database
• write and unlock data area

Implementation

• Create the data area named maxidarea from a CL prompt:

crtdtaara dtaara(maxidarea) type(*dec) len(11 0) value(0)

• Define the data area to the application, either in a separate *INZSR, or at the beginning of C statements; the area is referenced in the application by its internal name newid:

     C     *DTAARA       DEFINE    MAXIDAREA     NEWID            11 0

Actual code:

     C     *LOCK         IN        NEWID
     C                   ADD       1             NEWID
     C                   Z-ADD     NEWID         ID
     C                   WRITE(E)  MYTBL
     C                   EVAL      FSTAT=%STATUS(MYTABLE)
     C     FSTAT         IFGT      *ZERO
     C                   UNLOCK    NEWID
     C                   ELSE
     C                   OUT       NEWID
     C                   ENDIF

Annotation:

• Read-and-lock the data area,
• Increment the read value,
• Copy the new value to the variable id for the primary key field in the PF,
• (Try to) write the record.
  • If there was an error, release the lock, so other instances of our application can read and update the ID.
  • If there was no error, write back the new value to the data area, and release the lock.

This works, because locking the data area with the IN statement as shown disallows other applications to also lock the data area. This effectively serializes write access (updates) to the data area.

This way of deriving unique IDs for primary key values can also be used with commitment control being active.

See also

• Faking an identity column

Weblinks

• Data-Area Operations, ibm.com