Try OpenEdge Now
skip to main content
Database Administration
Reference : PROUTIL Utility : PROUTIL DBANALYS qualifier
 

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

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
Specifies that DBANALYS is to be filtered to only provide information related to objects that are 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
Specifies that DBANALYS is to be filtered to only provide information related to objects that are 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
Specifies that DBANALYS is to be filtered to only provide 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
For more information about the contents of the files, see the Databaseanalysis 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.

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 as follows: type:name:id where type is P for Partition, T for Tenant, or G for Group; name is the partition name, tenant name, or group name; and 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

*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 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.
* Database analysis output