Retrieving catalog information about aliases

Query SYSIBM.SYSTABLES to obtain information about aliases.

About this task

Begin general-use programming interface information.

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';

End general-use programming interface information.