Support and considerations for federated three-part names

When working with federated three-part names, you need to be aware of supported function and environments, and when to use federated three-part names or nicknames.

Support for federated three-part names

Federated three-part names are supported for all relational data sources in the following environments:
  • Db2® pureScale®
  • Massively parallel processing (MPP)
  • Serial mode
The following SQL statements support federated three-part table names specified for relational data sources:
  • SELECT
  • INSERT, UPDATE, DELETE
  • Positioned updates and deletes
  • CREATE VIEW
  • MERGE (the remote table cannot be a target table)
  • UNION, INTERSECT
These SQL statements are supported on the Export utility.

Support of three-part names in static SQL is limited to the Db2 and Oracle data sources. Static SQL statements that reference three-part names are always run with the VALIDATE RUN bind option. No dependency is recorded.

Considerations: When to use federated three-part names or nicknames

For the following functions, you need to use nicknames instead of federated three-part names:
  • Referencing remote objects for non-relational data sources

    Federated three-part names are supported for relational data sources only.

  • Defining informational constraints and creating indexes.

    These functions are not supported for federated three-part names.

  • Controlling authorizations and privileges at the object level.

    You cannot control privileges for federated three-part names.

  • Updating statistics.

    Statistics are collected when you use federated three-part names to access the remote tables. However, because the metadata for remote objects is not in the federated database catalog, you cannot update statistics.

In the following situation, you can use nicknames or federated three-part names:
  • For altering a column data type to a data type other than the default mapping, you need to use nicknames. As an alternative, you can create views over the federated three-part names or use casting functions in DML statements.