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

The SQLStatistics() function retrieves index information for a given table. The SQLStatistics() function also returns the cardinality and the number of pages that are associated with the table and the indexes on the table.

Specification:

CLI 2.1
ODBC 1.0

The information is returned in a result set, which you can retrieve by using the same functions that you use to process a result set that is generated by a query.

Unicode equivalent: You can also use this function with the Unicode character set. The corresponding Unicode function is SQLStatisticsW(). For information about ANSI to Unicode function mappings, see Unicode functions (CLI).

Syntax

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

Function arguments

Table 1. SQLStatistics arguments
Data type	Argument	Use	Description
SQLHSTMT	StatementHandle	Input	The statement handle.
SQLCHAR *	CatalogName	Input	A catalog qualifier of a 3-part table name. If the target DBMS does not support 3-part naming, and CatalogName is not a null pointer and does not point to a zero-length string, then an empty result set and SQL_SUCCESS isreturned. Otherwise, this is a valid filter for DBMSs that supports 3-part naming.
SQLSMALLINT	NameLength1	Input	The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store CatalogName, or SQL_NTS if CatalogName is null-terminated.
SQLCHAR *	SchemaName	Input	The schema qualifier of the specified table.
SQLSMALLINT	NameLength2	Input	The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store SchemaName, or SQL_NTS if SchemaName is null-terminated.
SQLCHAR *	TableName	Input	The table name.
SQLSMALLINT	NameLength3	Input	The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store TableName, or SQL_NTS if TableName is null-terminated.
SQLUSMALLINT	Unique	Input	The type of index information to return: SQL_INDEX_UNIQUE Only unique indexes are returned. SQL_INDEX_ALL All indexes are returned.
SQLUSMALLINT	Reserved	Input	Indicates 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 that specify this value will receive the same results as SQL_QUICK. SQL_QUICK : Statistics which are readily available at the server are returned. No attempt is made to ensure that the values are current.

Usage

The SQLStatistics() function returns two types of information:

Statistics information for the table (if it is available):
- if the TYPE column of the result set is set to SQL_TABLE_STAT, the number of rows in the table, and the number of pages that are used to store the table are returned.
- if the TYPE column of the result set indicates an index, the number of unique values in the index, and the number of pages that are used to store the indexes are returned.
Information about each index, where each index column is represented by one row of the result set. The result set columns are described in Columns returned by SQLStatistics. The rows in the result set are ordered by NON_UNIQUE, TYPE, INDEX_QUALIFIER, INDEX_NAME and KEY_SEQ columns.

In many cases, calls to the SQLStatistics() function map to a complex and thus expensive query against the system catalog, so you should use the calls sparingly, and save the results rather than repeating calls.

If the schema qualifier argument that is associated with a table name is not specified, the schema name defaults to the argument that is in effect for the current connection.

Call the SQLGetInfo() function 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 the actual lengths of the TABLE_CAT, TABLE_SCHEM, TABLE_NAME, and COLUMN_NAME columns that are supported by the connected DBMS.

You can specify *ALL as a value in the SchemaName to resolve unqualified stored procedure calls or to find libraries in catalog API calls. CLI searches on all existing schemas in the connected database. You are not required to specify *ALL, as this behavior is the default in CLI. Alternatively, you can set the SchemaFilter IBM® Data Server Driver configuration keyword or the Schema List CLI/ODBC configuration keyword to *ALL.

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

Columns returned by SQLStatistics

Column 1 TABLE_CAT (VARCHAR(128))

The catalog name of the table for which the index applies. The value is NULL if this table does not have catalogs.

Column 2 TABLE_SCHEM (VARCHAR(128))

The name of the schema containing TABLE_NAME.

Column 3 TABLE_NAME (VARCHAR(128) not NULL)

The name of the table.

Column 4 NON_UNIQUE (SMALLINT)

Indicates whether the index prohibits duplicate values:

SQL_TRUE is returned if the index allows duplicate values.
SQL_FALSE is returned 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).

Column 5 INDEX_QUALIFIER (VARCHAR(128))

The string that 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.

Column 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.

Column 7 TYPE (SMALLINT not NULL)

Indicates the type of information that is contained in this row of the result set:

SQL_TABLE_STAT indicates that this row contains statistics information about the table.
SQL_INDEX_CLUSTERED indicates that this row contains information about an index, and the index type is a clustered index.
SQL_INDEX_HASHED indicates that this row contains information about an index, and the index type is a hashed index.
SQL_INDEX_OTHER indicates that this row contains information about an index that is not clustered or hashed

Column 8 ORDINAL_POSITION (SMALLINT)

The ordinal position of the column in 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 9 COLUMN_NAME (VARCHAR(128))

The 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.

Column 10 ASC_OR_DESC (CHAR(1))

The sort sequence for the column; "A" for ascending, or "D" for descending. A NULL value is returned if the value in the TYPE column is SQL_TABLE_STAT.

Column 11 CARDINALITY (INTEGER)

If the TYPE column contains the value SQL_TABLE_STAT, this column contains the number of rows that are in the table.
If the TYPE column value is not SQL_TABLE_STAT, this column contains the number of unique values that are in the index.
A NULL value is returned if information is not available from the DBMS.

Column 12 PAGES (INTEGER)

If the TYPE column contains the value SQL_TABLE_STAT, this column contains the number of pages that are used to store the table.
If the TYPE column value is not SQL_TABLE_STAT, this column contains the number of pages that are used to store the indexes.
A NULL value is returned if information is not available from the DBMS.

Column 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.

Note: An application can check the SQLERRD(3) and SQLERRD(4) fields of the SQLCA to gather some statistics on a table. However, the accuracy of the information that is returned in those fields depends on many factors, such as the use of parameter markers and expressions within the statement. The main factor that you can control is the accuracy of the database statistics. For example, for Db2, the last time the RUNSTATS command was run. Therefore, the statistics information that is returned by SQLStatistics() is often more consistent and reliable than the statistics information that is contained in the SQLCA fields that were previously explained.

Return codes

SQL_ERROR
SQL_INVALID_HANDLE
SQL_STILL_EXECUTING
SQL_SUCCESS
SQL_SUCCESS_WITH_INFO

Diagnostics

Table 2. 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. It is likely that process-level memory has been exhausted for the application process. Consult the operating system configuration for information about process-level memory limitations.
HY008	Operation was Canceled.	Asynchronous processing was enabled for StatementHandle. The function was called and before it completed execution, `SQLCancel()` was called on StatementHandle from a different thread in a multithreaded application. Then the function was called again on StatementHandle.
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. The function was called before a statement was prepared on the statement handle.
HY014	No more handles.	Db2 CLI was unable to allocate a handle due to resource limitations.
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 that is 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.
HYT00	Timeout expired.	The timeout period expired before the data source returned the result set. You can set the timeout period by using the SQL_ATTR_QUERY_TIMEOUT attribute for `SQLSetStmtAttr()`. Note: This SQLSTATE applies only to .Net applications.

Restrictions

None.

Example

  /* get index and statistics information for a base table */
  cliRC = SQLStatistics(hstmt,
                        NULL,
                        0,
                        tbSchema,
                        SQL_NTS,
                        tbName,
                        SQL_NTS,
                        SQL_INDEX_UNIQUE,
                        SQL_QUICK);