column-name[{[,type=data-type |
Some of the column-name statement's operands are not specifiable depending on the format of input data file. The following table shows whether each operand is specifiable for each format.
Type of specified column | Operand | Input data format | ||
---|---|---|---|---|
DAT format | Fixed-size data format | pdrorg-output binary format | ||
Predefined type (column specification) | column-name | R | R | R |
type | N | R | O#3, #7 | |
null | N | O | O | |
element | O | O | N | |
elmtype | O | O | N | |
nullset | O | O | N | |
mode | N | O#4 | N | |
enclose_del | N | O#5 | N | |
filldata | N | O | N | |
sequence | O | O | O | |
Abstract data type (function specification) | column-name | R | R | R |
func | O#1 | O#1 | O#1 | |
param | O#2 | O#2 | O#2 | |
type | N | O#2 | O#3 | |
null | N | O | O | |
mode | N | O#4 | N | |
enclose_del | N | O#5 | N | |
filldata | N | O | N | |
filedir | O#6 | O#6 | N |
Specifies the name of the table column in which input data is to be stored. Note that a reserved column name cannot be specified. If a reserved column name is specified, pdload issues the KFPL25103-E message and terminates abnormally.
If the column-name is enclosed in double quotation marks ("), it is treated as being case sensitive; otherwise, it is treated as all uppercase letters. If the column-name contains a space, enclose the entire column-name in double quotation marks.
Specifies the data type of the column in the input data. The table below describes the specification of data types and argument types and whether the specification is permitted.
Table 5-42 Specification of data types and argument types and whether the specification is permitted
Specification | Whether data type can be specified | Coding rules | |
---|---|---|---|
Fixed-size data format | pdload unload file | ||
integer | Y | N | None |
smallint | Y | N | |
char(n)#1, #2 | Y | Y | n indicates the number of bytes in the input data, and m indicates the number of characters in the input data.#6 The maximum value of n is the CHAR or MCHAR length in bytes ![]() |
nchar(m)#1, #2 | Y | Y | |
mchar(n)#1, #2 | Y | Y | |
varchar(n) | Y | N | n indicates the number of bytes in the input data, and m indicates the number of characters in the input data.#6 The maximum value of n is the maximum length of VARCHAR or MVARCHAR in bytes ![]() |
nvarchar(m) | Y | N | |
mvarchar(n) | Y | N | |
float | Y | N | None |
smallflt | Y | N | |
dec(precision[,decimal-places]) | Y | N | None |
date | Y | N | None |
interval-year-to-day | Y | N | |
time | Y | N | |
interval-hour-to-second | Y | N | |
yy-mm-dd#3 | Y | N | You can use this format only when storing character data indicating the last two digits of the year in a DATE-type column. Any one-byte character can be specified as the separator between the year, month, and date (for example, yy-mm-dd or yy/mm/dd). |
yymmdd#3 | Y | N | You can use this format only when storing unsigned packed-format data indicating the last two digits of the year in a DATE-type column. |
timestamp(p) | Y | N | p indicates the length of the seconds fraction part (0, 2, 4, or 6). |
binary(n) | Y | Y#5 | If you are specifying this data type in the type operand in the case of pdrorg-output binary format, omit (n). n indicates the maximum length of the BINARY type in bytes. |
binaryprm | N | Y | None |
blob(n) | Y | Y#4 |
|
blobprm | N | Y | None |
adec(precision[,decimal-places]) | Y | N | This input data format specifies the data to be stored in a DECIMAL column as a decimal number in character string format. Because the data does not contain a decimal point, the precision and decimal places must be specified in the type operand to determine the decimal point. For details about the adec data format, see 5.5.3(3)(a) ADEC type (ASCII CODE DECIMAL). |
bdec(n) | Y | N | This input data format specifies the data to be stored in a DECIMAL column as binary data. n specifies the length of the binary data. Because the data does not contain a decimal point, the location of decimal point is determined based on the data type of the column. For details about the bdec data format, see 5.5.3(3)(b) BDEC type (BINARY DECIMAL). |
C1,type=char(3) |
C1,type=char(6) |
To determine whether or not the input data is stored in the database when the data type specified in the column structure information file does not match the data type of the table column, see (3) Data storage in the database when the data type specified in the column structure information file does not match the data type in the table.
Specifies that the null value is to be stored in specified columns of the table.
Because the column data is compared with the input data, specify a comparison value that corresponds to the data type specified in the type operand.
The available comparison method depends on the type of input data. The following table shows the comparison method for each type of input data:
Input data type | Comparison method | ||
---|---|---|---|
Numeric comparison | Character comparison | Hexadecimal comparison | |
DECIMAL | Y | N | Y |
DATE | Y | N | Y |
INTERVAL YEAR TO DAY | Y | N | Y |
TIME | Y | N | Y |
INTERVAL HOUR TO SECOND | Y | N | Y |
TIMESTAMP | Y | N | Y |
CHAR | N | Y | Y |
VARCHAR | N | Y | Y |
NCHAR | N | Y | Y |
NVARCHAR | N | Y | Y |
MCHAR | N | Y | Y |
MVARCHAR | N | Y | Y |
BINARY | N | Y | Y |
BLOB | N | Y | Y |
INTEGER | Y | N | Y |
SMALLINT | Y | N | Y |
FLOAT | Y | N | Y |
SMALLFLT | Y | N | Y |
yymmdd format | Y | N | Y |
yy-mm-dd format | N | Y | Y |
ADEC | N | Y | Y |
BDEC | N | N | Y |
Input data of CHAR(5) | Result of comparison with null=(2,'XYZ') | Handling of input data |
---|---|---|
*XYZ* | Perfectly matches the comparison value. | Treated as the null value. |
XYZ** | Matches the comparison value, but the beginning position does not match. | Treated as the input data. |
*XY** | Does not match the comparison value. |
Data type of column | Value treated as the null value | Specification following the part null= |
---|---|---|
DECIMAL | Character string NULL | (1,x'4e554c4c') |
DATE | 0000-00-00 | (1,x'00000000') |
INTERVAL YEAR TO DAY | Byte 1 is 0xff. | (1,x'ff') |
TIME | 99:99:99 | (1,x'999999') |
INTERVAL HOUR TO SECOND | Byte 2 is 0xff. | (2,x'ff') |
TIMESTAMP | Bytes 2 and 3 are 0xff. | (2,x'ffff') |
INTEGER# | Character string NULL | (1,x'4e554c4c') |
SMALLINT# | Character string NL | (1,x'4e4c') |
FLOAT# | Character string **NULL** | (1,x'2a2a4e554c4c2a2a') |
SMALLFLT# | Character string NULL | (1,x'4e554c4c') |
~<1 to number of elements specified for the corresponding column in table definition>
For a repetition column, specify the number of elements to be specified in the input data file.
Specify the element operand if the number of elements specified in the input data file is less than the number of elements specified for the corresponding column during table definition.
If the element operand is omitted, the system assumes the maximum number of elements specified for the corresponding column during table definition.
For a repetition column, specify its array data format.
This specifies how null value storage is to be handled if a null value is specified from an element through the end of specified elements for an FF arrayed data format.
Specifies the input data storage method when the input data is a fixed-length character string (type=char(n)) and the column data type of the storage target is CHAR or MCHAR. This operand is applicable only to the fixed-size data format.
Specifies the input data storage method when the input data is a fixed-length character string (type=char(n)) and the column data type of the storage target is VARCHAR or MVARCHAR. This operand is applicable only to the fixed-size data format.
Specifies the trailing filler data that is to be used when BINARY data is specified as CHAR type in the fixed-size data format. The filler data is 1-byte data in the range 0 to 255 expressed in hexadecimal (X'00" to X'ff'). When this operand is omitted, 0 is assumed.
Specifies that sequence numbers acquired from the sequence generator are to be stored as input data.
Specifies the directory containing a parameter attribute data file when the -K f option is specified to read parameter attribute data in a XML-type column based on one file per data.
column-1 column-2 column-3 |
column-1 |
/user/xml_data/xml001.txt |
If a specified column name has an abstract data type, this operand specifies information about the constructor function that generates the values to be stored.
For data storage in the database when the input parameter specified in the column structure information file has a different data type than the argument of the constructor function to be called, see (3) Data storage in the database when the data type specified in the column structure information file does not match the data type in the table.
Table 5-43 Data type of the parameter that is passed to the constructor function and whether the data type can be specified
Data type of parameter | Data type specification method | Whether data type can be specified | |||
---|---|---|---|---|---|
Classification | Data type | DAT format | Fixed-size data format | pdload unload file | |
Numeric data | INTEGER | integer | Y | Y | Y |
SMALLINT | smallint | Y | Y | Y | |
DECIMAL | dec() | Y | Y | Y | |
adec() | Y | Y | Y | ||
bdec() | Y | Y | Y | ||
FLOAT | float | Y | Y | Y | |
SMALLFLT | smallflt | Y | Y | Y | |
Character string data | CHARACTER | char | Y | Y | Y |
VARCHAR | varchar | Y | Y | Y | |
National character data | NCHAR | nchar | Y | Y | Y |
NVARCHAR | nvarchar | Y | Y | Y | |
Mixed character string data | MCHAR | mchar | Y | Y | Y |
MVARCHAR | mvarchar | Y | Y | Y | |
Date data | DATE | date | Y | Y | Y |
yy-mm-dd | N | N | N | ||
yymmdd | N | N | N | ||
Time data | TIME | time | Y | Y | Y |
Timestamp data | TIMESTAMP | timestamp | Y | Y | Y |
Date interval data | INTERVAL YEAR TO DAY | interval-year-to-day | Y | Y | Y |
Time interval data | INTERVAL HOUR TO SECOND | interval-hour-to-second | Y | Y | Y |
Large object data | BLOB | blob | Y | Y | Y |
blobprm | N | N | N | ||
Binary data | BINARY | binary | Y | Y | Y |
binaryprm | N | N | N |
Table 5-44 Data type specification method and whether the data type can be specified
Classification | Data type | Data type specification method | Whether data type can be specified | |
---|---|---|---|---|
Fixed-size data format | pdload unload file | |||
Numeric data | INTEGER | integer | Y | N |
SMALLINT | smallint | Y | N | |
DECIMAL | dec(precision[,decimal-places]) | Y | N | |
adec(precision[,decimal-places]) | Y | N | ||
bdec(n) | Y | N | ||
FLOAT | float | Y | N | |
SMALLFLT | smallflt | Y | N | |
Character string data | CHARACTER | char(n) | Y | N |
VARCHAR | varchar(n) | Y | N | |
National character data | NCHAR | nchar(m) | Y | N |
NVARCHAR | nvarchar(m) | Y | N | |
Mixed character string data | MCHAR | mchar(n) | Y | N |
MVARCHAR | mvarchar(n) | Y | N | |
Date data | DATE | date | Y | N |
yy-mm-dd | N | N | ||
yymmdd | N | N | ||
Time data | TIME | time | Y | N |
Timestamp data | TIMESTAMP[(p)] | timestamp(p) | Y | N |
Date interval data | INTERVAL YEAR TO DAY | interval-year-to-day | Y | N |
Time interval data | INTERVAL HOUR TO SECOND | interval-hour-to-second | Y | N |
Large object data | BLOB | blob(n)# | Y | Y |
blobprm | N | Y | ||
Binary data | BINARY | binary(n)# | Y | Y |
binaryprm | N | Y |
Table 5-45 Data storage in the database when the data type specified in the column structure information file does not match the data type in the table (1/2) and Table 5-46 Data storage in the database when the data type specified in the column structure information file does not match the data type in the table (2/2) show whether input data is stored in the database when a data type specified in the column structure information file does not match the data type in the table.
Table 5-45 Data storage in the database when the data type specified in the column structure information file does not match the data type in the table (1/2)
Data type specified in column structure information file | Data type in table | |||||||||
---|---|---|---|---|---|---|---|---|---|---|
IN | SI | CH | NC | MC | VC | NV | MV | FL | SM | |
integer | Y | -- | -- | -- | -- | -- | -- | -- | -- | -- |
smallint | -- | Y | -- | -- | -- | -- | -- | -- | -- | -- |
char | Y | Y | Y#1 | Y | Y | Y | Y | Y | Y | Y |
nchar | -- | -- | -- | Y#1 | -- | -- | -- | -- | -- | -- |
mchar | -- | -- | -- | -- | Y#1 | -- | -- | -- | -- | -- |
varchar | -- | -- | -- | -- | -- | Y#2 | -- | -- | -- | -- |
nvarchar | -- | -- | -- | -- | -- | -- | Y | -- | -- | -- |
mvarchar | -- | -- | -- | -- | -- | -- | -- | Y#2 | -- | -- |
float | -- | -- | -- | -- | -- | -- | -- | -- | Y | -- |
smallflt | -- | -- | -- | -- | -- | -- | -- | -- | -- | Y |
dec | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- |
date | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- |
interval-year- to-day | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- |
time | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- |
interval-hour- to-second | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- |
timestamp | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- |
binary | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- |
yy-mm-dd | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- |
yymmdd | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- |
blob | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- |
adec | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- |
bdec | -- | -- | -- | -- | -- | -- | -- | -- | -- | -- |
No. | Specification of character set for target column | Relationship between n and m | Data storage method |
---|---|---|---|
1 | Omitted | n = m | The input data is stored as is in the database. |
2 | n > m | An error results. | |
3 | n < m | The data following (n + 1) is padded with space characters. | |
4 | Specified | n' = m | The input data is stored as is in the database. |
5 | n' > m | The input data obtained after character encoding conversion is truncated to fit the definition length of the target, and then is stored in the database. For details, see Table 5-17 Relationships among input data length, defined column length, and lengover operand. | |
6 | n' < m | The data following (n + 1) is padded with space characters. |
No. | Specification of character set for target column | Relationship between n and m | Data storage method |
---|---|---|---|
1 | Omitted | n![]() | The input data is stored as is in the database. |
2 | n > m | The input data is truncated to fit the definition length of the target, and then is stored in the database. For details, see Table 5-17 Relationships among input data length, defined column length, and lengover operand. | |
3 | Specified | n'![]() | The input data is stored as is in the database. |
4 | n' > m | The input data obtained after character encoding conversion is truncated to fit the definition length of the target, and then is stored in the database. For details, see Table 5-17 Relationships among input data length, defined column length, and lengover operand. |
Table 5-46 Data storage in the database when the data type specified in the column structure information file does not match the data type in the table (2/2)
Data type specified in column structure information file | Data type in table | |||||||
---|---|---|---|---|---|---|---|---|
DEC | DATE | IYTD | TIME | IHTS | TMST | BIN | BLOB | |
integer | -- | -- | -- | -- | -- | -- | -- | -- |
smallint | -- | -- | -- | -- | -- | -- | -- | -- |
char | Y | Y | Y | Y | Y | Y | Y | Y |
nchar | -- | -- | -- | -- | -- | -- | -- | -- |
mchar | -- | -- | -- | -- | -- | -- | -- | -- |
varchar | -- | -- | -- | -- | -- | -- | -- | -- |
nvarchar | -- | -- | -- | -- | -- | -- | -- | -- |
mvarchar | -- | -- | -- | -- | -- | -- | -- | -- |
float | -- | -- | -- | -- | -- | -- | -- | -- |
smallflt | -- | -- | -- | -- | -- | -- | -- | -- |
dec | Y | -- | -- | -- | -- | -- | -- | -- |
date | -- | Y | -- | -- | -- | -- | -- | -- |
interval-year-to-day | -- | -- | Y | -- | -- | -- | -- | -- |
time | -- | -- | -- | Y | -- | -- | -- | -- |
interval-hour-to-second | -- | -- | -- | -- | Y | -- | -- | -- |
timestamp | -- | -- | -- | -- | -- | Y | -- | -- |
binary | -- | -- | -- | -- | -- | -- | Y | -- |
yy-mm-dd | -- | Y | -- | -- | -- | -- | -- | -- |
yymmdd | -- | Y | -- | -- | -- | -- | -- | -- |
blob | -- | -- | -- | -- | -- | -- | Y# | Y |
blobprm | -- | -- | -- | -- | -- | -- | Y# | Y# |
adec | Y | -- | -- | -- | -- | -- | -- | -- |
bdec | Y | -- | -- | -- | -- | -- | -- | -- |
#: Data can be stored for an input data file in pdrorg-output binary format.
The following table provides examples of type operand specifications in the column-name statement for an input data file in pdrorg-output binary format:
Input data type | Data type after conversion | |||
---|---|---|---|---|
BLOB type | BLOB parameter | BINARY type | BINARY parameter | |
BLOB type | -- | func=(F_BLOB, param=blob) | type=blob | func=(F_BIN, param=binary, type=blob) |
BLOB parameter | type=blobprm | func=(F_BLOB, param=blob) | type=blobprm | func=(F_BIN, param=binary, type=blobprm) |
BINARY type | N | N | -- | N |
BINARY parameter | N | N | N | func=(F_BIN, param=binary) |