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.
SQLStatisticsW()
. Refer to Unicode in Db2 for i CLI 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
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:
|
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.
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:
|
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:
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 |
|
12 PAGES | INTEGER |
|
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
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. |