Fields settings

ID

The ID used to identify registered database table information

Business group

A business group for which general users have access rights.

Alternatively, a business group that is used as a filter condition only.

Comment

Comment about the database table information.

Database connection ID

The database connection ID of the database that is connected to when the relevant database-table information is used.

Specify the ID registered in the database connection information.

I/O category

Select either input settings or output settings.

Input

: Set the information to be used when inputting from the database.

Output

: Set the information to be used when outputting to the database.

Specification method

The method for creating SQL statement for I/O.

Simple

: Create SQL statement internally according to the specified parameters.

SQL statement

: Directly specify the issued SQL statement.

Table name

The table name that is the I/O target.

Sort order

The sort order for the input code.

If I/O category is Input, specify this item.

Do not sort

: Obtain record as is from table without sorting.

Ascending

: Sort specified fields in ascending order and obtain.

Descending

: Sort specified fields in descending order and obtain.

Field name

Specify the field names used in sorting.

If Sort order is Do not sort, you cannot specify this item.

If Sort order is either Sort in ascending order or Sort in descending order, you cannot omit this item.

Update method

The method for updating a table

If I/O category is Output, specify this item.

INSERT

: A registration operation (INSERT) is always performed.

INSERT
by using direct path loading function

: A registration operation (INSERT) is performed by using the Oracle direct path loading function. This method is displayed if the connection-target database is Oracle. Two sessions are used when connecting.

BULK INSERT

: If the database type is ODBC and the destination is Redshift or Snowflake, records are registered using BULK INSERT.

This option is displayed only when the destination is an ODBC database. If the destination is not Redshift or Snowflake, a connection error occurs.

UPDATE rows that match the key value

An update operation (UPDATE) is performed on the rows that match the key, and a registration operation (INSERT) is performed on the rows that do not match the key.

SQL statement

Specify the SQL statement issued for input. This can be specified if Input is set for I/O Category.

Only one SQL statement, of 99999 bytes or less, can be specified. Operation when two or more SQL statements are specified cannot be guaranteed. Use a SELECT statement for the SQL statement.

Update method

Specify the execution method for the SQL statement.

If I/O category is Output, specify this item.

Execute only SQL 1

: Normally, only SQL 1 is executed.

Execute both SQL 1 and SQL 2:

Execute SQL 1 and if SQL 1 is successful, execute SQL 2.

If SQL 1 fails, a conversion error will occur.

Execute SQL 2 only
if SQL 1 fails:

First execute SQL 1 and only if SQL 1 fails, execute SQL 2.

SQL1

Specify the SQL statement issued for output. This can be specified if I/O Category is set to Output.

Only one SQL statement, with 99999 bytes or less can be specified. Operation when two or more SQL statements are specified cannot be guaranteed. Do not specify a DDL string.

SQL2

The SQL statement issued for output.

If SQL execution method is other than Execute only SQL 1, specify this item.

Only one SQL statement with 99999 bytes or less can be specified. If 2 or more SQL statements are specified, correct operation cannot be guaranteed. Do not specify a DDL string.

SQL statement before beginning

The SQL statement that executes before the SQL statement for I/O.

You can specify multiple SQL statements.

Specify the SQL statement with 99999 bytes or less.

SQL statement after ending normally

The SQL statement that executes after the SQL statement for I/O successfully finishes.

You can specify multiple SQL statements.

Specify the SQL statement with 99999 bytes or less.

Field name

The name that identifies relevant fields

Fixed/Variable length

Select whether relevant field size is fixed or variable.

Fixed length

: Field is fixed length as specified by Size.

Variable length

: Field length varies for each record.

Maximum size

Specify the size from 1 to 9999999999, or 0 (unrestricted). If Fixed/Variable length is Variable length, specify the maximum size of the relevant fields as a number of bytes.

Size

If I/O Category is Input, use values from 0 to 9999999999 to specify the size. In the case of Output, use values from 1 to 9999999999. If Fixed/Variable length is Fixed length, specify the size of the relevant fields as a number of bytes.

Field type

The data type for the relevant field.

B

: binary

F

: floating point

I

: image, no conversion

M

: variable-length characters

N

: double-byte graphic characters

D

: date

Z

: numeric character string

W

: Unicode character string

Concatenation sequence

Specify the order in which the relevant field is to be used in the SQL statement.

You can specify this only when I/O Category is Output and Specification method is SQL statement specification.

For details, see DataMagic Operation Manual.

Status

The enabled or disabled flags for relevant fields.

You can use only Enabled fields as input fields or output fields when mapping.

Comment

Comment about each field of the database table.

Key

Specify whether the field is a key field.

*

: Indicates that the field is a key field.

Empty

: Indicates that the field is not a key field.

Search key value

Enter search character string of connection ID that specifies the database connection ID on the Database Table - Detailed Information screen.

Field name list

The following two items are displayed: the I/O Category on the Database Table - Detailed Information screen, and the list of the fields that can be specified for the Field name specified when performing simple specification.