Altering a local type for a data source object - examples

This topic provides examples that show how to change the data types for a data source object.

Example: A numeric data type mapping

In an Oracle table for employee information, the BONUS column is defined with a data type of NUMBER(32,3). The Oracle data type NUMBER(32,3) is mapped by default to the data type DOUBLE, a double-precision floating-point number data type. A query that includes the BONUS column might return values that look like this:

5.0000000000000E+002
1.0000000000000E+003

The scientific notation indicates the number of decimal places and the direction that the decimal point should be moved. In this example +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.

Queries that include the BONUS column can return values that look like dollar amounts. You change the local definition for the BONUS column in the table from the DOUBLE data type to DECIMAL data type. Use a precision and scale that reflect the format of actual bonuses. For example, if the dollar portion of the bonuses would not exceed six figures, map NUMBER(32,3) to DECIMAL(8,2). Under the constraint of this new local type, queries that include the BONUS column return values like this:

 500.00
1000.00

The nickname for the Oracle table is ORASALES. To map the BONUS column in the ORASALES table to the DECIMAL (8,2) data type, issue the following ALTER NICKNAME statement:

ALTER NICKNAME ORASALES ALTER COLUMN BONUS 
   LOCAL TYPE DECIMAL(8,2)

ORASALES: The nickname that you defined for the Oracle table.
ALTER COLUMN BONUS: The name of the column that is defined locally in the federated database SYSCAT.COLUMNS catalog view.
LOCAL TYPE DECIMAL(8,2): Identifies the new local type for the column.

This mapping applies only to the BONUS column in the Oracle table that is identified by the nickname ORASALES. All other Oracle data source objects that include the BONUS column use the default data type mapping for the Oracle NUMBER data type.

Example: A date data type mapping

The nickname for an Oracle table named SALES is ORASALES. The SALES table contains a column that is the Oracle DATE data type. The default type mapping for the Oracle DATE data type is to the TIMESTAMP data type. However, you want to display only the date value when you retrieve data from this column. You can alter the nickname for the SALES table to change the local type to the DATE data type.

ALTER NICKNAME ORASALES ALTER COLUMN ORDER_DATE 
   LOCAL TYPE DATE

Example: A data type mapping for a non-relational data source

The nickname for a table-structured file named drugdata1.txt is DRUGDATA1. The drugdata1.txt file contains a column that lists pharmaceutical drug names. The column name is DRUG. The DRUG column was originally defined as a CHAR(20). The length of the column must be changed to CHAR(30). You can alter the nickname for the drugdata1.txt file to change the mapping to the correct length:

ALTER NICKNAME DRUGDATA1 ALTER COLUMN DRUG
   LOCAL TYPE CHAR(30)