Optimizedb -z Flags
The –z flags specify options to optimizedb. The -z flags are as follows:
-zc
Directs optimizedb to optimize 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.
-zcpk
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 ....
-zdn
Directs optimizedb to use its algorithm to estimate the number of distinct values and repetition factor for a column whose histogram is built with sampling (see the -zs# option).
-ze
Allows statistics to be generated for encrypted columns. Without the -ze flag, optimizedb skips encrypted or masked columns or issues an error if they are explicitly named with the -r and -a flags.
Caution! The existence of column statistics exposes a small sampling of the column data through the iihistogram catalog and the statdump command. Database administrators should weigh this partial exposure against possible performance gains when encrypted columns are involved in WHERE or ON clause predicates. Having to use the -ze flag is designed to prevent accidental exposure of sensitive data.
Note: For Ingres Star databases, optimizedb behaves as if -ze had been specified. Use the -r and -a flags to include or exclude encrypted columns.
For more information on data at rest encryption, see the Security Guide.
-zf filename
Directs optimizedb to read 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.
-zfq
Directs optimizedb to use 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.
-zh
Prints the histogram that was generated for each column. This flag also implies the -zv flag.
-zhex
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.
-zk
Generates statistics for columns that are keys on the table or are indexed, in addition to columns specified on the command line.
-zlr
Reuses existing repetition factor if there is one.
-zns
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.
-znt
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.
-zn#
Directs optimizedb to read floating-point numbers using the precision level specified by #. Use this flag in conjunction with the -i filename flag.
-zp
Directs optimizedb to read 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).
-zqq
Suppresses the "No rows" message for tables that contain no rows.
-zr#
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<#<32000 (that is, the minimum is 2 and the maximum is 31999).
The default number of cells is 100.
-zs[s]#
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.
-zu#
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.
-zv
Prints information about each column as it is being processed.
-zw
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.
-zx
Directs optimizedb to determine 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.
-zy
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.