Specifying federated three-part names

A federated three-part name represents a remote object. The three-part name consists of a server name, a remote schema name, and a remote object name.

Description

A federated three-part name differs from three-part names supported in previous releases. In previous releases, a three-part name referred to a table name consisting of database name, a schema name, and a table identifier in the format db.schema.table.

A federated three-part name consists of the following elements:

server_name: A server name that is assigned to the data source server in the CREATE SERVER statement.
remote_schema: The remote schema name to which the object belongs.
object_name: The object name of the remote object that you want to access.

You specify a federated three-part name in the following format: server_name.remote_schema.remote_object

Alternatively, you can specify:

remote_schema.remote.table@server_name.

To specify this syntax, you need to set the DB2_COMPATIBILITY_VECTOR registry variable to the bit 0x20000 or to ORA. When this variable is set, the "@" in a table, view, or column name is treated as a delimiter.

Authorization

The privileges held by the authorization ID at the data source must include the privilege to select data from the object that the federated three-part name represents.

The remote object metadata for objects referenced in federated three-part names is stored in the federated cache instead of the federated database catalog. You do not need privileges previously required for nicknames when you reference federated three-part names in SQL statements.

Notes®

You can create a server with the same name as the local database name. If these names are equivalent, when the server name is used in federated three-part name, the three-part name is treated as a local name. The local name is used to find the local table.

If the local table is not found, error SQL0204N is issued.
If the first part of the three-part name does not match the local database name, the SYSCAT.SERVERS catalog is checked to verify if the name matches an existing server name.
If a match for the server name is not found, error SQL0204N is issued.

For data sources that do not support schemas, you can specify a remote object as a two-part name, for example server.table. Any two-part name, for example P1.TABLE, is first resolved to the local object as schema_name.object_name.

If the attempt to resolve the name fails, the federated server attempts to resolve the name to a federated object as server_name.object_name.
If both attempts fail, error SQL0204N is issued.

Recommendation: Do not create a server with the same name as a local schema name or a local database name.

Examples

Example 1: In this example, a DRDA wrapper was created and a server named SUDB was created for a remote database named REMOTE. You want to run a query against a remote table named EMPLOYEE. The table is in the schema named RSCHEMA in database REMOTE. Instead of creating a nickname over the EMPLOYEE table, you can query the remote table directly by using a federated three-part name..

SELECT birthdate FROM sudb.rschema.employee WHERE firstname=’SAM’

SELECT sudb.rschema.employee.birthdate FROM sudb.rschema.employee 
WHERE sudb.rschema.employee.firstname=’SAM’

Similarly, you can issue UPDATE, INSERT, and DELETE statements against the remote table:

UPDATE sudb.rschema.employee SET firstname=’MARY’
INSERT INTO sudb.rschema.employee VALUES  (‘Bob’)
DELETE FROM sudb.rschema.employee

Example 2: In this example, a view is created based on a query that contains nicknames:

CREATE VIEW v_tpn AS (SELECT c1, c2 FROM sudb.rschema.employee)

When the schema of the remote object changes, for example if column c2 is dropped, the view remains valid. However, this change will result in an error when column c2 is referenced. For example, after c2 is dropped from the remote table employee, the following query is issued:

select c1 from v_tpn

This query will succeed. However, the following query referencing c2 will fail:

select c1, c2 from v_ptn

Example 3: In this example, a stored procedure is created that reference a remote object with a three-part name.

CREATE OR REPLACE PROCEDURE cursor_example
  IS

    v_empno         int;
    v_ename         varchar(100);
    CURSOR emp_cur_3 IS SELECT id, name FROM serv1.schema1.employee WHERE id = 10 ORDER BY id;

  BEGIN

    OPEN emp_cur_3;
    FETCH emp_cur_3 INTO v_empno, v_ename;
    insert into employee_hist_local values(v_empno,v_  ename);

  END;

Example 4: In this example, a function is created that reference a remote object with a three-part name.

CREATE FUNCTION update_salary_1 (updEmpNum int, amount INTEGER) 
  RETURNS TABLE (name VARCHAR(10), newSalary INTEGER)
  LANGUAGE SQL
  MODIFIES SQL DATA
  NO EXTERNAL ACTION
  NOT DETERMINISTIC
  BEGIN  ATOMIC
  RETURN (SELECT name, salary FROM serv1.schema1.employee WHERE  serv1.schema1.employee.id = updEmpNum );

  END