The following procedure lists all ACTIVE state transaction Ids along with the associated user and table numbers and names.
- The procedure starts by scanning the _Lock table to populate a TEMP-TABLE with only the _Lock records of interest.
- It then ties this TEMP-TABLE with the _Trans table to access the transaction IDs.
- Finally, it ties the TEMP-TABLE with the _File table to obtain the table name.
The TEMP-TABLE is used to enhance the performance of the code and limit the processing of the _Lock table to those records that are of interest to us:
This procedure will not capture any transactions that do not involve locked tables. For example, the ACTIVE transaction started by executing the following SQL query from a SQL client will not be captured because there are no locked tables involved:
"SELECT PUB.NextCustNum.NEXTVAL FROM PUB.Customer"
DEFINE TEMP-TABLE ttLock
FIELD LockId LIKE _Lock._Lock-Id
FIELD LockUsr LIKE _Lock._Lock-Usr
FIELD LockName LIKE _Lock._Lock-Name
FIELD LockTable LIKE _Lock._Lock-Table
FIELD LockFlags LIKE _Lock._Lock-flags
INDEX LockIdx IS PRIMARY UNIQUE LockId.
FOR EACH _Lock NO-LOCK:
IF _Lock._Lock-Usr = ? THEN NEXT .
CREATE ttLock.
ASSIGN
LockId = _Lock._Lock-Id
LockUsr = _Lock._Lock-Usr
LockName = _Lock._Lock-Name
LockTable = _Lock._Lock-Table
lockFlags = _Lock._Lock-flags.
END.
FOR EACH ttlock:
FIND _Trans NO-LOCK WHERE _Trans._Trans-Usrnum = ttLock.LockUsr NO-ERROR.
FIND _File NO-LOCK WHERE _File-Number = ttLock.LockTable.
MESSAGE
"Transaction Id:~t" (IF AVAILABLE _Trans THEN _Trans._Trans-Id ELSE ?) "~n"
"User Number:~t" ttLock.LockUsr "~n"
"User Name~t" ttLock.LockName "~n"
"Table Number:~t" ttLock.LockTable "~n"
"Table Name:~t" _File-Name "~n"
"Flags:~t" ttLock.LockFlags
VIEW-AS ALERT-BOX INFO BUTTONS OK.
END.
