In a federated database, nicknames are identifiers for data source tables, aliases, and views. Distributed requests typically reference nicknames, not data source tables or views.
Nicknames are part of the means by which DB2 provides location transparency. Nicknames rely on server definitions for data source location information to find and efficiently access data sources. An ALTER SERVER statement can, for example, transparently update server performance data and version information for all users and applications without requiring new nicknames or changes to application code.
Nicknames can be created in the Control Center or from the command line processor. You can define more than one nickname for the same data source table or view.
Nicknames cannot be used in static SQL statements.
Before creating a nickname, run the equivalent of the RUNSTATS command at the data source and update statistics for data source objects. Statistical information is gathered from data sources when a nickname is created and stored in the federated database catalog. This catalog data includes table and column definitions, and, if available, index definitions and statistics.
The following SQL statement creates the nickname CUSTOMER:
CREATE NICKNAME CUSTOMER for OS390A.SHAWNB.CUSTLIST
You must hold one of the SYSADM or DBADM authorities, or, you must have either the database privilege IMPLICIT_SCHEMA or the schema privilege CREATEIN (for the current schema) at the federated database to use this statement.
For additional details on using the CREATE NICKNAME statement, refer to the SQL Reference.
References to data source objects typically use the defined nickname. The one exception is a reference within a pass-through session (see Using Pass-through Sessions with Servers for more information). For example, if you define the nickname DEPT for the data source table DB2MVS1.PERSON.DEPT, the statement SELECT * FROM DEPT is allowed; the statement SELECT * FROM DB2MVS1.PERSON.DEPT is not allowed.
Most utility commands (LOAD, IMPORT, EXPORT, REORGCHK, REORGANIZE TABLE) do not support nicknames
COMMENT ON is supported; it updates the system catalog at the federated database.
INSERT, UPDATE, and DELETE operations are not supported against nicknames.
After you have created several nicknames, you might want to use the following information to identify to which data source a given nickname corresponds or identify all nicknames at a given data source.
This example assumes that you know the nickname (PAYROLL) and who created it (ACCTG), but need additional information about the data source. Use the following SQL statement to first obtain information about what PAYROLL is known as at its data source (SERVER).
select option, setting
from syscat.taboptions
where tabname = 'PAYROLL'
and tabschema = 'ACCTG'
and option in ('SERVER','REMOTE_SCHEMA','REMOTE_TABLE');
The answer set from this statement is DB2_MVS, FINANCE, DEPTJ35_PAYROLL. You now know that PAYROLL is the nickname for the table called DEPTJ35_PAYROLL owned by FINANCE at the server named DB2_MVS. You can use this information in a subsequent SELECT statement:
select option,setting
from syscat.serveroptions
where servername = 'DB2_MVS'
and option in ('NODE','DBNAME');
The answer set from this statement is REGIONW and DB2MVSDB3. You now know that the table DEPTJ35_PAYROLL is in a database named DB2MVSDB3, on a node called REGIONW.
With this information, you can use the LIST NODE DIRECTORY command to obtain information about the REGIONW node, such as the communications protocol and security type used. If the node had been for a data source other than the DB2 Family, you would need to check that data source's configuration files to find similar information. For example, if the node had been an Oracle data source, you would get similar information from the Oracle tnsnames.ora file.
For details on system catalog views, refer to the SQL Reference.
The following SQL statement provides a list of all nicknames known to the federated database, including the schema name and remote server for each nickname.
select tabname,tabschema, setting as remote_server
from syscat.taboptions
where option = 'SERVER';