API Reference
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:
- If the API is called from a node that contains a partition for the table,
the utility executes at this node.
- If the API is called from a node that does not contain a table partition,
the request is sent to the first node in the nodegroup that holds a partition
for the table. The utility then executes at this node.
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:
- sysadm
- sysctrl
- sysmaint
- dbadm
- CONTROL privilege on the table.
Required Connection
Database
API Include File
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.
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
- FORTRAN
- \sqllib\samples\fortran\dbstat.sqf
Usage Notes
Use RUNSTATS to update statistics:
- On tables that have been modified many times (for example, if a large
number of updates have been made, or if a significant amount of data has been
inserted or deleted)
- On tables that have been reorganized
- When a new index has been created.
After statistics have been updated, new access paths to the table
can be created by rebinding the packages using
sqlabndx - Bind.
Statistics for tables only should be collected before any indexes are
created. This will ensure that statistics gathered during index creation are
not overlaid by estimates gathered during the calculation of table statistics.
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 using this API.
In FORTRAN, use sqlgaddr - Get Address to initialize the pointers in the index list.
See Also
"REORGCHK" in the Command Reference
[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]
[ DB2 List of Books |
Search the DB2 Books ]