SQLStatistics - Get index and statistics information for a base table

SQLStatistics() retrieves index information for a given table. It also returns the cardinality and the number of pages associated with the table and the indexes on the table. The information is returned in a result set, which can be retrieved using the same functions that are used to fetch a result set generated by a SELECT statement.

Unicode (UTF-16) equivalent: This function can also be used with the Unicode (UTF-16) character set. The corresponding Unicode function is SQLStatisticsW(). Refer to Unicode in DB2 for iCLI for more information about Unicode support for DB2 CLI.

Syntax

SQLRETURN SQLStatistics  (SQLHSTMT       hstmt,
                          SQLCHAR        *szCatalogName,
                          SQLSMALLINT    cbCatalogName,
                          SQLCHAR        *szSchemaName,
                          SQLSMALLINT    cbSchemaName,
                          SQLCHAR        *szTableName,
                          SQLSMALLINT    cbTableName,
                          SQLSMALLINT    fUnique,
                          SQLSMALLINT    fAccuracy);

Function arguments

Table 1. SQLStatistics arguments
Data type	Argument	Use	Description
SQLHSTMT	hstmt	Input	Statement handle.
SQLCHAR *	szCatalogName	Input	Catalog qualifier of a three-part table name. This must be a null pointer or a zero length string.
SQLSMALLINT	cbCatalogName	Input	Length of cbCatalogName. This must be set to 0.
SQLCHAR *	szSchemaName	Input	Schema qualifier of the specified table.
SQLSMALLINT	cbSchemaName	Input	Length of szSchemaName.
SQLCHAR *	szTableName	Input	Table name.
SQLSMALLINT	cbTableName	Input	Length of cbTableName.
SQLSMALLINT	fUnique	Input	Type of index information to return: SQL_INDEX_UNIQUE Only unique indexes are returned. SQL_INDEX_ALL All indexes are returned.
SQLSMALLINT	fAccuracy	Input	Not currently used, must be set to 0.

Usage

SQLStatistics() returns the following types of information:

Statistics information for the table (if available):
- When the TYPE column in the following table is set to SQL_TABLE_STAT, the number of rows in the table and the number of pages used to store the table.
- When the TYPE column indicates an index, the number of unique values in the index, and the number of pages used to store the indexes.
- Information about each index, where each index column is represented by one row of the result set. The result set columns are given in the following table in the order shown; the rows in the result set are ordered by NON_UNIQUE, TYPE, INDEX_QUALIFIER, INDEX_QUALIFIER, INDEX_NAME and ORDINAL_POSITION.

Table 2. Columns returned by SQLStatistics
Column number/name	Data type	Description
1 TABLE_CAT	VARCHAR(128)	The name of the catalog containing TABLE_SCHEM. This is set to NULL.
2 TABLE_SCHEM	VARCHAR(128)	The name of the schema containing TABLE_NAME.
3 TABLE_NAME	VARCHAR(128) not NULL	Name of the table.
4 NON_UNIQUE	SMALLINT	This indicates whether the index prohibits duplicate values: TRUE if the index allows duplicate values. FALSE if the index values must be unique. NULL is returned if the TYPE column indicates that this row is SQL_TABLE_STAT (statistics information about the table itself).
5 INDEX_QUALIFIER	VARCHAR(128)	The identifier used to qualify the index name. This is NULL if the TYPE column indicates SQL_TABLE_STAT.
6 INDEX_NAME	VARCHAR(128)	The name of the index. If the TYPE column has the value SQL_TABLE_STAT, this column has the value NULL.
7 TYPE	SMALLINT not NULL	This indicates the type of information contained in this row of the result set: SQL_TABLE_STAT This indicates this row contains statistics information about the table itself. SQL_INDEX_CLUSTERED This indicates this row contains information about an index, and the index type is a clustered index. SQL_INDEX_HASHED This indicates this row contains information about an index, and the index type is a hashed index. SQL_INDEX_OTHER This indicates this row contains information about an index, and the index type is other than clustered or hashed. Note: Currently, SQL_INDEX_OTHER is the only possible type.
8 ORDINAL_POSITION	SMALLINT	Ordinal position of the column within the index whose name is given in the INDEX_NAME column. A NULL value is returned for this column if the TYPE column has the value of SQL_TABLE_STAT.
9 COLUMN_NAME	VARCHAR(2000)	Name of the column in the index.
10 ASC_OR_DESC	CHAR(1)	Sort sequence for the column; "A" for ascending, "D" for descending. NULL value is returned if the value in the TYPE column is SQL_TABLE_STAT.
11 CARDINALITY	INTEGER	If the TYPE column contains the value SQL_TABLE_STAT, this column contains the number of rows in the table. If the TYPE column value is not SQL_TABLE_STAT, this column contains the number of unique values in the index. A NULL value is returned if information is not available from the Database Management System (DBMS).
12 PAGES	INTEGER	If the TYPE column contains the value SQL_TABLE_STAT, this column contains the number of pages used to store the table. If the TYPE column value is not SQL_TABLE_STAT, this column contains the number of pages used to store the indexes. A NULL value is returned if information is not available from the DBMS.
13 FILTER_CONDITION	VARCHAR(128)	If the index is a filtered index, this is the filter condition. Since DB2 servers do not support filtered indexes, NULL is always returned. NULL is also returned if TYPE is SQL_TABLE_STAT.

For the row in the result set that contains table statistics (TYPE is set to SQL_TABLE_STAT), the columns values of NON_UNIQUE, INDEX_QUALIFIER, INDEX_NAME, ORDINAL_POSITION, COLUMN_NAME, and COLLATION are set to NULL. If the CARDINALITY or PAGES information cannot be determined, then NULL is returned for those columns.

If argument szSchemaName is not specified, the schema name qualifier defaults to the one currently in effect for the current connection.

Passing a NULL pointer for argument szTableName will result in an error.

Return codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 3. SQLStatistics SQLSTATEs
SQLSTATE	Description	Explanation
24000	Cursor state that is not valid	Cursor related information is requested, but no cursor is open.
40003 *	Statement completion unknown	The communication link between the CLI and the data source fails before the function completes processing.
HY001	Memory allocation failure	The driver is unable to allocate memory required to support the processing or completion of the function.
HY009	Argument or buffer length that is not valid	The value of one of the name length arguments is less than 0, but not equal to SQL_NTS.
HY021	Internal descriptor that is not valid	The internal descriptor cannot be addressed or allocated, or it contains a value that is not valid.
HYC00	Driver not capable	The catalog part (the first part) of a three-part table name is not supported by the data source.