Additional numeric fields considerations
Numeric EXTERNAL
A numeric EXTERNAL field consists of a valid integer, decimal, or floating-point EBCDIC constant as defined by Db2 SQL. LOADPLUS allows leading and trailing blanks. With the exception of DECFLOAT, you must specify a length or POSITION (start:end). For DECFLOAT, if you do not specify a length, LOADPLUS defaults to 42. A length specification indicates the number of characters in the field, not the precision. The scale defaults to 0 if the input data does not contain an explicit decimal point.
Scale
You can specify scale on integer, FLOAT, and DECIMAL fields with or without specifying a length. Specifying scale multiplies or divides the number by a power of 10. For example, a scale of 3 divides the number by 1000 while a scale of -2 multiplies the number by 100.
Additional information
The following additional information applies to the scale specification:
- If you specify scale on a DECIMAL EXTERNAL field, LOADPLUS ignores the specification for input values that contain a decimal point.
- LOADPLUS performs the scale function after any rounding but before any REMOVEDECIMAL and REMOVESIGN functions.
- If you do not specify scale, 0 is the default unless the column is DECIMAL and the field is DECIMAL(PACKED) (explicit or defaulted), or DECIMAL ZONED. In these cases, the scale defaults to the scale of the column.
- With the exception of a decimal field, LOADPLUS does not support the scale specification on an input field if you are converting to a DECFLOAT column.
Rounding and truncation
With the exception of DECFLOAT, if you specify ROUND, LOADPLUS rounds the value during conversion to fit into the target column. If you do not specify ROUND, LOADPLUS truncates any additional digits that are not needed for precision. LOADPLUS does not consider truncation to be a conversion error.
Additional information
The following additional information applies to the rounding function:
- For DECFLOAT columns, LOADPLUS always rounds to fit into the target column. The value of the DECFLOAT_ROUNDMODE option determines the rounding method.
- LOADPLUS performs any rounding before the scale function and before performing the REMOVEDECIMAL function.
Converting to character data
This section describes how LOADPLUS converts numeric data to character data.
INTEGER EXTERNAL, BIGINT, INTEGER, and SMALLINT
When converting an integer field to a CHAR column, LOADPLUS places your data in the CHAR column such that the last character of your input data is in the position described in the following table:
Input | CHAR column length definition | Position of last character in CHAR column |
|---|---|---|
INTEGER EXTERNAL or BIGINT | Greater than or equal to 20 | 20th position |
Less than 20 | Last position | |
INTEGER or SMALLINT | Greater than or equal to 11 | 11th position |
Less than 11 | Last position |
The following data conversion results occur:
- When the length of the value of your input data is smaller than the length of your CHAR column, LOADPLUS pads your column with leading blanks. Before padding with leading blanks, LOADPLUS removes any leading zeros and, if applicable based on your field specification, any sign.
- When the length of your CHAR column is greater than the position of the last character of input data as shown in converting to the character data table, LOADPLUS inserts blanks in the remainder of the column. For example, for BIGINT input, when the length of your CHAR column is greater than 20, LOADPLUS inserts blanks in positions 21 and greater.
- When the length of your CHAR column is smaller than the length of the value of your input data, LOADPLUS discards the record.
The following table illustrates how LOADPLUS handles integer input conversion to CHAR columns:
Data type input | Data type column | Data input 1 | Data column 2 | Comments |
|---|---|---|---|---|
INTEGER EXTERNAL(6) | CHAR(22) | 'bbb100' | 'bbbbbbbbbbbbbbbbb100bb' | |
INTEGER | CHAR(10) | X'00000064' | 'bbbbbbb100' | Input value = 100 |
INTEGER | CHAR(14) | X'00000064' | 'bbbbbbbb100bbb' | Input value = 100 |
INTEGER | CHAR(3) | X'000003E8' | Error | Input value = 1000 Target too small Record discarded |
SMALLINT | CHAR(6) | X'03E8' | 'bb1000' | Input value = 1000 |
SMALLINT | CHAR(8) | X'83E8' | 'bbb-1000' | Input value = -1000 |
SMALLINT | CHAR(4) | X'83E8' | Error | Input value = -1000 Target too small Record discarded |
SMALLINT REMOVESIGN | CHAR(4) | X'83E8' | '1000' | Input value = -1000 |
SMALLINT REMOVESIGN | CHAR(8) | X'83E8' | 'bbbb1000' | Input value = -1000 |
1 Integer input is shown in hexadecimal format.
2 The letter b represents a blank.
DECIMAL
When converting a DECIMAL field to a CHAR column, LOADPLUS performs the following conversion tasks:
- Unless your field specification includes the REMOVEDECIMAL option, LOADPLUS includes a place for the decimal point in the output column, regardless of whether you specify a value for scale in your input field.
- When the length of the value of your input data is smaller than the length of your CHAR column, LOADPLUS pads your column with leading blanks. Before padding with leading blanks, LOADPLUS removes any leading zeros and, if applicable based on your field specification, performs any REMOVESIGN and REMOVEDECIMAL functions.
- When the length of your CHAR column is smaller than the length of the value of your input data, LOADPLUS discards the record.
The following table illustrates how LOADPLUS handles DECIMAL input to CHAR columns:
Data type input | Data type column | Data input1 | Data column2 | Comments |
|---|---|---|---|---|
DECIMAL(5,0) | CHAR(6) | X'12345C' | '12345.' | |
DECIMAL(5,0) | CHAR(5) | X'12345C' | Error | Target too small Record discarded |
DECIMAL(5,0) | CHAR(5) | X'02345C' | '2345.' | |
DECIMAL(5,0) | CHAR(6) | X'02345C' | 'b2345.' | |
DECIMAL(5,0) | CHAR(6) | X'2345D' | '-2345.' | |
DECIMAL(5,0) | CHAR(5) | X'2345D' | Error | Target too small Record discarded |
DECIMAL(5,0) REMOVESIGN REMOVEDECIMAL | CHAR(5) | X'2345D' | 'b2345' |
1 Decimal input is shown in hexadecimal format.
2 The letter b represents a blank.