Column mapping rules

FM/Db2 uses the rules shown in Column mapping rules to determine if a given “From” column can be mapped to a given “To” column. “Yes” indicates that the mapping is valid. “No” indicates that the mapping is not valid.
Note: If mapping, you cannot select a From column with a data type that is incompatible with the data type of the To column. For example, From Column Mapping panel showing Delete “From column” selected shows the two DATE From columns, HIREDATE and BIRTHDATE, as un-selectable for mapping to the numeric To column, BONUS.
Table 1. Column mapping rules

This table has six columns. In the heading columns, the second column, "To" column, spans five columns, with subheadings "DATE","TIME", "TIMESTAMP", "Character", "Numeric". The final row, "Notes®:", spans all six columns.

“From” column “To” column
DATE TIME TIMESTAMP Character Numeric
DATE Yes No No Yes No
TIME No Yes No Yes No
TIMESTAMP Yes1 Yes2 Yes3 Yes No
Character4 Yes5 Yes5 Yes5 Yes Yes6
Numeric7 No No No No Yes
Notes:
  1. Conversion of TIMESTAMP data type to DATE data type results in truncation of time value. Data type conversion is not supported where the Db2® installation-defined format for dates is LOCAL.
  2. Conversion of TIMESTAMP data type to TIME data type results in truncation of date and microsecond values. Data type conversion is not supported where the Db2® installation-defined format for time is LOCAL.
  3. Conversion between unlike TIMESTAMP data types may result in loss of precision, or padding with zeroes in the fractional part of the second. Similarly the time zone information may be lost, or the default time zone for the current server may be added. For example: 
     
TIMESTAMP(4)                  ->       TIMESTAMP(10)
2012-12-02-12.34.56.1234               2012-12-02-12.34.56.1234567890

TIMESTAMP(4) WITH TIME ZONE   ->       TIMESTAMP(0)
2012-12-02-12.34.56.1234-12:00         2012-12-02-12.34.56
  4. The category “character” includes CHAR and VARCHAR data types.
  5. Character value must be in a valid format, depending on the Db2® installation-defined formats for date and time, otherwise the To column is set to the default value as specified by data create attributes. If the Db2® installation-defined format for dates or time is LOCAL, FM/Db2 is unable to validate the From column before inserting it into the To column.
  6. The “From” column must consist only of numeric characters, and is treated as a numeric column.
  7. The category “numeric” includes DECIMAL, NUMERIC, SMALLINT, INTEGER, REAL, DOUBLE, DOUBLE PRECISION, FLOAT, and DECFLOAT data types.
These rules describe how a validly mapped “From” column is moved to a “To” column. If the “To” column is:
DATE
If the “From” column has a data type of TIMESTAMP, only bytes 1–4 of the “From” column are moved to the “To” column; otherwise, the full four bytes of the “From” column are moved to the “To” column.
TIME
If the “From” column has a data type of TIMESTAMP, only bytes 5–7 of the “From” column are moved to the “To” column; otherwise, the full three bytes of the “From” column are moved to the “To” column.
TIMESTAMP
When the data types of the "FROM" and "TO" columns are identical all bytes of the "From" column are moved to the "To" column.

When the precision and TIME ZONE components of the "FROM" and "TO" columns are different:

  • Excess digits are lost in the fraction part of the second when the precision of the "FROM" column is greater than the precision of the "TO" column.
  • The fractional part of the second is padded on the right with zeroes when the precision of the "FROM" column is less than the precision of the "TO" column.
  • TIME ZONE information is lost when the "FROM" column includes a TIME ZONE and the "TO" column does not.
  • TIME ZONE information is added when the "FROM" column does not include a TIME ZONE and the "TO" column does.
Character
The data is aligned at the leftmost character position and, if necessary, truncated or padded with spaces at the right.

If the “From” column is a numeric column, the absolute (unsigned) value is used. If the column is defined as signed and the sign occupies a separate character, that character is not moved, and the sending item is considered to be one less character than the actual size.

Integer or packed
The data is aligned on the assumed decimal point and, if necessary, truncated or padded with zeros. If an assumed decimal point is not explicitly specified in the column definition, one is assumed immediately to the right of the column.

If the “From” column is numeric, the data is moved as if both the “From” column and “To” column were described as signed.

If the “From” column is alphanumeric, the data is moved as if the “From” column were described as a signed numeric.

Internal floating-point
A decimal point is assumed immediately to the left of the column. The data is aligned on the leftmost digit position following the decimal point, with the exponent adjusted accordingly.
External floating-point
The data is aligned on the leftmost digit position, and the exponent adjusted accordingly.

Related tasks