`SQLStatistics()` - Get index and statistics information for a base table

SQLStatistics() retrieves index information for a specific table. It also returns the cardinality and the number of pages that are associated with the table and the indexes on the table. The information is returned in a result set. You can retrieve the result set with the same functions that process a result set that is generated by a query.

ODBC specifications for `SQLStatistics()`

Table 1. `SQLStatistics()` specifications
ODBC specification level	In X/Open CLI CAE specification?	In ISO CLI specification?
1.0	Yes	No

Syntax

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

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. `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	The length, in bytes, of cbCatalogName. This must be set to 0.
SQLCHAR *	szSchemaName	input	Schema qualifier of the specified table.
SQLSMALLINT	cbSchemaName	input	The length, in bytes, of szSchemaName.
SQLCHAR *	szTableName	input	Table name.
SQLSMALLINT	cbTableName	input	The length, in bytes, of cbTableName.
SQLUSMALLINT	fUnique	input	Type of index information to return: SQL_INDEX_UNIQUE Only unique indexes are returned. SQL_INDEX_ALL All indexes are returned.
SQLUSMALLINT	fAccuracy	input	Indicate whether the CARDINALITY and PAGES columns in the result set contain the most current information: SQL_ENSURE : This value is reserved for future use, when the application requests the most up to date statistics information. Existing applications that specify this value receive the same results as SQL_QUICK. Recommendation: Do not use this value with new applications. SQL_QUICK: Statistics which are readily available at the server are returned. The values might not be current, and no attempt is made to ensure that they be up to date.

Usage

SQLStatistics() returns two types of information:

Statistics information for the table (if statistics are available):
- When the TYPE column in the table below 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 Table 3 in the order shown; the rows in the result set are ordered by NON_UNIQUE, TYPE, INDEX_QUALIFIER, INDEX_NAME and ORDINAL_POSITION.

Because calls to SQLStatistics() in many cases map to a complex and thus expensive query against the system catalog, they should be used sparingly, and the results saved rather than repeating calls.

The VARCHAR columns of the catalog functions result set are declared with a maximum length attribute of 128 bytes to be consistent with ANSI/ISO SQL standard of 1992 limits. Because the length of Db2 names are less than 128 bytes, the application can choose to always set aside 128 bytes (plus the nul-terminator) for the output buffer. Alternatively, you can call SQLGetInfo() with the InfoType argument set to each of the following values:

SQL_MAX_CATALOG_NAME_LEN, to determine the length of TABLE_CAT columns that the connected database management system supports
SQL_MAX_SCHEMA_NAME_LEN, to determine the length of TABLE_SCHEM columns that the connected database management system supports
SQL_MAX_TABLE_NAME_LEN, to determine the length of TABLE_NAME columns that the connected database management system supports
SQL_MAX_COLUMN_NAME_LEN, to determine the length of COLUMN_NAME columns that the connected database management system supports

Although new columns might be added and the names of the existing columns changed in future releases, the position of the current columns does not change. The following table lists the columns in the result set SQLStatistics() currently returns.

Table 3. Columns returned by `SQLStatistics()`
Column number	Column name	Data type	Description
1	TABLE_CAT	VARCHAR(128)	The is always 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	Indicates whether the index prohibits duplicate values: SQL_TRUE if the index allows duplicate values. SQL_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 on the table itself).
5	INDEX_QUALIFIER	VARCHAR(128)	The string is used to qualify the index name in the DROP INDEX statement. Appending a period (.) plus the INDEX_NAME results in a full specification of the index.
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	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 on an index, and the index type is a clustered index. SQL_INDEX_HASHED - Indicates this row contains information on an index, and the index type is a hashed index. SQL_INDEX_OTHER - Indicates this row contains information on an index, and the index type is other than clustered or hashed.
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(128)	Name of the column in the index. A null value is returned for this column if the TYPE column has the value of SQL_TABLE_STAT.
10	ASC_OR_DESC	CHAR(1)	Sort sequence for the column; A for ascending, D for descending. A 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.
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 database management system.
13	FILTER_CONDITION	VARCHAR(128)	If the index is a filtered index, this is the filter condition. Because 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 ASC_OR_DESC are set to NULL. If the CARDINALITY or PAGES information cannot be determined, then NULL is returned for those columns.

Important: The accuracy of the information returned in the SQLERRD(3) and SQLERRD(4) fields is dependent on many factors such as the use of parameter markers and expressions within the statement. The main factor which can be controlled is the accuracy of the database statistics. That is, when the statistics were last updated, (for example, for Db2 for z/OS® ODBC, the last time the RUNSTATS utility was run.)

Return codes

After you call SQLStatistics(), it returns one of the following values:

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 4. `SQLStatistics()` SQLSTATEs
SQLSTATE	Description	Explanation
08S01	Communication link failure.	The communication link between the application and data source fails before the function completes.
24000	Invalid cursor state.	A cursor is opened on the statement handle.
HY001	Memory allocation failure.	Db2 ODBC is not able to allocate the required memory to support the execution or the completion of the function.
HY010	Function sequence error.	The function is called during a data-at-execute operation. (That is, the function is called during a procedure that uses the `SQLParamData()` or `SQLPutData()` functions.)
HY014	No more handles.	Db2 ODBC is not able to allocate a handle due to low internal resources.
HY090	Invalid string or buffer length.	This SQLSTATE is returned for one or more of the following reasons: The value of one of the name length arguments is less than 0, but not equal to SQL_NTS. The valid of one of the name length arguments exceeds the maximum value supported for that data source. You can obtain this maximum value with `SQLGetInfo()`.
HY100	Uniqueness option type out of range.	An invalid fUnique value is specified.
HY101	Accuracy option type out of range.	An invalid fAccuracy value is specified.
HYC00	Driver not capable.	Db2 ODBC does not support catalog as a qualifier for table name.

Example

The following example shows an application that prints the cardinality and the number of pages associated with a table. This application retrieves this information with SQLStatistics().

Figure 1. An application that prints page and cardinality information about a table

/* ... */
SQLRETURN
list_stats(SQLHDBC hdbc, SQLCHAR *schema, SQLCHAR *tablename )
{
/* ... */
    rc = SQLStatistics(hstmt, NULL, 0, schema, SQL_NTS,
                    tablename, SQL_NTS, SQL_INDEX_UNIQUE, SQL_QUICK);
    rc = SQLBindCol(hstmt, 4, SQL_C_SHORT,
                          &non_unique, 2, &non_unique_ind);
    rc = SQLBindCol(hstmt, 6, SQL_C_CHAR,
                          index_name.s, 129, &index_name.ind);
    rc = SQLBindCol(hstmt, 7, SQL_C_SHORT,
                          &type, 2, &type_ind);
    rc = SQLBindCol(hstmt, 9, SQL_C_CHAR,
                          column_name.s, 129, &column_name.ind);
    rc = SQLBindCol(hstmt, 11, SQL_C_LONG,
                          &cardinality, 4, &card_ind);
    rc = SQLBindCol(hstmt, 12, SQL_C_LONG,
                          &pages, 4, &pages_ind);
    printf("Statistics for 
    while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS)
    {  if (type != SQL_TABLE_STAT)
       {   printf("  Column: %-18s Index Name: %-18s\n",
                  column_name.s, index_name.s);
       }
       else
       {   printf("  Table Statistics:\n");
       }
       if (card_ind != SQL_NULL_DATA)
          printf(" Cardinality = %13ld", cardinality); 
       else
          printf("    Cardinality = (Unavailable)");
       if (pages_ind != SQL_NULL_DATA)
          printf(" Pages = %13ld\n", pages);
       else
          printf(" Pages = (Unavailable)\n");
    }
/* ... */

SQLStatistics() - Get index and statistics information for a base table

ODBC specifications for SQLStatistics()