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 ]

Option	Description
@data	Reads in options from the specified environment variable or configuration file. See 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 Microsoft Excel file. If no name is specified, Microsoft Excel prompts you to enter one with a Save As window.

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 Microsoft 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.

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.

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 Microsoft 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 a Microsoft 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).

You can also retrieve histograms using the sa_get_histogram stored procedure.

Example

The following command (entered all on the same line) generates a Microsoft 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=%SQLANYSAMP12%\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 Microsoft Excel prompts you to enter one.

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

Discuss this page in DocCommentXchange.