statdump Command--Print Statistics in iistats and iihistogram Catalogs
The statdump command prints statistics contained in the iistats and iihistograms catalogs of the Standard Catalog Interface.
These views contain statistical information about columns used by the Query Optimizer as it selects an efficient query processing strategy. The statistical information is typically generated by issuing the optimizedb command.
Note: Statdump operates only on the tables owned by the user who is running it. A single statdump command will not print statistics for the entire database if multiple schemas are present. To run statdump individually for each schema, you must be the DBA or a privileged user and use the -u flag. You can also specify -r schemaname.tablename, which must be used in combination with the -u flag.)
The statdump command has the following format:
statdump [SQL option flags] [-zf filename] [-zc] [-zcpk] [-zdl]
[-zn#] [-zq] [‑zqq] [-o filename] [‑Ppassword]
dbname {-rtablename {-acolumnname}}|{-xrtablename} [-help]
SQL option flags
Passes any of the following SQL option flags. For a complete description of these flags, see the sql command description.
-fkxM.N
-cN
–ikN
-tN
-u
+U |-U
+w|-w
-xk
-zf filename
Reads filename for all command line arguments. This file must contain one flag only per line. If this flag is specified, no other flags or arguments can appear on the command line—they must appear in the specified file instead.
-zc
Displays statistics on the system catalogs as well as the base tables. If you want statistics for selected system catalogs, use this flag and specify the individual tables with the -r flag. To use this flag, you must be the DBA of the specified database.
-zcpk
Displays the composite histogram formed from the table columns listed by the ‑a flags. If no ‑a flags are listed, displays the composite histogram formed from the table's storage structure key columns.
-zdl
Deletes statistics from the system catalogs. When this flag is used, the statistics for the specified tables and columns (if any) are deleted rather than displayed.
-zhex
Produces histogram cell values in hex format, which is useful for seeing how Unicode data is stored.
WARNING! A file with histogram data represented in hex format (generated by the -zhex flag) cannot be used as input to the optimizedb -i command. Doing so will result in incorrect histogram data, which will affect the performance of optimization algorithms.
-zn#
Displays floating-point values in scientific notation (for example, 9.9999+e9) and sets the precision to the level specified by #. The total width of the displayed number will be equal to the value of the precision level + 7.
-zq
Displays only the information contained in the iistats catalog and not the histogram information contained in iihistograms (quiet mode).
-zqq
Suppresses the “No rows” message for tables that contain no rows.
-o filename
Directs output to the file specified by filename. The resulting file is an ASCII file whose content is identical to the information normally sent to the terminal screen.
The resulting file can be used as input to the optimizedb command on the -i flag.
‑Ppassword
Specifies the user password for the database.
Note: To avoid passwords being displayed in process listings, we recommend that you put command line options in a secure file and use the ‑zf option when using the -P option.
dbname
-rtablename
Produces statistics for the specified tables only. If omitted, then statistics for all tables are produced.
If the table cannot be found, a warning message is printed and processing continues.
Note: Only one -r flag per tablename is allowed (whether issued on the command line or from within a file with-zf). For example:
statdump mydb -rtable -acol1 -rtable -acol2
is invalid and will generate an error. The correct syntax is:
statdump mydb -rtable -acol1 -acol2
-xrtablename
Specifies tablenames to be excluded from processing by statdump. Except for these, statistics for all columns in all tables in the database are produced.
-acolumnname
Produces statistics for the specified columns only. To specify individual columns you must specify the table name with the -r flag. If column names are omitted, then all columns of the specified table are processed.
If the column cannot be found, a warning message is printed and processing continues.
-help
Displays command syntax online.
Note: The combination of -rtablename and -xrtablename parameters and of -xrtablename and -acolumnname parameters is not permitted.
statdump Examples
This command prints the statistical information for all columns in the employee table in the empdata database:
statdump empdata -remployee
This command prints the information in the iistats system table only, for all columns in all tables of the empdata database:
statdump -zq empdata
This command deletes statistics for all columns in the employee table:
statdump -zdl empdata -remployee