Normally the statistics for tables and indices (including TEMP-TABLEs and their indices) are available by reading the _
TableStat and _
IndexStat virtual system tables, while the tables are in use. Unfortunately, for TEMP-TABLEs this information is destroyed once the TEMP-TABLEs (and their indexes) are deleted.
Therefore, if there is a need to collect the statistics for usage of TEMP-TABLEs for later analysis, there are two ways to accomplish this.
- Use the TTStats log entry type with LOG-MANAGER to log all of this information into a flat file.
- This requires parsing the LOG-MANAGER output and massaging it into data structures in order to use it for analysis.
Examples: <PROEXE> -clientlog <logfilename> -logentrytypes TTStats[:<n>] | [-logginglevel <n>]
In PASOE: [AppServer.SessMgr.oepas1]
agentLogEntryTypes=ASPlumbing,DB.Connects,4gltrace:3
,TTStats:3 Classic AppServer and WebSpeed:
[UBroker.AS.asbroker1]
brkrLogEntryTypes=UBroker.Basic
srvrLogEntryTypes=ASPlumbing,DB.Connects
For more information about using TTStats logging, see:
Is there a LOG-ENTRY-TYPE to log TEMP-TABLE statistics?- Use the _TableStatHist and _IndexStatHist tables to capture the historical usage information for the session.
NOTE: You cannot see statistics for any temp-table from within the same procedure/scope of TEMP-TABLEs you wish to track.
This information can ONLY be captured after the procedure that defines the TEMP-TABL(s) have finished executing (The TEMP-TABLE(s) must be out of scope).
- There are a combination of steps you can use to capture statistical data about Table and Index usage in TEMP-TABLEs. To enable this feature and access this data, you must follow the steps below:
- Start your ABL session with either or both of the the -tttablerangesize <n> -ttindexrangesize <n> parameters. For instance, if you do not want to track index usage statistics in a temp-table you can omit the -ttindexrangesize <n> parameter.
For example: proenv>prowin -tttablerangesize 100 -ttindexrangesize 100
Note: Nothing will be captured if no range is defined for the resource (tables/indexes) you wish to capture using these parameters.
- Before running the code you wish to track, enable the Progress.Database.TempTableInfo:ArchiveTableStatistics and/or Progress.Database.TempTableInfo:ArchiveIndexStatistics properties.
Example:
/* enableTTHistory.p */
USING Progress.Database.*.
/* Enable the archival of Table and Index statistics
using the TempTableInfo class properties.
Note: -tttablerangesize and/or -ttindexrangesize must
have a value greater than 0 for this to be effective. */
TempTableInfo:ArchiveTableStatistics = TRUE.
TempTableInfo:ArchiveIndexStatistics = TRUE.
- Run code you wish to track to completion.
/* applicationCode.p */
DEFINE TEMP-TABLE ttTest NO-UNDO
FIELD f1 AS INT
FIELD f2 AS CHAR
INDEX idx1 IS PRIMARY UNIQUE f1.
DEFINE VARIABLE iCount AS INTEGER NO-UNDO.
DO iCount = 1 TO 10000:
CREATE ttTest.
ASSIGN ttTest.f1 = iCount
ttTest.f2 = STRING(iCount).
END.
DO iCount = 1 TO 10000 BY 3:
FIND ttTest WHERE ttTest.f1 EQ iCount NO-ERROR.
DELETE ttTest.
END.
- Read, View and/or Archive the data to XML or JSON:
/* queryTTUsageHistory.p */
USING Progress.Database.*.
/* Define handle variables to hold the TempTable and Index info */
DEFINE VARIABLE hTTInfo AS HANDLE NO-UNDO.
DEFINE VARIABLE hIdxInfo AS HANDLE NO-UNDO.
DEFINE VARIABLE hTTBuff AS HANDLE NO-UNDO.
DEFINE VARIABLE hIdxBuff AS HANDLE NO-UNDO.
DEFINE VARIABLE hTTQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE hIdxQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE cOutputFile AS CHARACTER NO-UNDO
INITIAL "tempTableStats_&1_&2_&3.".
DEFINE BROWSE brTT WITH SIZE 75 BY 10.
DEFINE BROWSE brIdx WITH SIZE 75 BY 10.
DEFINE FRAME fStats brTT SKIP(1) brIdx.
/* Use GetTableStatHistoryHandle() and GetIndexStatHistoryHandle()
methods to get the handles of the associated temp-tables. */
ASSIGN hTTInfo = TempTableInfo:GetTableStatHistoryHandle()
hTTBuff = hTTInfo:DEFAULT-BUFFER-HANDLE
hIdxInfo = TempTableInfo:GetIndexStatHistoryHandle()
hIdxBuff = hIdxInfo:DEFAULT-BUFFER-HANDLE.
/* Add a timestamp to the output filename */
cOutputFile = SUBSTITUTE(cOutputFile,"~&1",
REPLACE(STRING(TODAY,"99/99/9999"),"/",""),
REPLACE(REPLACE(STRING(TIME,"HH:MM:SS AM"),":","")," ","")).
/* Demonstrate writing out XML with the Table and Index history */
hTTInfo:WRITE-XML("FILE",SUBSTITUTE(cOutputFile,"table") + "xml").
hIdxInfo:WRITE-XML("FILE",SUBSTITUTE(cOutputFile,"index") + "xml").
/* Demonstrate writing out JSON with the Table and Index history */
hTTInfo:WRITE-JSON("FILE",SUBSTITUTE(cOutputFile,"table") + "json").
hIdxInfo:WRITE-JSON("FILE",SUBSTITUTE(cOutputFile,"index") + "json").
/* Demonstrate accessing the data using BROWSE widgets */
CREATE QUERY hTTQuery.
hTTQuery:SET-BUFFERS(hTTBuff).
BROWSE brTT:QUERY = hTTQuery.
ASSIGN BROWSE brTT:SENSITIVE = FALSE
BROWSE brTT:EXPANDABLE = FALSE.
BROWSE brTT:ADD-COLUMNS-FROM(hTTBuff).
ASSIGN BROWSE brTT:SENSITIVE = TRUE
BROWSE brTT:EXPANDABLE = TRUE.
CREATE QUERY hIdxQuery.
hIdxQuery:SET-BUFFERS(hIdxBuff).
BROWSE brIdx:QUERY = hIdxQuery.
ASSIGN BROWSE brIdx:SENSITIVE = FALSE
BROWSE brIdx:EXPANDABLE = FALSE.
BROWSE brIdx:ADD-COLUMNS-FROM(hIdxBuff).
ASSIGN BROWSE brIdx:SENSITIVE = TRUE
BROWSE brIdx:EXPANDABLE = TRUE.
hTTQuery:QUERY-PREPARE("FOR EACH " + hTTBuff:NAME).
hTTQuery:QUERY-OPEN().
hIdxQuery:QUERY-PREPARE("FOR EACH " + hIdxBuff:NAME).
hIdxQuery:QUERY-OPEN().
ENABLE ALL WITH FRAME fStats.
WAIT-FOR CLOSE OF THIS-PROCEDURE.
FINALLY:
/* Clean up */
IF VALID-HANDLE(hTTQuery) AND
hTTQuery:IS-OPEN THEN
hTTQuery:QUERY-CLOSE() NO-ERROR.
DELETE OBJECT hTTQuery NO-ERROR.
IF VALID-HANDLE(hIdxQuery) AND
hIdxQuery:IS-OPEN THEN
hIdxQuery:QUERY-CLOSE() NO-ERROR.
DELETE OBJECT hIdxQuery NO-ERROR.
END FINALLY.
The above program demonstrates writing out XML and JSON as well as displaying the history results in BROWSE widgets.
