HiRDB Dataextractor enables the user to specify desired information in the following SQL statements that are issued when data is extracted from a HiRDB table.

• LOCK TABLE statement
• SELECT statement

Table 4-4 explains the scope of this function.

Table 4-4 Scope of the SQL user specification function during data extraction

Y: Applicable

--: Not applicable to HiRDB Dataextractor alone.

For details, see the manual VOS3 Database Extraction Program XDM/XT.

(a) Use with XDM/XT linkage

To use this function with XDM/XT linkage, the following specifications are required:

• XDM/XT's JXUMCTL control statement
  RDBDEF and EXTRACT statements
  
• HiRDB Dataextractor's environment variable information setup file (source system)

Table 4-5 lists the environment variables that must be specified in the environment variable information setup file.

Table 4-5 Environment variables to be set to use the SQL user specification function (applicable to XDM/XT linkage)

As required: Specify as appropriate.

^* This environment variable is required when the RDBDEF statement is specified in XDM/XT's JXUMCTL control statement.

For details about the environment variables, see 2.2.3 Specifying environment variables.

(b) Use with HiRDB Dataextractor linkage

To use this function with HiRDB Dataextractor linkage, you execute the xtrep command.

Environment variable

If you use this function, make sure that the following environment variable is specified in the source system:

• XTSQL

For details about the environment variables, see 2.2.3 Specifying environment variables.

Command options

Table 4-6 describes the options whose specification is related to this function.

Table 4-6 Specification of xtrep command options when the SQL user specification function is used during data extraction

M: Mandatory

O: Optional

N: Not specifiable

--: Not specified

• -g [lock-information-file-name]
  The combination of this option and the XTSQL environment variable is used to perform lock control on the table from which data is extracted. Specification of this option is mandatory when 1 is specified in the XTSQL environment variable.
  The following table provides the details of the lock information file specification:
  

  XTSQL	-g option		Action
  0	Omitted	--	Issue LOCK TABLE using the table name entered on the command line.
  	Specified	No value specified	Do not issue LOCK TABLE.
  		Value specified	Issue LOCK TABLE based on information in the lock information file.
  1	Omitted	--	Error
  	Specified	No value specified	Do not issue LOCK TABLE.
  		Value specified	Issue LOCK TABLE based on information in the lock information file.

• -r [authorization-identifier,]table-identifier
  This option or the -R option must be specified when 1 is specified in the XTSQL environment variable.
  
• -R [{bin|dat}]
  This option or the -r option must be specified when 1 is specified in the XTSQL environment variable.
  
• -s column-name-specification-file-name
  If necessary, specify the absolute path of the column name specification file.
  
• -w table-expression-specification-file-name
  This option cannot be specified when 1 is specified in the XTSQL environment variable.
  
• [authorization-identifier,]table-identifier
  Specify this option when the XTSQL environment variable is omitted or 0 is specified in the XTSQL environment variable.
  
• table-expression-specification-file-name
  Specify this option when 1 is specified in the XTSQL environment variable.
  

For details about these options, see 4.2.2 xtrep command.

File specification method

This subsection describes the specification of files in options.

• Lock information file:
  This file specifies the lock options in the LOCK TABLE statement.
  Specify the file contents as follows, as appropriate to the specification of XTSQL:
  

  XTSQL	Specification
  0	Specify the lock options that follow LOCK TABLE table-name
  1	Specify the lock options that follow LOCK TABLE

• Table expression specification file:
  This file specifies the table expression in the SELECT statement.
  Specify the file contents as follows, as appropriate to the specification of XTSQL:
  

  XTSQL	Specification
  0	Specify the table expression that follows FROM table-name in the SELECT statement
  1	Specify the table expression that follows FROM in the SELECT statement

Note

When 1 is specified in the XTSQL environment variable, no column name can be specified in the null value information file. If you specify information such as a default null value or data format of repetition columns, specify applicable field numbers. Specifying column names results in an error.

When data is imported into a HiRDB table, HiRDB Dataextractor starts HiRDB's database load utility (pdload). The loader parameter specification function enables you to specify desired parameters for pdload.

Table 4-7 explains the scope of this function.

Table 4-7 Scope of loader parameter specification function

Y: Applicable

(a) Use with XDM/XT linkage

To use this function with XDM/XT linkage, the following specifications are required:

• XDM/XT's JXUMCTL control statement
  HIRDDEF statement's PDLODPRM operand
  LOAD statement's LPRMPATH and PDSRPATH operands
  
• HiRDB Dataextractor's environment variable information setup file (target system)
  XTLPRMxxxx
  XTPDSRxxxx
  Specify the environment variables as required. For details about the environment variables, see 2.2.3 Specifying environment variables.
  

(b) Use with HiRDB Dataextractor linkage

To use this function with HiRDB Dataextractor linkage, execute the xtrep command.

Environment variables

When you use this function, specify the following environment variables as required in the target system's environment variable information setup file:

• XTLPRMxxxx
• XTPDSRxxxx

For details about the environment variables, see 2.2.3 Specifying environment variables.

Command options

Table 4-8 describes the options whose specification is related to this function.

Table 4-8 Specification of the xtrep command options when the loader parameter specification function is used

M: One of these items is required in order to use this function.

O: This item is optional.

1, 1': These items are mutually exclusive.

2, 2': These items are mutually exclusive.

• -I [XTPDCFxxxx][,XTLPRMxxxx][,XTPDSRxxxx]
  Specify at least one value. You can specify multiple values in any order by using the comma (,) as the delimiter.
  

XTPDCFxxxx (name of pdload control information environment variable)

Specify this information as necessary. For details about the environment variables, see 2.2.3 Specifying environment variables.

XTLPRMxxxx (name of pdload command line information environment variable)

To use this function, ensure that either the XTLPRMxxxx or the XTPDSRxxxx environment variable is specified.

Notes

• When you specify this environment variable, none of the following options can be specified:
  -d option
  -i option
  -l option
  -n option
  -z option
  
• When the name of this environment variable is specified, HiRDB Dataextractor does not specify any parameter other than the table name, control information file name, -b, -k, -v, -W, or -c option when pdload starts; therefore, specify all necessary options in the pdload command line information file.

XTPDSRxxxx (name of pdload control information file's source statement information environment variable)

To use this function, ensure that either the XTLPRMxxxx or the XTPDSRxxxx environment variable is specified.

Note

When you specify this environment variable, do not specify the -q option, because it cannot be specified.

This function converts the character codes of the extracted data according to specified environment variables and then imports the data.

Table 4-9 explains the scope of this function.

Table 4-9 Scope of the character code conversion function

--: Not applicable to HiRDB Dataextractor.

For details, see the manual VOS3 Database Extraction Program XDM/XT.

Y: Applicable

For details about the character code sets that can be converted by this function, see 3.1.4 Converting the character codes of extracted data.

(a) Using the character code conversion function

By specifying environment variables and a null value information file, you can convert the character codes during data import processing.

Environment variables

Table 4-10 shows the environment variables that are required to use this function. For details about the location where the environment variables are to be set, see 2.2.3 Specifying environment variables.

Table 4-10 Environment variables required to use the character code conversion function

As required: Specify as appropriate

XTLOCALE

Specify a system of character codes used by the source and target systems. For details about the environment variables, see 2.2.3 Specifying environment variables.

The following table shows the combinations of environment variable values for the source and target systems:

Source	Target
	sjis	euc	utf-8	unknown
sjis	--	Y	Y	--
euc	Y	--	Y	--
utf-8	Y	Y	--	--
unknown	--	--	--	--

Y: Character codes specified by the source are converted to the character codes specified by the target.

--: Character codes are not converted.

XTUNDEF

Specify how to handle undefined codes. For details about the environment variables, see 2.2.3 Specifying environment variables.

Null value information file

To specify whether or not the character codes are to be converted for each column, specify a null information file in the -v option of the xtrep command.

For details about how to specify this file, see 4.2.4 Contents of files specified with the xtrep command.

(b) Converting Gaiji codes

To convert Gaiji characters of the SJIS or EUC character code sets, use a mapping table for converting character codes. The base mapping table that is used for conversion is installed automatically when HiRDB Dataextractor is installed. For details about the directory and file names, see (2) Directories and files created by HiRDB Dataextractor in 2.2.2 Installing HiRDB Dataextractor.

In the initial status, no Gaiji codes have been defined in this base mapping table. If you use Gaiji characters during conversion, you must update character code conversion mapping tables.

When you update mapping tables, verify that both the following mapping tables are updated, so that consistency is maintained:

• Mapping table used to convert the source system's locale to the target system's locale
• Mapping table used to convert the target system's locale to the source system's locale

Editing and referencing a mapping table for converting character codes

You use the xtccnvedt command to define Gaiji codes in a mapping table or to reference a table's contents.

HiRDB Dataextractor must not be running while a mapping table is being edited.

Function

On the basis of the mapping table that was provided during installation of HiRDB Dataextractor, create a new mapping table in the following directory:

/opt/HIRDBXT/lib/usermap/

If a mapping table already exists in this directory, it will be overwritten.

Execution environment

Execute the command as superuser on the target machine to which data is to be imported.

Command

To update a mapping table

xtccnvedt -w
          -f {sjis|euc|ucs2}
          -t {sjis|euc|ucs2}
          -d conversion-definition-file-name

To reference a mapping table

xtccnvedt -r
          -f {sjis|euc|ucs2}
          -t {sjis|euc|ucs2}
          -o output-file-name
         [-s output-start-code]
         [-e output-end-code]

Options

To update a mapping table

• -w
  Specify this option to update a mapping table.
  
• -f {sjis|euc|ucs2}
  Specify the source code set:
  sjis: SJIS character codes
  euc: EUC character codes
  ucs2: UTF-8 character codes
  

Note

If the character code set is UTF-8, specify ucs2.

• -t {sjis|euc|ucs2}
  Specify the target code set:
  sjis: SJIS character codes
  euc: EUC character codes
  ucs2: UTF-8 character codes
  

Note

If the character code set is UTF-8, specify ucs2.

• -d conversion-definition-file-name
  Specify the name (1-127 characters) of the conversion definition file that defines the information to be updated in the mapping table. If you specify a relative path, the command assumes that it is relative to the current directory.
  

To reference a mapping table

• -r
  Specify this option to reference a mapping table.
  
• -o
  Specify the name (1-127 characters) of the file to which the results of referencing the mapping table are to be output. If you specify a relative path, the command assumes that it is relative to the current directory.
  The results of referencing the mapping table are output in the format defined in the conversion definition file in the order of 1-byte, 2-byte, and 3-byte code character strings in ascending order of the source character codes.
  The command converts only those codes whose target codes have been defined; it does not output any codes for which no target code has been defined.
  
• -s output-start-code
  Specify as a hexadecimal character string the source character code where referencing is to begin. If this option is omitted, the command starts referencing at the beginning of the mapping table.
  

Note

Specify output-start-code as follows:

• Express a 1-byte code as 2 characters, a 2-byte code as 4 characters, and a 3-byte code as 6 characters (applicable only when the source codes are EUC). An error results if the number of characters does not match any of these.
• If a specified 3-byte code does not begin with 8f, an error results.
• If the specified value is greater than output-end-code specified in the -e option, an error results.

• -e output-end-code
  Specify as a hexadecimal character string the source character code where referencing is to end. If this option is omitted, the command references through the end of the mapping table.
  

Note

Specify output-end-code as follows:

• Express a 1-byte code as 2 characters, a 2-byte code as 4 characters, and a 3-byte code as 6 characters (applicable only when the source codes are EUC). An error results if the number of characters does not match any of these.
• If a specified 3-byte code does not begin with 8f, an error results.
• If the specified value is smaller than output-start-code specified in the -s option, an error results.

Note

The following table shows the code set combinations that can be specified with the -f and -t options.

-f option	-t option	Map file subject to processing
sjis	euc	jis2euc.map
	ucs2#	jis2ucs2.map
euc	sjis	euc2jis.map or eucg2j.map
	ucs2#	euc2ucs2.map or eucg2u.map
ucs2#	sjis	ucs22jis.map
	euc	ucs22euc.map

Note: If the specified source and target code sets are the same, the command does not update the character code conversion mapping table.

#: If the character code set is UTF-8, specify ucs2.

Conversion definition file

In the conversion definition file, you specify in the following format a source character code in the character code conversion mapping table that is to be updated and the target character code:

 
source-character-code,target-character-code
 

Rules

• Define 1 entry per line.
• Specify the source and target character codes as hexadecimal character strings (a-f are not case-sensitive).
• A line beginning with the hash mark (#) is handled as a comment. To specify a comment following a conversion definition, you must specify at least 1 space or tab between the definition and the hash mark.
• Express a 1-byte code as 2 characters, a 2-byte code as 4 characters, and a 3-byte code as 6 characters (applicable only to EUC character codes). An error results if the number of characters does not match any of these.
• If a specified 3-byte code does not begin with 8f, an error results.
• The command ignores any specification following the first space after the target-character-code.
• The command ignores any space or tab before and after each code.

(c) Code conversion error handling procedure

The code conversion error handling procedure depends on the cause of the error, as described below.

(i) When the resulting data length is greater than the size of the receiving area

The command discards the excess data and sets the remaining data.

(ii) When the source data (in units of columns) ends with part of a 2- or 3-byte code

• When the target attribute is fixed-length
  The command sets the data up to the erroneous data and sets spaces (0x20) in the remaining area.
  
• When the target attribute is variable-length
  The command sets data up to the erroneous data.
  

When the command detects a conversion error, it issues an error message (JXU7230I) and processes the column following the erroneous column. If the number of conversion error records reaches the cancellation count, the command issues an error message (JXU7230E) and terminates processing.