Home

	Expand Search

Access Service User's Guide DirectConnect for AS/400

Chapter 9 Understanding the Transfer process

Chapter 9

Understanding the Transfer process

This chapter describes several concepts of the transfer process, covered in the following sections:

• General description of the Transfer process

• Transfer targets

• Datatype conversion for Transfer processing

• Transfer errors and error handling

• Error reporting for Transfer processing

• Controlling processing with TransferErrorCount property

• Backward compatibility

All references to Adaptive Server 12.0 apply to SQL Server 11.1 and later, unless otherwise indicated.

General description of the Transfer process

The transfer process allows you to transfer rows and columns of data between tables in multiple databases from a client application. Based on your needs and limitations, you can select from one of two transfer options: bulk copy and destination-template.

Bulk copy and Destination-Template transfer

Bulk copy transfer and destination-template transfer processes differ as follows:

• The bulk copy transfer process allows you to quickly and directly transfer large amounts of data that is compatible between two databases.

• The destination-template transfer process allows you the flexibility and control to perform some action on the data before sending it to the target database, by inserting it in a template.

The following table further describes the conditions that determine the type of transfer you select.

Comparison of two transfer command types

Use bulk copy transfer to...	Use destination-template transfer to...
Execute the transfer quickly	Exercise more control over the transfer
Perform implicit datatype conversions	Move tables of data in which you need to explicitly specify datatype conversion, such as when the data is structurally incompatible
Move entire tables of data in which column types are compatible between the source and destination databases: transfer to secondary_connection; with replace into target_table; select col1, col 2 from source_table	Perform arbitrary actions against a target database using input from the source database, such as moving tables when the source table has more columns than the target: transfer to secondary_connection; select col2, col3 from source_table; insert into target_table values (?,?)

In both types of transfer, the transfer statement must be the only statement in a request.

For detailed information about bulk copy transfer, see Chapter 10, "Using Bulk Copy Transfer"

For detailed information about destination-template transfer, see Chapter 11, "Using Destination-Template Transfer"

Description of terms in the Transfer process

Following are terms used in the transfer process discussion.

This refers to the DBMS that is always implied in the transfer statement. For DirectConnect for AS/400 access services, this is an AS/400 database.

This database is another DBMS that is defined in the connection string within the transfer statement. The secondary database can be a database that is targeted by another access service or it can be an ASE. Always specify the secondary database in transfer statements.

The source is defined as the database the data is coming from.

This database is where the data is being sent, also called the target. Either the primary or secondary database can be the source or destination, depending on whether you use the transfer from or transfer to command.

The following figure shows the access service configured to access a particular AS/400 database, which is the primary database. The secondary database is an Adaptive Server database located on the LAN. The client application can transfer data from the AS/400 to Adaptive Server, or from Adaptive Server to the AS/400.

During a transfer:

• Data flows from a table in the source database, through the access service, to the target database. Although the client application initiates the transfer, the data does not flow through it.

• The access service becomes a client to the secondary database.

Transfer direction

You can transfer data in either of two directions:

• A transfer to statement transfers data to the secondary database from the primary database, the AS/400. This means that the primary database becomes the source database to the secondary database, which is the target.

• A transfer from statement transfers data from the secondary database to the AS/400. This means that the secondary database becomes the source database to the primary database, the AS/400, which is the target.

For example, when you execute a bulk copy transfer from statement, the secondary database (either Adaptive Server or another database) is the source of the data to be transferred. The primary database, AS/400, becomes the destination database, or target.

For implications of using one transfer direction over another, see "Datatype conversion for Transfer processing" .

Unit of Work

A unit of work is one or more requests that execute, commit, or roll back as a group. Following are descriptions of a unit of work for bulk copy and destination-template transfer.

Unit of work is based on the setting of the BulkCommitCount property.

• If BulkCommitCount is set to 0, the entire transfer is treated as a unit of work. The access service performs a commit after the last row of data is inserted into the target table, even if value errors occurred for individual rows of the transfer. (For information about value errors, see .)

• If BulkCommitCount is set to a non-zero value, each block of BulkCommitCount rows is treated as a unit of work. The access service issues a commit after each block of BulkCommitCount rows. For example, if BulkCommitCount is set to 50, each block of 50 rows is treated as a unit of work, and a commit is issued after each 50 rows.

When a destination-template transfer statement moves data from Adaptive Server to the AS/400, the access service automatically sets the StopCondition property to none. Subsequent commit and rollback processing is determined by whether short or long transactions are in effect:

• If short transactions are in effect, the access service issues a commit after each batch, whether or not errors occurred in the request. In this case, each batch of inserts is a unit of work.

• If long transactions are in effect, the access service issues a commit at the end of the entire transfer. Because StopCondition is set to none, the access service never issues a rollback. In this case, the entire transfer is a unit of work.

The value set in the TransferBatch configuration property determines the size of the batch.

Transfer targets

The transfer statement allows you to move data in either direction between the AS/400 and:

• Adaptive Server (previously known as SQL Server)

• ASE/CIS (formerly OmniConnect)

• Other access service and Legacy products or services:

  • DB2

  • Informix

  • AS/400

  • Oracle

  • Microsoft SQL Server

  • Any other database supported

  • InfoHub

• Any Open Server application that supports SQL

Datatype conversion for Transfer processing

The two transfer types handle datatype conversion differently. After converting the incoming source database datatypes into appropriate Open Client/Server datatypes, the access service does one of the following:

• For bulk copy transfer processing, the access service converts Open Client/Server datatypes into the datatypes of the target columns. If the source and target columns have incompatible datatypes, the transfer ends with an error.

• For destination-template transfer processing, the access service uses the datatype qualifiers specified with the question marks in the template. When the questions marks do not have qualifiers, the access service uses the datatypes of the source to determine default qualifiers.

When conversion fails, several configuration properties control how the error is handled. These properties are discussed on .

For more information about these configuration properties, see Chapter 2, "Configuring the DirectConnect Access Service Library"

When you transfer data between two access services (such as DB2 to AS/400, AS/400 to DB2), we recommend that you execute the transfer from command from the AS/400 to guarantee native datatype mapping. Also, the transfer from command is the only way you can transfer text and image columns between access services.

Transfer errors and error handling

Transfer processing errors can occur due to:

• Structural errors, for which the access service cancels the transfer process before any rows are transferred

• Value errors, which occur on a row-by-row basis during the transfer process

Each of these transfer error types is explained in the following sections.

Structural errors

A structural error can be one of two types:

• Incompatible datatypes

• An incompatible number of columns

Each type is described in the following sections.

Datatypes are incompatible when source and target table datatypes cannot be mapped to one another. For example, binary to datetime transfers are not allowed.

• In bulk copy transfers, before the access service moves any data from the source database to the target database, the access service compares datatypes in the source to datatypes in the target.

• In destination-template transfers, the access service compares the source table datatypes to the qualifiers set in the destination-template statement.

For both transfer types, if any columns have incompatible datatypes, the access service cancels the transfer, without attempting to transfer any rows.

When the access service detects an unequal number of columns between the source and the target, a structural error may occur.

• For bulk copy transfer:

  • If the number of source columns exceeds the number of target columns, the access service cancels the transfer.

  • If the number of target columns exceeds the number of source columns, processing continues if all of the extra columns of the target table accept nulls. If any one of the extra columns in the destination table does not accept nulls, the access service cancels the transfer.

• For destination-template transfer:

  • If the number of columns returned by the sourceselectstatement exceeds the number of question marks in the destination-template transfer statement, the access service cancels the transfer.

  • If the number of question marks in the destination-template transfer statement exceeds the number of source columns, the keyword null replaces the extra question marks for each row of the transfer.

Value errors

Value errors occur during transfer processing when the value being inserted has one of the following characteristics:

• It cannot be converted to the datatype of the target column.

• It is out of range for the target column's datatype.

The access service handles these errors using the following properties:

• CharConvertError

• NumConvertError

• DatetimeConvertError

• DefaultDate

• DefaultTime

• DefaultNum

If the SendWarningMessages property is set to yes, the access service sends a message to the client application when it encounters value errors.

In addition, as mentioned in "Datatype conversion for Transfer processing" , the preceding properties can be used to fill in default values when datatype conversion fails during both types of transfer.

For more information about the configuration properties mentioned here, see Chapter 2, "Configuring the DirectConnect Access Service Library"

For information about values that cause errors for the AS/400 access service during bulk copy transfer, see "Errors from data values" .

Error reporting for Transfer processing

You can use one of three methods to obtain error information about transfer processing:

• Include the with report phrase in the transfer statement. When you include this phrase, the access service returns a result set containing one VARCHAR column and one row that indicates the number of rows transferred, rejected, and modified during processing.

• Immediately after a transfer processes, execute the following:

  select @@ RowCount

  select @@RejectedRowCount

  select @@DefaultedRowCount

  These global variables return the number of rows that were transferred, as well as the rejected or defaulted rows.

• Set SendWarningMessages to yes, so that the access service returns warning messages to the client when data conversion errors occur.

Controlling processing with TransferErrorCount property

During transfer processing, the access service automatically sets the StopCondition property to none. Then, it uses the value set in the TransferErrorCount property to determine how many error rows are allowed before processing stops. You can set this value with the following statement:

set TransferErrorCount nnn

The default setting is 0 (zero), which causes the access service to ignore errors.

Backward compatibility

We recommend that you use the transfer from command from the AS/400 when you transfer data between:

• An access service and an MDI Database Gateway

• Two access services

• Two MDI Database Gateways

Using transfer from guarantees native datatype mapping.