5.5.1 DAT format

In the DAT format, both input data and the input parameters for a constructor function are specified as character string data.

Organization of this subsection
(1) Specification format
(2) Rules
(3) Example

(1) Specification format

Each column data item must be separated by a separator character.

Table 5-12 shows the specification format of column data and parameters for a construction function by the data type (DAT format).

Table 5-12 Specification format of column data and parameters by data type (DAT format)

Data typeSpecification format of column data and parameter
General dataNull value or default value1
Numeric dataINTEGER
  • Specify a numeric value in characters.
  • For a negative value, add a minus sign (-).
  • Specify a decimal point as integer fraction.
  • Specify a floating point as mantissa-e-exponent.
Integer (Example: -1234...0...1234)
Decimal point (Example: -1.56...0...1.56)
Floating point (Example: -2.4e+9...0e0...2.4e+9)
* or not specified
SMALLINT
DECIMAL
FLOAT
SMALLFLT
Character string dataCHARACTERSpecify as characters or a character string enclosed in double quotation marks (").
(Example: abcdef, ABCDEF)
(Example: "abcd", "ABCD")
Not specified
VARCHAR
National character string dataNCHAR
NVARCHAR
Mixed character string dataMCHAR
MVARCHAR
Date dataDATESpecify in the numeric format yyyy-mm-dd.
yyyy: Year. mm: Month. dd: Day.
(Example: January 1, 1995 [Figure] 1995-01-01)
* or not specified
Date interval dataINTERVAL YEAR TO DAYSpecify in the numeric format [-]yyyymmdd. For a negative value, add a minus sign (-).
(Example: 1111 years, one month, and one day [Figure] 11110101​)
Time dataTIMESpecified in the numeric format hh:mm:ss.
hh: Hour. mm: Minute. ss: Second.
(Example: 12:01:01 [Figure] 12:01:01)
Time interval dataINTERVAL HOUR TO SECONDSpecified in the numeric format [-]hhmmss. For a negative value, add a minus sign (-).
(Example: 1 hour, 1 minute, and 1 second [Figure] 010101)
Time stamp dataTIMESTAMPSpecified in the numeric format YYYY-MM-DD hh:mm:ss[.nnnnnn].
For the fraction part of the second, specify a value with a length of 0 to 6 digits. If the fraction part is less than the defined length, the system assumes 0; if it is longer than the defined length, the system discards the excess portion. If the fraction part consists of more than 6 digits, an error (KFPL31002-E) results.2
Binary dataBINARYSpecifies the binary data or binary data enclosed in the enclosing characters.
(Examples: abc 123 or "abc 123")
Not specified
Large object dataBLOB-k f specified:
Specify the name of the LOB input file.
(Example: lobfile01)
-k c specified:
Specify a character string with a length of 1-1,023 bytes without any separator characters. This character string is ignored when LOB data is loaded.
(Example: @)
Notes
The system handles the null values for each data type as follows:
  • For the fixed-length character string, national character string, mixed character string, binary, and large object data types, the system treats the following specification as the null value or default value:
    ..., , ...
    ..., " ", ...
  • For the variable-length character string, national character string, mixed character string, and binary data types with the -z option specified, the system treats the following specification as the null value or default value:
    ..., , ...
    ..., " ", ... is treated as data with a length of 0.
  • For the other data types, the system treats the following specification as the null value or default value:
    ..., , ...
    ..., " ", ...
    ..., *, ...

If the enclose operand is specified in the extdat statement, the double quotation mark (") becomes the enclosing character specified in the enclose operand.
1 The value to be stored depends on the null_string operand value specified in the option statement.
2 The following table describes the storage method when the length of the fraction part for seconds is specified for the TIMESTAMP type:
Defined digitsLength of the fraction part of the second in input data
01234567 or more
0Stored as is.1Discards excess digits.Error (KFPL31002-E)
2Adds zeros up to the defined length.2Stored as is.1Discards excess digits.
4Adds zeros up to the defined length.2Stored as is.1Discards excess digits.
6Adds zeros up to the defined length.2Stored as is.1
1 The input data is converted to the TIMESTAMP type and then stored as is.
2 The real data is in bytes. If the defined length consists of an odd number of bytes, as many zeros as equals the defined length + 1 are added.

(2) Rules

Legend:
,: Separator character
": Enclosing character
\0: Null character (0x00)
\n: Linefeed code
* The value to be stored depends on the null_string operand value specified in the option statement.

* If cutdtmsg=on is specified in the option statement and data truncation occurs, the system outputs warning messages to the error information file.

Legend: [Figure]: Single-byte space
* The value to be stored depends on the null_string operand value in the option statement.
Legend: [Figure]: Single-byte space

(3) Example

Figure 5-12 shows a specification example of DAT-format data.

Figure 5-12 Specification example of DAT-format data

[Figure]

Note
The data types are, from left to right, NCHAR, INTEGER, DATE, and VARCHAR.