Retrieving catalog information about aliases
Query SYSIBM.SYSTABLES to obtain information about aliases.
About this task
You can use the SYSIBM.SYSTABLES table to find information about aliases by referencing the following three columns:
- LOCATION contains your subsystem's location name for the remote system, if the object on which the alias is defined resides at a remote subsystem.
- TBCREATOR contains the schema table or view.
- TBNAME contains the name of the table or the view.
You can also find information about aliases by using the following user-defined functions:
- TABLE_NAME returns the name of a table, view, or undefined object found after resolving aliases for a user-specified object.
- TABLE_SCHEMA returns the schema name of a table, view, or undefined object found after resolving aliases for a user-specified object.
- TABLE_LOCATION returns the location name of a table, view, or undefined object found after resolving aliases for a user-specified object.
The NAME and CREATOR columns of the SYSTABLES table contain the name and schema of the alias, and three other columns contain the following information for aliases:
- TYPE is A.
- DBNAME is DSNDB06.
- TSNAME is SYSTSTAB.
If similar tables at different locations have names with
the same second and third parts, you can retrieve the aliases for
them with a query like this one:
SELECT LOCATION, CREATOR, NAME
FROM SYSIBM.SYSTABLES
WHERE TBCREATOR='DSN8D10' AND TBNAME='EMP'
AND TYPE='A';