If you specify the -W option at the time of unloading, you can use the unload data file as the input data file to the database load utility.
In the DAT and extended DAT formats, data is output as character string data. For details about the differences between the DAT and extended DAT formats, see (c) Differences between DAT and extended DAT formats. Except for the cited differences, the DAT and extended DAT formats are identical.
To output data, use the following rules:
For repetition columns, the system outputs data from each element by separating it with separator characters. The row data format depends on what is specified in the array statement in the control information file. Figure 8-13 Row data output format with ff specified in the array statement shows the row data output format when ff is specified in the array statement: Figure 8-14 Row data output format with vv specified in the array statement shows the row data output format when vv is specified in the array statement.
Figure 8-13 Row data output format with ff specified in the array statement
Figure 8-14 Row data output format with vv specified in the array statement
If you specify the sup option during unload operation, the system compresses any trailing spaces in any column that is shorter than the column length specified in the table definitions and then outputs them to the unload data file. The following shows the format depending on the specification of the sup option:
CREATE TABLE T1(C1 INTEGER,C2 CHAR(10),...);
1,"AA",...
1,"AA........",...
CREATE TABLE T1(C1 INTEGER,C2 MCHAR(10),...1,);
1,".",...
1,"..........",...
CREATE TABLE T1(C1 INTEGER,C2 NCHAR(10),...);
1,"AA",...
1,"AA",...
CREATE TABLE T1(C1 INTEGER,C2 NCHAR(10),...1,);
1,"",...
1,"",...
CREATE TABLE T1(C1 INTEGER,C2 NCHAR(10),...);
1,"AA",...
1,"AA................",...
CREATE TABLE T1(C1 INTEGER,C2 NCHAR(10),...1,);
1,"..",...
1,"....................",...
The following table describes the differences between the DAT and extended DAT formats:
Item | DAT format | Extended DAT format |
---|---|---|
When the data contains the null character (\0) or linefeed character (\n) | The row containing the corresponding column data value is not output. | The row containing the corresponding column data value is output (\0 and \n are output as is). |
When the enclosing character (") is used as part of the data | The enclosing character used as part of the corresponding column data value is output as is. | Two consecutive enclosing characters are output for an enclosing character that is used as part of the column data value.# Example: " ![]() |
If you are creating a pdload unload file in extended DAT format with UTF-16 as the character encoding and the following data is stored in the table, you must take an action when you unload the data:
Data type of column | Data contents |
---|---|
BINARY type | Data consisting of an odd number of bytes |
Abstract data type that is unloaded by using a constructor parameter reverse creation function that returns BINARY type data | Data whose value returned by the constructor parameter reverse creation function consists of an odd number of bytes |
You can determine whether a table stores the above data by executing the SQL statement shown below. Execute the SQL statement for all BINARY type and abstract data type columns that are unloaded by using a constructor parameter reverse creation function that returns BINARY type data.
SELECT COUNT(*) FROM [authorization-identifier.]table-identifier
WHERE constraint-expression [OR constraint-expression ...]
constraint-expression ::= { MOD(LENGTH(BINARY-type-column-name),2)=1
| MOD(LENGTH(function-name(column-name[,column-name ...])),2)=1 }
function-name ::= name-of-constructor-parameter-reverse-creation-function-that-returns-BINARY-type-data
In the binary format, data is output in an internal format based on the database column definition. If this type of file is used as an input to the database load utility, the unloaded table and the data loading target table must have the same column definitions. For a non-FIX table, data cannot be loaded into a FIX table, even if the column definitions are the same.
The output format is as follows:
If data is output to CMT, a 512-byte CMT header is output first.
The output format is different for FIX tables than it is for non-FIX tables. For details about the output format (binary format) of column data for each data type, see 5.5.2 Binary format.
Figure 8-15 Output format for FIX tables
Figure 8-16 Output format for non-FIX tables
Figure 8-17 Output format for the control section of a non-FIX table
Table 8-3 Value output to the base data section
Column attribute | Column's data type | Data type returned by constructor parameter reverse creation function | Value output to the base data section |
---|---|---|---|
Repetition column | -- | -- | # |
Other column | Abstract data type | BINARY type | Length returned by the constructor parameter reverse creation function |
BLOB type | Length returned by the constructor parameter reverse creation function | ||
Other | Data value returned by the constructor parameter reverse creation function | ||
BINARY type | -- | Length of data value for the column | |
BLOB type | -- | Length of data value for the column | |
Other | -- | Data value for the column |
Figure 8-18 Output format for the base data section for repetition columns
Table definition: CREATE TABLE T1(c1 ADT1,C2 INTEGER,C3 ADT2) |
Specification of the unld_func statement: unld_func type=ADT1,func=adt1_func1(ADT1),adt1_func2(ADT1),adt1_func3(ADT1) |
Figure 8-19 Output format for the BINARY attribute data section (example)
In the fixed-size data format, data is output as fixed-length character string data.
Table 8-4 Output format of each type of column data
Data type of column | Output format | Output length (bytes)#1 | |
---|---|---|---|
Numeric data | INTEGER |
| 11 |
SMALLINT |
| 6 | |
DECIMAL |
![]() | Number of digits + 2 | |
FLOAT |
![]() | 23 | |
SMALLFLT |
| 23 | |
Character string data | CHARACTER |
![]() | Depends on the conditions#2 |
VARCHAR |
![]() | Depends on the conditions#2 | |
Mixed character string data | MCHAR | Same as for CHARACTER. | Defined length#3 |
MVARCHAR | Same as for VARCHAR. | Defined length#3 | |
National character data | NCHAR | Same as for CHARACTER. | Defined length![]() |
NVARCHAR | Same as for VARCHAR. | Defined length![]() | |
Date data | DATE | Data is output in the format yyyy-mm-dd. (yyyy: year, mm: month, dd: date).
| 10 |
Time data | TIME | Data is output in the format hh:mm:ss (hh: hour, mm: minute, ss: second).
| 8 |
Date interval data | INTERVAL YEAR TO DAY |
| 10 |
Time interval data | INTERVAL HOUR TO SECOND |
| 8 |
Time stamp data | TIMESTAMP | Data is output in the format yyyy-mm-dd![]() ![]()
| 19 Fractions of a second: 0: +0 2: +3 4: +5 6: +7 |
Large object data | BLOB | No data is output. | 0 |
Binary data | BINARY | No data is output. | 0 |
Abstract data type | -- | No data is output. | 0 |
No. | Character encoding of the unload data file | Column's character set | enclose operand | Output length (bytes) |
---|---|---|---|---|
1 | HiRDB's default character encoding | UTF-16 | Omitted | definition length![]() |
2 | Specified | definition length![]() | ||
3 | Other (including when the specification is omitted) | Omitted | definition length | |
4 | Specified | definition length + 2 | ||
5 | Other | UTF-16 | Omitted | definition length |
6 | Specified | definition length + 2 ![]() | ||
7 | Other (including when the specification is omitted) | Omitted | definition length![]() | |
8 | Specified | definition length![]() ![]() |