A. Command Reference : optimizedb Command--Generate Statistics for the Query Optimizer : optimizedb -z Flags
Share this page                  
optimizedb -z Flags
The –z flags specify options to optimizedb:
Optimizes the system catalogs in addition to the base tables. If you want to optimize selected system catalogs only, use this flag and specify the individual tables with the -r flag. This flag is valid only if the user issuing the command is the DBA for the specified database.
Requests a composite histogram on the table columns supplied with the ‑a flags. If no ‑a flags are listed, the composite histogram is constructed on the table's storage structure key columns.
If the table has no Hash, ISAM, or Btree primary storage structure, the ‑a columns are required; otherwise, optimizedb issues an error.
Naming a secondary index table with ‑r is a shorthand way of generating a base table composite histogram on the secondary index's storage structure key columns; in other words, ‑rindex is the same as ‑zcpk ‑rbase_table ‑aindex_column1 ‑aindex_column2 ....
Estimates the number of distinct values and repetition factor for a column whose histogram is built with sampling (see the -zs# option).
Includes (generates statistics for) encrypted columns.
By default, optimizedb skips encrypted columns unless the -ze flag is used or unless the -r and -a flags are used to specifically name the encrypted columns.
Reads filename for all other command line flags, database names, and any other command line arguments. This file must contain only one flag per line (see the examples below). If this flag is specified, no other flags or arguments can appear on the command line; they must, instead, appear in the specified file.
(Default for Vector and does not need to be specified.) Uses the “fast query” option, which significantly reduces the time to build a histogram. This option improves performance only when the repetition factor of the column is 20 or higher.
The -zfq flag can also cause optimizedb to generate a global temporary table from the values of the histogrammed columns when more than one column is identified in the optimizedb command. The histograms are then built by reading from the faster temporary table, rather than from the base table. The smaller and faster temporary table offers additional performance benefits for the fast query option.
Optimizedb builds the global temporary table when -zfq is specified, and when the number of histogrammed columns and the size of the temporary table row (relative to the size of a base table row) meet certain criteria. See the description of the –znt flag, which can be used with the –zfq flag.
Because there is no performance benefit in building more than one histogram on a table with a single execution of optimizedb, it is recommended that repetitious columns be specified in one execution of optimizedb (with the –zfq flag) and that the others be specified in a separate execution.
Prints the histogram that was generated for each column. This flag also implies the -zv flag.
Generates histogram cell values in hex format, which is useful for seeing how Unicode data is stored. This flag is only effective when used with the -zh and the -o flags.
Generates statistics for columns that are keys on the table or are indexed, in addition to columns specified on the command line.
Reuses existing repetition factor if there is one.
Disables the default behavior of creating histograms from a maximum 500,000 row sample. Using this parameter assures that all rows are read from a table during the histogram building process.
Disables the use of global temporary tables when using the “fast query” option (-zfq) if disk space is not sufficient.
This flag is used only with the -zfq flag.
Reads floating-point numbers using the precision level specified by #. Use this flag with the -i filename flag.
Reads the row and page count values in the file specified with the -i flag and to store those values in the appropriate system catalog (they can be viewed in iitables).
Suppresses the "No rows" message when optimizing tables that contain no rows.
Specifies the maximum number of cells that the histogram can contain if optimizedb creates an inexact histogram. In an inexact histogram, each cell represents a range of values.
The allowable range is 1<#<15000 (that is, the minimum is 2 and the maximum is 14999).
The default number of cells is 100.
Creates statistics based on sample data. The percentage of table rows sampled is determined by the value of #. This number must be a floating-point number in the range of 0 to 100. Specifying the optional s (-zss) will cause the tuple identifiers (TIDs), which are used to retrieve the sample rows, to be sorted before the rows are retrieved. This decreases retrieval time but increases the amount of memory used by optimizedb.
Specifies the maximum number of cells an exact histogram can contain. In an exact histogram, each cell represents a single, unique value.
The allowable range is 1 to 15000.
The default number of cells is 100.
Prints information about each column as it is being processed.
Sets the complete flag, which indicates whether a column contains all possible values. The range of values in a column affects query optimization. By default, columns are assumed to be not complete.
Determines only the minimum and maximum values for each column rather than full statistics. Because minimum and maximum values for columns from the same table can be determined by a single scan through the table, this flag provides a quick way to generate a minimal set of statistics. Minimal statistics cannot be created on columns holding only null values.
Updates the iirelation table only the first time statistics are created. The default is to update the iirelation table every time statistics are created. Optimizedb takes an exclusive lock on the table being optimized during the update of the record in iirelation by the SET STATISTICS tablename command.
Note:  The RDF cache will not be updated after executing optimizedb with the -zy flag, leading to OPF using stale optimizer statistics until the cache is cleared (with trace point RD10, for example) or the DBMS Server is restarted.