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.
Beginning in version 10.1, 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.
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.
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.
SELECT birthdate FROM sudb.rschema.employee WHERE firstname=’SAM’
SELECT sudb.rschema.employee.birthdate FROM sudb.rschema.employee
WHERE sudb.rschema.employee.firstname=’SAM’
UPDATE sudb.rschema.employee SET firstname=’MARY’
INSERT INTO sudb.rschema.employee VALUES (‘Bob’)
DELETE FROM sudb.rschema.employee
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
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;
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