Kbase P27702: How to find who is locking what table(s) using 4GL under 8.2
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
26/06/2003 |
|
Status: Unverified
GOAL:
How to find who is locking what table(s) using 4GL under 8.2x & 8.3x when the database has many tables?
FIX:
/******************************************************************************/
/* Implementing the code of solution P27146 would generate a large procedure */
/* that may produce an action segment error when run or compiled.This solution*/
/* breakes the generated code into several internal procedures to avoid the */
/* action segement limitation imposed on versions 8.2x and 8.3x. */
/******************************************************************************/
OUTPUT TO F:\PROGRESS\WRK91c\dbTableRecid.p.
DEFINE VARIABLE iTableCounter AS INTEGER NO-UNDO.
DEFINE VARIABLE iNumOfTables AS INTEGER NO-UNDO.
DEFINE VARIABLE iProcCounter AS INTEGER NO-UNDO.
DEFINE VARIABLE iNumOfProcs AS INTEGER NO-UNDO.
DEFINE VARIABLE iLastProcNum AS INTEGER NO-UNDO.
DEFINE VARIABLE iTablesPerProc AS INTEGER NO-UNDO.
/* get the total number of data tables */
FOR EACH _File WHERE _File-Number > 0 AND _File-Number < 32767 NO-LOCK:
iNumOfTables = iNumOfTables + 1.
END.
/* set the number of internal procedures to 4. This number may be increased as */
/* desired. If it more than half the number of tables, only one internal */
/* procedure will be generated. */
ASSIGN
iNumOfProcs = 4.
PUT "DEFINE TEMP-TABLE ttRecidTable" FORMAT "X(80)" SKIP.
PUT " FIELD cTableName AS CHARACTER" FORMAT "X(80)" SKIP.
PUT " FIELD rRecordid AS RECID" FORMAT "X(80)" SKIP.
PUT " INDEX RecordId IS PRIMARY rRecordid ASCENDING." FORMAT "X(80)" SKIP(1).
PUT "DEFINE INPUT PARAMETER ipcRecordNum AS INTEGER NO-UNDO." FORMAT "X(80)" SKIP.
PUT "DEFINE OUTPUT PARAMETER opcTableName AS CHARACTER NO-UNDO." FORMAT "X(80)" SKIP(1).
/* generate the RUN statement for each of the internal procedures to be generated */
DO iProcCounter = 1 TO iNumOfProcs:
PUT "RUN PROC" + STRING(iProcCounter) + "." FORMAT "X(80)" SKIP.
END.
PUT SKIP(1).
PUT "FIND FIRST ttRecidTable WHERE" FORMAT "X(80)" SKIP.
PUT " ttRecidTable.rRecordid = ipcRecordNum" FORMAT "X(80)" SKIP.
PUT " NO-LOCK NO-ERROR." FORMAT "X(80)" SKIP(1).
PUT "ASSIGN" FORMAT "X(80)" SKIP.
PUT " opcTableName = ttRecidTable.cTableName." FORMAT "X(80)" SKIP(1).
PUT "RETURN." FORMAT "X(80)" SKIP(1).
/* initialize the number of generated procedures to 1 */
ASSIGN
iProcCounter = 1
iTableCounter = 1
iTablesPerProc = (iNumOfTables - (iNumOfTables MOD iNumOfProcs)) / iNumOfProcs
iLastProcNum = iNumOfTables - (iNumOfTables MOD iTablesPerProc) - iTablesPerProc + 1.
/* Write the PROCEDURE 'header' statement for the first internal procedure */
PUT "PROCEDURE " + "PROC" + STRING(iProcCounter) + ":" FORMAT "X(80)" SKIP(1).
FOR EACH _File WHERE _File-Number > 0 AND _File-Number < 32767 NO-LOCK:
IF iTableCounter MODULO (iTablesPerProc) = 1 AND
iTableCounter > 1 AND
iTableCounter <= iLastProcNum THEN DO:
iProcCounter = iProcCounter + 1.
IF iProcCounter <= iNumOfProcs THEN DO:
PUT "END PROCEDURE." FORMAT "X(80)" SKIP(1).
PUT "PROCEDURE " + "PROC" + STRING(iProcCounter) + ":" FORMAT "X(80)" SKIP.
END.
END.
PUT " FOR EACH " + _File-Name + " NO-LOCK:" FORMAT "X(80)" SKIP.
PUT " CREATE ttRecidTable." FORMAT "X(132)" SKIP.
PUT " ASSIGN" FORMAT "X(80)" SKIP.
PUT " cTableName = " + '"' + _File-Name + '"' FORMAT "X(80)" SKIP.
PUT " rRecordid = RECID(" + _File-Name + ")." FORMAT "X(80)" SKIP.
PUT " END." SKIP(1).
iTableCounter = iTableCounter + 1.
END.
/* Write the END PROCEDURE 'footer' statement for the last internal procedure */
PUT "END PROCEDURE." FORMAT "X(80)" SKIP(1).
OUTPUT CLOSE.