Feedback
Did this article resolve your question/issue?

   

Article

Tracing Operations Per Table: VST's _tablestat _indexstat

« Go Back

Information

 
TitleTracing Operations Per Table: VST's _tablestat _indexstat
URL Name19450
Article Number000120685
EnvironmentProduct: Progress
Version: 8.3x, 9.x
Product: OpenEdge
Version: All supported versions
OS: All supported platforms
Other: _tablestat _indexstat
Question/Problem Description
Tracing operations per table: VST's _tablestat _indexstat
How to trace database activity per table to monitor how many records are being created, deleted, read and updated
How to trace database table activity for each index being created, deleted, read and updated
How to get number of create, read, update, delete on a table or an  index?
How to trace the activity on a table or an index?
Steps to Reproduce
Clarifying Information
Error Message
Defect Number
Enhancement Number
Cause
Resolution
Monitoring database activity per table or table activity for each index can be achieved by using Virtual System Tables (VST) _tablestat _indexstat.  This Article applies to Progress Versions 8.3 and later, because the tables needed to trace record's activity were not implemented until 8.3

Starting in Progress 8.2A, Progress implemented a new feature called 'Virtual System Tables' to provide general information on database activity from 4GL, but _tablestat _indexstat were not added until Version 8.3. These tables need to be explicitly enabled by running the following against an offline database:
 
$   proutil <db-name> -C enablevst

Starting in Progress 9 and later, VST's are enabled by default, so this step is not necessary, but it may be necessary to update to the current version's VST fields by running the following against an offline database:
 
$   proutil <db-name> -C updatevst

Setting Database Startup Parameters for Activity tracing

There are startup parameters that need to be used to activate table / index activity tracing.  These parameters define the table/index range to be traced, all application tables/indexes or just some of them.  The parameters are different for Progress 8 and Progress 9 or later), so two sections follow below.

Progress Version 8.3

When these parameters are not specified, the tables/indexes activity tables will be empty.

For table tracing:
  • -tablebase n   /* the first table' number for the range*/
  • -tablelimit n  /* the last table' number  for the range*/
Where "n" is the table number.

For index tracing:
  • -indexbase n  /* the first index' number for the range */
  • -indexlimit n /* the last index' number for the range */
Where "n" is the index number.

Example:
$   proserve db-name -tablebase 2 -tablelimit 30

Progress 9 and later

When these parameters are not specified, the first 50 tables and indexes will be traced by default.

For table tracing:
  • -basetable n   /* the first table' number for the range*/
  • -tablerangesize n  /* the number of tables you want to trace starting from -basetable */
Where "n" is the table number.

For index tracing:
  • -baseindex n  /* the first index' number for the range */
  • -indexrangesize n /* the number of indexes you want to trace starting from -baseindex */
Where "n" is the index number.

These parameters can to be set:
  • On the client startup parameters when running in single-user mode, or
  • On the database startup paramters line for multi-user mode
To find the table/index number:
/*Shows the table number */

FOR EACH _file where _file-number > 0:
  DISPLAY _file-number _file-name.
END.

/* 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.

How to trace table and index activity

Once the startup parameters have been appropriately set, there are two VST tables that can be used to trace the activity. These tables are:
  • _TableStat (for table activity) and
  • _IndexStat (for index activity).
The following simplified examples demonstrate:
 
/* 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.

A combined, runnable sample is attached to this article (000001484.p).
Workaround
Notes
Last Modified Date12/3/2021 11:06 AM
Attachment 
Files 1. 000001484
Disclaimer The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.