Creating new remote tables using transparent DDL

To create a remote table using transparent DDL, you specify the CREATE TABLE statement.

Before you begin

Before you create a remote table, you must configure the federated server to access that data source. This configuration includes:
  • Creating the wrapper for that data source type
  • Supplying the server definition for the server where the remote table will be located
  • Creating the user mappings between the federated server and the data source server

To issue transparent DDL statements, your authorization ID must have the necessary privileges on the nickname (for the federated server to accept the request), and the comparable privileges on the remote data source server (for the data source to accept the request).

The privileges held by the authorization ID issuing the transparent DDL statements must include at least one of the following:
  • SYSADM or DBADM authority
  • CREATETAB authority on the database and USE privilege on the table space as well as one of:
    • IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the table does not exist
    • CREATEIN privilege on the schema, if the schema name of the table refers to an existing schema

About this task

Restrictions

The following restrictions apply to creating a remote table using transparent DDL:
  • You cannot modify or drop tables that were natively created at the remote data source.
  • Materialized query tables cannot be created on remote data sources.
  • You can specify basic column information in the table definition, but you will not be able to specify table options or column options. For example, the LOB options (LOGGED and COMPACT) are not supported.
  • You cannot specify a comment on a column.
  • You cannot generate column contents.
  • You can specify a primary key, but you cannot specify a foreign key or check constraints. The columns used for a primary key must be NOT NULL, and cannot include columns containing LOBs.
  • You cannot modify the parameters of existing columns, such as the data type or length.
  • The DEFAULT clause in the CREATE TABLE statement is not supported.

Procedure

To create a remote table from the command line prompt, issue the CREATE TABLE statement with the appropriate parameters set.