BASE_TABLE

Edit online

The BASE_TABLE function returns the object names and schema names of the object found for an alias.

Read syntax diagramSkip visual syntax diagramBASE_TABLE(object-schema ,object-name)
The schema is SYSPROC.
object-schema
A character or graphic string expression that identifies the SQL or system schema name used to qualify the supplied object-name. object-schema must have an actual length less than 129 characters. A special value of *LIBL may be specified, in which case, the first instance of a file named object-name found in the library list will be used. This name is case sensitive and must not be delimited.
object-name
A character or graphic string expression that identifies the SQL or system name of the object to be resolved. object-name must have an actual length less than 129 characters. This name is case sensitive and must not be delimited.

If the specified object does not refer to an alias or it is not found, the result of the function is the input object name and schema.

The result of the function is a table containing a single row with the format shown in the following table. All the columns are nullable.
Table 1. Format of the resulting table for BASE_TABLE
Column name Data type Contains
BASESCHEMA VARCHAR(128) Name of the SQL schema that contains the table or view referenced by the alias. This is object-schema if no alias was found. The name is undelimited and case sensitive.
BASENAME VARCHAR(128) Name of the table or view referenced by the alias. This is the object-name if no alias was found. The name is undelimited and case sensitive.
SYSTEM_TABLE_SCHEMA CHAR(10) System schema name. This column will contain the NULL value if no alias was found or if the alias references a remote RDB. The name may be delimited and is case sensitive.
SYSTEM_TABLE_NAME CHAR(10) System table name. This column will contain the NULL value if no alias was found or if the alias references a remote RDB. The name may be delimited and is case sensitive.
MEMBER_NAME CHAR(10) The member name that was identified for a member alias. This column will contain the NULL value if no alias was found or if the alias does not reference a specific member. The name may be delimited and is case sensitive.
RDBNAME VARCHAR(128) The RDB if the object is a three-part alias for a remote object. This column will contain the NULL value if no alias was found or there is no RDB is associated with the alias.

The CCSID of the result columns is the default CCSID at the current server.

Example

  • The following query will return the base table information for every alias identified in SYSTABLES:
    SELECT C.BASESCHEMA, C.BASENAME
FROM QSYS2.SYSTABLES A, 
LATERAL (
    SELECT * FROM TABLE(SYSPROC.BASE_TABLE(A.TABLE_SCHEMA,A.TABLE_NAME)) AS X)
         AS C
WHERE A.TABLE_TYPE=’A’