This section discusses the data types that can be converted during the import operation and compared to selected rows to be extracted.

Tables 3-7 and 3-8 list the data types that HiRDB Dataextractor can convert and compare. When it imports data, HiRDB Dataextractor compares a column to be extracted with the column to be imported and executes data type conversion if they constitute any of the combinations listed in Tables 3-7 and 3-8. When it selects a row to be extracted, HiRDB Dataextractor compares the specified data if the conditions in the table expression specification file match the appropriate combination of data types listed in Tables 3-7 and 3-8.

Notes

• Overflow error during data type conversion
  If an overflow error occurs during data type conversion, HiRDB Dataextractor takes one of the following actions, depending on the specification of pd_overflow_suppress in the HiRDB system common definitions:
  
  • If the suppression of overflow errors is specified:
    Regards the result of the operation that caused the overflow error as the null value.
    
  • If the suppression of overflow errors is not specified:
    Outputs an error message and terminates processing.
    
• Character code set
  The HiRDB systems on the source and target systems must use the same character code set; do not execute HiRDB Dataextractor if they use different character code sets (if you execute HiRDB Dataextractor, the results may not be correct).
  

  Table 3-7 Data types that HiRDB Dataextractor can convert and compare (1/2)

  Data type on source system		Data type on target system
  		Numeric data	Character string data, mixed character string data 14	National character data 14	Date data
  Numeric data	INT	Y	--	--	--
  	SMALLINT
  	DEC15
  	FLOAT
  	SMALLFLT
  Character string data15	CHAR	--	Y11	Y14	2
  	VARCHAR
  Mixed character string data15	MCHAR	--	Y11	Y14	--
  	MVARCHAR
  National character data15	NCHAR	--	Y12	Y11	--
  	NVARCHAR
  Date data	DATE	--	3	--	Y
  Time data	TIME	--	8	--	--
  Date interval data	YEAR	4	--	--	--
  Time interval data	HOUR	9	--	--	--
  Time stamp data	TIMESTAMP	--	--16	--	--
  Large object data15, 20	BLOB BINARY	--	--	--	--
  Abstract data	SGMLTEXT	--	--	--	--

  Table 3-8 Data types that HiRDB Dataextractor can convert and compare (2/2)

  Data type on source system		Data type on target system
  		Time data	Date interval data	Time interval data	Time stamp data	Large object data 15, 20	Abstract data
  Numeric data	INT	--	1	6	--	--	--
  	SMALLINT
  	DEC15
  	FLOAT
  	SMALLFLT
  Character string data15	CHAR	7	--	--	--17	--19	--
  	VARCHAR
  Mixed character string data15	MCHAR	--	--	--	--	--19	--
  	MVARCHAR
  National character data15	NCHAR	--	--	--	--	--	--
  	NVARCHAR
  Date data	DATE	--	--	--	--	--	--
  Time data	TIME	Y	--	--	--	--	--
  Date interval data	YEAR	--	Y5	--	--	--	--
  Time interval data	HOUR	--	--	Y10	--	--	--
  Time stamp data	TIMESTAMP	--	--	--	Y18	--	--
  Large object data15, 20	BLOB BINARY	--	--	--	--	Y	--
  Abstract data	SGMLTEXT	--	--	--	--	--	Y

Y: Can be converted and compared.

--: Cannot be converted or compared.

YEAR: INTERVAL YEAR TO DAY

HOUR: INTERVAL HOUR TO SECOND

¹ Comparison with and conversion to date interval data is supported in the following cases:

• Comparison with a literal that expresses the date interval in decimal
• Comparison and conversion with DECIMAL(8,0) data

² Comparison with date data is supported in the following cases:

• Comparison with a literal that expresses the date in characters
• Comparison with CHARACTER(10) data

³ Comparison with date data and conversion to character data is supported in the following cases:

• Comparison with a literal that expresses the date in characters
• Comparison with CHARACTER(10) data
• Conversion of date data to CHARACTER or VARCHAR data with a length of at least 10 bytes

⁴ Comparison with date interval data and conversion to decimal data is supported in the following cases:

• Comparison with a literal that expresses a date interval in decimal
• Comparison with DECIMAL(8,0) data
• Conversion from date interval data to DECIMAL(8,0) data

⁵ Comparison between two date interval data items takes place in the order of year, month, and date.

⁶ Comparison with and conversion to time interval data is supported in the following cases:

• Comparison with a literal that expresses a time interval in decimal
• Comparison and conversion with data corresponding to DECIMAL(6,0)

⁷ Comparison with time data is supported in the following cases:

• Literal that expresses the time in characters
• Comparison with CHARACTER(8) data

⁸ Comparison with time data and conversion to character data is supported in the following cases:

• Comparison with a literal that expresses the time in characters
• Comparison with CHARACTER(8) data
• Conversion from time data to CHARACTER or VARCHAR data with a length of at least eight bytes

⁹ Comparison with time interval data and conversion to decimal data is supported in the following cases:

• Comparison with a literal that expresses a time interval in decimal
• Comparison with DECIMAL(6,0) data
• Conversion from time interval data to DECIMAL(6,0) data

¹⁰ Comparison between two time interval data items takes place in the order of hour, minute, and second.

¹¹ If data on the source system is shorter than the data on the target system, HiRDB Dataextractor pads the data with trailing spaces.

¹² You should note the following when you execute with this combination:

• The data length on the target system must be equal to or greater than 2 times the number of characters on the source system.
• If the data on the source system is shorter than the data on the target system, HiRDB Dataextractor sets one-byte space characters.
• HiRDB Dataextractor stores the data from the source system into the target system as is.
• If N(VAR)CHAR spaces are intermixed with one-byte spaces, comparison involving spaces may not produce reliable results.

¹³ You should note the following when you execute with this combination:

• The data length on the source system must be equal to or less than 2 times the number of characters on the target system.
• Do not assign single-byte codes in a column defined as N(VAR)CHAR (if you assign single-byte codes, the data search may not produce reliable results).
• If the data on the source system is shorter than the data on the target system, HiRDB Dataextractor sets one-byte space characters.
• HiRDB Dataextractor stores the data from the source system into the target system as is.

¹⁴ Data truncation occurs in the following cases:

If a nonzero value is specified in the XTERRLEVEL environment variable, processing depends on the specified value:

• Character string, mixed character string, or national character string data:
  Data length on the source system > data length on the target system
  
• Decimal data:
  Length of integer part on the source system > length of the integer part on the target system
  or
  Length of decimal part on the source system > length of decimal part on the target system
  

¹⁵ Data conversion and comparison is not supported if the data on the source system is longer than the data on the target system. However, if the XTLOBBUFSIZE environment variable is specified, HiRDB Dataextractor processes the data according to the specification of the XTERRLEVEL environment variable.

¹⁶ Conversion from time stamp data to character data (CHAR(n), VARCHAR(n)) is supported in the following cases:

• The time stamp data does not include decimal places for seconds and the length of the character data is at least 19 bytes.
• The time stamp data has 2 decimal places for seconds and the length of the character data is at least 22 bytes.
• The time stamp data has 4 decimal places for seconds and the length of the character data is at least 24 bytes.
• The time stamp data has 6 decimal places for seconds and the length of the character data is at least 26 bytes.

¹⁷ Conversion from character data (CHAR(n), VARCHAR(n)) to time stamp data is supported in the following cases:

• CHARACTER or VARCHAR is the time stamp data.
• The TIMESTAMP_FORMAT scalar function is specified in the column name specification file. Decimal places for seconds are truncated if the target is too short and are padded with 0x00 if the target is too long.

¹⁸ If the decimal places for seconds do not match, they are truncated if the target is too short and are padded with 0x00 if the target is too long.

¹⁹ Conversion is supported in the following cases:

• Character or mixed character data is converted to BINARY data.
• Hexadecimal literals are specified in the column name specification file and imported to BINARY data.

²⁰ Types other than the BLOB type for large object data can be used.