BASE_TABLE table function
The BASE_TABLE function returns both the object name and schema name of the object found after any alias chains have been resolved.
The schema is SYSPROC.
The specified objectname (and objectschema) are used as the starting point of the resolution. If the starting point does not refer to an alias, the schema name and the unqualified name of the starting point are returned. The function returns a single row table consisting of the following columns:
| Column name | Data type | Description |
|---|---|---|
| BASESCHEMA | VARCHAR(128) | Schema name of the object found after any alias chains have been resolved. Matches objectschema if no matching alias was found. |
| BASENAME | VARCHAR(128) | Unqualified name of the object found after any alias chains have been resolved. Matches objectname if no matching alias was found. The name may identify a table, a view, or an undefined object. |
- objectschema
- A character expression representing the schema used to qualify the supplied objectname value before resolution. objectschema must have a data type of CHAR or VARCHAR and a length greater than 0 and less than 129 bytes.
- objectname
- A character expression representing the unqualified name to be resolved. objectname must have a data type of CHAR or VARCHAR and a length greater than 0 and less than 129 bytes.
Note: The BASE_TABLE table function improves
performance in partitioned database configurations by avoiding the
unnecessary communication that occurs between the coordinator partition
and catalog partition when using the TABLE_SCHEMA and TABLE_NAME scalar
functions.
Example
The following statement using the TABLE_SCHEMA and TABLE_NAME functions is written as:
SELECT COLCOUNT INTO :H00030
FROM SYSCAT.TABLES
WHERE OWNER = TABLE_SCHEMA(:H00031 ,:H00032 )
AND TABNAME = TABLE_NAME(:H00031 ,:H00032 )
The equivalent statement using the BASE_TABLE function can be written as:
SELECT COLCOUNT INTO :H00030
FROM SYSCAT.TABLES A, TABLE(SYSPROC.BASE_TABLE(:H00032, :H00031)) AS B
WHERE A.OWNER = B.BASESCHEMA
AND A.TABNAME = B.BASENAME