Home

	Expand Search

Access Service User's Guide DirectConnect for AS/400

Chapter 10 Using Bulk Copy Transfer

Chapter 10

Using Bulk Copy Transfer

This chapter describes the bulk copy transfer statement and covers the following topics:

• General description of Bulk Copy Transfer

• Syntax for Bulk Copy transfer statements

• Guidelines for processing Bulk Copy values

• Datatype conversion for transfer from statements

• Datatype conversion for transfer to statements

• Errors from data values

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

General description of Bulk Copy Transfer

The bulk copy transfer process initiates a direct transfer of data between two databases from the client application. Bulk copy transfer allows you to:

• Use SQL statements to append rows (insert) and replace existing rows (replace or truncate)

• Copy large amounts of data between similar tables.

When you use a bulk copy transfer statement, the following restrictions apply:

• The transfer statement must be the only statement in a request.

• The table into which you want to transfer data (the target table) must already exist; the transfer statement does not create new tables in the transfer target.

• The structure of the target table must be compatible with rows selected from the source table.

Syntax for Bulk Copy transfer statements

The required syntax for a bulk copy transfer statement is:

transfer [with report]

{to | from} 'secondaryname
userid password';

with {insert | replace | truncate} into tablename;

sourceselectstatement

where:

• transfer is the command to initiate a data transfer.

• with report is an optional phrase specified in the first line of the transfer statement. It instructs the access service to return processing information to the client application, after the transfer completes processing.

  This information is returned as a result set consisting of a VARCHAR column and a single row. The row contains the number of rows transferred, rejected, and modified during processing.

• {to | from} indicates the direction of the transfer:

  • to specifies that the data transfer originates from the primary database and sends data to the secondary database.

  • from specifies that the data transfer originates from the secondary database and moves data to the primary database.

• 'secondaryname userid password' is a three-part character string, enclosed in single or double quotes in the order shown, that provides the information needed to connect to the secondary database:

  • secondaryname is the name used to identify the secondary database, which can be an access service connected to another database. This is the name of an entry in your platform-specific interface file, such as sql.ini in Windows.

  • userid and password must be valid on the secondary database. You can substitute an asterisk for password, if a password is not set.

• with {insert | replace | truncate} into specifies whether the data is appended onto the target table (insert) or the existing data is deleted and replaced (replace). The truncate option is equivalent to replace, except when the target is Adaptive Server, in which case the truncate option causes the DirectConnect to issue a truncate command against the target table, rather than delete.

  In a transfer to Adaptive Server, the replace option deletes rows in the target table. The truncate option truncates the target table (and is faster than delete), but a truncation cannot be rolled back if a subsequent error occurs. For all other targets, replace and truncate are equivalent.

• tablename specifies the table into which data is inserted or replaced.

• sourceselectstatement specifies a SQL statement that is executed against the source database to produce the result set used in the transfer. This statement can be as complex as the source database will accept, including stored procedures. SQL transformation is not performed on the sourceselectstatement. It must be in the source database's SQL dialect, such as Transact-SQL or AS/400 SQL.

Examples

This section contains some examples of bulk copy transfer statement syntax that are submitted through ISQL. The following table shows the database and user information used in the examples.

The following examples assume that the source and target databases are datatype-compatible.

Task: Transfer data from the AS/400 to append to an Adaptive Server table.

c:>isql -Sas4 - Uas4user -Pas4pass

1>transfer to 'ss ssuser sspass' ;

2>with insert into sstable1 ;

3>select * from as4table3 ;

4>go

Result: Data from the AS/400 table named as4table3 is appended to the Adaptive Server table sstable1

Task: Transfer data from Adaptive Server to replace data in the AS/400.

c:>isql -Sas4 -Uas4user -Pas4pass

1>transfer from 'ss ssuser sspass' ;

2>with replace into as4table1 ;

3>select * from sstable3 ;

4>go

Result: Data from the Adaptive Server table named sstable3 replaces the data in the AS/400 table as4table1.

Task: Transfer data from two tables in the AS/400 to a single Adaptive Server table.

c:>isql -Sas4 -Uas4user -Pas4pass

1>transfer to 'ss ssuser sspass' ;

2>with insert into sstable1 ;

3>select a.ssno, a.name2, b.empid

4>from as4table1 a, as4table2 b

5>where a.ssno=b.ssno ;

6>go

Result: A social security number (ssno), a name (name2), and an ID (empid) are selected from two AS/400 tables, based on a join of social security numbers. The information is appended to the Adaptive Server table called sstable1.

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.

Datatype conversion for transfer from statements

Use transfer from when you want to transfer data from a secondary database, either Adaptive Server or another database through another access service, to the primary database. The primary database, the AS/400, is the target and is implied in the transfer statement; the secondary and source database is specified in the transfer statement.

The following figure shows the data flow of one of the preceding transfer from examples, which transfers data from sstable3 in Adaptive Server to replace data in the AS/400 table named as4table1.

The access service performs datatype conversions for bulk copy transfer from statements. These conversions are defined within the access service and are not affected by the settings of the datatype conversion configuration properties. The access service converts these datatypes into the actual datatypes of the target columns. If the access service cannot convert the columns, an error occurs. For information about errors, see "Transfer errors and error handling" and "Errors from data values" .

The following table shows the acceptable Open Server datatypes that the access service can convert into corresponding AS/400 datatypes. To save space, the CS_ prefix is removed from Open Server datatypes, and similar AS/400 datatypes are combined.

Datatype conversion for transfer to statements

Use the transfer to statement when you want to move data to a secondary database from the primary database. When you execute a bulk copy transfer using transfer to, the secondary database is the target, and the primary database is the source. You can use another access service, an MDI Database Gateway, Open Server, or OmniConnect as the secondary database.

The following figure shows the data flow of one of the preceding transfer to examples, in which a social security number (ssno), a name (name2), and an ID (empid) are selected from two AS/400 tables. The information is appended to the Adaptive Server table called sstable1. The transfer is based on a join of social security numbers.

The access service performs datatype conversions for bulk copy transfer to statements. These conversions are defined within the access service and are not affected by the settings of the datatype conversion configuration properties. The access service converts these datatypes into the datatypes of the target Open Server columns. If a column does not match, the access service ends the transfer with an error.

The following table shows the AS/400 datatypes that the access service can convert into corresponding Open Server datatypes. To save space, theCS_ prefix is removed from the Open Server datatypes, and Open Server datatypes with the same results are combined.

Authorization for transfer to an Adaptive Server

When you execute a transfer to an Adaptive Server, you must have create procedure authorization on the tempdb database on Adaptive Server; otherwise, you receive the following error message:

create procedure permission denied, database tempdb, owner dbo

Because tempdb is the Adaptive Server standard temporary database where CT-Library implements dynamic prepare and execute, the System Administrator must grant create procedure authorization so users can execute transfer to statements to an Adaptive Server.

To grant create procedure permission to public:

• Log onto the target Adaptive Server as "sa":

      c:\> isql -Usa -Psa_password -Ssql_servername

• Specify the tempdb database:

      1> use tempdb

      2> go

• Grant create procedure authorization:

      1> grant create procedure to public

      2> go

• Log off the target Adaptive Server:

      1> quit

Errors from data values

The following four tables show the values that cause errors for the access service during bulk copy transfer. These tables are divided into the four major groups of input datatypes:

• Character datatypes

• Numeric datatypes

• Date datatypes

• Binary datatypes

Each table shows the output datatype and the kind of error that can occur.

Character datatype errors

This table uses the following as the source character datatypes:

• Open Server datatypes:

  • CS_CHAR

  • CS_VARCHAR

  • CS_TEXT

• AS/400 datatypes:

  • CHAR

  • VARCHAR

The following table shows the errors that can occur when the access service converts the preceding character datatypes to those listed in the Open Server and AS/400 Output Datatype column.

In the Output Datatype column of the following four tables, the CS_ prefix is attached to the Open Server datatypes to distinguish them from AS/400 datatypes.

Numeric datatype errors

This section uses the following as source numeric datatypes:

• Open Server datatypes:

  • CS_BIT

  • CS_TINYINT

  • CS_SMALLINT

  • CS_INT

  • CS_REAL

  • CS_FLOAT

  • CS_DECIMAL

  • CS_MONEY

  • CS_MONEY4

• AS/400 datatypes:

  • SMALLINT

  • INTEGER

  • FLOAT

  • REAL

  • DECIMAL

The following table shows the errors that can occur when the access service converts the preceding source numeric datatypes to those listed in the Open Server and AS/400 Output Datatype column.

Date datatype errors

This section uses the following as source date datatypes:

• Open Server datatype:

  • CS_DATETIME

  • CS_DATETIME4

• AS/400 datatypes:

  • DATE

  • TIME

  • TIMESTAMP

The following table shows the errors that can occur when the access service converts the preceding source date datatypes to those listed in the Open Server and AS/400 Output Datatype column.

Binary datatype errors

This section uses the following as source binary datatypes:

• Open Server datatype:

  • CS_BINARY

  • CS_VARBINARY

  • CS_IMAGE

• AS/400 datatypes:

  • CHAR FOR BIT DATA

  • VARCHAR FOR BIT DATA

The following table shows the errors that can occur when the access service converts the preceding source binary datatypes to those listed in the Open Server and AS/400 Output Datatype column.