Creating a type mapping for all data source objects on a server - example

In this example, the server is defined to the federated database as ORA2SERVER. Each table contains a column with an Oracle DATE data type.

The Oracle DATE data type contains the century, year, month, day, hour, minute, and second. The Oracle DATE data type is mapped by default to the local TIMESTAMP data type. However, when you query any object on this server, the result set must return only the time information (hour, minute, and second).

Use the ALTER NICKNAME statement to change the local types of existing nicknames. You must modify each nickname separately to change the local data type to TIME.

If the nicknames do not exist, create a data type mapping that specifies the data source type.

To map the Oracle DATE data type to the TIME data type for the ORA2SERVER, issue the following statement:

CREATE TYPE MAPPING ORA2_DATE FROM SYSIBM.TIME 
   TO SERVER ORA2SERVER TYPE DATE

ORA2_DATE: The name that you give to the type mapping. The name cannot duplicate a data type mapping name that already exists in the catalog.
FROM SYSIBM.TIME: The local schema and the local data type. If the length or precision and scale are not specified, then these values are determined from the source data type.
TO SERVER ORA2SERVER: The local name of the data source server.
TYPE DATE: The data source data type that you are mapping to the local data type. User-defined data types are not allowed.

The federated database locally defines the TIME data type for the Oracle columns of data type DATE.

When you create nicknames on Oracle tables and views that contain DATE columns, the Oracle DATE data type maps to the Db2 DECIMAL(8,2) data type.

Data source objects on other Oracle servers are not affected by this data type mapping.