When you create a nickname for a data source table, DB2 populates the global catalog with information about the table. This information includes, but is not limited to, the nickname, the table's name, all column names and, for each column:
This section explains how DB2 uses data type mappings to determine what DB2-supported data type should be defined for the column of a data source table. The section then discusses data type mappings (sometimes called "type mappings", for short) in two subsections. The first describes default mappings; the second shows how you can override default mappings and create new mappings.
How does DB2 determine what local type to use for a remote column? It consults a mapping between the column's type at the data source and a comparable local type, and chooses the latter. For example, in a default mapping supplied by DB2, the DB2 for VSE & VM data type CHAR, which supports up to 254 bytes, points to the DB2 data type CHAR. So if you create a nickname for a DB2 for VSE & VM table, and column C1 of the table has a data type of CHAR with a maximum length of 200, then, unless you override the default, the DB2 type CHAR is defined locally for C1.
Because of differences between RDBMSs, a default mapping between a data source data type and a federated server data type is not always one-to-one. However, the mapping is close enough to ensure that all requested values are returned.
For example, there is a default type mapping between:
Suppose that you create a nickname for an Oracle table that has a column C2 with a type of NUMBER(9,0). If you do not change the default mapping, the type for C2 will be locally defined as INTEGER. And because the 4 bytes of INTEGER support a maximum precision of 10, you can be sure that all values of C2 will be returned when C2 is queried from the federated server.
For listings of the default data type mappings, see the SQL Reference.
As the preceding example indicates, the local type and remote type in a default mapping are similar enough to ensure that when you query remote columns for which the remote type is defined, all values that conform to both types will be returned. But sometimes, you might require an alternative mapping. Consider these scenarios:
Certain columns in three tables in an Oracle data source have a data type DATE for time stamps. In a default mapping, this type points to the local DB2 type TIMESTAMP. So if you were to create nicknames for the three tables without changing the default, TIMESTAMP would be defined locally for these columns, and DB2 queries of the columns would yield time stamps. But suppose that you want such queries to yield times only. You could then map Oracle DATE to the DB2 type TIME, overriding the default. That way, when you create the nicknames, TIME, not TIMESTAMP, would be defined locally for the columns. As a result, when you query them, only the time portion of the time stamps would be returned. To override the default type mapping, you would use the CREATE TYPE MAPPING statement.
In the CREATE TYPE MAPPING statement, you can indicate whether the new mapping that you want is to apply to a specific data source (for example, a data source that a department in your organization uses) or to all data sources of a specific type (for example, all Oracle data sources), or to all data sources of a specific version of a type (for example, all Oracle 8.0.3 data sources).
You can change the local type in a type mapping for a specific table. For example, Oracle data type NUMBER(32,3) maps by default to the DB2 data type DOUBLE, a floating decimal data type. Suppose that in an Oracle table for employee information, a column BONUS was defined with a data type of NUMBER(32,3). Because of the mapping, a query of BONUS could return values that look like this:
5.0000000000000E+002 1.0000000000000E+003
where +002 signifies that the decimal point should be moved two places to the right, and +003 signifies that the decimal point should be moved three places to the right.
So that queries of BONUS can return values that look like dollar amounts, you could, for this particular table, remap NUMBER(32,3) to a DB2 DECIMAL type with a precision and scale that reflect the format of actual bonuses. For example, if you knew that the dollar portion of the bonuses would not exceed six figures, you could remap NUMBER(32,3) to DECIMAL(8,2). Under the constraint of this new mapping, a query of BONUS would return values like this:
500.00 1000.00
To change the type mapping for a column of a specific table, use the ALTER NICKNAME statement. With this statement, you can change the type defined locally for a column of a table for which a nickname has been defined.