Updating Statistics in the DBA Planning Calendar (Informix)

Updating Statistics in the DBA Planning Calendar (Informix)

Use

You can use the DBA Planning Calendar in the Computing Center Management System (CCMS) to schedule update statistics for the Informix database. You can update statistics for all tables in the database or only for one table.

You can also schedule a check statistics in the DBA Planning Calendar. Refer to Checking Statistics in the DBA Planning Calendar (Informix).

For more information about update statistics – which you can also perform with SAPDBA (that is, outside CCMS) – see Update Statistics with SAPDBA.

Prerequisites

You are ready to use CCMS. Refer to Getting Started in CCMS with Informix DBA.

You know how to use the DBA Planning Calendar. For more information, see:

DBA Planning Calendar for an overview

DBA Planning Calendar (Informix) for Informix-specific aspects

For more information about scheduling an action (for example, check statistics) in the DBA Planning Calendar, see Scheduling Actions in the DBA Planning Calendar.

Procedure

Choose Tools ® CCMS ® DB Administration ® DBA Planning Calendar.

Choose the day when you want the statistics to be updated.

Choose Create action.

Select one of the following:

Update Optimizer Statistics (all tables)

Update Optimizer Statistics (one table)

Enter data as required in the fields Start Time, Period (weeks), and Calendar. For more information, see Scheduling Actions in the DBA Planning Calendar.

Choose Continue.

If you are scheduling update statistics for one table, enter the Table name.

Enter the parameters to specify the update statistics:

Parameter	Meaning
Threshold for Update Statistics	The statistics for a table are only updated when the optimizer value for the number of rows in the table deviates from the current (that is, correct) number of rows in the table by more than a certain value, that is, the "threshold". You can either use the default value of 10% or enter a value of your own.
Execution strategy	To optimize the run time for update statistics, you can choose an execution strategy that performs update statistics in parallel. You can determine how many parallel processes are started for update statistics. The default for this parameter is the number of CPU VPs configured in your system. The number of CPU VPs is specified in the ONCONFIG parameter NUMCPUVPS . If you choose a value less than 2, update statistics is performed sequentially. For more information, see NUMCPUVPS (Informix). With update statistics for all tables, processing is performed in parallel at table level. If the statistics for an individual table need to be updated, processing is performed in parallel at column level.
Application Monitor Statistics	The default for this parameter is "no" (that is, the box is not selected). If you select this to activate the parameter, additional space statistics are calculated for each table. These can be displayed by the application monitor. Since this calculation is very time-consuming, SAP recommends that you only activate this parameter if you work with the application monitor.
Maximum Runtime	The default for this parameter is "no limit". If you want to make sure that the update statistics does not last too long, you can specify a maximum runtime in minutes. When this limit is reached, the update statistics ends after the current table.
Log file	Using this parameter, you can enter the directory and file name of the log file for update statistics. The default is $INFORMIXDIR/sapreorg/updstat_<SID>.log . If the directory you enter does not exist, the default directory is used. If the default directory does not exist, the log is written to /tmp/updstat_<SID>.log .
Detailed	The system writes additional information for each table to the log. The default is to write only overview information to the log.

If you perform update statistics with an R/3 release prior to 3.1G, it runs as follows:

Default threshold value (10%)

No parallel processing

Calculation of application monitor data is activated

No runtime limit

Log file defaults to $INFORMIXDIR/sapreorg/updstat_<SID>.log , or (if the required directory does not exist) to /tmp/updstat_<SID>.log .

If you wish to alter this, you must delete the planned actions and re-schedule them with R/3 Release 3.1G or later. However, note that parallel processing and the specification of the log file are only available for jobs scheduled with R/3 Release 4.0B or later.

Choose Continue.

Result

The update statistics is now scheduled. It will be executed at the scheduled date and time. For more information about looking at the results of the update statistics, see Checking the Results of Actions in the DBA Planning Calendar.