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 |
---|---|
@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.
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.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |