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