Column/field mapping rules

File-AID/RDX enables you to map Db2 columns or MVS fields of different types and lengths. The following sections outline the rules that govern column/field mapping.

Data Type Compatibility

File-AID/RDX enables you to map a column or field containing one type of data to a column containing a different type of data. The following table outlines which data types are compatible

Column/Field Mapping Data Type Compatibility

	Target Data Types
Source Data Types	CHAR or CH	VCHAR or VC	LVCHAR or LV	GRPH	VGRPH	LVGRPH	SMALLINT	INTEGER	BIGINT	DEC	REAL	DOUBLE	DECFLOAT	DATE	TIME	TMSTMP	TMESTZ	BINARY	FLTPIC	FIXPIC	HEXADEC	N (National)
CHAR or CH	X	X	X							X
VCHAR or VC	X	X	X							X
LVCHAR or LV	X	X	X							X
GRPH				X	X	X
VGRPH				X	X	X
LVGRPH				X	X	X
SMALLINT							X	X	X	X
INTEGER							X	X	X	X
BIGINT							X	X	X	X
DEC							X	X	X	X¹	X	X	X
REAL											X	X	X
DOUBLE											X	X	X
DECFLOAT											X	X	X
DATE														X
TIME															X
TMSTMP														X	X	X	X
TMESTZ															X	X	X
BINARY																		X
FLTPIC																			X
FIXPIC																				X
HEXADEC																					X
N (National)																						X

¹: For Db2 Utility load, the precision and scale of the target must match the source.

Character and Graphic Data

For character data (CHAR, VCHAR, and LVCHAR) and graphic data (GRPH, VGRPH, and LVGRPH), the following applies:

If the length of the source column is greater than the length of the target column/field, data is truncated on the right to a length equal to the target column/field.
If the length of the source column/field is less than the length of the target column/field, the data is padded on the right with blanks.

Numeric Data

For numeric data (SMALLINT, INTEGER, BIGINT, DECIMAL, DECFLOAT, and FLOAT) the source data value must not be greater than the defined target data type. If the source data value is greater, then the columns cannot be mapped. The following table lists the numeric data Type Value Ranges.

Numeric Data Type Value Ranges

Data Type	Value Range
SMALLINT	-32768 to +32767
INTEGER	-2,147,483,648 to +2,147,483,647
BIGINT	-9223372036854775808 to 9223372036854775807
DEC	-999999999999999 to +999999999999999
DECFLOAT(16)	10^-383 to 10⁺³⁸⁴
DECFLOAT(34)	10^-6143 to 10⁺⁶¹⁴⁴
FLOAT	5.4E-79 to 7.2E+75

Miscellaneous Rules

In addition to the rules regarding data type and column length, the following miscellaneous rules apply:

If you are using the Db2 LOAD Utility and a column for which the CMD field is blank is defined as NOT NULL, File-AID/RDX will display an error message to notify you to use the SQL INSERT option.
Unmapped columns/fields from the source table that do not exist in the target object are not loaded.
Submitting existing load JCL with load control tables that include IDENTITY columns does not require column remapping even when additional columns have been added to the target table.
Any additional columns added to the target table will require default values to be defined to Db2.
If you leave the CMD field blank and the target column is defined as NOT NULL WITH DEFAULT, File-AID/RDX will use the Db2 default.
If you leave the CMD field blank and the target column is defined as NULL, the default is NULL.
If you are using the Db2 LOAD Utility and a source column that allows NULLs matches a target column that does not, File-AID/RDX will not automatically map the two columns.
If you are using the Db2 LOAD Utility and a target column is defined as NOT NULL, you must map a source column to it.

Column Mapping Example

The Column Mapping screen shown in the following figure is displayed after you selected some literals and matched some Db2 source columns with Db2 target columns.

Db2 Column Mapping Screen After Some Column Assignments

File-AID/RDX ----------------- Column Mapping -------------------------------
Command ===>                                                   SCROLL ===> PAGE
                                                                SSID: DSN
Source Table:                          Target Table:
  TSOID01.BASE_EMPLOYEE                  TSOID02.EMPLOYEE_ADD

Line Commands:
   Use Column Number to map columns      S - Specify default column value
   Blank - For column default value

Column
Number Source Columns     Type(Length)    CMD Target Columns     Type(Length)
------- ------------------ ------------    --- ------------------ ------------
1       EMPNO              CHAR(6)         ___ EMPLOYEE_NUMBER    CHAR(6)
2       FIRSTNAME          VCHAR(12)       __2 FIRSTNAME          VCHAR(12)
3       LASTNAME           VCHAR(15)       L04 MIDNAME            VCHAR(12)
4       HIREDATE           DATE            __3 LASTNAME           VCHAR(15)
                                            ___ SEX                CHAR(1)
                                            ___ BIRTHDATE          DATE
         * End of Columns *                 ___ PHONENO            CHAR(4)
                                            __4 HIREDATE           DATE
                                            L05 BONUS              INTEGER

The example shown in the above figure, the extracted source table has four (4) columns. The literal definitions L04 and L05 were added during the current column mapping session. They can be assigned to more than one target column. Columns 2, 3, and 4 were matched automatically to the target columns with the same name, type and length.

The target table will be loaded with data from the

Source columns 2, 3, and 4
Literal definitions L04 and L05

For more detailed information about column to column mapping, see Column/Field Mapping Rules.