Displays optimizer statistics or loads updated statistics into system tables. optdiag is located in $SYBASE/$SYBASE_ASE/bin.
(Windows) The utility is optdiag.exe, located in %SYBASE%\%SYBASE_ASE%\bin.
optdiag [binary] [simulate] statistics
{ -i input_file | database[.owner[.[table[.column] ] ] ] [-o output_file] }
[-U user_name]
[-P password]
[-T trace_value]
[-I interfaces_file]
[-S server]
[-v]
[-h]
[-s]
[-z language]
[-J client_character_set]
[-a display_charset]
extracts statistics in human-readable form and in binary form. When used with an input file (-i input_file), loads binary statistics into system tables.
specifies that optdiag display or load simulated statistics. See the Performance and Tuning Guide.
specifies the name of the operating system file to use for optdiag input. Specifying an input file causes optdiag to update optimizer statistics for the table or column by using the values in the specified file (also called “input mode”).
is the name of the database whose statistics you want displayed. In input mode, optdiag uses the database name as specified in the file, and does not accept a database name from the command line.
is the name of a table owner.
In display mode, if you do not specify an owner, but do specify a table name, optdiag displays output for all of the owners of a table.
In input mode, optdiag ignores the table owner specified on the command line and uses the value in the input file.
is the name of the table to survey for statistics.
If the command does not include an owner name or a table name, optdiag displays statistics for all tables in the database.
If the command includes an owner name, but no table name, optdiag displays all of the tables that belong to the specified owner.
In input mode, optdiag ignores the table name specified on the command line and uses the value from the input file.
is the name of the colum to survey.
If the command does not include a column name, optdiag displays all statistics for a table.
In input mode, optdiag ignores the column name on the command line and uses the values from the input file.
specifies the name of an operating system file to store the output from optdiag. If a file with the same name already exists, optdiag overwrites that file without warning.
specifies an Adaptive Server login name.
specifies your Adaptive Server password. If you do not specify the -P flag, optdiag prompts for a password.
sets trace flags for the optdiag session. The optdiag trace flags are:
Flag value |
Meaning |
|---|---|
1 |
Do not stop with a warning if the optdiag version of Adaptive Server in use does not match the Adaptive Server version in the input file. |
2 |
Display status message “Next table is table_name” when in input mode. |
4 |
Skip consistency checking for step numbers while loading histograms in input mode. |
6 |
Display lines of input file during input mode. This flag has no effect in display mode. |
7 |
Do not stop with a warning if the optdiag input file does not include sampling percent information. |
specifies the name and location of the interfaces file to use when connecting to Adaptive Server.
If you do not use -I and specify an interfaces file name, optdiag looks for the interfaces file (interfaces in UNIX), in the directory specified by the SYBASE environment variable. In Windows, optdiag looks for a file named sql.ini in the ini subdirectory in the Sybase installation directory (d:\sybase). Then, if SYBASE is not set, optdiag looks for the file in the default $SYBASE directory (%SYBASE% in Windows).
specifies the name of the Adaptive Server to which to connect. optdiag looks for this name in the interfaces file (sql.ini in Windows).
If you use -S without specifying a server name, optdiag looks for a server named SYBASE.
When you do not use -S, optdiag looks for the server that your DSQUERY environment variable specifies.
displays the version number of and a copyright message for optdiag and exits.
displays the optdiag syntax help.
includes system tables in optdiag output. By default, only user tables are included.
is the official name of an alternate language that the server uses both for date formats and to display optdiag prompts and messages. Without the -z flag, optdiag uses the server’s default language.
You can add languages to Adaptive Server either during or after installation, After Adaptive Server installation, use either the langinstall utility or the sp_addlanguage stored procedure to add a language.
specifies the character set to use on the client. A filter converts input between client_charset and the Adaptive Server character set.
By using -J client_charset, you request that Adaptive Server convert data to and from client_charset, the client’s character set.
By using -J without a character set name, you specify character set conversion as NULL; no conversion takes place. Use this -J alone when the client and server are using the same character set.
By omitting -J, you set the character set to the default set for the platform. A filter converts input between the default set and the Adaptive Server character set. Keep in mind that the default may not necessarily be the character set that the client is using.
For more information about character sets and their associated flags, see the System Administration Guide.
runs optdiag from a terminal with a character set that differs from that of the machine on which optdiag is running.
Use -a in conjunction with -J to specify the character set translation (.xlt) file required for the conversion.
Use -a without -J only if the client character set is the same as the default character set.
The ascii_7 character set is
compatible with all character sets. If either the Adaptive Server
character set or the client character set is set to ascii_7,
any 7-bit ASCII character
can pass unaltered between client and server. Any other characters
produce conversion errors. For more information on character-set conversion,
see the System Administration Guide.
On some Linux platforms, the LANG environment variable might be set by default to “en_US.UTF-8,” which can cause unnecessary LONGCHAR conversion between the client and server. If your server and client have different charsets, Sybase recommends that you bypass the conversion using one of these methods:
unsetenv LANG
setenv LANG C
optdiag -J
optdiag -Jiso-1 (if your server uses iso-1)
Displays statistics for all user tables in the pubs2 database and places the output in the pubs2.opt file:
optdiag statistics pubs2 -Usa -Ppasswd -o pubs2.opt
Displays statistics for the titles table:
optdiag statistics pubs2..titles -Usa -Ppasswd -o titles.opt
Displays statistics using the roman8 character set and row labels and error messages in French:
optdiag statistics pubs2..titles -Usa -Ppasswd -o titles.opt -J roman8 -z french
Displays binary statistics for the price column in the titles table:
optdiag binary statistics pubs2..titles.price -Usa -Ppasswd -o price.opt
Loads edited statistics from the price.opt file:
optdiag statistics -i price.opt -Usa -Ppasswd
You must set the SYBASE environment variable to the location of the current version of Adaptive Server before you can use optdiag.
By default, optdiag does not include the system tables when you display statistics for a database. To include the system tables in the output, use the -s flag.
You cannot specify a particular partition on the optdiag command line; optdiag displays statistics for all partitions of a specified table.
When you use binary mode, optdiag displays the human-readable values with comment marks (#s) at the beginning of the lines, as shown in this example:
Statistics for column: "price" Last update of column statistics: Jan 20 1998 7:16PM Statistics loaded from Optdiag. Range cell density: 0x3f8b9cfefece26bf # Range cell density: 0.0134830400000000 Total density: 0x3f8b9cfefece26bf # Total density: 0.0134830400000000 Range selectivity: default used (0.33) # Range selectivity: default used (0.33) In between selectivity: default used (0.25) # In between selectivity: default used (0.25)
When you use optdiag with an input file to change statistics, it ignores all characters after the “#” in a line.
Converting floating-point values may lead to rounding errors when you use files for input.
When you are loading statistics on the same hardware platform, edit the statistics using the binary values to provide greater precision.
optdiag displays the statistic, sampling percent last used, which indicates that statistics are gathered with a user-specified sampling percent.
optdiag displays statistics for each partition of a multi-partitioned table or index.
optdiag displays global- and partition-level statistics for each column in a table with multiple partitions.
Do not use the binary mode option to move statistics between Adaptive Servers on machines that use different byte ordering.
On an incompatible architecture server, always comment out binary statistics and load the human-readable statistics.
On a compatible architecture server, you can load either binary statistics or human-readable statistics.
When you use the -i input_file syntax, optdiag reads the file as named and updates statistics in sysstatistics.
optdiag input mode changes the allow update to system tables configuration parameter by setting the parameter to 1 at the beginning of the session, and then to 0 at the end of the session.
During histogram input, the process checks the following rules and displays error messages for any violated rules:
The step numbers must increase monotonically, unless the command includes the -T4 trace flag.
The column values for the steps must increase monotonically.
The weight for each cell must be between 0.0 and 1.0.
The total of weights for a column must be close to 1.0.
The first cell represents null values, and it must be present, even in columns that do not allow null values. There must be only one cell to represent the null value.
Two adjacent cells must not both use the < (less than) operator.
Commands create index, delete statistics, set, update statistics
Documentation Performance and Tuning Guide for more information about the optdiag command and an explanation of the optdiag output, and Performance and Tuning Guide for more information on changing statistics using optdiag.
System procedures sp_addlogin, sp_configure, sp_defaultlanguage, sp_droplanguage, sp_flushstats, sp_helplanguage
