optdiag

Syntax

optdiag [binary] [simulate] statistics
	{ -i input_file | database[.owner[.[{table|prs_name}[.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]

Parameters

• binary – 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.
• simulate – specifies that optdiag display or load simulated statistics. See the Performance and Tuning Guide.
• -i input_file – 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”).
• database – 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.
• owner – 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.

  • Input mode – optdiag ignores the table owner specified on the command line and uses the value in the input file.

• table – 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.

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

• prs_name – indicates the name of the precomputed result set.
• column – 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.

• -o output_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.
• -U user_name – specifies an SAP ASE login name.
• -P password – specifies your SAP ASE password. If you do not specify the -P flag, optdiag prompts for a password.
• -T trace_value – sets trace flags for the optdiag session. The optdiag trace flags and their meanings are:

  • 1 – do not stop with a warning if the optdiag version of SAP ASE in use does not match the SAP ASE 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.

• -I interfaces_file – specifies the name and location of the interfaces file to use when connecting to an SAP ASE 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).

• -S server – specifies the name of the SAP ASE 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.

• -v – displays the version number of and a copyright message for optdiag and exits.
• -h – displays the optdiag syntax help.
• -s – includes system tables in optdiag output. By default, only user tables are included.
• -z language – 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.

  Add languages to SAP ASE either during or after installation, After SAP ASE installation, use either the langinstall utility or the sp_addlanguage stored procedure to add a language.

• -J client_charset – specifies the character set to use on the client. A filter converts input between client_charset and the SAP ASE character set.

  By using -J client_charset, you request that the SAP ASE 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 SAP ASE 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.

• -a display_charset – 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.

  • Without -J only if the client character set is the same as the default character set.

  Note: The ascii_7 character set is compatible with all character sets. If either the SAP ASE 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 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, we recommend 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)

Examples

• User tables – 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

• Table – Displays statistics for the titles table:

  optdiag statistics pubs2..titles -Usa -Ppasswd
      -o titles.opt

• Precomputed Result Sets – Displays information for the precomputed result set prs1.

  optdiag statistics prsdb..prs1 -Usa -Ppass –Sserver
  
  Server name:                            ""
  
  Specified database:                     "prsdb"
  Specified table owner:                  not specified
  Specified table:                        "prs1"
  Specified column:                       not specified
  
  Table owner:                            "dbo"
  Table name:                             "prs1"
  Table type:                             "precomputed result set"
  
  Statistics for table:                   "prs1"
  
  . . . 
  
  No statistics for remaining columns:    "a"
  (default values used)                                 
  
  Optdiag succeeded.
  

• Character set – 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

• Binary statistics – Displays binary statistics for the price column in the titles table:

  optdiag binary statistics pubs2..titles.price
      -Usa -Ppasswd -o price.opt

• Edited statistics – Loads edited statistics from the price.opt file:

  optdiag statistics -i price.opt -Usa -Ppasswd