5.5.2 Binary format
(1) Specification format
(a) Specification format of column data
Table 5-18 shows the specification format of column data and parameters by the data type (binary format).
Table 5-18 Specification format of column data and parameters (binary format)
Data and parameter types | Column data specification format |
---|
Numeric data | INTEGER, SMALLINT | ![[Figure]](figure/zx050100.gif) |
DECIMAL(m, n) 1 m 29 0 n 29 m n | ![[Figure]](figure/zu050110.gif) |
FLOAT, SMALLFLT | ![[Figure]](figure/zx050120.gif) |
Character string, national character string, and mixed character string data | CHARACTER(n), VARCHAR(n), MCHAR(n), MVARCHAR(n) | ![[Figure]](figure/zu050130.gif) |
NCHAR(n), NVARCHAR(n) | ![[Figure]](figure/zu050140.gif) |
Date data | DATE | ![[Figure]](figure/zu050150.gif) |
Date interval data | INTERVAL YEAR TO DAY | ![[Figure]](figure/zu050160.gif) |
Time data | TIME | ![[Figure]](figure/zu050170.gif) |
Time interval data | INTERVAL HOUR TO SECOND | ![[Figure]](figure/zu050180.gif) |
Time stamp data | TIMESTAMP(p) | ![[Figure]](figure/zu050181.gif) |
BINARY data, BINARY attribute data | BINARY | ![[Figure]](figure/zu050182.gif) |
LOB data, LOB attribute data | BLOB | ![[Figure]](figure/zu050190.gif) |
* Only when the -z option is specified, does the system set the actual data length to 0 and treats the part without the actual data as 0-length data.
(b) Input parameter specification format
The following describes the input parameter specification format:
- Code the input parameters for a constructor function for generating values that are stored in an abstract data type using the same structure as the function's parameter type.
- Specify the input parameters in the same order as the arguments for the constructor function.
- If the target storage column is an abstract data type column, the constructor function must have a name identical to the abstract data type. If there are multiple constructor functions with the same name or if you are using a constructor function with a name that is different from the name of abstract data type, specify the name of the function in the null value/function information file.
- If there are multiple input parameters, specify parameter values one after another, without placing any space between them.
(2) Rules
- If you are using a magnetic tape unit to create input data in the variable-length block mode, set the block length to 32 KB or less.
- In input data, columns must have the same sequence as the columns in the table definition.
- There must be no space between column data items. For a variable-length character string, if the actual data is shorter than the defined length, specify the data with its actual length and move up the subsequent data item.
- If you are using EasyMT to create a table containing LOB columns, make sure that only LOB input files in EasyMT are specified as the input data files for the LOB column structure base table.
- If the input data contains any of the following data, you should not use data loading with the synchronization point specification. If the input data contains the following data, the system stores all up to the synchronization point in the event of an error:
- Repetition column
- Variable-length data (VARCHAR, NVARCHAR, MVARCHAR, BINARY, BLOB)
- Variable-length data for an argument of abstract data type
- In the binary format, the default value cannot be stored because input data is written directly. To store the default value, directly write the default value when you create the input data file.
(3) Example
Figure 5-13 shows a specification example of binary-format data.
Figure 5-13 Specification example of binary-format data
![[Figure]](figure/zu050200.gif)
- Notes
- The upper row of the input data indicates the data, and the lower row indicates the values to be stored.
- The data types are, from left to right, NCHAR(5), INTEGER, SMALLFLT, and VARCHAR.
- National character data is in shift JIS or EUC Chinese Kanji codes.
(4) Coding LOB data and LOB parameters when v or d is specified in the -k option
(a) -k v
When specifying v in the -k option, directly code the input file name for LOB data and the data for the LOB parameter in the data input file.
When compared with specifying f in the -k option, this method can reduce input/output time for the LOB input file.
- Rules
- Specify the LOB data and the LOB parameter in a format in which a given column in row data has a 4-byte length section, such that the data section is coded following the row data. Specify these items in the column order of the row data.
- If the LOB data or the LOB parameter is the null value, specify a value of -1 in the length section. The system ignores any null comparison values produced by a null value/function information file or a function information file. Do not specify the data section.
- If the LOB data or the LOB parameter has 0 bytes, specify a value of 0 in the length section without specifying a data section.
- Example
- Table with a column of abstract data type with BLOB input:
Table definition: CREATE TABLE T1 (C1 INTEGER, C2 SGMLTEXT, C3 DEC(7,0))
![[Figure]](figure/zx050210.gif)
- Table with two columns of abstract data type with BLOB input:
Table definition: CREATE TABLE T2 (C1 SGMLTEXT, C2 INTEGER, C3 SGMLTEXT)
![[Figure]](figure/zx050220.gif)
- Table having a column of abstract data type with input of a LOB column and BLOB:
Table definition: CREATE TABLE T1 (C1 INTEGER, C2 BLOB (100), C3 SGMLTEXT)
![[Figure]](figure/zx050230.gif)
(b) -k d
When specifying d in the -k option, specify the data itself, not the name of the LOB data storage file, in the input data file.
Rules
- Specify the LOB data and the LOB parameter data in a format in which a given column in row data has a 4-byte length section, such that the data section is coded following the row data.
- Specify the input data file in the following order:
[row data] [LOB parameter data] [LOB data]
1 2 3
- Explanation:
- 1. Specify the length section in the row data in the order of column definition.
- 2. Specify the LOB parameter data immediately after the row data in the order of column definition.
- 3. Specify the LOB data immediately following item B in the order of column definition.
- If the LOB data or the LOB parameter data is the null value, specify a value of -1 in the 4-byte length section. The system ignores any null comparison values produced by a null value/function information file or a function information file. Do not specify the data section. There is no data section.
- If the LOB data or the LOB parameter has 0 bytes, specify a value of 0 in the length section without specifying a data section.
- Example
- Table with one LOB column
Table definition: CREATE TABLE T1 (C1 BLOB(100),C2 INTEGER);
![[Figure]](figure/zx050231.gif)
Specification method:
- Specify the LOB data's length section (4 bytes) before the row data.
- Specify the LOB data following item 1.
- Table with two LOB columns
Table definition: CREATE TABLE T2 (C1 BLOB(100),C2 INTEGER,C3 BLOB(100));
![[Figure]](figure/zx050232.gif)
Specification method:
- Specify the LOB data's length section (4 bytes) in the row data in the order of column definition (which is LOB data length (C1) and LOB data length (C3)).
- Specify the LOB data following item 1 in the order of column definition (which is LOB data (C1) and LOB data (C3)).
- Table with two LOB columns and an abstract data type column with LOB input
Table definition: CREATE TABLE T3(C1 BLOB(100),C2 INTEGER,C3 SGMLTEXT,C4 BLOB(100));
![[Figure]](figure/zx050233.gif)
Specification method:
- Specify the LOB data and LOB parameter length section (4 bytes) in the row data in the order of column definition (which is LOB data length (C1), LOB parameter length section (C3), and LOB data length (C4)).
- Specify the LOB parameter data immediately after the row data in the order of column definition (which is LOB parameter data (C4) only).
- Specify the LOB data following item 2 in the order of column definition (which is LOB data (C1) and LOB data (C4)).