The null and default values that are stored during data loading depend on the specification of these values during table definition and the control statement specification for execution of pdload. Table 5-30 describes the null and default values that are stored during data loading.

Table 5-30 Null and default values that are stored during data loading

Format of input data file	Specification value during table definition			Value of null_string in option statement during execution of pdload	Value stored during data loading
	NOT NULL constraint	WITH DEFAULT	DEFAULT clause		When input data is null value1	When column name statement is omitted2	non-null value
DAT format	NULL	N/A	Specified	default	Default value in DEFAULT clause	Default value in DEFAULT clause	Value of input data
				null	Null value
			Not specified	default		Null value
				null
	NOT NULL	Specified	N/A	default	Default value in WITH DEFAULT clause	Default value in WITH DEFAULT clause
				null
		Not specified	Specified	default	Default value in DEFAULT clause	Default value in DEFAULT clause
				null	Null value (results in an error)
			Not specified	default		D
				null
Extended DAT format	NULL	N/A	Specified	default	Default value in DEFAULT clause	Default value in DEFAULT clause	Value of input data
				null	Null value
			Not specified	default		Null value
				null
	NOT NULL	Specified	N/A	default	Default value in WITH DEFAULT clause	Default value in WITH DEFAULT clause
				null
		Not specified	Specified3	default null	Default value in DEFAULT clause	Default value in DEFAULT clause
					Null value (results in an error)
			Not specified3	default		D
				null
			Specified4	default	Default value in DEFAULT clause	Default value in DEFAULT clause
				null	Default value in WITH DEFAULT clause
			Not specified4	default		D
				null
Binary format	NULL	N/A	Specified	N/A	Null value	D	Value of input data
			Not specified
	NOT NULL	Specified	N/A		D
		Not specified	Specified
			Not specified
Fixed-size data format	NULL	N/A	Specified	N/A	Null value	Default value in DEFAULT clause	Value of input data
			Not specified			Null value
	NOT NULL	Specified	N/A		D	Default value in WITH DEFAULT clause
		Not specified	Specified			Default value in DEFAULT clause
			Not specified			D
pdrorg-created binary format	NULL	N/A	Specified	N/A	Null value	Default value in DEFAULT clause	Value of input data
			Not specified			Null value
	NOT NULL	Specified	N/A		Null value (results in an error)	Default value in WITH DEFAULT clause
		Not specified	Specified			Default value in DEFAULT clause
			Not specified			D

Legend:

Default value in DEFAULT clause: For details, see Table 5-31.

Default value in WITH DEFAULT clause: For details, see Table 5-32.

N/A: Not applicable

D: The column name statement cannot be specified or omitted depending on specifications when the corresponding table was defined or on the combination of values specified for execution of pdload.

Note

If a primary key or cluster key with UNIQUE specified has been defined for the column for which the WITH DEFAULT or DEFAULT clause is specified, storing the default value results in a key duplication error. In such a case, make sure that you specify the input data.

¹ The following input data is treated as the null value:

In DAT format:

• "*"
• Data with no specification

In extended DATA format:

• "*"
• Data with no specification
• Input data for a column of numeric, date, date interval, time, time interval, or time stamp data type that consists of all single-byte spaces or tabs

In binary format:

Data that matches the comparison value specified in the null operand of the column number statement in the null value/function information file

In fixed-size data format:

Data that matches the comparison value specified in the null operand of the column name statement in the column structure information file

In pdrorg-created binary format:

Data whose column data offset is 0

² This applies when the column name statement for the corresponding column is omitted in the column structure information file.

³ This applies to the following columns:

• Index component column with UNIQUE specified
• Cluster key column with UNIQUE specified
• Cluster key column with PRIMARY specified
• Primary key column

⁴ Columns other than those in ³.

Table 5-31 Default value in the DEFAULT clause that is stored during data loading

Data type		Specification of DEFAULT clause for the column to be stored
		Literal	USER	C_T or CT	C_D or CD	C_TS or CTS	NULL	Omitted
Numeric data	INTEGER SMALLINT DECIMAL FLOAT SMALLFLT	Default value	N/A	N/A	N/A	N/A	Null value	Same as the default value in the WITH DEFAULT clause
Charac- ter string data	CHARAC TER		Authorization identifier of the user who inserted rows	pdload execution time1	pdload execution date2	pdload execution date and time3
	VARCHAR			N/A	N/A	N/A
Mixed charac- ter string data	MCHAR
	MVARCHAR
Nation- al charac- ter string	NCHAR		N/A
	NVARCHAR
Date data	DATE				pdload execution date
Date interval data	INTERVAL YEAR TO DAY				N/A
Time data	TIME			pdload execution time
Time interval data	INTERVAL HOUR TO SECOND			N/A
Time stamp data	TIMESTAMP					pdload execution date and time4
Binary data	BINARY					N/A
Large object data	BLOB	N/A

Legend:

C_T or CT: CURENT_TIME or CURRENT TIME

N/A: CURRENT_DATE or CURRENT DATE

N/A: CURRENT_TIMESTAMP or CURRENT TIMESTAMP

N/A: Cannot be set.

¹ Stored in the format hh:mm:ss.

² Stored in the format yyyy-mm-dd.

³ Stored in the format yyyy-mm-dd hh:mm:ss[.000000].

⁴ Zeros are stored for the fraction part of seconds.

Table 5-32 Default value of WITH DEFAULT that is stored during data loading

Data type		Value to be stored
Numeric data	INTEGER SMALLINT DECIMAL FLOAT SMALLFLT	0
Character string data	CHARACTER	Space
	VARCHAR	Space equivalent to 1 byte
National character string	NCHAR	Space
	NVARCHAR	Space equivalent to 1 character
Mixed character string data	MCHAR	Space
	MVARCHAR	Space equivalent to 1 byte
Date data	DATE	Current date
Date interval data	INTERVAL YEAR TO DAY	0 year, 0 month, and 0 day
Time data	TIME	Current time
Time interval data	INTERVAL HOUR TO SECOND	00:00:00
Time stamp data	TIMESTAMP	Current date and time
Binary data	BINARY	Data with a length of 0
Large object data	BLOB	Data with a length of 0

Note: The current date, current time, and current date and time are based on the date and time the database load utility is executed.