PROSTRCT STATISTICS can be used to monitor the space usage of extents in each storage area. The report provides detailed information such as:
- The database name
- DB/AI/BI block sizes
- The location and size of database extents
- The number of active blocks (blocks with data and free blocks), empty blocks, total number of blocks which can be used to identify amount of blocks being utilized by the database, verses the amount of blocks allocated for use by the database
- If and where the database is using Type I or Type II storage area structures
- The date and time of the last full backup.
Since Progress 9.1E04, OpenEdge 10.0B05 and 10.1A, PROSTRCT STATISTICS can be run whilst the database is online as well as offline. Prior to these versions, it can only be run offline. When the utility is run online a message is displayed to stdout which indicates that the database is in use in update mode, and is therefore being accessed in a -RO (Read Only) manner.
To use PROSTRCT STATISTICS:
$: prostrct statistics <db name>
To send output to the screen redirect the output to a file:
$: prostrct statistics <dbname> > filename
Example: The following is an example of the PROSTRCT STATISTICS output for a database that is in a multi-user mode. Notice the Warning that the database is in update mode:
WARNING: Before-image file of database dbname is not truncated. (1552)
OpenEdge Release 10.2B08
Storage Utilization Statistics
Database: dbname
Primary data block size: 8192
BI block size: 16384
AI block size: 16384
Database Physical structure information
Statistics for Area: Data
Files in Area: Data
C:\db\dbname_7.d1 62834868224
C:\db\dbname_7.d2 4194304
...
Database Block Usage for Area: Data
Active blocks: 7669759
Data blocks: 7669254
Free blocks: 505
Empty blocks: 1025
Total blocks: 7670784
Extent blocks: 2
Records/Block: 16
Cluster size: 512
...
Database Block Usage Summary
Active blocks: 8399284
Data blocks: 8398608
Free blocks: 676
Empty blocks: 1244
Extent blocks: 6
Total blocks: 8400528
Last full database backup on <date/time>. (6942)
or
NO FULL BACKUP HAS BEEN DONE. (6943)
Interpreting PROSTRCT "Statistics for Area" output:
- Files in Area: The location and size (bytes) of every extent in the Area
- Database Block Usage: The Active, Data, Free, Empty, Total block values reported can be converted to size by using the information from the first part of the report which shows the data block size.
- Extent Blocks: Is the number of extents in the Area
- Records/Block: The number of recids each database block is assigned.
- Cluster Size: A value of 1 reports Type I, values of 8,64,512 reports a Type II area structure where the value is the number of blocks formatted for each cluster.
Example: Interpreting "Data" Area
Files in Area:
The total size of the two extents is 62834868224 + 4194304 = 64,366,272 KB
Database Block Usage:
Total number of blocks is 7,670,784 x data block size(8) = 64,366,272 KB in total.
766,9759 blocks are Active = 61,358,072 KB is used
Since there are 1025 empty blocks, the available space: 4 x 4096 = 8,200 KB available, which is typical of a variable extent. In reality, for a Type II Storage area which this is, it is only one cluster that is empty (512 blocks or 4 MB) For a more detailed description refer to Article:
Why run PROSTRCT Statistics
- It is advisable to run PROSTRCT statistics periodically, for example whenever full backups are taken.
- These provide metrics to analyse and plan for database growth, in particular when to add new fixed extents when this space model is used.
- Alert for when an areas dbkeys straddle the 32/64-bit boundary which mean that a new set of issues may be encountered if left unaddressed like the need to convert fields that store these values to INT64. Another example is documented in Article Defect: Database crashes with 8783 or 210 error due to index corruption.
- Alert when Storage Areas approach the MAXAREA. Refer to Article When does the 2 billion rowid limit per Storage Area apply?
