Displays information about the degree of fragmentation for each table in a database. Also displays summary information about record and LOB (if present) sizes for each table.
Specifies that TABANALYS is to be performed on the area-name area only.
tenant tenant-name
Specifies that TABANALYS is to be filtered to only provide information related to objects that are owned by tenant-name. If tenant-name is not valid, TABANALYS exits with an error. Specifying tenant is only allowed on databases enabled for multi-tenancy.
group group-name
Specifies that TABANALYS is to be filtered to only provide information related to objects that are owned by group-name. If group-name is not valid, TABANALYS exits with an error. Specifying group is only allowed on databases enabled for multi-tenancy.
shared
Specifies that TABANALYS is to be filtered to only provide information related to shared objects. Specifying shared is only allowed on databases enabled for multi-tenancy.
-csoutput
Specifies that TABANALYS output the analysis data to text files in addition to the screen. The result is that the following files are created:
When -csoutput is specified, sep-value indicates the value to separate the columns of output. A space is used by default. Accepted values for sep-value are:
Any ASCII character between 0x21 and 0x7E
sp — to denote a space (the default)
tab — to denote a the TAB character (<\t> or ASCII character 0x09)
-csfilePrefix pref-name
When -csoutput is specified, pref-name indicates a file name prefix. The database name is used by default. All remaining elements of the file name are not customizable.
-verbose
When -csoutput is specified, the first row of the output files contains header names for each column of output.
Output format
In a complete report where no optional elements are specified on the command line, shared objects (both partitioned and non-partitioned) appear in alphabetical order by name. Partitions of partitioned tables are ordered as follows: the initial partition, then the remainder of the partitions in alphabetical order by partition name.
Example
The following output is a sample display of PROUTIL TABANALYS:
RECORD BLOCK SUMMARY FOR AREA "DataArea": 17
-------------------------------------------------------
RECORD BLOCK SUMMARY FOR SHARED TABLES
--------------------------------------
Summary for AREA "dbArea3": 9
----------------------------------------------
Subtotals: 1200 149.2M 1.0B 976.6K 127.3K
.
.
.
.
Table
Table owner and table name.
Records
Total number of records in the database for the table.
Bytes
Total number of bytes used in the database for the table.
Min
Minimum number of bytes used by any record for the table.
Max
Maximum number of bytes used by any record for the table.
Mean
Mean number of bytes used by any record for the table.
Count
Total number of record fragments found in the database for the table.
Factor
Degree of record fragmentation for the table. This value is determined by the number of fragments divided by the ideal number of fragments (for example, if the data is freshly loaded). A value of 1.0 is ideal. A value of 2.0 indicates that there are twice as many fragments than there would be when the data is freshly loaded.
Use the Index value to determine whether to dump and reload your data to improve fragmentation. If the value is 2.0 or greater, dumping and reloading will improve performance. If the value is less than 1.5, dumping and reloading is not warranted.
Scatter Factor
Degree of distance between records in the table.
The best achievable Scatter Index value is that of a freshly loaded database. This is the baseline number against which you should compare future Scatter Index measurements. A value of 1 indicates that the records occupy completely contiguous database blocks. A value of 2 indicates that the records are scattered ten times wider than the ideal.
Use this value to determine the performance impact caused by fragmentation. If the value is 1.5 or greater, performance will be poor for sequential record access, and dumping and loading the data might be warranted. A value of 1.5 or greater might also reduce performance for random access; however, this depends on the system type and the application.
Notes
The PROUTIL DBANALYS display includes the information displayed by PROUTIL TABANALYS.
If you specify a specific area for analysis, and the area contains LOB data, but not the corresponding records, a warning message is issued indicating that totals cannot be calculated for the LOBs in the LOB Summary and Database Summary sections of the output.
db-name.tab.txt — For table analysis output