Gathering statistics

The database driver provides an option to gather driver-wide statistics on actions performed, such as:

  • Number of cursors (opened, closed, current open).
  • Number of parses, binds, executes, fetches.
  • Number of logons (sessions or connections).
  • Number of inserts, updates, deletes.
  • Number of commits, rollbacks.

For each action, the cumulative elapsed time and the average time spent is logged. The statistics can be enabled with the environment variable MSQLSTAT. When the variable is set to zero, a statistics report is generated when the driver terminates (exit from LN). When a value n greater than zero is specified, the driver logs an incremental report every n seconds (the driver must be active). The statistics report is written to the file MSQLSTAT in the directory %BSE%\tmp.

These examples show how to set MSQLSTAT: 

SET MSQLSTAT=0 
SET MSQLSTAT=30

In the first example, MSQLSTAT is set to zero. With this value, only a final report is generated. In the second example, MSQLSTAT is set to 30. This logs a report every 30 seconds while the driver is active.

The following is a sample output of MSQLSTAT. Since the report is generic for all databases, some information, such as the specific row actions, may not be appropriate for a particular database driver. 

<3472> 2009-06-23[16:36:47]: Statistics [interval = 300]

DB-Cursor    Open     Close     Parse      Bind    Define   Execute     Fetch     Break
Count           7         7         7         7        13         9         5         5
Time(s)    0.0001    0.0001    0.0003    0.0001    0.0002    0.0100    0.0037    0.0000
Avg        0.0000    0.0000    0.0000    0.0000    0.0000    0.0011    0.0007    0.0000

Retained        #    Reused         %  Reparsed         %    Detach
Count           0         0       0.0         0       0.0         0

           BlobRd   BlobApp    BlobSz   BlobClr
Count          60        59        61         1
Time(s)    0.0198    0.3063    0.0085    0.0008
Avg        0.0003    0.0052    0.0001    0.0008

3/4GL       CrIdx     DrIdx     CrTbl     ClTbl     DrTbl     LkTbl     NrRow
Count           0         0         0         0         0         0         0
Time(s)    0.0000    0.0000    0.0000    0.0000    0.0000    0.0000    0.0000
Avg        0.0000    0.0000    0.0000    0.0000    0.0000    0.0000    0.0000

            Logon    Logoff    Commit  Rollback  ReadOnly
Count           1         1         1         1         0
Time(s)    0.0069    0.0014    0.0051    0.0723    0.0000
Avg        0.0069    0.0014    0.0051    0.0723    0.0000