For an input data file in fixed-size data format, specify the input data in characters or in the format stored in the database. When using a constructor function to generate the data to be stored in an abstract data type, code the input parameter either in character string data or in the data type of the function argument.
Numeric data in character string format and character string data in character string format are stored in columns of the data types listed in the table below.
Table 5-30 Numeric data and character string data in character string format
Numeric data in character string format | Character string data in character string format |
---|---|
|
|
For details about how to specify input data in characters, see 5.5.1 DAT format. For details about how to specify input data in the format stored in the database, see 5.5.2 Binary format.
Table 5-31 Handling of space (0x20) and enclosing character (0x22) when the input data is specified in character string format (fixed-size data format)
Data type of table column or data type of parameter for constructor function | Specification of column-name statement and option statement | Space (0x20) | Enclosing character (") | |
---|---|---|---|---|
Not enclosed in enclosing characters | Enclosed in enclosing characters | |||
INTEGER, SMALLINT, DECIMAL, FLOAT, SMALLFLT | allspace option specified in the option statement | The portion of the data without the consecutive leading and trailing spaces is treated as the input data. You must not specify any spaces between numeric values or between a sign and a numeric value because such spaces will not be removed. If the data consists of only spaces, 0 is assumed as the specified value. | An input data error results. | |
allspace option omitted from the option statement | The portion of the data without the consecutive leading and trailing spaces is treated as the input data. You must not specify any spaces between numeric values or between a sign and a numeric value because such spaces will not be removed. If the data consists of only spaces, the utility assumes that no input data is specified, resulting in an input data error. | |||
CHAR, MCHAR | mode=text | The portion of the data without the consecutive trailing spaces is treated as the input data. | The value including spaces is treated as the input data. | " is treated as an enclosing character. The value without " is treated as the input data. |
mode=bin | The entire value, including the spaces, is treated as the input data. | " is not treated as an enclosing character. The entire value, including ", is treated as the input data. | ||
NCHAR | -- | The portion of the data without the consecutive trailing spaces is treated as the input data. | The value including spaces is treated as the input data. | " is treated as an enclosing character. The value without " is treated as the input data. |
VARCHAR, MVARCHAR | enclose_del=yes | The portion of the data without the consecutive trailing spaces is treated as the input data. If the data consists of only spaces, 1-byte space data is assumed. | The value including spaces is treated as the input data. | " is treated as an enclosing character. The value without " is treated as the input data. Consecutive enclosing characters ("") are treated as 0-length data. |
enclose_del=no | " is not treated as an enclosing character. The value including " is treated as the input data. | |||
NVARCHAR | -- | The portion of the data without the consecutive trailing spaces is treated as the input data. If the data consists of only spaces, 1-byte (odd-byte) space is assumed, resulting in an input data error. | The value including spaces is treated as the input data. | " is treated as an enclosing character. The value without " is treated as the input data. Consecutive enclosing characters ("") is treated as 0-length data. |
DATE, INTERVAL YEAR TO DAY, TIME, INTERVAL HOUR TO SECOND, TIMESTAMP | -- | The portion of the data without the consecutive leading and trailing spaces is treated as the input data. | An input data error results. | |
BLOB | -- | The portion of the data without the consecutive trailing spaces is treated as the input data. | The value including spaces is treated as the input data. | " is not treated as an enclosing character. The value including " is treated as the input data. |
BINARY(7) | -- | The value including spaces is treated as the input data. | " is not treated as an enclosing character. The value including " is treated as the input data. | |
XML-type BINARY attribute parameter (when a file path is specified) | -- | The portion of the data without the consecutive trailing spaces is treated as the file path name. | The portion of the data without the consecutive trailing spaces is treated as the file path name. Enclosing characters are also treated as part of the file path name. | " is not treated as an enclosing character. The value including " is treated as the file path name. |
Table 5-32 Data storage when the input data is specified in character string data format (fixed-size data format)
Data type of table column or parameter data type of constructor function | Specification of column-name statement and option statement | Data specified in the input data file | Storage data | |
---|---|---|---|---|
INTEGER, SMALLINT, DECIMAL, FLOAT, and SMALLFLT | type=char(3) | 1![]() | 1 for each data type | |
![]() | ||||
![]() ![]() | ||||
+![]() | Input data error | |||
1![]() | ||||
type=char(3) | allspace operand specified in the option statement | ![]() | 0 for each data type | |
allspace operand omitted from the option statement | ![]() | Input data error | ||
CHAR(7) and MCHAR(7) | type=char(7) or type=char(7),mode=text | ABC![]() | ABC![]() | |
"ABC"![]() | ||||
"ABC![]() | ||||
""ABC"" | "ABC"![]() | |||
![]() | ![]() | |||
""![]() | ||||
![]() ![]() ![]() ![]() | ![]() ![]() ![]() ![]() | |||
type=char(7),mode=bin | ABC![]() | ABC![]() | ||
"ABC"![]() | "ABC"![]() | |||
"ABC![]() | "ABC![]() | |||
""ABC"" | ""ABC"" | |||
![]() | ![]() | |||
""![]() | ""![]() | |||
![]() ![]() ![]() ![]() | ![]() ![]() ![]() ![]() | |||
NCHAR(3) | type=char(6) | ![]() ![]() ![]() | ![]() ![]() ![]() | |
"![]() ![]() | "![]() ![]() | |||
"![]() ![]() | ![]() ![]() ![]() | |||
""![]() | "![]() ![]() | |||
![]() ![]() ![]() | ![]() ![]() ![]() | |||
""![]() ![]() | ""![]() ![]() | |||
VARCHAR(7) and MVARCHAR(7) | type=char(7) or type=char(7),enclose_del=yes | ABC![]() | ABC (3 bytes) | |
"ABC"![]() | ||||
"ABC![]() | ABC![]() | |||
""ABC"" | "ABC" (5 bytes) | |||
![]() | ![]() | |||
""![]() | (0 byte) | |||
![]() ![]() ![]() ![]() | ![]() ![]() ![]() | |||
type=char(7),enclose_del=no | ABC![]() | ABC (3 bytes) | ||
"ABC"![]() | "ABC" (5 bytes) | |||
"ABC![]() | "ABC![]() | |||
""ABC"" | ""ABC"" (7 bytes) | |||
![]() | ![]() | |||
""![]() | "" (2 bytes) | |||
![]() ![]() ![]() ![]() | ![]() ![]() ![]() ![]() | |||
NVARCHAR(3) | type=char(6) | ![]() ![]() ![]() | ![]() ![]() ![]() | |
"![]() ![]() | "![]() ![]() | |||
"![]() ![]() | ![]() ![]() | |||
""![]() | "![]() | |||
![]() ![]() ![]() | ![]() ![]() ![]() | |||
""![]() ![]() | ""![]() ![]() | |||
DATE | type=char(12) | 2003-12-31![]() | 2003-12-31 | |
![]() | ||||
![]() ![]() | ||||
INTERVAL YEAR TO DAY | type=char(11) | 00010101.![]() | +00010101. | |
![]() | ||||
![]() ![]() | ||||
TIME | type=char(10) | 08:45:00![]() | 08:45:00 | |
![]() | ||||
![]() ![]() | ||||
INTERVAL HOUR TO SECOND | type=char(9) | 010101.![]() | +010101. | |
![]() | ||||
![]() ![]() | ||||
TIMESTAMP | type=char(21) | 1991-01-01_11:22:33![]() | 1991-01-01_11:22:33 | |
![]() | ||||
![]() ![]() | ||||
BLOB | type=char(13) | /tmp/file01![]() | Data in /tmp/file01 | |
![]() | Error | |||
![]() ![]() | ||||
BINARY(7) | type=char(7) | ![]() | ![]() |
An input data file in the fixed-size data format is created using the same data format as for the table's column data type or using character strings that can be converted to the column data type. For the input data that is stored in columns of DECIMAL type, you can use the ADEC (ASCII CODE DECIMAL) and BDEC (BINARY DECIMAL) types.
The ADEC type is used to specify numeric data in character string format. The data does not contain decimal points. Instead, the location of the decimal point is specified in the column structure information file. The following shows the format of the ADEC type:
Table 5-33 Example of storing data specified as the ADEC type in DECIMAL(10,4)
Data specified in the input data file | Data stored in DECIMAL(10,4) | Description |
---|---|---|
1![]() | +000001.0000 | If the data contains even 1 byte of space, the precision and decimal places specified in the type operand are ignored and the data is treated as a value with no decimal point. |
![]() | +000001.0000 | |
![]() ![]() | +000001.0000 | |
![]() | +010000.0000 | |
000001![]() | +000001.0000 | |
1000000000 | +100000.0000 | If the data consists of only 0 to 9, the location of decimal point is determined by the precision and decimal places specified in the type operand. |
0000000001 | +000000.0001 | |
0000010000 | +000001.0000 | |
+000010000 | +000001.0000 | If the data consists of 0 to 9 and the sign part, the location of decimal point is determined from the end of the data. |
-000010000 | -000001.0000 |
The BDEC type is used to specify binary data represented in a binary number as a decimal number. The location of the decimal point is determined based on the table's column data type and the number of digits in the decimal number. The first bit of data of the BDEC type is treated as the sign. The following shows the format of the BDEC type:
Table 5-34 Example of storing data specified as the BDEC type in a DECIMAL column
Data specified in the input data file | Column definition of the storage | Data to be stored |
---|---|---|
0xff 0xff 0xfc 0xf7 | DECIMAL(10,0) | -0000000777 |
DECIMAL(10,2) | -00000007.77 | |
DECIMAL(10,10) | -0.0000000777 |
The figure below shows an example of coding data in the fixed-size data format.
Figure 5-17 Example of data in fixed-size data format