Consultor Eletrônico

Status: Unverified

GOAL:

Tracing operations per table: VST's _tablestat _indexstat

GOAL:

How to trace the operations on your database per table, so you can check how many records are being created, deleted, read and updated on each table.

GOAL:

How to trace the operations on your database per table?

GOAL:

How to check how many user have been created per table?

GOAL:

How to check how many records have been deleted per table?

GOAL:

How to check how many records have been read per table?

GOAL:

How to check how many records have been updated per table?

FACT(s) (Environment):

Progress 8.3
Progress 9.x
OpenEdge 10.x

FIX:

This can be achieved by using Virtual System Tables (VST).

Starting on 8.2A, Progress implemented a new feature called 'Virtual System Tables' to provide general information on database activity from 4GL.
In version 8.3, you need to enable these tables. The command to enable them is:

proutil <db-name> -C enablevst

NOTE: VST's ARE ALREADY enabled on version 9 by default, so this step is not necessary.

This solution applies only to 8.3 and 9.X, but not 8.2, because the tables needed to trace record's activity were implemented in 8.3.


FIRST STEP:

There are startup parameters that need to be used to activate the table activity tracing.

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

Basically, they define the table/index range you want to trace, therefore you can trace all tables/indexes or just some of them.

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.
The parameters are different for version 8 and 9, so two sections follow below:

Progress Version 8.3
--------------------
For version 8.3, the startup parameters are:

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.


NOTE: You MUST specify these parameters, or the tables/indexes activity tables will be empty.

For example: proserve <db-name> -tablebase 2 -tablelimit 30

Version 9.X
-----------
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 */


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

If you don't specify any of these parameters for version 9.X, Progress will trace the first 50 tables and indexes, by default.


HOW TO TRACE:
There are two tables that allow you to trace the activity. These tables are _TableStat (for table activity) and _IndexStat (for index activity).

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.

&nbsp.; /* 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.
.