Specify in the column name specification file a selection expression for the SELECT statement. For details about how to specify a selection expression with the SELECT statement, see the HiRDB SQL Reference manual.

Notes

• If a column name contains a space or hyphen (-), enclose the column name in double quotation marks (").
• To have a column name handled as being case sensitive, enclose it in double quotation marks ("). If you do not enclose it in double quotation marks, the column name is assumed to be all uppercase letters.
• To extract a column of the SGMLTEXT type, specify the extracts abstract data type function.

The following is the format of the extracts abstract data type function:

Format

extracts (column-name)

Specify in the table expression specification file the following clauses for the SELECT statement:

• WHERE clause
• ORDER BY clause

For details about how to specify the WHERE and ORDER BY clauses in the SELECT statement, see the HiRDB SQL Reference manual.

Notes

• If a value in the table expression contains a space or hyphen (-), enclose the value in double quotation marks (").
• To have a value in the table expression handled as being case sensitive, enclose the value in double quotation marks ("). If you do not enclose it in double quotation marks, the value is assumed to be all uppercase letters.
• For details about the data type combinations that can be specified in the table expression specification file, see Tables 3-7 and 3-8.
• If you specify the ORDER BY clause and attributes or character codes are converted during extraction, the sort order in the target system may be different.

Specify the null value information file in the following cases:

• The default null value is to be changed.
  If you want to store the null value in a column that is subject to data import, you can use a null value information file to change the null value. If an extract data item matches the specified value, HiRDB Dataextractor stores the null value in the corresponding table column.
  In the case of file creation, specify the default null value to be stored in the file when the extracted data contains a null value.
  Table 4-12 shows the default null value that is used by HiRDB Dataextractor based on the value specified in the null value information file and when the null value information file is omitted (-v option is omitted).
  
• The data format and null value import method must be specified in order to output repetition columns to a file.
  For details about the data format and null value import method for repetition columns, see (6) in 3.1.1 Extracting selected data.
  

Format

{column-name|field-number=NUM}
  [={HIGH|LOW|ZERO|SPACE|('literal')|
          (X'hexadecimal-literal')}]
      [,ARRAY={FF|FV|VV}[,NULLELM={C|E}]]
    [,{NOCODECONV|CODECONV}]

Description

column-name

Specify the name of the column whose default null value is to be changed.

• Importing data into a HiRDB table
  Specify the column name of the table on the target system.
  
• Only creating a file
  Specify the name of the column of the table on the source system.
  

field-number=NUM

Specify the sequence number, from the beginning, of the column whose default null value is to be changed. The permitted value range is 1-99999.

• When importing into a HiRDB table
  Specify the sequence number from the beginning of the target table definition.
  
• When only creating a file
  -s option omitted: Specify the sequence number from the beginning of the source table definition.
  -s option specified: Specify the sequence number, from the beginning, of the column name specified in the column name specification file.
  

{HIGH|LOW|ZERO|SPACE|('literal')|(X'hexadecimal-literal')}

Specify the default null value.

For the value that is used by HiRDB Dataextractor based on this specification, see Table 4-11.

ARRAY={FF|FV|VV}

Specify the data format to be used to store the repetition column in the file.

For the permitted combinations of the ARRAY and NULLELM values, see Table 4-12.

FF

The number of elements is not at the beginning of a column and there is data for all column elements.

FV

The number of elements is at the beginning of a column and there is data for all column elements.

VV

The number of elements is at the beginning of a column and there are as many data items as there are elements.

NULLELM={C|E}

In the case of storing a repetition column in the file, specify how to handle the null value (null value import method) if all elements are the null value.

For the permitted combinations of the ARRAY and NULLELM values, see Table 4-12.

C

When there is no data for any element, handle the entire column as the null value.

E

When there is no data for any element, handle each element as a null value.

{NOCODECONV|CODECONV}

For each column, specify whether or not the codes are to be converted.

NOCODECONV: Specify this value when you are not using the character code conversion function. This value is effective only when the XTLOCALE environment variable is specified for codes to be converted.

CODECONV: Specify this value when you are using the character code conversion function. This value is effective only when the XTLOCALE environment variable is specified for codes to be converted.

Note

If all the conditions listed below are satisfied, make sure that this value is specified for SGMLTEXT columns; if this value is omitted, the SGMLTEXT type will not be subject to character code conversion processing:

• Character codes are to be converted.
• Data is to be extracted and only file creation is to be performed (without importing to a HiRDB table).
• The data to be extracted contains the SGMLTEXT type.

Rules

• Specify information for one column per line. To specify information about multiple columns, specify as many lines as there are columns.
• A comment begins with the number sign (#) and ends at the end of the line.
• If a column name contains a space or hyphen (-), enclose the column name in double quotation marks (").
• To have a column name handled as being case sensitive, enclose it in double quotation marks ("). If you do not enclose it in double quotation marks, the column name is assumed to be all uppercase letters.
• When you specify a literal, check that its length does not exceed 255 bytes. Do not specify ') (a single quotation mark immediately followed by a close parenthesis) in the literal.
• In the case of data import into a HiRDB table, if a target column has the non-NULL attribute, the default null value is stored as is as the data.
• If the column on the source system has the non-NULL attribute during file creation, HiRDB Dataextractor ignores this option.
• For each abstract data type column, only a value specifying whether or not to use the code conversion function (NOCODECONV or CODECONV) can be specified.
• For details about how to specify the data format of repetition columns and the null value import method, see (6) in 3.1.1 Extracting selected data.
• Because the default null value is not subject to character code conversion, if you are outputting a binary file, use the target system's character codes to specify the literal.
• Character codes are not converted when the following data types are converted; therefore, if conversion is needed, specify CODECONV:
  (VAR)CHAR to BINARY type
  M(VAR)CHAR to BINARY type
  
• To specify a hexadecimal literal for the numeric attribute, use the target system's endian expression.
• To specify a hexadecimal literal for the FLT or SFLT attribute, observe the IEEE floating point standard.
• When a DAT file is to be created, FV cannot be specified in ARRAY.
• If the target table contains a variable-length character string, neither FF nor FV can be specified in this environment variable.
• When a binary or DAT file is to be created, NULLEM cannot be specified.
• Neither ARRAY nor NULLEM can be specified for normal columns.

Notes

• If a hexadecimal literal is specified for the FLT or SFLT attribute, HiRDB Dataextractor may correct the value in order to import the null value correctly.
• The specification rules for the null value information file differ depending on the specification in the XTNLDFLT environment variable. For details about the specification rules, see Table 4-13. For details about the relationship between the specified value of the XTNLDFLT environment variable and the default null value, see Table 4-14.

Example

CLM01=HIGH                        #INT
CLM02=LOW                         #DATE
CLM03=('FFFF')                    #CHAR(4)
CLM04=SPACE,ARRAY=FV,NULLELM=E    #CHAR(4)
CLM05, NOCODECONV
CLM06=(X'A4A2')

Table 4-11 Default null value

Data type		-v option omitted9	-v option value
			HIGH	LOW	ZERO	SPACE	Literal	Hexadecimal literal
INTEGER		-2147483648	2147483647	-2147483648	0	N	N	X'hexadecimal-literal' (4 bytes)
SMALLINT		-32768	32767	-32768	0	N	N	X'hexadecimal-literal' (2 bytes)
DECIMAL(p,s)		-99...99	99...99	-99... 99	01	N	N	X'hexadecimal-literal'((p + 1)/2 bytes)
FLOAT		2	3	2	0	N	N	X'hexadecimal-literal' (8 bytes)
SMALLFLT		4	5	4	0	N	N	X'hexadecimal-literal' (4 bytes)
CHAR(n) VARCHAR(n) MCHAR(n) MVARCHAR(n)	n < 4	n bytes of '#' (single bytes)	N	N	N	n bytes of spaces (single bytes)	literal (n bytes)	X'hexadecimal-literal' (n bytes)
	n 4	4 bytes of '#' (single bytes)	N	N	N	4 bytes of spaces (single bytes)	literal (max. 255 bytes)	X'hexadecimal-literal' (max. 127 bytes)
NCHAR(n) NVCHAR(n)	n < 2 (n = 1)	2 bytes of '#' (double bytes)6	N	N	N	2 bytes of a space (double bytes)6	literal (2 bytes)	X'hexadecimal-literal'(2n bytes)
	n 2	4 bytes of '##' (double bytes)6	N	N	N	4 bytes of spaces (double bytes)6	literal (max. 254 bytes)	X'hexadecimal-literal' (max 127 bytes)
DATE		0001- 01-01	9999- 12-31	0001- 01-01	N	N	N	X'hexadecimal-literal' (4 bytes)
INTERVAL YEAR TO DAY		-9999 yrs, 11 mos, 99 days	9999 yrs, 11 mos, 99 days	-9999 yrs, 11 mos, 99 days	0000 yrs, 00 mos, 00 days	N	N	X'hexadecimal-literal' (5 bytes)
TIME		00:00:00	23:59:59	00:00:00	N	N	N	X'hexadecimal-literal' (3 bytes)
INTERVAL HOUR TO SECOND		-99 hrs, 59 mins, 59 secs	99 hrs, 59 mins, 59 secs	-99 hrs, 59 mins, 59 secs	00 hrs, 00 mins, 00 secs	N	N	X'hexadecimal-literal' (4 bytes)
BLOB		XTNL0	N	N	N	N	N	N
BINARY(n)	n < 128	X'00...00' (n bytes)	X'FF...FF' (n bytes)	X'00...00' (n bytes)	N	N	N	X'hexadecimal-literal ' (n bytes)
	n 128	X'00...00' (127 bytes)	X'FF...FF' (127 bytes)	X'00...00' (127 bytes)	N	N	N	X'hexadecimal-literal ' (max 127 bytes)
TIMESTAMP(n)		0001- 01-01 at 00:00:00.0000007	9999- 12-31 at 23:59:59.9999997	0001- 01-01 at 00:00:00.0000007	N	N	N	X'hexadecimal-literal ' (7 + (n/2) bytes)

N: Cannot be specified.

yrs: years

mos: months

hrs: hours

mins: minutes

secs: seconds

¹ The value 0 indicates a value with a sign part (C: positive sign).

² -1.7976931348623157e + 308

³ 1.7976931348623157e + 308

⁴ -3.4028234663852886e + 38

⁵ 3.4028234663852886e + 38

⁶ Regardless of the character code set, X'8140'is assumed for the space and X'8194' for '#'.

⁷ The number of decimal places depends on the definition (0-6 decimal places).

Table 4-12 Permitted combinations of ARRAY and NULLELM

Function		Specified value		Permitted	Processing
Source	Target	ARRAY	NULLELM
HiRDB	HiRDB	FF	--	Y	Continues
			C
			E
		FV	--
			C	N	Error
			E
		VV	--	Y	Continues
			C	N	Error
			E
	Binary file	FF	--	Y	Continues
			C	N	Error
			E
		FV	--	Y	Continues
			C	N	Error
			E
		VV	--	Y	Continues
			C	N	Error
			E
	DAT file	FF	--	Y	Continues
			C	N	Error
			E
		FV	--
			C
			E
		VV	--	Y	Continues
			C	N	Error
			E

--: Not specified

Y: Permitted

N: Not permitted

Table 4-13 Null-value information file specification rules (applicable to data extraction from a HiRDB table)

Specification in the null value information file		XTNLDFLT environment variable
		0 or 1			2
		Import to HiRDB	File creation		Import to HiRDB	File creation
			Binary format	DAT format		Binary format	DAT format
column-name| field-number=NUM		M	M	M	M	M	M
=default-null-value		Y	Y	Y	N	N	N
ARRAY=	FF	Y	Y	Y	N	N	Y
	FV	Y	Y	N	N	N	N
	VV	Y	Y	Y	N	N	Y
NULLELM={C|E}		Y	N	N	N	N	N
NOCODECONV|CODECONV		Y	Y	Y	Y	Y	Y

M: Must be specified

Y: May be specified

N: Cannot be specified

Table 4-14 Relationship between the specified environment variable value and the default null value

Data type	Default value when -v option is omitted
	Value specified in XTNLDFLT environment variable
	0	1
INTEGER	-2147483648	Same as at left
SMALLINT	-32768	Same as at left
DECIMAL	-99...99	X'FF...FF'
FLOAT	1	Same as at left
SMALLFLT	2	Same as at left
CHAR(n) VARCHAR(n) MCHAR(n) MVARCHAR(n)	When n 4: n bytes of '#' When n > 4: 4 bytes of '#'	Same as at left
NCHAR(n) NVARCHAR(n)	When n 2: n characters of '#' (double bytes) When n > 2: 2 characters of '#' (double bytes)	Same as at left
DATE	0001-01-01	X'FFFFFFFF'
INTERVAL YEAR TO DAY	-9999 years, 11 months, and 99 days	X'FFFFFFFFFF'
TIME	00:00:00	X'FFFFFF'
INTERVAL HOUR TO SECOND	-99 hours, 59 minutes, and 59 seconds	X'FFFFFFFF'
BLOB	XTNL0	Same as at left
BINARY(n)	When n <128: X'00...00' (n bytes) When n 128: X'00...00' (127 bytes)	Same as at left
TIMESTAMP(n)	0001-01-01 at 00: 00:00.000000 3	X'FF...FF' (7 + (N)/2 bytes)

¹ -1.7976931348623157e + 308

² -3.4028234663852886e + 38

³ The number of decimal places depends on the definition (0-6 decimal places).

An output file is used to store extracted data. This section explains the format used by HiRDB Dataextractor to store extracted data in an output file. The specification format for an output file is based on the format of input files used by HiRDB's database load utility. There are two formats for an output file:

• DAT format
• Binary format

(a) DAT format

When you use the DAT format, specify the extracted data as character string data. The specification method is described below:

• Each line in the file corresponds to one row of table data. Each line must end with a linefeed code.
• Use a separator character between the column data items. You can specify the separator character to be used in the -R option. If you omit the -R option, HiRDB Dataextractor uses the comma (,) as the separator character.
• If column data is the null value, do not specify it regardless of the data type (it is assumed that there is no data).

Table 4-15 shows the specification format of column data (DAT format) for each data type.

Table 4-15 Specification format of column data (DAT format)

Data type		Specification format of column data
Numeric data	INTEGER	Specify a numeric value in characters. For a negative value, add a minus sign (-). Specify a decimal point as integer.fraction. Specify a floating point as mantissa-e exponent. Integer (Example: -1234 ... 0 ... 1234) Decimal point (Example: -1.56 ... 0 ... 1.56) Floating point (Example: -2.4e+9 ... 0e0 ... 2.4e+9)
	SMALLINT
	DECIMAL
	FLOAT
	SMALLFLT
Character data	CHARACTER	Specify as a character string enclosed in double quotation marks ("). Example: "abcd" or "ABCD" For a variable-length data type that has a length of 0, specify "".
	VARCHAR
National character string data	NCHAR
	NVARCHAR
Mixed character string data	MCHAR
	MVARCHAR
Date data	DATE	Specify in the numeric format yyyy-mm-dd. Example: January 1, 1998 1998-01-01
Date interval data	INTERVAL YEAR TO DAY	Specify in the numeric format [-]yyyymmdd. For a negative value, add a minus sign (-). Example: 1111 years, one month, one day 11110101.
Time data	TIME	Specify in the numeric format hh:mm:ss. Example: 12:01:01 12:01:01
Time stamp data	TIMESTAMP(n) n = 2, 4, 6	Specify in numeric format YYYY-MM-DD_hh:mm:ss.nnnnnn. YYYY: year, MM: month, DD: day, hh: hour, mm: minute, ss: second, nnnnnn: 0-6 decimal places for the second. _: Single-byte space (Example: 1997-10-15 at 15: 30:25.666666 1997-10-15 15:30:25.666666)
Time interval data	INTERVAL HOUR TO SECOND	Specify in the numeric format [-]hhmmss. For a negative value, add a minus sign (-). Example: 1 hour, 1 minute, 1 second 010101.
Large object data	BLOB	Specify the absolute pathname of the LOB input file. A LOB input file is created for each LOB data item and stored in the LOB input file storage directory.
	BINARY(n) 1 n 2,147,483,647	Enclose the data in double quotation marks ("). (Example: "abcdef")
Abstract data type	SGMLTEXT	Specify the absolute pathname of the original text file. A LOB input file is created for each LOB data item and stored in the LOB input file storage directory.

(b) Binary format

When you use the binary format, specify the extracted data in the storage format for the HiRDB database. The data format depends on the specification of the XTNLDFLT environment variable.

This subsection describes the data format that depends on the specification of the XTNLDFLT environment variable.

(i) When the XTNLDFLT environment variable is omitted or the value 0 or 1 is specified

• Do not place any spaces between column data items.
• Table 4-16 shows the specification format of column data (binary format) for each data item.
  

  Table 4-16 Specification format of column data (binary format)

  Data and parameter types		Column data specification format
  Numeric data	INTEGER, SMALLINT
  	DECIMAL(m,n) 1 m 29 0 n 29 m n
  	FLOAT, SMALLFLT
  Character string data, National character string data, Mixed character string data	CHARACTER(n) VARCHAR(n) MCHAR(n) MVARCHAR(n)
  	NCHAR(n) NVARCHAR(n)
  Date data	DATE
  Date interval data	INTERVAL YEAR TO DAY
  Time data	TIME
  Time stamp data	TIMESTAMP(n) n = 0, 2, 4, 6
  Time interval data	INTERVAL HOUR TO SECOND
  Large object data	BLOB
  	BINARY(n) 1 n 2,147,483,647
  Abstract data type	SGMLTEXT

  Note: The real data length is set in binary. For data whose length is zero (0-byte characters), the value 0 is set as the real data length and the data is stored with no real data.

(ii) When the value 2 is specified in the XTNLDFLT environment variable

See Figure 4-1.

Figure 4-1 Specification format of data when 2 is specified in the XTNLDFLT environment variable (binary format)

Explanation

Line length: In the 4-byte area, set the length from the beginning to the end of the last column data.

Offset: In the 4-byte area, set the length from the beginning of one column data item to the beginning of another.

For null-value data, 0 is set, and neither column data, nor large object data, nor abstract data is created.

Column data: Table 4-17 describes the specification format for each type of column data.

Large object or abstract data: Use one of the data types listed below that consists of the column data section containing the size and the real data at the end. The real data is stored in the following order; if there are multiple data items with the same data type, they are stored in the order defined:

1. BINARY

2. SGMLTEXT

3. BLOB

Table 4-17 Specification format of column data (when 2 is specified in XTNLDFLT environment variable)

In the import information file, specify information about the columns whose data type is to be converted and the data types.

Specify the import information file in the following cases:

• Extracted data is to be stored in a file only.
  HiRDB Dataextractor outputs the data to a file with the specified data type. If the import information file (-L option) or data type is omitted, HiRDB Dataextractor assumes the data type of the extracted column when the data was stored.
  
• The following data types are to be extracted and imported or stored in a file:
  
  • BLOB
  • BINARY
  • SGMLTEXT
  Specify the maximum data length for each column. This economizes on buffer space because HiRDB Dataextractor uses the specified buffer size. If the import information file (-L option) or data type is omitted, the buffer size for the corresponding column depends on the XTLOBBUFSIZE environment variable.
  

Format

FORMAT
  {FIELD_NUM field-number|FIELD_NAME column-name}ATTR data-type
        [{FIELD_NUM field-number|FIELD_NAME}ATTR data-type]...

Description

FORMAT

Specifies that the data type is to be converted.

FIELD_NUM

Specify the sequence number, from the beginning, of the extracted column whose data type is to be converted.

-s option omitted

Specify the sequence number, from the beginning, in the target table definition.

-s option specified

Specify the sequence number, from the beginning, of the column name specified in the column name specification file.

The permitted value range is 1-99999. The same field number cannot be specified more than once.

FIELD_NAME

Specify the name of the extracted column whose data type is to be converted.

When the -s option is specified, this option cannot be specified, in which case specify FIELD_NUM.

The same column name cannot be specified more than once.

ATTR

Specify the data type.

Table 4-18 shows the data types that can be specified.

Notes

• Specify the information for one column on a single line. To specify information about multiple columns, use as many lines as there are columns.
• A line beginning with the hash mark (#) is regarded as a comment through the end of the line.
• Enclose a column name containing a space or hyphen (-) in double quotation marks (").
• To specify a case-sensitive column name, enclose the entire name in double quotation marks (").
  A column name that is not enclosed in double quotation marks (") is treated as all upper-case letters.
  
• When extracted data is imported and the buffer size specified in the import information file is greater than the size of the BLOB data type defined in the target system, HiRDB Dataextractor ignores this specification and uses the buffer equivalent to the defined length of the BLOB column.
  

  Table 4-18 Data types permitted in ATTR

  Permitted data type	Corresponding data type	File creation	Import to HiRDB
  INT	INTEGER	Y	N
  SINT	SMALLINT	Y	N
  DEC(m,n)2	DECIMAL(m,n)	Y	N
  FLT	FLOAT	Y	N
  SFLT	SMALLFLT	Y	N
  CHAR(n)3	CHAR(n)	Y	N
  VARCHAR(n)3	VARCHAR(n)	Y	N
  MCHAR(n)3	MCHAR(n)	Y	N
  MVARCHAR(n)3	MVARCHAR(n)	Y	N
  NCHAR(n)4	NCHAR(n)	Y	N
  NVARCHAR(n)4	NVARCHAR(n)	Y	N
  DATE	DATE	Y	N
  TIME	TIME	Y	N
  YEAR	INTERVAL YEAR TO DAY	Y	N
  HOUR	INTERVAL HOUR TO SECOND	Y	N
  BLOB(n[{K|M|G}])5	BLOB(n[{K|M|G}])	Y6	Y7
  SGMLTEXT(n{K|M|G})5	SGMLTEXT	Y6	Y6
  BINARY(n)8	BINARY(n)	Y6	Y7
  TIMESTAMP(n)9	TIMESTAMP(n)	Y	N

Y: Can be specified.

N: Cannot be specified (the specification is ignored and the data type of the target system is assumed).

¹ For details about the data type combinations that can be specified, see Tables 3-7 and 3-8.

² m and n satisfy the conditions: 1 m 29, 0 n 29, and m n.

³ For n, specify the length in bytes as an unsigned integer. The specified value must satisfy the condition 1 n 32767.

⁴ For n, specify the length in characters as an unsigned integer. The specified value must satisfy the condition 1 n 16383.

⁵ The specified length serves the same purpose as the XTLOBBUFSIZE environment variable, and the former takes precedence over the latter. The following table shows the permitted value ranges and the actual maximum lengths:

Unit	Range of n	Actual size (bytes)
None	1 n 2147483647	Same as at left
K	1 n 2097152	n x 1024
M	1 n 2048	n x 1048576
G	1 n 2	n x 1073741824

Note that if the calculation result of the actual maximum length is 2147483648, HiRDB Dataextractor uses the value 2147483647.

⁶ If the XTLOBBUFSIZE environment variable is omitted, HiRDB Dataextractor may be unable to allocate the buffer, resulting in error termination.

⁷ If the specified length is greater than the length of the BLOB data type defined at the target system, HiRDB Dataextractor ignores this specification and assumes the defined length.

⁸ For n, specify the length in bytes as an unsigned integer. The specified value must satisfy the condition 1 n 2147483647.

⁹ The value of n must be 0, 2, 4, or 6.

In the lock information file, specify the lock information for data extraction.

The information to be specified in the lock information file is shown below.

• When the specified value of the XTSQL environment variable is 0 or when the XTSQL environment variable is omitted
  Specify the LOCK statement after LOCK TABLE table-name.
  

  Format

  IN EXCLUSIVE MODE NOWAIT

• When the specified value of the XTSQL environment variable is 1
  Specify the LOCK statement after LOCK TABLE.
  

  Format

  table-name IN EXCLUSIVE MODE NOWAIT

Actual LOCK statement that is issued

 
LOCK TABLE table-name IN EXCLUSIVE MODE NOWAIT
 

For details about how to specify the LOCK statement, see the manual HiRDB SQL Reference.

Note

Do not specify a semicolon (;) at the end of the specified value.