Home

	Expand Search

Access Service User's Guide DirectConnect

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

• Processing Bulk Copy transfer from statements

• Processing Bulk Copy transfer to statements

• Processing for Bulk Copy transfer statements

• Errors from data values

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)

• Replace existing rows (replace)

Use the bulk copy transfer statement to copy large amounts of data between similar tables.

General rules for Bulk Copy Transfer

Generally, 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 the structure of the source table. For more information, see "Structural errors" .

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 Sybase 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 Sybase 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.

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 DB2 to append to a Adaptive Server table.

c:>isql -Sdb2 -Udb2user -Pdb2pass

1>transfer to 'ss ssuser sspass' ;

2>with insert into sstable1 ;

3>select * from db2table3

4>go

Result: Data from the DB2 table named db2table3 is appended to the SQL Server table sstable1.

Task: Transfer data from SQL Server to replace data in D B2.

c:>isql -Sdb2 -Udb2user -Pdb2pass

1>transfer from 'ss ssuser sspass' ;

2>with replace into db2table1 ;

3>select * from sstable3

4>go

Result: Data from the SQL Server table named sstable3 replaces the data in the DB2 table db2table1.

Task: Transfer data from two tables in DB2 to a single SQL Server table.

c:>isql -Sdb2 -Udb2user -Pdb2pass

1>transfer to 'ss ssuser sspass' ;

2>with insert into sstable1 ;

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

4>from db2table1a, db2table2b

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 DB2 tables, based on a join of social security numbers. The information is appended to the SQL Server table called sstable1.

Processing Bulk Copy transfer from statements

Use transfer from when you want to transfer data from a secondary database, either Sybase Adaptive Server or another database through another access service, to the primary database. The primary database, DB2, 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 Sybase Adaptive Server to replace data in the DB2 table named db2table1.

Datatype conversion for transfer from processing

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 and set statements.Each access service specification will include specific definitions of datatype mapping for that target DBMS. The access service converts these datatypes into the actual datatypes of the target columns. If the access service cannot convert the columns, the access service ends the transfer with an error.

The access service performs datatype conversions for bulk copy transfers. These conversions are defined within the access service and are not affected by the settings of the datatype conversions configuration properties. Each access service specification includes specific definitions of datatype mapping for that target DBMS.

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

Processing Bulk Copy 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 the CIS functionality in ASE (formerly 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 DB2 tables. The information is appended to the SQL Server table called sstable1. The transfer is based on a join of social security numbers.

Datatype conversion for transfer to processing

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 and set statements.Each access service specification will include specific definitions of datatype mapping for that target DBMS. The access service converts these datatypes into the actual datatypes of the target Open Server columns. If a column does not match, the access service ends the transfer with an error.

The access service performs datatype conversions for bulk copy transfers. These conversions are defined within the access service and are not affected by the settings of the datatype conversions configuration properties. Each access service specification includes specific definitions of datatype mapping for that target DBMS.

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

Authorization for transfer to a Sybase Adaptive Server

When you execute a transfer to the Sybase Adaptive Server, you must have create procedure authorization on the tempdb database on the Sybase 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 a Sybase Adaptive Server.

To grant create procedure permission to public:

• Log onto the target Sybase 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 Sybase Adaptive Server:

      1> quit

Processing for Bulk Copy transfer statements

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

Details of which particular values cause errors are access service-specific.

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 converted 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 source and destination columns are BINARY datatypes.

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

• DB2 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 DB2 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 DB2 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

• DB2 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 DB2 Output Datatype column.

Date datatype errors

This section uses the following as source date datatypes:

• Open Server datatype:

  • CS_DATETIME

  • CS_DATETIME4

• DB2 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 DB2 Output Datatype column.

Binary datatype errors

This section uses the following as source binary datatypes:

• Open Server datatype:

  • CS_BINARY

  • CS_VARBINARY

  • CS_IMAGE

• DB2 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 DB2 Output Datatype column.