Using the DISTRIBUTIONS ONLY Option to Suppress Index Information

In Dynamic Server, when you specify the DISTRIBUTIONS ONLY option, you do not update index information. This option does not affect existing index information.

Use this option to avoid the examination of index information that can consume considerable processing time.

This option does not affect the recalculation of information on tables, such as the number of pages used, the number of rows, and fragment information. UPDATE STATISTICS needs this information to construct accurate column distributions and requires little time and system resources to collect it.

Routine Statistics

3Before the database server executes a new SPL routine the 3first time, it optimizes the statements in the SPL routine. Optimization makes 3the code depend on the structure of tables referenced by the routine. If a 3table schema changes after the routine is optimized, but before it is executed, 3the routine can fail with an error.

To avoid this error after DDL operations, or to reoptimize SPL routines after table distributions might have been modified by DML operations, you can use the Routine Statistics segment of UPDATE STATISTICS to update the optimized execution plans for SPL routines in the sysprocplan system catalog table.

Routine Statistics:
 
|--FOR---------------------------------------------------------->
 
>--+-PROCEDURE--+---------+-------------------------------------------------------+--|
   |            '-routine-'                                                       |
   |  (1)                                                                         |
   +--------+-PROCEDURE-+--+----------------------------------------------------+-+
   |        +-FUNCTION--+  '-routine--(--+---------------------------------+--)-' |
   |        '-ROUTINE---'                |                            (2)  |      |
   |                                     '-| Routine Parameter List |------'      |
   |                                            (3)                               |
   '-SPECIFIC--+-PROCEDURE-+--| Specific Name |-----------------------------------'
               +-FUNCTION--+
               '-ROUTINE---'

Notes:

Dynamic Server only
See Routine Parameter List
See Specific Name

Element	Description	Restrictions	Syntax
routine	Name declared for a SPL routine in a CREATE FUNCTION or CREATE PROCEDURE statement	Must reside in the database. In ANSI-compliant databases, qualify routine with owner if you are not owner.	Database Object Name

The following table explains the keywords of the Routine Statistics segment.

Keyword: Which Execution Plan is Reoptimized
SPECIFIC: The plan for the SPL routine called specific name
FUNCTION: The plan for any SPL function with the specified name (and with parameter types that match routine parameter list, if supplied)
PROCEDURE: The plan for any SPL procedure with the specified name (and parameter types that match routine parameter list, if supplied)
ROUTINE: The plan for SPL functions and procedures with the specified name (and parameter types that match routine parameter list, if supplied)

If you specify no routine, the execution plans are reoptimized for all SPL routines in the current database.

3The database server keeps a list of tables that the SPL 3routine references explicitly. Whenever an explicitly referenced table is 3modified, the database server reoptimizes the procedure the next time the 3procedure is executed.

The sysprocplan system catalog table stores execution plans for SPL routines. Two actions can update the sysprocplan system catalog table:

Execution of an SPL routine that uses a modified table
The UPDATE STATISTICS statement

If you change a table that an SPL routine references, you can run UPDATE STATISTICS to reoptimize the procedures that reference the table, rather than waiting until the next time an SPL routine that uses the table executes. (If a table that an SPL routine references is dropped, however, running UPDATE STATISTICS cannot prevent the SPL routine from failing with an error.)