TABLE_LOCATION

The TABLE_LOCATION function searches for an object and returns the location name of the object after any alias chains have been resolved.

>>-TABLE_LOCATION(-object-name-+-------------------------------------+-)-><
                               '-, object-schema-+-----------------+-'     
                                                 '-, location-name-'

The schema is DSN8.

The starting point of the resolution is the object that is specified by object-name and, if specified, object-schema and location-name. If the starting point does not refer to an alias, the location name of the starting point is returned. The resulting name can be of a table, view, or undefined object. The function returns a blank if there is no location name.

object-name: A character expression that specifies the unqualified name to be resolved. The unqualified name is usually of an existing alias. object-name must have a data type of VARCHAR and an actual length that is no greater than 18 bytes.
object-schema: A character expression that represents the schema that is used to qualify the value specified in object-name before resolution. object-schema must have a data type of VARCHAR and an actual length that is no greater than 8 bytes.
If object-schema is not specified or is null, the default schema is used for the qualifier.
location-name: A character expression that represents the location that is used to qualify the value specified in object-name before resolution. location-name must have a data type of VARCHAR and an actual length that is no greater than 16 bytes.
If location-name is not specified or is null, the location name is equivalent to "any".

The result of the function is VARCHAR(16). If object-name can be null, the result can be null; if object-name is null, the result is the null value.

The following table shows the external and specific names for TABLE_LOCATION. The specific names differ depending on the number of input arguments to the function.

Table 1. External and specific names for TABLE_LOCATION
Input arguments	External name	Specific name
object-name (VARCHAR)	DSN8DUTI	DSN8.DSN8DUTILV
object-name (VARCHAR) object-schema (VARCHAR)	DSN8DUTI	DSN8.DSN8DUTILVV
object-name (VARCHAR) object-schema (VARCHAR) location-name (VARCHAR)	DSN8DUTI	DSN8.DSN8DUTILVVV

Example: Assume that:

DSN8.ALIAS_RS_SYSTABLES is an alias of SYSIBM.SYSTABLES at location name 'REMOTE_SITE'.
The CURRENT SQLID is DSN8.

Use TABLE_LOCATION to find the location name where the base object for ALIAS_RS_SYSTABLES resides.

   VALUES DSN8.TABLE_LOCATION( 'ALIAS_RS_SYSTABLES' );

The result of the function is 'REMOTE_SITE'.