DB2 Version 9.7 for Linux, UNIX, and Windows

BASE_TABLE table function


>>-BASE_TABLE--(--objectschema--,--objectname--)---------------><

The schema is SYSPROC.

The BASE_TABLE function returns both the object name and schema name of the object found after any alias chains have been resolved. 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:

Table 1. Information returned by the BASE_TABLE function
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