5.10 Null and default values during data loading

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. The table below describes the null and default values that are stored during data loading.

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

Format of input data fileSpecification value during table definitionValue of null_string in option statement during execution of pdloadValue stored during data loading
NOT NULL constraintWITH DEFAULTDEFAULT clauseWhen input data is null value#1When column-name statement is omitted#2non-null value
DAT formatNULLN/ASpecifieddefaultDefault value in DEFAULT clauseDefault value in DEFAULT clauseValue of input data
nullNull value
Not specifieddefaultNull value
null
NOT NULLSpecifiedN/AdefaultDefault value in WITH DEFAULT clauseDefault value in WITH DEFAULT clause
null
Not specifiedSpecifieddefaultDefault value in DEFAULT clauseDefault value in DEFAULT clause
nullNull value (results in an error)
Not specifieddefaultD
null
Extended DAT formatNULLN/ASpecifieddefaultDefault value in DEFAULT clauseDefault value in DEFAULT clauseValue of input data
nullNull value
Not specifieddefaultNull value
null
NOT NULLSpecifiedN/AdefaultDefault value in WITH DEFAULT clauseDefault value in WITH DEFAULT clause
null
Not specifiedSpecified#3default
null
Default value in DEFAULT clauseDefault value in DEFAULT clause
Null value (results in an error)
Not specified#3defaultD
null
Specified#4defaultDefault value in DEFAULT clauseDefault value in DEFAULT clause
nullDefault value in WITH DEFAULT clause
Not specified#4defaultD
null
Binary formatNULLN/ASpecifiedN/ANull valueDValue of input data
Not specified
NOT NULLSpecifiedN/AD
Not specifiedSpecified
Not specified
Fixed-size data formatNULLN/ASpecifiedN/ANull valueDefault value in DEFAULT clauseValue of input data
Not specifiedNull value
NOT NULLSpecifiedN/ADDefault value in WITH DEFAULT clause
Not specifiedSpecifiedDefault value in DEFAULT clause
Not specifiedD
pdrorg-output binary formatNULLN/ASpecifiedN/ANull valueDefault value in DEFAULT clauseValue of input data
Not specifiedNull value
NOT NULLSpecifiedN/ANull value (results in an error)Default value in WITH DEFAULT clause
Not specifiedSpecifiedDefault value in DEFAULT clause
Not specifiedD
Legend:
Default value in DEFAULT clause: For details, see Table 5-49 Default value in the DEFAULT clause that is stored during data loading.
Default value in WITH DEFAULT clause: For details, see Table 5-50 Default value of WITH DEFAULT that is stored during data loading.
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.
#1: 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-output binary format:
Data whose column data offset is 0
#2: This applies when the column-name statement for the corresponding column is omitted in the column structure information file.
#3: 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
  • Partitioning key component column
#4: Columns other than those in #3.

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

Data typeSpecification of DEFAULT clause for the column to be stored
LiteralUSERC_T or CTC_D or CDC_TS or CTSNULLOmitted
Numeric dataINTEGER
SMALLINT
DECIMAL
FLOAT
SMALLFLT
Default valueN/AN/AN/AN/ANull valueSame as the default value in the WITH DEFAULT clause
Charac- ter string dataCHARAC TERAuthorization identifier of the user who inserted rowspdload execution time#1pdload execution date#2pdload execution date and time#3
VARCHARN/AN/AN/A
Mixed charac- ter string dataMCHAR
MVARCHAR
Nation- al charac- ter stringNCHARN/A
NVARCHAR
Date dataDATEpdload execution date
Date interval dataINTERVAL YEAR TO DAYN/A
Time dataTIMEpdload execution time
Time interval dataINTERVAL HOUR TO SECONDN/A
Time stamp dataTIMESTAMPpdload execution date and time#4
Binary dataBINARYN/A
Large object dataBLOBN/A
Legend:
C_T or CT: CURENT_TIME or CURRENT TIME
C_D or CD: CURRENT_DATE or CURRENT DATE
C_TS or CTS: CURRENT_TIMESTAMP or CURRENT TIMESTAMP
N/A: Cannot be set.
#1: Stored in the format hh:mm:ss.
#2: Stored in the format yyyy-mm-dd.
#3: Stored in the format yyyy-mm-dd hh:mm:ss[.000000].
#4: Zeros are stored for the fraction part of seconds.

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

Data typeValue to be stored
Numeric dataINTEGER
SMALLINT
DECIMAL
FLOAT
SMALLFLT
0
Character string dataCHARACTERSpace
VARCHARSpace equivalent to 1 byte
National character stringNCHARSpace
NVARCHARSpace equivalent to 1 character
Mixed character string dataMCHARSpace
MVARCHARSpace equivalent to 1 byte
Date dataDATECurrent date
Date interval dataINTERVAL YEAR TO DAY0 year, 0 month, and 0 day
Time dataTIMECurrent time
Time interval dataINTERVAL HOUR TO SECOND00:00:00
Time stamp dataTIMESTAMPCurrent date and time
Binary dataBINARYData with a length of 0
Large object dataBLOBData 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.