PROUTIL DBANALYS qualifier

Displays statistical information about index, record, and free chain blocks.

Syntax

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

Parameters

db-name
Specifies the database you are using.
area area-name
Specifies that DBANALYS is to be performed on the area-name area only.
tenant tenant-name
Returns only information related to objects owned by tenant-name. If tenant-name is not valid, DBANALYS exits with an error. Specifying tenant is only allowed on databases enabled for multi-tenancy.
group group-name
Returns only information related to objects owned by group-name. If group-name is not valid, DBANALYS exits with an error. Specifying group is only allowed on databases enabled for multi-tenancy.
shared
Returns only information related to shared objects. Specifying shared is only allowed on databases enabled for multi-tenancy.
-scan
Specifies that DBANALYS performs chain analysis with minimal locking (does not turn on table locks).
-csoutput

Specifies that DBANALYS output the analysis data to text files in addition to the screen. The result is that the following files are created:

  • db-name .ch.txt—For chain analysis output
  • db-name .ix.txt—For index analysis output
  • 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 and -csoutput, DBANALYS 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

Both non-partitioned and partitioned tables appear in the summary for shared tables in alphabetical order by name. For partitioned tables, the initial partition will appear first, followed by the remainder of the partitions in alphabetical order by partition name.

Example

The following output is a partial sample of the summary section of PROUTIL DBANALYS:


                             DATABASE SUMMARY


SUMMARY FOR AREA "Control Area": 1
-------------------------------------------------------


SUMMARY FOR SHARED OBJECTS:
--------------------------------------------
                          Records            Indexes            LOBs            Combined
NAME                    Size  Tot %       Size  Tot %       Size  Tot %       Size  Tot %
PUB._Area              473.0B    0.0     107.0B    0.0       0.0B    0.0     580.0B    0.0
PUB._AreaExtent        981.0B    0.0     129.0B    0.0       0.0B    0.0       1.1K    0.0


SUMMARY FOR AREA "Schema Area": 6
-------------------------------------------------------


SUMMARY FOR SHARED OBJECTS:
--------------------------------------------
                          Records            Indexes            LOBs            Combined
NAME                    Size  Tot %       Size  Tot %       Size  Tot %       Size  Tot %
PUB._Client-Session
                         0.0B    0.0      12.0B    0.0       0.0B    0.0      12.0B    0.0
PUB._Codepage            0.0B    0.0       6.0B    0.0       0.0B    0.0       6.0B    0.0
PUB._Collation           0.0B    0.0       6.0B    0.0       0.0B    0.0       6.0B    0.0
PUB._Constraint          0.0B    0.0      15.0B    0.0       0.0B    0.0      15.0B    0.0
PUB._Constraint-Keys
                         0.0B    0.0       3.0B    0.0       0.0B    0.0       3.0B    0.0
PUB._Db                  1.1K    0.0      17.0B    0.0       0.0B    0.0       1.2K    0.0
PUB._Db-Detail          36.0B    0.0      37.0B    0.0       0.0B    0.0      73.0B    0.0
PUB._Db-Option         459.0B    0.0     159.0B    0.0       0.0B    0.0     618.0B    0.0
PUB._Field             553.6K    0.5      92.8K    0.1       0.0B    0.0     646.4K    0.6
PUB._Field-Trig          0.0B    0.0       6.0B    0.0       0.0B    0.0       6.0B    0.0
PUB._File               64.9K    0.1       9.2K    0.0       0.0B    0.0      74.1K    0.1
PUB._File-Trig           0.0B    0.0       3.0B    0.0       0.0B    0.0       3.0B    0.0
PUB._Index              35.0K    0.0       8.7K    0.0       0.0B    0.0      43.7K    0.0
PUB._Index-Field        58.1K    0.1       6.9K    0.0       0.0B    0.0      65.0K    0.1
PUB._KeyEvent            0.0B    0.0       9.0B    0.0       0.0B    0.0       9.0B    0.0
PUB._Partition-Policy
                       265.0B    0.0      92.0B    0.0       0.0B    0.0     357.0B    0.0
PUB._Partition-Policy-Detail
                         2.5K    0.0     651.0B    0.0       0.0B    0.0       3.2K    0.0
.
.
.

SUMMARY FOR AREA "tpArea1": 10
-------------------------------------------------------


SUMMARY FOR SHARED OBJECTS:
--------------------------------------------
                          Records            Indexes            LOBs            Combined
NAME                    Size  Tot %       Size  Tot %       Size  Tot %       Size  Tot %
PUB.tpCustomer.Initial
                         0.0B    0.0       1.6K    0.0       0.0B    0.0       1.6K    0.0
PUB.tpCustomer.CustomerQtr1
                         0.0B    0.0       1.1K    0.0       0.0B    0.0       1.1K    0.0
PUB.tpInvoice.InvoiceQtr1
                        15.3K    0.0       0.0B    0.0      38.5M   34.6      38.5M   34.6
PUB.tpInvoice.InvoiceQtr5
                         0.0B    0.0       0.0B    0.0       0.0B    0.0       0.0B    0.0
PUB.tpItem               5.0K    0.0       0.0B    0.0       0.0B    0.0       5.0K    0.0
PUB.tpOrder              0.0B    0.0     110.1K    0.1       0.0B    0.0     110.1K    0.1
PUB.tpOrder-Line         0.0B    0.0       2.0K    0.0       0.0B    0.0       2.0K    0.0

SUMMARY FOR AREA "tpArea2": 11
-------------------------------------------------------


SUMMARY FOR SHARED OBJECTS:
--------------------------------------------
                          Records            Indexes            LOBs            Combined
NAME                    Size  Tot %       Size  Tot %       Size  Tot %       Size  Tot %
PUB.tpCustomer.CustomerQtr1
                        38.8K    0.0       0.0B    0.0       6.0M    5.4       6.0M    5.4
PUB.tpInvoice.Initial
                         0.0B    0.0       1.1K    0.0       0.0B    0.0       1.1K    0.0
PUB.tpInvoice.InvoiceQtr1
                         0.0B    0.0       1.7K    0.0       0.0B    0.0       1.7K    0.0
PUB.tpItem               0.0B    0.0       4.5K    0.0       0.0B    0.0       4.5K    0.0
PUB.tpLargeField.LrgFldQtr1
                       371.8K    0.3       0.0B    0.0       0.0B    0.0     371.8K    0.3
PUB.tpOrder            244.7K    0.2       0.0B    0.0       0.0B    0.0     244.7K    0.2
PUB.tpOrder-Line        30.3K    0.0       8.1K    0.0       0.0B    0.0      38.5K    0.0


SUMMARY FOR AREA "tpArea3": 12
-------------------------------------------------------


SUMMARY FOR SHARED OBJECTS:
--------------------------------------------
                          Records            Indexes            LOBs            Combined
NAME                    Size  Tot %       Size  Tot %       Size  Tot %       Size  Tot %
PUB.tpCustomer.Initial
                         0.0B    0.0       1.1K    0.0       0.0B    0.0       1.1K    0.0
PUB.tpCustomer.CustomerQtr2
                         0.0B    0.0       0.0B    0.0       0.0B    0.0       0.0B    0.0
PUB.tpCustomer.CustomerQtr5
                         0.0B    0.0       0.0B    0.0       0.0B    0.0       0.0B    0.0
PUB.tpInvoice.Initial
                         0.0B    0.0       1.4K    0.0       0.0B    0.0       1.4K    0.0
PUB.tpInvoice.InvoiceQtr2
                         0.0B    0.0       0.0B    0.0       0.0B    0.0       0.0B    0.0
PUB.tpInvoice.InvoiceQtr5
                         0.0B    0.0       3.0B    0.0       0.0B    0.0       3.0B    0.0
PUB.tpLargeField.LrgFldQtr1
                         0.0B    0.0     286.0B    0.0       0.0B    0.0     286.0B    0.0
PUB.tpLargeField.LrgFldQtr2
                       371.8K    0.3       0.0B    0.0       0.0B    0.0     371.8K    0.3
PUB.tpLargeField.LrgFldQtr5
                         0.0B    0.0       0.0B    0.0       0.0B    0.0       0.0B    0.0


SUMMARY FOR AREA "tpArea4": 13
-------------------------------------------------------


SUMMARY FOR SHARED OBJECTS:
--------------------------------------------
                          Records            Indexes            LOBs            Combined
NAME                    Size  Tot %       Size  Tot %       Size  Tot %       Size  Tot %
PUB.BLOBTABLE           31.9K    0.0       0.0B    0.0     391.4K    0.3     423.3K    0.4
PUB.tpCustomer.CustomerQtr2
                         0.0B    0.0       0.0B    0.0       0.0B    0.0       0.0B    0.0
PUB.tpLargeField.LrgFldQtr2
                         0.0B    0.0     286.0B    0.0       0.0B    0.0     286.0B    0.0
PUB.tpLargeField.LrgFldQtr3
                         0.0B    0.0     286.0B    0.0       0.0B    0.0     286.0B    0.0


SUMMARY FOR AREA "tpArea5": 14
-------------------------------------------------------


SUMMARY FOR SHARED OBJECTS:
--------------------------------------------
                          Records            Indexes            LOBs            Combined
NAME                    Size  Tot %       Size  Tot %       Size  Tot %       Size  Tot %
PUB.tpCustomer.CustomerQtr5
                         0.0B    0.0       3.0B    0.0       0.0B    0.0       3.0B    0.0
PUB.tpInvoice.Initial
                         0.0B    0.0       1.4K    0.0       0.0B    0.0       1.4K    0.0
PUB.tpInvoice.InvoiceQtr3
                         7.0K    0.0       1.3K    0.0      21.3M   19.2      21.4M   19.2
PUB.tpInvoice.InvoiceQtr5
                         0.0B    0.0       0.0B    0.0       0.0B    0.0       0.0B    0.0


SUMMARY FOR AREA "tpArea6": 15
-------------------------------------------------------


SUMMARY FOR SHARED OBJECTS:
--------------------------------------------
                          Records            Indexes            LOBs            Combined
NAME                    Size  Tot %       Size  Tot %       Size  Tot %       Size  Tot %
PUB.tpCustomer.Initial
                         0.0B    0.0     518.0B    0.0       0.0B    0.0     518.0B    0.0
PUB.tpCustomer.CustomerQtr3
                        39.2K    0.0       1.1K    0.0       6.1M    5.5       6.1M    5.5
PUB.tpLargeField.LrgFldQtr3
                       371.8K    0.3       0.0B    0.0       0.0B    0.0     371.8K    0.3


SUMMARY FOR AREA "tpArea7": 16
-------------------------------------------------------


SUMMARY FOR SHARED OBJECTS:
--------------------------------------------
                          Records            Indexes            LOBs            Combined
NAME                    Size  Tot %       Size  Tot %       Size  Tot %       Size  Tot %
PUB.BLOBTABLE            0.0B    0.0       7.0K    0.0       0.0B    0.0       7.0K    0.0
PUB.tpCustomer.CustomerQtr4
                        37.2K    0.0       1.1K    0.0       0.0B    0.0      38.3K    0.0
PUB.tpInvoice.InvoiceQtr4
                        11.4K    0.0       1.3K    0.0       0.0B    0.0      12.7K    0.0
PUB.tpLargeField.LrgFldQtr4
                       371.8K    0.3     286.0B    0.0       0.0B    0.0     372.1K    0.3


SUMMARY FOR AREA "tpArea8": 17
-------------------------------------------------------


SUMMARY FOR SHARED OBJECTS:
--------------------------------------------
                          Records            Indexes            LOBs            Combined
NAME                    Size  Tot %       Size  Tot %       Size  Tot %       Size  Tot %
PUB.tpCustomer.CustomerQtr4
                         0.0B    0.0       0.0B    0.0       5.1M    4.6       5.1M    4.6
PUB.tpInvoice.InvoiceQtr4
                         0.0B    0.0       0.0B    0.0      30.9M   27.8      30.9M   27.8

                    ----------------------------------------------------------------------
Total                    2.6M    2.4     278.1K    0.2     108.3M   97.4     111.2M  100.0

Size key:
 B = bytes
 K = kilobytes
 M = megabytes
 G = gigabytes
 T = terabytes



DATABASE BLOCK ANALYSIS:
-----------------------

1 master block(s) found in the database.

8 area block(s) found in the database.

1 control block(s) found in the database.

2 object block(s) found in the database.

80 cluster list block(s) found in the database.

80 cluster allocation block(s) found in the database.

80 object block(s) found in the database.

8 object list block(s) found in the database.

80 object allocation block(s) found in the database.

7089 free block(s) found in the database.

1 sequence block(s) found in the database.

214 empty block(s) found in the database.

26630 total blocks found in the database.

Database analysis complete Wed Aug 12 14:16:50 2015

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. When partitioned indexes are listed, the indexes appear in alphabetical order. For each partitioned index, the Index of Indexes appears first, followed by the remaining partitions in alphabetical order by partition name.

The chain analysis report appears in internal storage order (as stored in the object block for the area) with a column to identify Partition/Tenant/Group. The format of this column is type:name:id:

  • type is P for Partition,T for Tenant, or G for Group
  • name is the partition name, tenant name, or group name
  • id is the partition-id, tenant-id, or group-id

The column is blank if there is no partition, tenant, or group.

The output line listing the number of blocks on chains associated with the Index of Indexes displays _Partition-Policy-Detail._Partition-Internal-Value in the Object column, and P:FullyQualifiedOwningTableName:TableId in the Partition/Tenant/Group column. Composite partitions display C:partition-name:partition-id.

Note

  • 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 output of PROUTIL DBANALYS is the combination of the three individual analysis utilities (CHANALYS, IDXANALYS, and TABANALYS) combined.
  • When DBANALYS performs chain analysis, only the length of the record management (RM) chain is reported. You must run CHANALYS to get a more detailed report including the list of free chain blocks.
  • 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.