SQLStatistics - Get index and statistics information for a base table

Purpose

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.

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
SQLCHAR * szCatalogName Input Catalog qualifier of a three-part table name. This must be a null pointer or a zero length string.
SQLCHAR * szSchemaName Input Schema qualifier of the specified table.
SQLCHAR * szTableName Input Table name.
SQLHSTMT hstmt Input Statement handle.
SQLSMALLINT cbCatalogName Input Length of cbCatalogName. This must be set to 0.
SQLSMALLINT cbSchemaName Input Length of szSchemaName.
SQLSMALLINT cbTableName Input Length of cbTableName.
SQLSMALLINT fAccuracy Input Not currently used, must be set to 0.
SQLSMALLINT fUnique Input Type of index information to return:
  • SQL_INDEX_UNIQUE

    Only unique indexes are returned.

  • SQL_INDEX_ALL

    All indexes are returned.

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 name Data type Description
TABLE_CAT VARCHAR(128) The name of the catalog containing TABLE_SCHEM. This is set to NULL.
TABLE_SCHEM VARCHAR(128) The name of the schema containing TABLE_NAME.
TABLE_NAME VARCHAR(128) not NULL Name of the table.
NON_UNIQUE SMALLINT 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).
INDEX_QUALIFIER VARCHAR(128) The identifier used to qualify the index name. This is NULL if the TYPE column indicates SQL_TABLE_STAT.
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.
TYPE SMALLINT not NULL Indicates the type of information contained in this row of the result set:
  • SQL_TABLE_STAT

    Indicates this row contains statistics information on the table itself.

  • SQL_INDEX_CLUSTERED

    Indicates this row contains information about an index, and the index type is a clustered index.

  • SQL_INDEX_HASHED

    Indicates this row contains information about an index, and the index type is a hashed index.

  • SQL_INDEX_OTHER

    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.
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.
COLUMN_NAME VARCHAR(128) Name of the column in the index.
COLLATION 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.
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).
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.

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.

Return codes

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.
Parent topic: DB2 UDB CLI functions