Home

	Expand Search

Access Service User's Guide DirectConnect for AS/400

Chapter 10 Using Bulk Copy Transfer

How Bulk Copy transfer statements are processed

How Bulk Copy transfer statements are processed

Bulk copy transfer processing occurs in the following sequence:

• The client application initiates a transfer request.

• The access service receives the transfer request and executes the sourceselectstatement against the source database to retrieve data, including column datatypes, length, precision, and scale.

• The access service queries the target table for a description of the target table columns. Then, it compares this information to the structure of the sourceselectstatement result set for these criteria:

  • The target table must have at least as many columns as the result set.

  • The datatype of each result set column must be convertible to the type of its target column.

  If either of these tests fails, a structural error occurs, and the access service stops processing the transfer.

• If the transfer statement includes the with replace or truncate phrase, the access service deletes data in the target table, if the user ID of the person executing the request is authorized to do so. If the user ID is not authorized, the transfer fails.

• The access service maps the columns from the result set to the columns in the target table in the order in which they appear. The access service checks the nullabilty of all columns in the target table that do not have corresponding columns in the result set. If the columns are non-nullable, the transfer fails.

• The access service prepares an insert or replace statement for execution against the target table.

• The access service retrieves rows from the sourceselectstatement and executes the insert statement against the target database for each row retrieved.

• If conversion errors occur as rows are inserted (for example, a value is out of range), the invalid columns are handled according to the values set in the following properties:

  • CharConvertError

  • NumConvertError

  • DatetimeConvertError

  • DefaultDate

  • DefaultTime

  • DefaultNum

  Then, the transfer continues processing. If the SendWarningMessages property is set to yes, the access service sends a warning message to the client application.

• One of the following occurs:

  • If TransactionMode is set to short, the access service issues a commit after each insert or replace, regardless of any conversion errors.

  • If TransactionMode is set to long, the access service issues a commit at the end of the transfer or after each batch of BulkCommitCount rows if BulkCommitCount is not set to zero. This commit occurs regardless of any conversion errors.

Guidelines for processing Bulk Copy values

To avoid errors in the bulk copy transfer process, review the following guidelines that apply to values.

Character datatypes (CHAR, VARCHAR, TEXT) can be converted to any other datatype. Conversely, every datatype can be converted to character data. However, you must be sure the character string is convertible to the target datatype. For example, the character string "450" can be converted to a numeric datatype such as INTEGER or DECIMAL, but the character string "Hello" causes a value error going to a numeric datatype.

Numeric datatypes can be converted to other numeric datatypes or to character datatypes. However, they cannot be converted to binary or date datatypes.

Additional guidelines are:

• All numeric conversions use truncation.

• Any loss of digits to the left of the decimal results in an error. For example, an integer of value 123 cannot be converted to a decimal(4,2) value without losing a digit to the left of the decimal point.

• Any loss of digits to the right of the decimal point is not considered an error. For example, a float of value 123.456 is truncated to an integer of value 123 without error.

• When you transfer data from a decimal column to a float column, the precision of the result is not better than the precision of the target column. For example, if you transfer data from a decimal(15,0) column to a float column, then back to a decimal(15,0) column, the results in the target decimal(15,0) column do not match the results of the source decimal(15,0) column, due to the float column precision.

Date datatypes can be converted to other date datatypes or to character strings. However, they cannot be converted to numeric or binary datatypes.

Binary datatypes can be converted to other binary datatypes and to character strings. However, they cannot be converted to numeric or date datatypes.

ASCII to EBCDIC translation does not occur if the source column, the destination column, or both are BINARY datatypes.