Kbase P5281: How to get numbers of create, read, update, delete on a table or an index
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
01/06/2011 |
|
Status: Unverified
GOAL:
how to get number of create, read, update, delete on a table or an index
GOAL:
how to trace the activity on a table or an index
FACT(s) (Environment):
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
FIX:
This can be achieved by using the two Virtual System Tables (VST): _TableStat (for table activity) and _IndexStat (for index activity).
There are startup parameters that can be used to define the table/index range you want to trace. If you don't specify any of these parameters, by default Progress
will trace the first 50 tables and indexes.
These parameters are
-basetable n /* the first table' number for the range*/
-tablerangesize n /* the number of tables you want to
trace starting from -basetable */
-baseindex n /* the first index' number for the range */
-indexrangesize n /* the number of indexes you want to
trace starting from -baseindex */
The parameters need to be set:
- on the client startup command line for single-user mode
or
- on the database startup command line for multi-user mode
To find out the table/index number, you might connect to the database and run the following code:
/*it shows the table number */
For each _file where _file-number > 0:
display _file-number _file-name.
end.
/*it shows the index number*/
for each _file where _file-number > 0:
display _file-name with frame f.
for each _index of _file:
display _idx-num _index-name with frame g.
end.
end.
Here are two simple examples to show you how to trace table and index activity:
/* Traces table activity for each table in the range defined
by the startup parameters */
for each _tablestat.
find _file where _file-number = _tablestat-id.
display _file-name _tablestat-create _tablestat-update
_tablestat-read _tablestat-delete.
end.
/* Traces table activity for each index in the range defined
by the startup parameters */
for each _indexstat.
find _index where _idx-num = _indexstat-id.
display _index._index-name _indexstat-create
_indexstat-read _indexstat-delete.
end.
The counters are 32-bit integers and they will roll over when the maximum value is reached; they cannot be zeroed. You will have to save the data (within your own statistics tables for example) and make some calculations if you do not stop the database server.