In the column name specification file, specify information for the SELECT statement about the columns to be extracted. HiRDB Dataextractor combines the values specified in the column name specification file with the SELECT statement in order to extract data from the ORACLE database.

select values-specified-in-column-name-specification-file FROM name-of table-subject-to-
extraction

If you omit the -s option, HiRDB Dataextractor assumes an asterisk (*). For details about how to specify selection expressions with the SELECT statement, see the ORACLE manual.

In the table expression specification file, specify conditions for the SELECT statement. HiRDB Dataextractor combines the values specified in the table expression specification file with the SELECT statement to extract data from the ORACLE database.

select values-specified-in-column-name-specification-file FROM name-of-table-subject-
to-extraction values-specified-in-table-expression-specification-file

If you omit the -w option, you cannot specify conditions. For details about how to specify conditions with the SELECT statement, see the ORACLE manual.

Note

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

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 ORACLE manual.

Note

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

In the import information file, specify the columns that are to be subject to data type conversion and the applicable data types. You specify an import information file in the following cases:

• Extracted data is only to be output to a file.
  HiRDB Dataextractor extracts the data with the specified data types. Table 5-5 shows the values that are assumed when the import information file (-L option) is omitted and when no data type is specified for a column.
  
• The following data types are to be extracted as HiRDB BLOB or BINARY type and imported or stored in a file:
  
  • LONG
  • LONG RAW
  • BLOB
  • BFILE
  • CLOB
  • NCLOB
  Specify the maximum data length for each column. This saves buffer space because HiRDB Dataextractor uses only the specified buffer size. If the import information file (-L option) is omitted or if no data type is specified for a column, the buffer size depends on the value set in 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

Indicates that a data type is to be converted.

FIELD_NUM

Specify a column subject to data type conversion, expressed as its sequential number from the first column that is to be subject to extraction.

-s option omitted

Sequence number from the beginning of the definitions of the extraction table

-s option specified

Sequence number from the first column name specified in the column name specification file

You must specify a value in the range 1 to 99999. The same field number cannot be specified more than once.

FIELD_NAME

Specify the name of a column to be extracted that is to be subject to data type conversion. If you have specified the -s option, you cannot specify FIELD_NAME, and must specify FIELD_NUM instead. Do not specify the same column name more than once.

ATTR

Specify the data type. Table 5-6 lists the permitted data types.

Notes

• Use one line to specify the information about one column. 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.
  

  Table 5-5 Default data types

  ORACLE data type	Length		Assumed data type
  CHAR(n) VARCHAR2(n) NCHAR(n) NVARCHAR2(n)	N/A		MCHAR(x)1
  NUMBER	NUMBER(p) or NUMBER(p,0)	p < 5	SINT
  		p < 10	INT
  		9 < p < 30	DEC(p)
  	NUMBER(p,s) and s <> 0	p < 30 and 0 < s p	DEC(p,s)
  		p < s and s < 30	DEC(s,s)
  		s < 0 and p+|s| < 30	DEC(p+|s|,0)
  	Other		FLT
  DATE	N/A		DATE2
  LONG	N/A		BLOB(x)3
  LONG RAW			--
  RAW			--
  BLOB			--
  CLOB			--
  NCLOB			--
  BFILE			--

¹ If the ORACLE data type is NCHAR or NVARCHAR2, x = n x 2; If the ORACLE data type is not NCHAR or NVARCHAR2, x = n.

² Only the date information is retrieved. If the data is a B.C. year, HiRDB Dataextractor terminates with an error.

³ The value of x depends on the value set in the XTLOBBUFSIZE environment variable.

Table 5-6 Data types permitted for ATTR

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

Y: Can be specified.

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

N: Cannot be specified (results in an error if specified).

¹ For the combinations of ORACLE data types and permitted data types, see Tables 3-7 and 3-8.

² The values of m and n must satisfy the conditions 1 m 29, 0 n 29, and m n.

³ For n, specify the length in bytes as an unsigned integer. The permitted value range is 1 n 32767.

⁴ For n, specify the number of characters as an unsigned integer. The permitted value range is 1 n 16383.

⁵ The length of the specified value has the same meaning as the value set in the XTLOBBUFSIZE environment variable and takes precedence over this environment variable. The following shows the value ranges and actual maximum lengths:

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

If the actual maximum length obtained from the calculation is 2147483648, 2147483647 is assumed.

⁶ If you have omitted the XTLOBBUFSIZE environment variable, you must specify this value. If you do not specify this value, HiRDB Dataextractor may terminate with an error because it cannot allocate buffer space.

⁷ If the specified length is greater than the length defined for the BLOB data type on the target system, HiRDB Dataextractor ignores this specification and uses 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.

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.
  For details about the null value when the null value information file is omitted (the -v option is omitted) and the default value of the null value that is set by this specification, see Table 4-11.
  

Format

{column-name|field-number=NUM}
  {[={HIGH|LOW|ZERO|SPACE|('literal')|
         (X'hexadecimal-literal')}]|
    [,{NOCODECONV|CODECONV}]}

Description

column-name

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

• Importing data to 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. When the -s option is specified, you cannot specify this information.
  

field-number=NUM

Specify the column whose default null value is to be changed, expressed as its sequential number from the beginning. You must specify a value in the range 1 to 99999. You cannot specify the same field number more than once.

• Importing data into a HiRDB table
  Sequence number from the beginning of the definitions of the import table.
  
• Only creating a file
  -s option omitted
  Sequence number from the beginning of the definitions of the extraction table.
  -s option specified
  Sequence number from the first column name specified in the column name specification file.
  

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

Specify the default null value.

For details about the value that is set by this specification, see Table 4-11.

{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.

Rules

• Use one line to specify information for one column. 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 (").
• A column name that is not enclosed in double quotation marks (") 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 closing parenthesis) in the literal.
• In the case of data import into a HiRDB table, if the 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.

Example

CLM01=HIGH                        #INT
CLM02=LOW                         #DATE
CLM03=('FFFF')                    #CHAR(4)
4=NUM=SPACE                       #CHAR(4)

The specification rules for the null value information file differ depending on the specification in the XTNLDFLT environment variable. Table 5-7 describes these specification rules.

Table 5-7 Null-value information file specification rules (applicable to data extraction from an ORACLE database)

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
NOCODECONV|CODECONV	Y	Y	Y	Y	Y	Y

M: Mandatory

Y: Can be specified

N: Cannot be specified