optimizedb Command--Generate Statistics for the Query Optimizer

The optimizedb command generates statistics that are used by the Query Optimizer to select an efficient query processing strategy.

Statistics are generated on the specified columns, and stored in system catalogs (iistats and iihistograms).

Complete and accurate statistics in the system catalogs result in more efficient query execution strategies and faster system performance. The process of generating complete and accurate statistics requires time, but a balance between accurate statistics and the time to generate them can be achieved by specifying the -zx or -zs flag. Statistics need to be refreshed only when a significant change in the distribution of a column’s values has occurred.

The statistics generated by the optimizedb command for any column consist of two elements:

The accuracy of the histograms can be controlled by the -zu# and -zr# flags described below. Increasing the number of cells in the histograms increases the amount of space required for the iihistograms table and thus increases somewhat the amount of space and time used by the optimizer. However, the increased accuracy of the statistics generally results in more efficient query execution strategies.

Note: By default, sampled statistics are used for tables that have more than one million rows. To prevent sampling, use the –zns flag of optimizedb or the WITH NOSAMPLE option of the CREATE STATISTICS statement.

We recommend that you generate the statistics for all columns that appear in the qualification (WHERE clause) of a query statement. If statistics are missing or incorrect, the query will still execute, but the speed of query processing can be affected.

After running optimizedb, it is prudent to run sysmod. This is especially true the first time optimizedb is run on a database.

• By default, optimizedb collects statistics only for the tables owned by the user who is running it. You can use the -all flag (all tables) or -u flag (tables for a specified user). To use the ‑all or ‑u flags, you must be the DBA or a privileged user.

• Although optimizedb does not lock the database or individual tables while it is retrieving values and generating statistics, after the statistics have been collected and stored in the appropriate catalogs, optimizedb takes an exclusive lock on the database or individual tables to complete its task.

The optimizedb command has the following format:

optimizedb [SQL option flags]
[-i filename] [-o filename] [-z flags] [‑all] [-Ppassword]
dbname[/server_class] {-rtablename {-acolumnname}} | {-xrtablename} [-help]

Indicate SQL option flags that are automatically passed. The optimizedb command accepts the following SQL option flags. For a complete description of these flags, see the sql command.

The filename must be a file in ASCII format that was generated by the statdump command using the -o flag. While you can edit this file, only two types of changes are acceptable: modifying values and adding rows that describe cells. Do not change the format of the file, that is, do not change the order in which data appears or add an incomplete new row.

The -r and -a flags, when used with this flag, act as filters. Optimizedb reads in from the file only those statistics that belong to the specified table or column.

Optimizedb does not use the row and page count values in the file unless the -zp flag is also specified.

Note: These values are vital for correct operation of the DBMS. If you use the -zp flag, be sure to put new values for row and page counts in iitables.

Warning! A file with histogram data represented in hex format (generated by the -zhex flag) cannot be used as input to the optimizedb -i command. Doing so will result in incorrect histogram data, which will affect the performance of optimization algorithms.

Allows statistics to be created for all tables, not only for the tables owned by the calling user or by the user specified on the ‑u flag. The db_admin privilege is required. The ‑all flag cannot be used with the ‑i, ‑o, or ‑r flag.

Note: To avoid passwords being displayed in process listings, we recommend that you put command line options in a secure file and use the ‑zf option when using the -P option.

Indicates the name of the database, and if required, the server_class, as described in Standard Flags and Parameters.

Note: The vnode:: option can be used only if the client and server machines have identical architectures.

Specifies table names to be processed. If no table name is specified, then all columns for all tables in the database are processed.

If tablename specifies a secondary index name, optimizedb creates a composite histogram on the key columns comprising the index. This is shorthand for: ‑zcpk ‑rbase_table_name ‑aindex_key_column1 ‑aindex_key_column2 ....

Note: Only one -r flag per tablename is allowed (whether issued on the command line or from within a file with-zf).

Specifies one or more tablenames to be excluded from processing. Except for these tables, all columns in all tables in the database are processed.

Note: Using both the -rtablename and -xrtablename parameters is not permitted in a single optimizedb request; nor is using both the -xrtablename and -acolumnname parameters.

Limits processing to the specified columns plus any columns included through the -zk flag. You can use the -acolumname flag only if the -rtablename parameter is specified.