SQLTables function (CLI) - Get table information

The SQLTables() function returns a list of table names and associated information that is stored in the system catalog of the connected data source. The list of table names is returned as 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.

Specification:

  • CLI 2.1
  • ODBC 1.0

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

Syntax

SQLRETURN   SQLTables        (
               SQLHSTMT          StatementHandle,   /* hstmt */
               SQLCHAR           *CatalogName,      /* szCatalogName */
               SQLSMALLINT       NameLength1,       /* cbCatalogName */
               SQLCHAR           *SchemaName,       /* szSchemaName */
               SQLSMALLINT       NameLength2,       /* cbSchemaName */
               SQLCHAR           *TableName,        /* szTableName */
               SQLSMALLINT       NameLength3,       /* cbTableName */
               SQLCHAR           *TableType,        /* szTableType */
               SQLSMALLINT       NameLength4);      /* cbTableType */

Function arguments

Table 1. SQLTables arguments
Data type Argument Use Description
SQLHSTMT StatementHandle Input The statement handle.
SQLCHAR * CatalogName Input A catalog qualifier of a 3-part table name that can contain a pattern value. 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 is returned. Otherwise, this is a valid filter for DBMSs that support 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 A buffer that can contain a pattern value to qualify the result set by the schema name.
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 A buffer that can contain a pattern value to qualify the result set by 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.
SQLCHAR * TableType Input A buffer that can contain a value list to qualify the result set by table type.
The value list is a list of uppercase comma-separated single values for the table types of interest. The following list contains valid table type identifiers.
  • ACCEL-ONLY TABLE1
  • ALIAS
  • AUXILIARY TABLE
  • GLOBAL TEMPORARY TABLE
  • HIERARCHY TABLE
  • INOPERATIVE VIEW
  • MATERIALIZED QUERY TABLE
  • NICKNAME
  • SYNONYM
  • SYSTEM TABLE
  • TABLE
  • TYPED TABLE
  • TYPED VIEW
  • VIEW
If TableType argument is a NULL pointer or a zero length string, this is equivalent to specifying all of the possibilities for the table type identifier.

If SYSTEM TABLE is specified, both system tables and system views (if there are any) are returned.
SQLSMALLINT NameLength4 Input The number of SQLCHAR elements (or SQLWCHAR elements for the Unicode variant of this function) that are required to store TableType, or SQL_NTS if TableType is null-terminated.

The CatalogName, SchemaName, and TableName input arguments accept search patterns.

Usage

Table information is returned in a result set where each table is represented by one row of the result set. To determine the type of access that is permitted on any table in the list, the application can call the SQLTablePrivileges() function. The application must be able to handle a situation where the user selects a table for which SELECT privileges are not granted.

To support obtaining just a list of schemas, you can apply the succeeding semantics for the SchemaName argument: if SchemaName is a string that contains a single percent (%) character, and CatalogName and TableName are empty strings, then the result set contains a list of valid schemas in the data source.

If TableType is a single percent character (%) and CatalogName, SchemaName, and TableName are empty strings, then the result set contains a list of valid table types for the data source. (All columns except the TABLE_TYPE column contain NULLs.)

If TableType is not an empty string, it must contain a list of uppercase, comma-separated values for the types of interest. You can enclose each value in single quotation marks or place double quotation marks around all of the values. For example, "'TABLE','VIEW'" or "TABLE, VIEW". If the data source does not support or does not recognize a specified table type, nothing is returned for that type.

Sometimes, an application calls the SQLTables() function with null pointers for some or all of the SchemaName, TableName, and TableType arguments so that no attempt is made to restrict the result set that is returned. A large result set that is returned by the SQLTables() function can result in a long retrieval time. You can specify three configuration keywords (SCHEMALIST, SYSSCHEMA, TABLETYPE) in the CLI initialization file to help restrict the result set when the application supplied null pointers for SchemaName, TableType, or both. If the application specifies a SchemaName string, the SCHEMALIST keyword is still used to restrict the output. Therefore, if the schema name that is supplied is not in the SCHEMALIST string, the result is an empty result set.

The result set that is returned by the SQLTables() function contains the columns that are listed in Columns returned by SQLTables in the order given. The rows are ordered by the TABLE_TYPE, TABLE_CAT, TABLE_SCHEM, and TABLE_NAME columns.

If you set the ExtendedTableInfo keyword to True when you are connected to the Db2 for z/OS Version 10 or 11 server with the PTF PI9449 applied, the result set that is returned by the SQLTables() function contains the following four new columns.
  • TEMPORAL_TABLE_TYPE
  • IS_ACCELERATED
  • ACCEL_ARCHIVE_STATUS
  • IS_ARCHIVE_ENABLED

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

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 or *USRLIBL as values in the SchemaName to resolve unqualified stored procedure calls or to find libraries in catalog API calls. If you specify *ALL, 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. For IBM® Db2 for IBM i servers, if you specify *USRLIBL, CLI searches on the current libraries of the server job. For other Db2 servers, *USRLIBL does not have a special meaning and CLI searches using *USRLIBL as a pattern. Alternatively, you can set the SchemaFilter IBM Data Server Driver configuration keyword or the Schema List CLI/ODBC configuration keyword to *ALL or *USRLIBL.

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

Columns returned by SQLTables

Column 1  TABLE_CAT (VARCHAR(128))
Name of the catalog, which contains the schema name that is specified in the TABLE_SCHEM column. The value is NULL if this table does not have catalogs.
Column 2  TABLE_SCHEM (VARCHAR(128))
Name of the schema, which contains database object that is named in the TABLE_NAME column.
Column 3  TABLE_NAME (VARCHAR(128))
Name of the table, view, alias, or synonym.
Column 4  TABLE_TYPE (VARCHAR(128))
Identifies the type that is given by the name in the TABLE_NAME column. It can have one of the following string values.
  • ACCEL-ONLY TABLE
  • ALIAS
  • AUXILIARY TABLE
  • GLOBAL TEMPORARY TABLE
  • HIERARCHY TABLE
  • INOPERATIVE VIEW
  • MATERIALIZED QUERY TABLE
  • NICKNAME
  • SYNONYM
  • SYSTEM TABLE
  • TABLE
  • TYPED TABLE
  • TYPED VIEW
  • VIEW
Column 5  REMARKS (VARCHAR(254))
Descriptive information about the table.

The following columns are returned when you are connected to the Db2 for z/OS Version 10 or 11 server with the PTF PI9449 applied and the ExtendedTableInfo keyword set to True.

Column 6  TEMPORAL_TABLE_TYPE (VARCHAR(11))
Identifies the type of temporal table. The TEMPORAL_TABLE_TYPE column can return one of the following values.
  • SYSTEM
  • APPLICATION
  • BITEMPORAL
  • Empty string
Column 7  IS_ACCELERATED (VARCHAR(3))
Identifies the table as the IBM Db2 Analytics Accelerator table. The table that is IBM Db2 Analytics Accelerator tables return YES and all other tables return NO.
Column 8  ACCEL_ARCHIVE_STATUS (VARCHAR(1))
Identifies the archive status of the IBM Db2 Analytics Accelerator table. The returned value corresponds to the ARCHIVE column value in the SYSACCEL.SYSACCELERATEDTABLES table.
Column 9  IS_ARCHIVE_ENABLED (VARCHAR(3))
Identifies whether the archive feature is enabled for the table. The table that has archive feature enabled returns YES while table that has archive feature disabled returns NO.

Return codes

  • SQL_ERROR
  • SQL_INVALID_HANDLE
  • SQL_STILL_EXECUTING
  • SQL_SUCCESS
  • SQL_SUCCESS_WITH_INFO

Diagnostics

Table 2. SQLTables 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 during a data-at-execute (SQLParamData(), SQLPutData()) operation.

The function was called while in 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 value 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.
HYT00 Timeout expired. The timeout period expired before the data source returned the result set. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetStmtAttr().
Note: This SQLSTATE applies only to .Net applications.

Restrictions

None.

Example

  /* get table information */
  cliRC = SQLTables(hstmt,
                    NULL,
                    0,
                    tbSchemaPattern,
                    SQL_NTS,
                    tbNamePattern,
                    SQL_NTS,
                    NULL,
                    0);
1 You can specify the ACCEL-ONLY TABLE table type only when you are connected to the Db2® for z/OS® Version 10 or 11 server with the PTF PI9449 applied and the ExtendedTableInfo keyword is set to True.