Administrative API Reference

sqlustat - Runstats

Updates statistics about the characteristics of a table and any associated indexes. These characteristics include, among many others, number of records, number of pages, and average record length. The optimizer uses these statistics when determining access paths to the data.

This utility should be called when a table has had many updates, after reorganizing a table, or after creating a new index.

Statistics are collected based on the table partition that is resident on the node where the API executes. Global table statistics are derived by multiplying the values obtained at a node by the number of nodes on which the table is completely stored. The global statistics are stored in the catalog tables.

The node from which the API is called does not have to contain a partition for the table:

Scope

This API can be called from any node in the db2nodes.cfg file. It can be used to update the catalogs on the catalog node.

Authorization

One of the following:

Required Connection

Database

Version

sqlutil.h

C API Syntax 



/* File: sqlutil.h */
/* API: Run Statistics */
/* ... */
SQL_API_RC SQL_API_FN
  sqlustat (
    _SQLOLDCHAR * pTableName,
    unsigned short NumIndexes,
    _SQLOLDCHAR ** ppIndexList,
    unsigned char StatsOption,
    unsigned char ShareLevel,
    struct sqlca * pSqlca);
/* ... */

Generic API Syntax 



/* File: sqlutil.h */
/* API: Run Statistics */
/* ... */
SQL_API_RC SQL_API_FN
  sqlgstat (
    unsigned short TableNameLen,
    unsigned short NumIndexes,
    unsigned char StatsOption,
    unsigned char ShareLevel,
    unsigned short * pIndexLens,
    struct sqlca * pSqlca,
    _SQLOLDCHAR ** ppIndexList,
    _SQLOLDCHAR * pTableName);
/* ... */

API Parameters

TableNameLen
Input. A 2-byte unsigned integer representing the length in bytes of the table name.

NumIndexes
Input. The number of indexes specified in this call. This value is used with the StatsOption parameter. Valid values are:

0
All the indexes are to be calculated.

n
The number of indexes contained in the index list. The names of the indexes to be calculated are specified in ppIndexList.

StatsOption
Input. Statistical option, indicating which calculations are to be performed. Valid values (defined in sqlutil) are:

SQL_STATS_TABLE
Table only.

SQL_STATS_EXTTABLE_ONLY
Table with extended (distribution) statistics.

SQL_STATS_BOTH
Both table and indexes.

SQL_STATS_EXTTTABLE_INDEX
Both table (with distribution statistics) and basic indexes.

SQL_STATS_INDEX
Indexes only.

SQL_STATS_EXTINDEX_ONLY
Extended statistics for indexes only.

SQL_STATS_EXTINDEX_TABLE
Extended statistics for indexes and basic table statistics.

SQL_STATS_ALL
Extended statistics for indexes and table statistics with distribution statistics.

ShareLevel
Input. Specifies how the statistics are to be gathered with respect to other users. Valid values (defined in sqlutil) are:

SQL_STATS_REF
Allows others to have read-only access while the statistics are being gathered.

SQL_STATS_CHG
Allows others to have read and write access while the statistics are being gathered.

pIndexLens
Input. An array of 2-byte unsigned integers representing the length in bytes of each of the index names in the index list.

pSqlca
Output. A pointer to the sqlca structure. For more information about this structure, see SQLCA.

ppIndexList
Input. An array of strings. Each string contains one fully qualified index name.

pTableName
Input. The table on which to update statistics. Can be an alias, except in the case of down-level servers, when the fully qualified table name must be used.

For row types, pTableName must be the name of the hierarchy's root table.

REXX API Syntax 



RUNSTATS ON TABLE tname
[WITH :statsopt INDEXES {ALL | USING :value}]
[SHRLEVEL {REFERENCE|CHANGE}]

REXX API Parameters

tname
The fully qualified name of the table on which statistics are to be gathered.

statsopt
A host variable containing a statistical option, indicating which calculations are to be performed. Valid values are:

T
Indicates that basic statistics are to be updated for the specified table only. This is the default

D
Indicates that extended (distribution) statistics are to be updated for the specified table

B
Indicates that basic statistics are to be updated for both the specified table and the specified indexes

E
Indicates that extended statistics are to be updated for the specified table, and that basic statistics are to be updated for the indexes

I
Indicates that basic statistics are to be updated for the specified indexes only

X
Indicates that extended statistics are to be updated for the specified indexes only

Y
Indicates that basic statistics are to be updated for the specified table, and that extended statistics are to be updated for the indexes

A
Indicates that extended statistics are to be updated for both the specified table and the specified indexes.

value
A compound REXX host variable containing the names of the indexes for which statistics are to be generated. In the following, XXX represents the host variable name:

XXX.0
The number of indexes specified in this call

XXX.1
First fully qualified index name

XXX.2
Second fully qualified index name

XXX.3
and so on.

REFERENCE
Other users can have read-only access while updates are being made.

CHANGE
Other users can have read or write access while updates are being made. This is the default.

Sample Programs

C
\sqllib\samples\c\dbstat.sqc

COBOL
\sqllib\samples\cobol\dbstat.sqb

Usage Notes

This API is not supported for declared temporary tables.

Use RUNSTATS to update statistics:

After statistics have been updated, new access paths to the table can be created by rebinding the packages using sqlabndx - Bind.

If index statistics are requested, and statistics have never been run on the table containing the index, statistics on both the table and indexes are calculated.

After calling this API, the application should issue a COMMIT to release the locks.

To allow new access plans to be generated, the packages that reference the target table must be rebound after calling this API.

Statistics are collected based on the table data that is located on the database partition where the API executes. Global table statistics for an entire partitioned table are derived by multiplying the values obtained at a database partition by the number of database partitions in the nodegroup over which the table is partitioned. The global statistics are stored in the catalog tables.

The database partition from which the API is called does not have to contain a partition for the table:

If inconsistencies are found when running a portion of this API (resulting from activity on the table since the API was last called), a warning message is returned. For example, if table distribution statistics were gathered on the first call, and only index statistics are gathered on the second call, then if inconsistencies are detected as a result of activity on the table, the table distribution statistics are dropped. At this point, it is recommended to call the API again to refresh the table distribution statistics.

See Also

"REORGCHK" in the Command Reference

sqlfxdb - Get Database Configuration

sqlureot - Reorganize Table.

[ Top of Page | Previous Page | Next Page ]