Altering remote tables using transparent DDL

You can alter remote data source tables that were created through the federated database using transparent DDL. You cannot alter tables that were created directly at the remote data source.

Before you begin

The privileges held by the authorization ID of 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

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).

About this task

You can use the ALTER TABLE statement to modify tables created through Federation component using transparent DDL. Using the ALTER TABLE statement you can:
  • Add new columns
  • Add the table primary key

Do not use the ALTER TABLE statement to add or modify column options. Use the ALTER NICKNAME statement instead.

Restrictions

The following restrictions apply to altering a remote table using transparent DDL:
  • You cannot modify tables that were natively created at the remote data source.
  • An existing primary key cannot be altered or dropped in a remote table.
  • Altering a remote table invalidates any packages dependent on the nickname associated with the remote table.
  • The remote data source must support the changes in the ALTER TABLE statement. For example, suppose that the remote data source does not support primary keys. Depending on how the data source responds to requests it does not support, an error might be returned or the request might be ignored.
  • 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 ALTER TABLE statement is not supported.

Procedure

To alter a remote table using transparent DDL, issue the ALTER TABLE statement.
Example: You want to add a primary key on a remote table EMPLOYEE that you created using transparent DDL. Using the following ALTER TABLE statement to modify the table: 
ALTER TABLE EMPLOYEE
   ADD PRIMARY KEY (EMP_NO, WORK_DEPT)
The columns used for a primary key must be NOT NULL, and cannot be columns that contain LOBs.
Example: You want to add the columns ORDER_DATE and SHIP_DATE to the remote table SPALTEN that was created using transparent DDL. Using the following ALTER TABLE statement to create the table: 
ALTER TABLE SPALTEN 
   ADD COLUMN ORDER_DATE  DATE
   ADD COLUMN SHIP_DATE  DATE