column-name[{[,type=data-type
[,null={'numeric-comparison-value'|(begin-position,{c|x}'comparison-value)}]]
[,element=elements-count][,elmtype=arrayed-data-format]
[,nullset=null-value-option][,mode={text|bin}]
[,enclose_del={yes|no}][,filldata=filler-data]
|[,func=([authorization-identifier.]function-name
,param=argument-type[,type=data-type
[,null={'numeric-comparison-value'|(begin-position,{c|x}'comparison-value')}]]
[,mode={text|bin}][,enclose_del={yes|no}]
[,filldata=filler-data]
[,param=argument-type[,type=data-type
[,null={'numeric-comparison-value'|(begin-position,{c|x}'comparison-value')}]]
[,mode={text|bin}][,enclose_del={yes|no}]
[,filldata=filler-data]]...)]
}] |
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-generated binary format |
---|
Predefined type (column specification) | column-name | R | R | R |
type | N | R | O3 |
null | N | O | O |
element | O | O | N |
elmtype | O | O | N |
nullset | O | O | N |
mode | N | O4 | N |
enclose_del | N | O5 | N |
filldata | N | O | N |
Abstract data type (function specification) | column-name | R | R | R |
func | O1 | O1 | O1 |
param | O2 | O2 | O2 |
type | N | O2 | O3 |
null | N | O | O |
mode | N | O4 | N |
enclose_del | N | O5 | N |
filldata | N | O | N |
R: Required
O: Optional
N: Not specifiable
1 Required if a function is specified.
2 Required if the func operand is specified with the function.
3 Specifiable if the data type of the column or the data specified in param with function specification is BLOB or BINARY. If any one of the following conditions is applicable, make sure that the operand is specified:
- Loading unload data from a BLOB column to a column of an abstract data type for which the BINARY or BLOB parameter has been specified
- Loading unload data from the BLOB parameter of an abstract data type to a column of the BLOB or BINARY type
- Loading unload data from a BLOB column to a BINARY column
- Loading unload data from the BLOB parameter of an abstract data type to a column of the abstract data type for which the BINARY parameter has been specified
4 Specifiable only when the type operand value is char and the table definition is CHAR or MCHAR.
5 Specifiable only when the type operand value is char and the table definition is VARCHAR or MVARCHAR.
(a) column-name
Specifies the name of the table column in which input data is to be stored.
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.
(b) type=data-type
Specifies the data type of the column in the input data. For the data types for columns in input data, see (3) Specification of data types and argument types. 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 (4) 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.
(c) null={'numeric-comparison-value'|(begin-position,{c|x}'comparison-value')}
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 |
- Legend:
- Y: Can be specified
- N: Cannot be specified
- Rules
- The null value is stored in the columns containing the value specified in this operand.
- This operand is not applicable to a column with the NOT NULL attribute, a cluster key component column, or a primary key component column.
- For an input data file in pdrorg-generated BINARY format, the null value is specified by setting the offset to 0 for LOB and BINARY column data and the LOB and BINARY parameters of a abstract data type; therefore, you cannot specify this operand for these columns. When these data types have real data, to treat them as the null value by setting the offset to 0, you must first have deleted the real data part from the corresponding input data (simply editing the corresponding offset to 0 does not result in correct processing).
- For an input data file in fixed-size data format, the system performs comparison after dividing the data into columns; thus, all trailing spaces are deleted before data comparison.
- numeric-comparison-value
((up to 255 characters)) - For numeric input data, this operand specifies the numeric value to be compared.
- Rules
- The specification method is the same as for numeric data in the DAT format, but do not include spaces.
- The numeric attribute applies to the INTEGER, SMALLINT, FLOAT, SMALLFLT, DEC, DATE, TIME, INTERVAL YEAR TO DAY, INTERVAL HOUR TO SECOND and TIMESTAMP data types, and to the yymmdd format.
- If a numeric value of TIMESTAMP type is specified and the fraction part is shorter than the defined value, the system assumes 0.
- Example
- DATE: yyyy-mm-dd
Example: January 1, 1995
1995-01-01
- TIME: hh:mm:ss
Example: 12:01:01
12:01:01
- INTERVAL YEAR TO DAY: yyyymmdd.
Example: 1900 years, 1 month, and 1 day
19000101.
- INTERVAL HOUR TO SECOND: hhmmss.
Example: 12 hours, 1 minute, and 1 second
120101.
- TIMESTAMP: yyyy-mm-dd hh:mm:ss.nnnn
Example: 1900-01-01 at 01:10:30:45
1900-01-01 01:10:30.4500 or 1900-01-01 01:10:30.45
- (begin-position,{c|x}'comparison-value')
- For a character comparison value, specify this information when the input data has the character attribute or is binary or LOB data. The character attribute applies to the CHAR, NCHAR, MCHAR, VARCHAR, NVARCHAR, and MVARCHAR data types and to the yy-mm-dd format.
- For a hexadecimal comparison value, this information can be specified even if the input data has the numeric attribute (comparison is available with all data types).
- begin-position
<unsigned integer> ((1-32000)) - Specifies in bytes the beginning position of the storage data for the target column that is to be compared with the comparison value in order to determine whether or not the input data is treated as the null value.
- The system compares the portion of the input data beginning at the specified position with the specified comparison value. If they match, the system treats the input data as the null value. The following shows examples:
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. |
- c:
- Specifies that the comparison value is a character string.
- If the number of characters specified for begin-position + comparison-value is longer than the defined length of the corresponding column, a control statement error results.
- x:
- Specifies that the comparison value is a hexadecimal value.
- Two hexadecimal characters (0 to f) are treated as 1 byte.
- The specified characters are not case sensitive.
- If the specified character string does not consist of hexadecimal characters or if the number of characters specified for begin-position + comparison-value is longer than the defined length of the corresponding column, a control statement error results.
- For a numeric-type column, the comparison value may be the same as the input data. In such a case, specify the comparison value as hexadecimal characters. The following shows examples:
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') |
- * A specified hexadecimal comparison value may match some value (in the example of SMALLINT, this comparison value matches the big-endian 20044). To treat the value as the null value, you must specify a value that is not stored.
- comparison-value
((up to 255 characters)) - Specifies the characters to be compared with the input data. If you are specifying x, be sure to specify an even number of characters (with a maximum of 254 characters).
(d) element=elements-count
<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.
- Rules
- If the arrayed data format is the VV format, the system always uses the number of elements specified in the input data file. Therefore, this operand is ignored whether or not it is specified.
- An error results if you specify this operand for an input data file in the pdrorg-generated binary format.
(e) elmtype=arrayed-data-format
For a repetition column, specify its array data format.
- arrayed-data-format
- Specify one of the following formats:
- ff: FF format
- fv: FV format
- vv: VV format
- For details about the arrayed data format, see 5.5.4 Arrayed data format for tables containing repetition columns.
- Rules
- If you omit the elmtype operand and also omit the array statement in the control information file, the system assumes the following format for the arrayed data format:
- DAT format file: FF format
- Binary format file: VV format
- Fixed-size data format file: FF format
- An error results if you specify this operand for an input data file in the pdrorg-generated binary format.
(f) nullset=null-value-option
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.
- null-value-option
- c: Nothing is stored in specified element.
- e: Null value is stored in specified element.
- Rules
- When the nullset operand is omitted, the system assumes the following value:
- If an array statement is not specified in the control information file: c
- If an array statement is specified in the control information file: Value specified in the nullset operand of the array statement
- An error results for the binary-format input data file generated by pdrorg.
(g) mode={text|bin}
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.
- Criteria
- To store the following characters as part of the input data, specify bin:
- Trailing single-byte spaces (0x20), tab characters (\t), and null characters (\0) in the input data
- Quotation marks (") at both ends of the input data
- To delete the above characters from the input data, specify text (default value).
- text:
- Specifies that the input data is to be edited as follows and then stored:
- Delete the trailing single-byte spaces (0x20), tab characters (\t), and null characters (\0) from the input data.
- Delete the quotation marks (") at both ends of the input data.
- If the input data is shorter than the defined length of the storage column, pad the column with single-byte spaces.
- bin:
- Specifies that the input data is to be edited as follows and then stored:
- If the input data is shorter than the defined length of the storage column, pad the column with single-byte spaces.
- When bin is specified, space conversion cannot be performed (the spacelvl operand cannot be specified in the option statement).
(h) enclose_del={yes|no}
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.
- Criteria
- To store the quotation marks (") at both ends of the input data as part of the input data, specify no.
- To delete the quotation marks at the ends of the input data, specify yes (default value).
- yes:
- Specifies that the input data is to be edited as follows and then stored:
- Delete the trailing single-byte spaces (0x20), tab characters (\t), and null characters (\0) from the input data.
- Delete the quotation marks (") at both ends of the input data.
- no:
- Specifies that the input data is to be edited as follows and then stored:
- Delete the trailing single-byte spaces (0x20), tab characters (\t), and null characters (\0) from the input data.
- To store single-byte spaces, tab characters, and null characters as part of the input data, enclose them in quotation marks.
(i) filldata=filler-data
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-255 expressed in hexadecimal (X'00" to X'ff'). When this operand is omitted, 0 is assumed.
- Rules
- When converting data from CHAR to BINARY, the system treats the data without the consecutive trailing fillers as the data section of the BINARY data.
- A control statement error results if this operand is specified for an input data file that is not in the fixed-size data format or for a column that is not the BINARY type.
- Specifying this operand for a parameter other than a BINARY-type parameter results in a control statement error.
(j) func=([authorization-identifier.]function-name,param=argument-type[,type=data-type[,null={'numeric-comparison-value'|(begin-position,{c|x}'comparison-value')}]][,null={'numeric-comparison-value'|(begin-position,{c|x}'comparison-value')}]][,mode={text|bin}][,enclose_del={yes|no}][,filldata=filler-data][,...])
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 (4) 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.
- [authorization-identifier.]function-name
- When generating the data to be stored by calling a constructor function supplied by a plug-in, specify the name of the function.
- You cannot specify a function that produces a return value different from the abstract data type of the column to be stored, or that has an abstract data type argument.
- If the authorization identifier is omitted, the system assumes the user who defined the abstract data type.
- param=argument-type
- Specify the data type of the parameter that is to be passed to the constructor function.
- If there are multiple parameters, specify them in the order based on the function's input format. For details about how to specify the argument types, see (3) Specification of data types and argument types. Note that you cannot specify either yy-mm-dd or yymmdd. blobprm is permitted only for an input data file in the pdrorg-generated binary format.
- type=data-type
- If the input data file is a fixed-size data format file, specify the data type of the parameter to be input. If there are multiple parameters, specify them in the order based on the function's input format. For details about how to specify the argument types, see (3) Specification of data types and argument types. Note that you cannot specify either yy-mm-dd or yymmdd. blobprm is permitted only for an input data file in the pdrorg-generated binary format.
- null={'numeric-comparison-value'|(begin-position,{c|x}'comparison-value')}
- For an input data file in the fixed-size data format, specify this operand to handle the input parameter as the null value.
- The input parameter's data type is subject to comparison. For the specification method, see (c) null={'numeric-comparison-value'|(begin-position,{c|x}'comparison-value')}, previously.
- mode={text|bin}
- For details about this specification, see (g) mode={text|bin}.
- enclose_del={yes|no}
- For details about this specification, see (h) enclose_del={yes|no}.
- filldata=filler-data
- For details about this specification, see (i) filldata=filler-data.
- Specification order in func operand:
- The following shows the order in which param, type, null, filldata, mode, and enclose_del are specified in the func operand:
- In the fixed-size data format
![[Figure]](figure/zu050330.gif)
- In the binary format
![[Figure]](figure/zu050340.gif)
- In the DAT format
![[Figure]](figure/zu050350.gif)
Table 5-27 shows the method for specifying data types and argument types.
Table 5-27 Specification of data types and argument types
Specification | Remarks |
---|
integer | ![[Figure]](figure/zueng033.gif) |
smallint |
char(n)1, 2 | n indicates the CHAR or MCHAR length in bytes; m indicates the NCHAR length in digits. These values may not match the column length defined for the target table. If converting characters to numeric values, do not include the null character (\0) in the character string. For the param operand, omit (n) and (m). |
nchar(m)1 |
mchar(n)1 |
varchar(n)1 | n indicates the maximum length of VARCHAR or MVARCHAR in bytes; m indicates the maximum length of NVARCHAR in digits. These values may not match the column length defined for the target table. For the param operand, omit (n) and (m). |
nvarchar(m)1 |
mvarchar(n)1 |
float | ![[Figure]](figure/zueng033.gif) |
smallflt |
dec (precision [, decimal-places]) | For the param operand, omit the precision and scaling factor. |
date | ![[Figure]](figure/zueng033.gif) |
interval-year-to-day |
time |
interval-hour-to-second |
yy-mm-dd1, 3 | 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 (e.g., yy-mm-dd or yy/mm/dd). |
yymmdd3 | You can use this format only when storing the unsigned packed-format data indicating the last two digits of the year in a DATE-type column. |
timestamp(p) | p indicates the length of the fraction part for seconds (0, 2, 4, or 6). For the param operand, omit (p). |
binary(n) | n indicates the maximum length of the BINARY type in bytes. For the param operand, omit (n). |
binaryprm | For an input data file in pdrorg-generated binary format, you can use this type in the type operand. When skipping abstract data-type data, if the abstract data type is the BINARY-type parameter, use this type in the type operand of the skipdata statement. A control statement error results if this type is specified in an operand other than the type operand of the skipdata statement for an input data file in pdrorg-generated binary format. |
blob(n) | - When specifying in the type operand for the fixed-size data format
n indicates the maximum length of the LOB input file name and may not match the length defined for the LOB column. Use this format for binary-format data containing its length in the first four bytes.
- When specifying in the type operand for the pdrorg-generated binary format
Omit (n).
- When specifying in the param operand
Omit (n).
- When specifying in the type operand of a skipdata statement
Omit (n).
|
blobprm | For an input data file in the pdrorg-generated binary format, you can use this format in the type operand. When unloading an abstract data type, use this format for the BLOB parameter data that has been unloaded. |
1 Specify a left-justified column value.
2 If the characters are to be converted to a numeric value, there is no need to left-justify them.
3 The last two digits of the year are expanded to the format 19yy and then stored.