Choosing appropriate configuration parameters

Certain database configuration parameters affect the optimization of your queries.

Default configuration parameter values are oriented toward the average class of machine that is used as a dedicated database server. These parameters may not be suitable for your environment.

To change configuration parameters for a database, select the database from the Control Center, and then select Configure from the pop-up menu. The Configure notebook for the database opens.

The following parameters in the Configure Database notebook influence the optimizer's choice of access path. (For information about any of these parameters, consult the help for the Configure notebook.)

• Average number of active applications, AVG_APPLS, on the Applications page.

• Sort heap size, SORTHEAP, on the Performance page.

• Maximum storage for lock lists, LOCKLIST, on the Performance page.

• Maximum percent of lock list before escalation, MAXLOCKS, on the Applications page.

• Number of most frequent values, NUM_FREQVALUES, on the Status page.

• Number of quantiles, NUM_QUANTILES, on the Status page.

• Database heap size, DBHEAP, on the Performance page.

• Average time to execute a CPU instruction, CPUSPEED, on the Environment page of the Configure Instance notebook.

  Note:

  This is a database manager configuration parameter. To change the value of this parameter, select the instance from the Control Center, then select Configure from its pop-up menu. The Configure notebook for the instance opens. CPUSPEED can be found on the Environment page.

• Buffer pool size, BUFFPAGE, on the Performance page.

  Note:

  This configuration parameter will only be used if a -1 is specified in the size field of any buffer pool in the buffer pool system catalog.

To see the updated access plan graph after running statistics or updating configuration parameters, statements should be re-explained, packages should be rebound and another explain snapshot should be created. For any of the above configuration parameters to take effect, the database manager must be stopped and restarted (everyone has to disconnect).

For more information, see the section on configuration parameters affecting query statements in the Administration Guide.