Histogram utility (dbhist)

Converts a histogram into a Microsoft Excel chart containing information about the selectivity of predicates.

Syntax

dbhist [ options ] -t table-name [ excel-output-filename ]

Options

Option	Description
@data	Reads in options from the specified environment variable or configuration file. See Using configuration files. If you want to protect passwords or other information in the configuration file, you can use the File Hiding utility to obfuscate the contents of the configuration file. See File Hiding utility (dbfhide).
-c options	Specifies connection parameters. See Connection parameters.
-n colname	Specifies the name of the column to associate the histogram with. If you do not specify a column, all columns that have histograms in the table are returned.
-t table-name	Specifies the name of the table or materialized view for which to generate the chart.
-u owner	Specifies the owner of the table or materialized view.
excel-output-name	Specifies the name of the generated Excel file. If no name is specified, Excel prompts you to enter one with a Save As window.

Remarks

Histograms are stored in the ISYSCOLSTAT system table and can also be retrieved with the sa_get_histogram stored procedure. The Histogram utility converts a histogram into a Microsoft Excel chart containing information about the selectivity of predicates. The Histogram utility (dbhist) only works on Windows, and you must have Excel 97 or later installed.

Statistics (including histograms) may not be present for a table or materialized view, for example, if statistics were recently dropped. In this case, the Histogram utility returns the message Histogram contains no data, aborting. In this case, you must create the statistics, and then run the Histogram utility again. To create statistics for a table or materialized view, execute a CREATE STATISTICS statement. See CREATE STATISTICS statement.

To determine the selectivity of a predicate over a string column, you should use the ESTIMATE or ESTIMATE_SOURCE functions. Attempting to retrieve a histogram from string columns causes both sa_get_histogram and the Histogram utility to generate an error. See ESTIMATE function [Miscellaneous] and ESTIMATE_SOURCE function [Miscellaneous].

The sheets are named with the column name. Column names are truncated after 24 characters, and all occurrences of \, /, ?, *, [, ], and : (which are not allowed in Excel) are replaced with underscores ( _ ). Chart names are prefixed with the word chart, followed by the same naming convention above. Duplicate names (arising from character replacement, truncation, or columns named starting with chart) result in an Excel error stating that no duplicate names can be used. However, the spreadsheet is still created with those names created with their previous version (Sheet1, Chart1, and so on).

Exit codes are 0 (success) or non-zero (failure). See Software component exit codes.

You can also retrieve histograms using the sa_get_histogram stored procedure. See sa_get_histogram system procedure.

Example

The following command (entered all on the same line) generates an Excel chart for the column ProductID in the table SalesOrderItems for database demo.db, and saves it as histogram.xls.

dbhist -c "UID=DBA;PWD=sql;DBF=samples-dir\demo.db" -n ProductID -t SalesOrderItems histogram.xls

The following statement generates charts for every column with a histogram in the table SalesOrders, assuming that the sample database is already started. This statement also attempts to connect using UID=DBA and PWD=sql. No output file name is specified, so Excel prompts you to enter one.

dbhist -t SalesOrders -c "UID=DBA;PWD=sql"

For information about samples-dir, see Samples directory.