Creating new remote tables using transparent DDL - examples

The following examples illustrate what to specify to create remote tables using transparent DDL and the use of data type mappings.

When you create remote tables using transparent DDL:

The remote data source must support the column data types and primary key option in the CREATE TABLE statement.
Example: 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.
The remote server must be specified in the OPTIONS clause. The OPTIONS clause can be used to override the remote name or the remote schema of the table being created. The SQL_SUFFIX option is allowed at the end of the CREATE TABLE statement. You can specify this option for any relational data source to add data source-specific options to the CREATE TABLE statement that is issued at the data source.

Example: You want to create the table EMPLOY on an Oracle server. In the CREATE TABLE statement, use the database data types when you specify each column. Using the CLP, the syntax to create the table is:

CREATE TABLE EMPLOY 
   ( EMP_NO       CHAR(6) NOT NULL,
     FIRSTNAME    VARCHAR(12) NOT NULL,
     MIDINT       CHAR(1) NOT NULL,
     LASTNAME     VARCHAR(15) NOT NULL,
     HIREDATE     DATE,
     JOB          CHAR(8),
     SALARY       DECIMAL(9,2),
     PRIMARY KEY (EMP_NO) )
     OPTIONS (REMOTE_SERVER 'ORASERVER', 
     REMOTE_SCHEMA 'J15USER1', REMOTE_TABNAME 'EMPLOY' )

EMPLOY: The name of the nickname associated with the table.
REMOTE_SERVER 'ORASERVER': The name that you supplied for the server in the CREATE SERVER statement. This value is case-sensitive.
REMOTE_SCHEMA 'J15USER1': The remote schema name. Although this parameter is optional, it is recommended that you specify a schema name. If this parameter is not specified, the nickname schema is used for the remote schema name. This value is case-sensitive.
REMOTE_TABNAME 'EMPLOY': The remote table name. This parameter is optional. If this parameter is not specified, the local table name is used for the remote table name. This value must be a valid name on the remote data source and cannot be an existing table name. This value is case-sensitive.

In the example above, the federated database uses reverse data type mappings to map the database data types to Oracle data types. On the remote Oracle server, the EMPLOY table is created using Oracle data types. The following table shows the mappings from the database data types to the Oracle data types for the columns specified in the example.

Table 1. An example of reverse data type mappings from the federated database to Oracle
Column	Db2® data type specified in the CREATE TABLE statement	Oracle data type used in the remote table
EMP_NO	CHAR(6) NOT NULL	CHAR(6) NOT NULL
FIRST_NAME	VARCHAR(12) NOT NULL	VARCHAR2(12) NOT NULL
MID_INT	CHAR(1) NOT NULL	CHAR(1) NOT NULL
LAST_NAME	VARCHAR(15) NOT NULL	VARCHAR2(15) NOT NULL
HIRE_DATE	DATE	DATE
JOB	CHAR(8)	CHAR(8)
SALARY	DECIMAL(9,2)	NUMBER(9,2)