You can extract data from a table and import it into another table with the identical format. The following is an example:

Example

Import the ORDER table from the source system to the target system. The ORDER tables on the two systems have the same format, table name, and column names.

Figure 3-2 shows an example of extracting data in units of tables.

Figure 3-2 Extracting data in units of tables

You can select the columns to be extracted and sort them. To do this, use the following SQL statement provided by HiRDB:

• Selection expression in the SELECT statement

Use the xtrep command to specify the name of the file that contains the SELECT statement's selection expression; this file is called the column name specification file. The following is an example:

Example

Select the following columns from the ORDER table, sort them, and then import them:

• Product code
• Order quantity
• Form No.

Figure 3-3 shows an example of selecting and sorting columns to be extracted.

Figure 3-3 Selecting and sorting columns to be extracted

You can select the rows to be extracted and sort them in ascending or descending order of the data in any column. To do this, use the following SQL statement provided by HiRDB:

• WHERE and ORDER BY clauses in the SELECT statement

To extract selected rows, specify the selection condition in the SELECT statement's WHERE clause. To extract sorted rows, specify the sort key and sorting method in the SELECT statements' ORDER BY clause.

Use the xtrep command to specify the name of the file that contains the SELECT statement specifying the WHERE and ORDER BY clauses; this file is called the table expression specification file. The following is an example:

Example

Select the following rows from the ORDER table, sort them, and then import them:

• Extract all rows whose order quantity is at least 10.
• Sort the rows in ascending order of the customer codes.

Figure 3-4 shows an example of selecting and sorting rows to be extracted.

Figure 3-4 Selecting and sorting rows to be extracted

To extract data from a table with the FIX attribute, you can use a line-by-line interface. The line-by-line interface can improve performance because it eliminates overhead for retrieving individual columns. To extract data using the line-by-line interface, use the following SQL statement provided by HiRDB:

• Selection expression (ROW specified) in the SELECT statement

Use the xtrep command to specify the name of the column name specification file that contains the SELECT statement specifying the selection expression. The following is an example:

Example

Import the ORDER table from the source system to the target system using the line-by-line interface. The ORDER tables on the source and target systems have the same format, table name, and column names.

Figure 3-5 shows an example of extracting data by a line-by-line interface.

Figure 3-5 Extracting data by a line-by-line interface

When data that is being extracted is the null value, HiRDB Dataextractor sets the data to HiRDB Dataextractor's default null value. During data import, HiRDB Dataextractor compares the extracted data with its default null value and sets the data to the null value if they match. If an applicable column has the NOT NULL constraint, HiRDB Dataextractor imports the default null value as the data.

If the data to be extracted contains HiRDB Dataextractor's default null value, you must change the default null value. To do this, specify a null value information file in the -v option of the xtrep command.

See Table 4-12 for HiRDB Dataextractor's default null value (which is assumed when the -v option is omitted) and the default null values that are specified in the null value information file. The following is an example:

Example

Change the default null values for character string data:

• For column CHAR01, which is subject to extraction, use the existing default null value. If data contains ##...#, it will also be set to the null value.
• For column CHAR02, which is subject to extraction, change the default null value to a space. If data contains a space, it will also be set to the null value.
• For column CHAR03, which is subject to extraction, change the default null value to the literal 'FFFF'. If data contains this literal, it will also be set to the null value.

Figure 3-6 shows an example of changing the default null value.

Figure 3-6 Changing the default null value

If you specify only the default null value that is assumed by HiRDB Dataextractor and the default null values that are specified in the null value information file, the real data in the source table that is the same as the default null value is imported as the null value. To prevent such real data from being imported as the null value, use one of the methods described below.

• Changing the default null value
• Adding null value checking information to the extracted data

(a) Changing the default null value

For the data types listed below (containing a range of values that cannot be specified as data), you can prevent real data from being imported as the null value by setting as HiRDB Dataextractor's default null value a range of values that cannot be specified as real data:

• DECIMAL
• DATE
• INTERVAL YEAR TO DAY
• TIME
• INTERVAL HOUR TO SECOND
• TIMESTAMP

For other data types, you must set a value that is not used operationally as the default null value because there will be no such value that cannot be specified as data.

(b) Adding the null value checking information to the extracted data

You can prevent real data from being imported as the null value by adding information used to check for the null value to the extracted data without using the default null value.

This data format is the same as for the input data when the database load utility's (pdload) -W option is specified.

HiRDB Dataextractor can extract and import repetition columns. This section discusses the following topics associated with the use of repetition columns:

• Combination of columns between source and target systems when there are repetition columns
• Specification of repetition column's data format and null value handling method
• Notes on the specification of data format and null value handling method
• Data format of repetition columns during file output

(a) Combination of columns between source and target systems when there are repetition columns

Table 3-1 shows whether or not HiRDB Dataextractor supports the combination of columns between source and target systems when there are repetition columns.

Table 3-1 Support of the combination of columns between source and target systems when there are repetition columns

Column on source system		Column on target system		Number of elements	Support
Non-repetition column		Non-repetition column		N/A	P
		Repetition column	All elements	N/A	T
			Specific elements	N/A	N/A
Repetition column	All elements	Non-repetition column		N/A	T
		Repetition column	All elements	Source system = target system	P
				Source system < target system	T
				Source system > target system	T
			Specific elements	N/A	N/A
	Specific elements	Non-repetition column		N/A	P
		Repetition column	All elements	N/A	T
			Specific elements	N/A	N/A

Number of elements: Number of elements in the repetition column.

Support: HiRDB Dataextractor's processing.

P: Processed normally.

T: Terminated.

N/A: Not applicable.

(b) Specification of repetition column's data format and null value handling method

When the repetition columns are output to a file, you can use the following to specify the data format of the repetition columns and the null value handling method:

• XTARRAY environment variable
• Null value information file (specified in the xtrep command's -v option)

Use the XTARRAY environment variable to specify the data format and null value handling method for each table. To specify this information for each column, use the null value information file that is specified in the -v option of the xtrep command. If you specify both the XTARRAY environment variable and a null value information file, the null value information file takes precedence. If you omit the XTARRAY environment variable, VV and C are assumed.

If 2 is specified in the XTNLDFLT environment variable, and data is imported into a HiRDB table or a binary-format file is created, this environment variable is ignored, and VV and C are assumed.

Data format

Specify the data format of a repetition column that is to be output to a file:

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.

Null value handling method

Specify how to handle the null value if all elements are the null value when a repetition column is imported into a HiRDB table. You can specify the null value handling method only when data is to be imported into a HiRDB table and the data format is FF.

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.

Table 3-2 shows the supported combinations data format and null value handling method for a repetition column.

Table 3-2 Combinations of data format and null value handling method for a repetition column

Facility to be executed		Specified value		Support
		Data format	Null value handling method
Import to HiRDB table		FF	None	Y
			C
			E
		FV	None	Y
			C	--
			E
		VV	None	Y
			C	--
			E
File creation	Binary format	FF	None	Y
			C	--
			E
		FV	None	Y
			C	--
			E
		VV	None	--
			C
			E
	DAT format	FF	None	Y
			C	--
			E
		FV	None	--
			C
			E
		VV	None	Y
			C	--
			E

Y: Supported.

--: Not supported.

(c) Notes on the specification of data format and null value handling method

You should note the following about specifying the data format and null value handling method:

• You cannot specify a data format or null value handling method for a non-repetition column.
• If you are creating a file in DAT format, you cannot specify FV for the data format.
• If the table subject to processing contains a variable-length character string, FF or FV cannot be specified for the data format.
• If you are only creating a file, you cannot specify a null value handling method.
• If you specify the FF data format and a null value handling method, the specified null value handling method takes effect when data is imported into the specified HiRDB table. Therefore, the null value for the entire column can no longer be distinguished from the null value for the entire element; as a result, the data status may change between the source and target systems.
  

  Example

  If both HiRDB Dataextractor and HiRDB Datareplicator are used at the same time in the same system and the following conditions are satisfied, HiRDB Datareplicator results in an error during import processing:

  1. Data format FF and null value handling method E were specified with HiRDB Dataextractor to import the null value for the entire column on the source system as the null value for the entire element.
  2. A UAP updated (added elements to) the record processed in 1 above and HiRDB Datareplicator was used to incorporate the change.

(d) Data format of repetition columns during file output

Table 3-3 shows the data format of repetition columns (binary format) during data import into a HiRDB table. Table 3-4 shows the data format of repetition columns during creation of a binary-format file. Table 3-5 shows the data format of repetition columns during creation of a DAT-format file.

Table 3-6 describes the data format of repetition columns when 2 is specified in the XTNLDFLT environment variable and data is imported into a HiRDB table or a binary-format file is created.

Table 3-3 Data format of repetition columns during data import into a HiRDB table (binary format)

Extracted data		Specification for repetition column		Contents of output data*						Imported data
		Data format	Null value handling method
There are elements	All	FF	N/A	E1	...				EN	All
		FV	N/A	N	E1	...			EN
		VV	N/A	N	E1	...			EN
	Some	FF	N/A	E1	...	En	...		NVN	Some
		FV	N/A	n	E1	...	En	...	NVN
		VV	N/A	n	E1	...	En	--
There is no element	Null value in entire column	FF	Not specified	NV1	...				NVN	Null value in entire column
			C	NV1	...				NVN
			E	NV1	...				NVN	Null value in each element
		FV	N/A	0	NV1	...			NVN	Null value in entire column
		VV	N/A	0	--
	Null value in all elements	FF	Not specified	NV1	...				NVN	Null value in entire column
			C	NV1	...				NVN
			E	NV1	...				NVN	Null value in each element
		FV	N/A	N	NV1	...			NVN	Null value in each element
		VV	N/A	N	NV1	...			NVN

N/A: Not applicable

E_x: Element x

NV_x: Null value x

N: Maximum number of elements

n: Actual number of elements

* Indicates each element sequentially from left to right that is placed in the output data. A - column means that there is no corresponding element.

Table 3-4 Data format of repetition columns during creation of a binary-format file

Extracted data		Specification for repetition column		Contents of output data*						Remarks
		Data format	Null value handling method
There are elements	All	FF	N/A	E1	...				EN	--
		FV	N/A	N	E1	...			EN
		VV	N/A	N	E1	...			EN
	Some	FF	N/A	E1	...	En	...		NVN	--
		FV	N/A	N	E1	...	En	...	NVN	--
		VV	N/A	N	E1	...	En	--
There is no element	Null value in entire column	FF	N/A	NV1	...				NVN	--
		FV	N/A	0	NV1	...			NVN
		VV	N/A	0	--					--
	Null value in all elements	FF	N/A	NV1	...				NVN	--
		FV	N/A	N	NV1	...			NVN
		VV	N/A	N	NV1	...			NVN

N/A: Not applicable

E_x: Element x

NV_x: Null value x

N: Maximum number of elements

n: Actual number of elements

* Indicates each element sequentially from left to right that is placed in the output data. A - column means that there is no corresponding element.

Table 3-5 Data format of repetition columns during creation of a DAT-format file

Extracted data		Specification for repetition column		Contents of output data*	Remarks
		Data format	Null value handling method
There are elements	All	FF	N/A	Element1, ..., ElementN	Number of commas = N - 1
		FV	N/A	N/A	Not specifiable
		VV	N/A	N, Element1, ..., ElementN	Number of commas = N
	Some	FF	N/A	Element1, ..., Elementn, ...,	Number of commas = N - 1
		FV	N/A	N/A	Not specifiable
		VV	N/A	n, Element1, ..., Elementn	Number of commas = n
There is no element	Null value in entire column	FF	N/A	, ...,	Number of commas = N - 1
		FV	N/A	N/A	Not specifiable
		VV	N/A	0	Number of commas = 0
	Null value in all elements	FF	N/A	, ...,	Number of commas = N - 1
		FV	N/A	N/A	Not specifiable
		VV	N/A	N, ...,	Number of commas = N

N/A: Not applicable

N: Maximum number of elements

n: Actual number of elements

* Indicates each element sequentially from left to right that is placed in the output data.

Table 3-6 Data format of repetition columns during data import into a HiRDB table, and creation of a binary-format file when 2 is specified in the XTNLDFLT environment variable

Extracted data		Contents of output data1						Imported data2
There are elements	All	N	NVF1	E1	...	NVFN	EN	There are elements
	Some	n	NVF1	E1	...	NVFn	En	There are some elements
There are no elements	Null value in entire column	(Offset is set to 0 and no column data is created)						Null value in entire column
	Null value in all elements	N	NVF1	...	NVFN	...		Null value in all elements

N: Maximum number of elements (2-byte area)

n: Actual number of elements (2-byte area)

E_x: Element x; this is not created when null value flag is 1.

NVF_x: Null value flag x; 1-byte area

The following table describes the relationship between the contents of the null value flag and element:

Extracted data	Null value flag	Whether or not element information is created
Null value	1	Not created. There is only null value flag.
Non-null value	0	Created.

¹ Indicates each element contained in the output data from left to right. A blank column means that there is no applicable element.

² Indicates the imported data if data is imported into a HiRDB table.

HiRDB Dataextractor can extract and import abstract data type (ADT) columns. The abstract data type supported by HiRDB Dataextractor Version 5.0 and later is the SGMLTEXT type.

The SGMLTEXT type defines original text data as having the BLOB attribute. HiRDB Dataextractor can extract such original text data.

This section explains how to extract SGMLTEXT-type data.

(a) Specifying environment variables

To extract SGMLTEXT-type data, you must specify the following environment variables:

• XTLOBBUFSIZE environment variable
  Specify the maximum length of the real data in the SGMLTEXT-type column with the BLOB attribute.
  You can use the xtrep command to obtain the maximum length of the real data. You do this by using HiRDB Text Search Plug-in's extracts abstract data type function to specify in the column name specification file the selection expression to be used to obtain the maximum length of the SGMLTEXT-type column that is to be subject to extraction, Then, you execute the xtrep command, and specify the obtained value in the XTLOBBUFSIZE environment variable.
  
• XTERRLEVEL environment variable
  Specify the value 1 in this environment variable so that processing will be canceled in the event of an error.
  

(b) Specifying the xtrep command

To extract SGMLTEXT-type data, you must specify the following information in the xtrep command:

• Column name specification file in the -s option
  Use the extracts abstract data type function to specify in the column name specification file the SGMLTEXT-type column and other columns to be extracted, and then execute the xtrep command. For an execution example, see (f) Example 6 in (2) Execution examples of 4.4 Examples.
  

(c) Notes

• To extract SGMLTEXT-type data, all the following conditions must be satisfied:
  
  • The source and target systems must have the same abstract data type definitions (type name, number of attributes, attribute data type, etc.).
  • The name of the constructor function must be the same as the name of the abstract data type.
  • The source and target systems must have the same abstract data type definitions for the HiRDB plug-in (the text type definitions for the SGMLTEXT type).
• When processing involves only file creation, a LOB input file is created and stored in the LOB input file storage directory for each LOB data item in the SGMLTEXT-type column with the BLOB attribute.
  Note that you can store this LOB data in the input file by specifying the SGMLTEXT data type in the import information file.