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.

R: Required

O: Optional

N: Not specifiable

¹ Required if a function is specified.

² Required if the func operand is specified with the function.

³ 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

⁴ Specifiable only when the type operand value is char and the table definition is CHAR or MCHAR.

⁵ 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:

Legend:

Y: Can be specified

N: Cannot be specified

Rules

1. The null value is stored in the columns containing the value specified in this operand.
2. This operand is not applicable to a column with the NOT NULL attribute, a cluster key component column, or a primary key component column.
3. 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).
4. 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

1. The specification method is the same as for numeric data in the DAT format, but do not include spaces.
2. 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.
3. 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

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

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

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

1. 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.
2. 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.
3. 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
  

  

• In the binary format
  

  

• In the DAT format
  

  

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
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
smallflt
dec (precision [, decimal-places])	For the param operand, omit the precision and scaling factor.
date
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.

¹ Specify a left-justified column value.

² If the characters are to be converted to a numeric value, there is no need to left-justify them.

³ The last two digits of the year are expanded to the format 19yy and then stored.

Tables 5-28 and 5-29 show whether or not input data is stored in database if the data type specified in the column structure information file does not match the data type in the table.

Table 5-28 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	Y	Y	Y	Y	Y	Y	Y
nchar				Y
mchar					Y
varchar						Y
nvarchar							Y
mvarchar								Y
float									Y
smallflt										Y
dec
date
interval-year- to-day
time
interval-hour- to-second
timestamp
binary
yy-mm-dd
yymmdd
blob

Legend:

IN: INTEGER

SI: SMALLINT

CH: CHAR

NC: NCHAR

MC: MCHAR

VC: VARCHAR

NV: NVARCHAR

MV: MVARCHAR

FL: FLOAT

SF: SMALLFLT

Y: Can be stored.

: Cannot be stored.

Table 5-29 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

Legend:

DEC: DECIMAL

DATE: DATE

IYTD: INTERVAL YEAR TO DAY

TIME: TIME

IHTS: INTERVAL HOUR TO SECOND

TMST: TIMESTAMP

BIN: BINARY

BLOB: BLOB

Y: Can be stored.

: Cannot be stored.

^* Data can be stored for an input data file in the pdrorg-created binary format.

The following table provides examples of type operand specification in the column name statement for an input data file in pdrorg-generated 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)

Legend:

: Specification is not necessary.

N: The corresponding input data type cannot be converted to the indicated data type.

F_BIN: This is a plug-in-provided constructor function of an abstract data type that takes BINARY type as a parameter.

F_BLOB: This is a plug-in-provided constructor function that takes BLOB type as a parameter.