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.

Syntax

proutil db-name -C tabanalys [ area area-name ]
     [ tenant tenant-name| group group-name| shared ]
     [-csoutput [ -fieldsep sep-value][ -csfilePrefix pref-name ] 
           [ -verbose ][ -RO ]]

Parameters

db-name

Specifies the database to display information.

area area-name

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:

• db-name.tab.txt — For table analysis output
• db-name .block.txt — For block analysis output
• db-name .lob.txt — For LOB analysis output

If you specify an area with -csoutput, TABANALYS appends the area number to the file name, for example, block analysis output for the sports2020 database in area 20 would be sports2020.block_20.txt.

For more information about the contents of the files, see the Database analysis output.

-fieldsep sep-value

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.

-RO

Runs the utility in read-only mode. Use read-only mode to connect to databases on read-only media or to diagnose and repair issues on a stopped database before starting it. Read-only mode allows any number of connections at once, up to the maximum number of connections. You may run multiple diagnostic utilities concurrently, while the database broker is down, if they all support read-only connection and you run them in read-only mode. This reduces bottlenecks and expedites disaster recovery on large databases.

Note: Use -RO with caution because it is designed to work only with a read-only connection. Unexpected errors may result if you specify -RO when using a single-user or multi-user connection.

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

                                       -Record Size (B)- ---Fragments--- Scatter
Table                 Records    Size   Min   Max  Mean  Count Factor Factor
PUB.cust                   26    1.7K    55    76    68	26    1.0     1.0 
PUB.cust-list.Initial       0    0.0B     0     0     0      0    0.0     0.0
PUB.cust-list.list1         3  202.0B    66    68    67      3    1.0    20.3
PUB.cust-list.list2        10  645.0B    56    77    64     10    1.0    20.3
PUB.cust-list.list3         4  291.0B    71    74    72      4    1.0    20.3
PUB.cust-list-range.Initial 0    0.0B     0     0     0      0    0.0     0.0
PUB.cust-range.Initial      0    0.0B     0     0     0      0    0.0     0.0
PUB.cust-rvalue.Initial     0    0.0B     0     0     0      0    0.0     0.0
PUB.state                  51    1.7K    29    40    34     51    1.0     1.0
PUB.stock.Initial(Composite)               
                        50000   18.5M   368   406   387  50000    1.0     1.0
.
.
.
LOB SUMMARY FOR AREA dbArea3: 9


LOB SUMMARY FOR SHARED TABLES:                                    
----------------------------------------------


                                                         ---    LOB Size   ---
Table                                 LOBs     Size      Min      Max     Mean
PUB.Invoice:1
    InvBlob1:5                         400    55.1M     1.0B   976.6K   141.0K
    InvBlob2:6                         400    54.9M     1.0B   976.6K   140.4K
    InvClob1:7                         400    39.3M     1.0B   976.6K   100.5K
                                 ---------------------------------------------
Subtotals:                            1200   149.2M     1.0B   976.6K   127.3K

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

• You can run this utility using a read-only connection. You may run multiple diagnostic utilities concurrently, while the database broker is down, if they all support read-only connection and you run them in read-only mode. To run it in read-only mode, append -RO to the utility start command.
• 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.
• See Manage Performance for more information about database fragmentation.