Call Level Interface Guide and Reference

SQLStatistics - Get Index and Statistics Information For A Base Table

Purpose

Specification: DB2 CLI 2.1 ODBC 1.0

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 process a result set generated by a query.

Syntax

SQLRETURN   SQLStatistics    (SQLHSTMT          StatementHandle,   /* hstmt */
                              SQLCHAR      FAR *CatalogName,       /* szCatalogName */
                              SQLSMALLINT       NameLength1,       /* cbCatalogName */
                              SQLCHAR      FAR *SchemaName,        /* szSchemaName */
                              SQLSMALLINT       NameLength2,       /* cbSchemaName */
                              SQLCHAR      FAR *TableName,         /* szTableName */
                              SQLSMALLINT       NameLength3,       /* cbTableName */
                              SQLUSMALLINT      Unique,            /* fUnique */
                              SQLUSMALLINT      Reserved);         /* fAccuracy */

Function Arguments

Table 174. SQLStatistics Arguments

Data Type Argument Use Description
SQLHSTMT StatementHandle Input Statement handle.
SQLCHAR * CatalogName Input Catalog qualifier of a 3 part table name. This must be a null pointer or a zero length string.
SQLSMALLINT NameLength1 Input Length of NameLength1. This must be set to 0.
SQLCHAR * SchemaName Input Schema qualifier of the specified table.
SQLSMALLINT NameLength2 Input Length of SchemaName.
SQLCHAR * TableName Input Table name.
SQLSMALLINT NameLength3 Input Length of NameLength3.
SQLUSMALLINT Unique Input Type of index information to return:

SQL_INDEX_UNIQUE
Only unique indexes will be returned.
SQL_INDEX_ALL
All indexes will be returned.

SQLUSMALLINT Reserved 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. New applications should not use this value. Existing applications specifying this value will receive the same results as SQL_QUICK.
SQL_QUICK : Statistics which are readily available at the server are returned. The values may not be current, and no attempt is made to ensure that they be up to date.

Data Type	Argument	Use	Description
SQLHSTMT	StatementHandle	Input	Statement handle.
SQLCHAR *	CatalogName	Input	Catalog qualifier of a 3 part table name. This must be a null pointer or a zero length string.
SQLSMALLINT	NameLength1	Input	Length of `NameLength1`. This must be set to 0.
SQLCHAR *	SchemaName	Input	Schema qualifier of the specified table.
SQLSMALLINT	NameLength2	Input	Length of `SchemaName`.
SQLCHAR *	TableName	Input	Table name.
SQLSMALLINT	NameLength3	Input	Length of `NameLength3`.
SQLUSMALLINT	Unique	Input	Type of index information to return: SQL_INDEX_UNIQUE Only unique indexes will be returned. SQL_INDEX_ALL All indexes will be returned.
SQLUSMALLINT	Reserved	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. New applications should not use this value. Existing applications specifying this value will receive the same results as SQL_QUICK. SQL_QUICK : Statistics which are readily available at the server are returned. The values may 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 it is 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 175 in the order shown; the rows in the result set are ordered by NON_UNIQUE, TYPE, INDEX_QUALIFIER, INDEX_NAME and ORDINAL_POSITION.

Since 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 have been declared with a maximum length attribute of 128 to be consistent with SQL92 limits. Since DB2 names are less than 128, the application can choose to always set aside 128 characters (plus the null-terminator) for the output buffer, or alternatively, call SQLGetInfo() with the SQL_MAX_CATALOG_NAME_LEN, SQL_MAX_OWNER_SCHEMA_LEN, SQL_MAX_TABLE_NAME_LEN, and SQL_MAX_COLUMN_NAME_LEN to determine respectively the actual lengths of the TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME columns supported by the connected DBMS.

Although new columns may be added and the names of the existing columns changed in future releases, the position of the current columns will not change.

Table 175. Columns Returned By SQLStatistics

Column Number/Name Data Type Description
1 TABLE_CAT VARCHAR(128) This 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 that would be 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. 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 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 DATABASE 2 servers do not support filtered indexes, NULL is always returned. NULL is also returned if TYPE is SQL_TABLE_STAT.

Column Number/Name	Data Type	Description
1 TABLE_CAT	VARCHAR(128)	This 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 that would be 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. 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 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 DATABASE 2 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.
Note: 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 Universal Database, the last time the RUNSTATS command was run).

Return Codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_STILL_EXECUTING
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 176. SQLStatistics SQLSTATEs

SQLSTATE Description Explanation
24000 Invalid cursor state. A cursor was already opened on the statement handle.
40003 08S01 Communication link failure. The communication link between the application and data source failed before the function completed.
HY001 Memory allocation failure. DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY008 Operation canceled.
Asynchronous processing was enabled for the StatementHandle. The function was called and before it completed execution, SQLCancel() was called on the StatementHandle. Then the function was called again on the StatementHandle.
The function was called and, before it completed execution, SQLCancel() was called on the StatementHandle from a different thread in a multithread application.
HY009 Invalid argument value. TableName is null.
HY010 Function sequence error.
The function was called while in a data-at-execute (SQLParamData(), SQLPutData()) operation.
The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation.
An asynchronously executing function (not this one) was called for the StatementHandle and was still executing when this function was called.
HY014 No more handles. DB2 CLI was unable to allocate a handle due to internal resources.
HY090 Invalid string or buffer length. The value of one of the name length arguments was less than 0, but not equal to SQL_NTS.
The valid of one of the name length arguments exceeded the maximum value supported for that data source. The maximum supported value can be obtained by calling the SQLGetInfo() function.
HY100 Uniqueness option type out of range. An invalid Unique value was specified.
HY101 Accuracy option type out of range. An invalid Reserved value was specified.
HYC00 Driver not capable. DB2 CLI does not support catalog as a qualifier for table name.
HYT00 Timeout expired. The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetConnectAttr().

SQLSTATE	Description	Explanation
24000	Invalid cursor state.	A cursor was already opened on the statement handle.
40003 08S01	Communication link failure.	The communication link between the application and data source failed before the function completed.
HY001	Memory allocation failure.	DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY008	Operation canceled.	Asynchronous processing was enabled for the `StatementHandle`. The function was called and before it completed execution, `SQLCancel()` was called on the `StatementHandle`. Then the function was called again on the `StatementHandle`. The function was called and, before it completed execution, `SQLCancel()` was called on the `StatementHandle` from a different thread in a multithread application.
HY009	Invalid argument value.	`TableName` is null.
HY010	Function sequence error.	The function was called while in a data-at-execute (`SQLParamData()`, `SQLPutData()`) operation. The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation. An asynchronously executing function (not this one) was called for the `StatementHandle` and was still executing when this function was called.
HY014	No more handles.	DB2 CLI was unable to allocate a handle due to internal resources.
HY090	Invalid string or buffer length.	The value of one of the name length arguments was less than 0, but not equal to SQL_NTS. The valid of one of the name length arguments exceeded the maximum value supported for that data source. The maximum supported value can be obtained by calling the `SQLGetInfo()` function.
HY100	Uniqueness option type out of range.	An invalid `Unique` value was specified.
HY101	Accuracy option type out of range.	An invalid `Reserved` value was specified.
HYC00	Driver not capable.	DB2 CLI does not support `catalog` as a qualifier for table name.
HYT00	Timeout expired.	The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for `SQLSetConnectAttr()`.

Restrictions

None.

Example

/* From CLI sample browser.c */
/* ... */
SQLRETURN list_stats( SQLHANDLE hstmt,
                      SQLCHAR * schema,
                      SQLCHAR * tablename
                    ) {
 
/* ... */
 
    rc = SQLStatistics(hstmt, NULL, 0, schema, SQL_NTS,
                    tablename, SQL_NTS, SQL_INDEX_UNIQUE, SQL_QUICK);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 4, SQL_C_SHORT,
                          &non_unique, 2, &non_unique_ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 6, SQL_C_CHAR,
                          index_name.s, 129, &index_name.ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 7, SQL_C_SHORT,
                          &type, 2, &type_ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 9, SQL_C_CHAR,
                          column_name.s, 129, &column_name.ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 11, SQL_C_LONG,
                          &cardinality, 4, &card_ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLBindCol(hstmt, 12, SQL_C_LONG,
                          &pages, 4, &pages_ind);
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    printf("Statistics for %s.%s\n", schema, tablename);
 
    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");
    }

References

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]