DataGate® 16.0 for SQL Server Reference Guide

Locking Considerations

The following table details important data access considerations when converting or communicating between DataGate® for IBM i, DataGate® for SQL Server, and the core DataGate® program.

Record Locking


DB2/400 determines the type and duration of records locks depending on how the file was opened.

For read-only files, when a record is read, there is no lock requested on it, and if some other application has the record lock, the reading application does not block on the lock, that is, the record is read in spite of being locked by somebody else.

For files open for update, every time a record is read it is write-locked so that other updating applications cannot read it. The write lock is held until the record is updated or explicitly unlocked by the application or when another record is read or positioned to.


DSS (using server cursors) determines the locking characteristics based on how the file is opened.

For read-only files DSS behaves like DG/400, that is, locks are neither placed nor considered on records being read.

The behavior of DSS when the file is opened for update is similar to DG/400 but with two significant differences: updating a record does not release the lock on the record and explicitly unlocking a record causes the 'current record position' to be lost. These differences bear the following considerations.




Unlock Record

Cursor position is unchanged.

The file has no 'current' position after the Unlock.

Update Record

The record just updated is released.

The record just updated is kept locked.

*NoLock option on Read operations

Supported but deprecated.


The better way to achieve this is to open the file twice, once for input only and the other for update. Where the read appears with the *NoLock option, the file should be substituted with the one open for input only. By doing this, the application can take advantage of network blocking - yielding better performance.

Range operations

When the end of the range is reached, the file has no 'current' position.

When the end of the range is reached, the file has no 'current' position.

Hit EOF on a ReadE (P)

Lose Record position.

Lose Record position.

Other Operations like SetLL

Unlock Record.

Unlock Record.

Loops involving SetLL/SetGT and Read/ReadE/ReadPE should be re-coded to use the Range operations.

The most demanding change is the one requiring segments of code involving CHAIN-UPDATE. Combinations have to be studied and possibly modified.

  • If the CHAIN-UPDATE happens in a tight loop, then at the end of the loop an UNLOCK should be issued to release the last record updated. Note however that the record position will be lost after the UNLOCK.
  • If the CHAIN-UPDATE is sprinkled throughout the code, then each case has to be closely studied.
Object Locking

For DSS, object locking is implemented only for Data Area objects.