Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - Database Administration » Database Administration Utilities

File Hiding utility (dbfhide) Next Page

Histogram utility (dbhist)

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


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

Option Description

Use this option to read 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

Specify connection parameters. See Connection parameters.

-n colname

Specify 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 Specify the name of the table or materialized view for which to generate histograms.
-u owner Specify the owner of the table or materialized view.
excel-output-name Specify the name of the generated Excel file. If no name is specified, Excel prompts you to enter one with a Save As dialog.

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.

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

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


Assuming that a histogram has been created for the column, 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 histgram.xls.

dbhist -c "UID=DBA;PWD=sql;DBF=samples-dir\demo.db" -n ProductID -t SalesOrderItems histgram.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.