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 type		Specification format of column data and parameter
		General data	Null value or default value1
Numeric data	INTEGER	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 data	CHARACTER	Specify as characters or a character string enclosed in double quotation marks ("). (Example: abcdef, ABCDEF) (Example: "abcd", "ABCD")	Not specified
	VARCHAR
National character string data	NCHAR
	NVARCHAR
Mixed character string data	MCHAR
	MVARCHAR
Date data	DATE	Specify in the numeric format yyyy-mm-dd. yyyy: Year. mm: Month. dd: Day. (Example: January 1, 1995 1995-01-01)	* or not specified
Date interval data	INTERVAL YEAR TO DAY	Specify in the numeric format [-]yyyymmdd. For a negative value, add a minus sign (-). (Example: 1111 years, one month, and one day 11110101)
Time data	TIME	Specified in the numeric format hh:mm:ss. hh: Hour. mm: Minute. ss: Second. (Example: 12:01:01 12:01:01)
Time interval data	INTERVAL HOUR TO SECOND	Specified in the numeric format [-]hhmmss. For a negative value, add a minus sign (-). (Example: 1 hour, 1 minute, and 1 second 010101)
Time stamp data	TIMESTAMP	Specified 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 data	BINARY	Specifies the binary data or binary data enclosed in the enclosing characters. (Examples: abc 123 or "abc 123")	Not specified
Large object data	BLOB	-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.

¹ The value to be stored depends on the null_string operand value specified in the option statement.

² The following table describes the storage method when the length of the fraction part for seconds is specified for the TIMESTAMP type:

Defined digits	Length of the fraction part of the second in input data
	0	1	2	3	4	5	6	7 or more
0	Stored as is.1	Discards excess digits.						Error (KFPL31002-E)
2	Adds zeros up to the defined length.2		Stored as is.1	Discards excess digits.
4	Adds zeros up to the defined length.2				Stored as is.1	Discards excess digits.
6	Adds zeros up to the defined length.2						Stored as is.1

¹ The input data is converted to the TIMESTAMP type and then stored as is.

² 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.

• One line in the file corresponds to one line of table data (each line must end with the linefeed code).
• When coding input parameters that are passed to a constructor function, delimit each parameter with a separator character.
• Neither the null character (0x00), linefeed code (0x0a), nor carriage return at the end of a line (0x0d0a) is treated as data. To treat them as data, specify the extdat statement and enclose the input data in the enclosing character.
• The maximum length of one line is 32 KB. However, if you specify the source statement with the maxreclen operand, the system accepts more than 32 KB of data per line during data loading.
• If an input value begins with the enclosing character, consecutive enclosing and separator characters are treated as the end of the column. To specify an input value consisting of the consecutive enclosing and separator characters, use the -s option to change the separator character.
• If character string data immediately before or after a separator character is enclosed in the enclosing character ("), the enclosing characters are not subject to the input operation. If the separator character is encountered in a character string that is enclosed in the enclosing character, the separator character is treated as data. Table 5-13 shows how character string data and enclosing characters are handled.
  

  Table 5-13 Handling of character string data and enclosing characters

  Character string data in input data file	Data stored in table
  	DAT format	Extended DAT format
  ...,ABCD,...	ABCD	ABCD
  ...,AB"CD,...	AB"CD	Input data error
  ...,AB""CD,...	AB""CD	AB"CD
  ...,"ABCD",...	ABCD	ABCD
  ...,"AB"CD",...	AB"CD	Input data error
  ...,"AB""CD",...	AB""CD	AB"CD
  ...,""ABCD"",...	"ABCD"	Input data error
  ...,"""ABCD""",...	""ABCD""	"ABCD"
  ...,,...	Null value or default value*	Null value or default value*
  ...,"",...	Null value or default value*	Null value or default value*
  ...,"""",...	""	"
  ...,"AB,CD",...	AB,CD	AB,CD
  ...,AB\0CD,...	Input data error	Input data error
  ...,AB\nCD,...	Input data error	Input data error
  ...,"AB\0CD",...	Input data error	AB\0CD
  ...,"AB\nCD",...	Input data error	AB\nCD

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.

• Table 5-14 shows the format of character string data that is converted to numeric data. For the limitations to the specification of character string data for a numeric data type in input data, see the limitations on the use of numeric literals in the manual HiRDB Version 8 SQL Reference.
  

  Table 5-14 Format of character string data converted to numeric data

  Numeric value expressed as character string data	Notation
  	Specification format	Example (specification stored value)
  Integer	[{+ | -}] natural-number A natural number is a string of numeric characters 0-9.	+10 10 -009 -9 0 0
  Decimal number	[integer].[fraction] A fraction is an unsigned integer. Either the integer or the fraction must be specified.	.05 0.05 -0.8 -0.8 1.0 1.0 +1. 1.0
  Floating point number	mantissa-e[exponent] Mantissa is expressed as an integer or decimal number. E may be specified for e. Exponent is expressed as a one-digit or two-digit integer.	.3e 0.310 2.4e-02 2.4 10-2 67e-5 67 10-5

  : Indicates an optional space.
  
• Table 5-15 shows the handling of storage data when the length of input data does not match the data length in table.
  

  Table 5-15 Handling of storage data when the length of input data doesn't match the data length in table

  Data length in table (m)	Data length of input data (n)
  	Fixed-length character string data	Variable-length character string data or binary data
  m = n	Input data is stored as is.
  m < n	When the lengover operand is omitted in the option statement, the portion of the input data following (m + 1) is discarded.* When the lengover operand is specified in the option statement, the input data is not stored because it is regarded as an input data error.
  m > n	Input data is padded with spaces after (n + 1).	Input data is stored as is.

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

• If a data section in the input data contains separator characters (or code that is the same as the separator characters), the system treats the data as separator characters unless the entire data item is enclosed in double quotation marks (").
• Table 5-16 describes the data to be stored and how to specify the null value when enclose=" is specified in the extdat statement.
  

  Table 5-16 Data to be stored and how to specify the null value when enclose=" is specified in the extdat statement

  Data type		Input data	Data to be stored in table	How to specify null value or default value*
  Numeric data	INTEGER	"1234"	1234	*, "", "*", or not specified
  	SMALLINT	"1234"	1234
  	DECIMAL(6,1)	"000001"	00001.0
  	FLOAT	"2.225075e-308"	+2.225075000000000E-308
  	SMALLFLT	"1.175495e-38"	+1.175495051471520E-38
  Character string data	CHAR	"ABCDE"	ABCDE	"" or not specified
  	VARCHAR	"ABCDE"	ABCDE
  National character string data	NCHAR	""
  	NVARCHAR	""
  Mixed character string data	MCHAR	"ABCDE"	ABCDE
  	MVARCHAR	"ABCDE"	ABCDE
  Date data	DATE	"2000-05-31"	2000-05-31	*, "", "*", or not specified
  Date interval data	INTERVAL YEAR TO DAY	"+00000000."	00000000
  Time data	TIME	"09:00:00"	09:00:00
  Time interval data	INTERVAL HOUR TO SECOND	"+000000."	000000
  Time stamp data	TIMESTAMP(6)	"2002-08-0820:25:43.39"	2002-08-08 20:25:43.390000
  Binary data	BINARY	"ABCDE"	ABCDE	"" or not specified
  Large object data	BLOB	"LOB-input-file-name"	Contents of the LOB input file

Legend: : Single-byte space

^* The value to be stored depends on the null_string operand value in the option statement.

• Table 5-17 shows examples of space deletion when enclose=" is specified in the extdat statement.
  

  Table 5-17 Examples of space deletion when enclose=" is specified in the extdat statement

  Data type		Input data	Data to be stored in table
  Numeric data	INTEGER	"1234"	1234
  	SMALLINT	""	Null value
  	DECIMAL(6,1)	"000001"	00001.0
  	FLOAT	"2.225075e-308"	+2.225075000000000E-308
  	SMALLFLT	"1.175495e-38"	+1.175495051471520E-38
  Date data	DATE	"2000-05-31"	2000-05-31
  Date interval data	INTERVAL YEAR TO DAY	"+00000000."	00000000
  Time data	TIME	"09:00:00"	09:00:00
  Time interval data	INTERVAL HOUR TO SECOND	"+000000."	000000
  Time stamp data	TIMESTAMP	"2002-08-08 20:25:43.39"	2002-08-08 20:25:43.39

Legend: : Single-byte space

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

Figure 5-11 Specification example of DAT-format data

Note

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