Creating a type mapping for a data source data type - example

In this example, all Oracle tables and views that use the Oracle NUMBER data type must map to the DECIMAL(8,2) data type. The Oracle NUMBER data type is mapped by default to the DOUBLE data type, a floating decimal data type.

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 DECIMAL(8,2). If the nicknames do not exist, create a data type mapping that specifies the data source type. Ensure that the data source wrapper is created before running the CREATE TYPE MAPPING statement. To create the type mapping from Oracle NUMBER data type to the DECIMAL(8,2) data type, run the CREATE TYPE MAPPING statement, for example:

CREATE TYPE MAPPING MY_ORACLE_DEC FROM SYSIBM.DECIMAL(8,2) 
    TO SERVER TYPE ORACLE TYPE NUMBER

MY_ORACLE_DEC: 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.DECIMAL(8,2): The local Db2® 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 TYPE ORACLE: The type of data source.
TYPE NUMBER: The data source data type that you are mapping to the local data type. User-defined data types are not allowed.

The DECIMAL(8,2) data type is defined locally for the Oracle columns. When you create nicknames on Oracle tables and views that contain NUMBER columns, the Oracle NUMBER data type maps to the DECIMAL(8,2) data type.