What is transparent DDL

Transparent DDL provides the ability to create and modify remote tables through the federated database without using pass-through sessions.

The SQL statements you use with transparent DDL are CREATE TABLE, ALTER TABLE, and DROP TABLE.

A transparent DDL CREATE TABLE statement creates a remote table at the data source and a nickname for that table at the federated server. It will map the database data types you specify to the remote data types using the default reverse type mappings. In general, the wrappers provide type mappings. You can also create user-defined reverse type mappings to override the default mappings.

The advantage of using transparent DDL is that database administrators can use procedures that they are familiar with to create both local and remote tables. Transparent DDL centralizes table administration and facilitates granting authorizations.

Transparent DDL is supported with the following data sources:
  • Db2® for z/OS®
  • Db2 for IBM® i
  • Db2
  • Db2 Server for VM and VSE
  • Informix®
  • JDBC
  • Microsoft SQL Server
  • ODBC
  • Oracle
  • Sybase
  • Teradata

The database administrator can use the command line processor (CLP) to create the tables. Using transparent DDL avoids the need to learn the different DDL syntax required for each data source.

Before you can create remote tables on a data source through the federated database, you need to configure access to the data source:
  • The wrapper for that data source needs to be registered in the global catalog
  • The server definition needs to be created for the server where the remote table will be located
  • The user mappings, if required, need to be created between the federated server and the data source server
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).