Display Optimizer Statistics
You can view statistics that have been collected. Use the following methods:
• statdump command
• Display Statistics dialog in Director or VDBA
The usual output is based on statistics generated by the optimization process, as described in
Database Statistics.
Display Optimizer Statistics for Individual Tables and Columns
By default, optimizer statistics are shown for all tables and columns in the current database, but you can view statistics for specific table columns.
The following command lists statistics for the empno column of the emp table:
statdump demodb -remp -aempno
In Director or VDBA, use the Specify Tables and Specify Columns check boxes in the Display Statistics dialog.
Delete Optimizer Statistics
To delete statistics use any of these methods:
• Use the -zdl flag with the statdump command. Use the -r and -a flags to specify the tables and columns for which to delete statistics.
This command deletes statistics from the demodb database: columns empno and sex from the emp table and the empno column from the task table:
statdump -zdl demodb -remp -aempno -asex -rtask -aempno
• DROP STATISTICS statement
• In Director, use the Display Statistics dialog, Options page.
• In VDBA, enable the Delete Statistics from Syscat check box. Using this check box with the Specify Tables and Specify Columns check boxes, you can specify the tables and columns for which to delete statistics.
Floating Point Precision in Optimizer Statistics Display
You can specify the precision with which floating point numbers are displayed in the statistics by enabling the Set Precision Level to check box and entering a value in the corresponding edit control to determine the number of decimal digits in the text format of the floating point numbers.
For example, assume a table, t_float, is defined with a column named c_float of type float, and that the following statements are used to insert values (all of which are approximately 1.0):
INSERT INTO t_float VALUES (0.99999998);
INSERT INTO t_float VALUES (0.99999999);
INSERT INTO t_float VALUES (1.0);
INSERT INTO t_float VALUES (1.00000001);
INSERT INTO t_float VALUES (1.00000002);
You can create statistics for this table using the optimization procedure described in
Database Statistics.
With its default floating point precision, the standard output is show seven places after the decimal point. For greater precision, you can enable the Set Precision Level check box and enter a larger value.
For example, specifying a precision level of 14 generates output similar to the following, in which there is sufficient precision to maintain a visible difference in the values:
*** statistics for database demodb version: 00850
*** table t_float rows:5 pages:3 overflow pages:0
*** column c_float of type float (length:8, scale:0, nullable)
date:2000_02_24 15:15:30 GMT unique values:5.000
repetition factor:1.000 unique flag:Y complete flag:0
domain:0 histogram cells:10 null count:0.00000000000000 value length:8
cell: 0 count:0.00000000000000 repf:0.00000000000000 value:0.99999997999999
cell: 1 count:0.20000000298023 repf:1.00000000000000 value:0.99999998000000
cell: 2 count:0.00000000000000 repf:0.00000000000000 value:0.99999998999999
cell: 3 count:0.20000000298023 repf:1.00000000000000 value:0.99999999000000
cell: 4 count:0.00000000000000 repf:0.00000000000000 value:0.99999999999999
cell: 5 count:0.20000000298023 repf:1.00000000000000 value:1.00000000000000
cell: 6 count:0.00000000000000 repf:0.00000000000000 value:1.00000000999999
cell: 7 count:0.20000000298023 repf:1.00000000000000 value:1.00000001000000
cell: 8 count:0.00000000000000 repf:0.00000000000000 value:1.00000001999999
cell: 9 count:0.20000000298023 repf:1.00000000000000 value:1.00000002000000
This can be useful when statistics are output to a text file or input from a text file. For more information, see
Statistics in Text Files. When reading statistics from a text file, the optimization process assumes that all cell values are in ascending order. You can use the Set Precision Level option to preserve sufficient precision for floating point numbers.