Kbase P158475: 4GL/ABL: Is there any way to see what table the transaction is against?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/01/2010 |
|
Status: Unverified
GOAL:
4GL/ABL: Is there any way to see what table the transaction is against?
GOAL:
How to relate the _Lock, _Trans and _File table to list the database transactions with the associated users and tables?
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
OpenEdge Category: Language (4GL/ABL)
FIX:
The following procedure lists all active transaction Ids along with their associated user and table numbers and names. The procedure starts by scanning the _Lock table to populate a TEMP-TABLE is 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 here to enhance the performance of the code and limit the processing of the _Lock table to those records that are of interest to us:
IMPORTANT: This procedure will not capture any transactions that do not involve locked tables. For example, the active transaction started by executing the SQL query: "SELECT PUB.NextCustNum.NEXTVAL FROM PUB.Customer" from an SQL client will not be captured by this 4GL/ABL procedure because there are no locked tables involved:
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
INDEX LockIdx IS PRIMARY UNIQUE LockId.
FOR EACH _Lock NO-LOCK:
IF _Lock._Lock-Table = ? OR _Lock._Lock-Usr = ? THEN LEAVE.
CREATE ttLock.
ASSIGN
LockId = _Lock._Lock-Id
LockUsr = _Lock._Lock-Usr
LockName = _Lock._Lock-Name
LockTable = _Lock._Lock-Table.
END.
FOR EACH ttlock:
FIND _Trans NO-LOCK WHERE _Trans._Trans-Usrnum = ttLock.LockUsr NO-ERROR.
IF AVAILABLE _Trans THEN DO:
FIND _File NO-LOCK WHERE _File-Number = ttLock.LockTable.
IF AVAILABLE _File THEN
MESSAGE
"Transaction Id:~t" _Trans._Trans-Id "~n"
"User Number:~t" ttLock.LockUsr "~n"
"User Name~t" ttLock.LockName "~n"
"Table Number:~t" ttLock.LockTable "~n"
"Table Name:~t" _File-Name
VIEW-AS ALERT-BOX INFO BUTTONS OK.
END.
END.