Express transfer

To improve performance transferring bulk data between data sources, Sybase has a bulk copy transfer called “express transfer.” It transfers data faster than bulk copy transfer using ODBC bulk APIs. Because the new express transfer feature uses the same syntax as the DirectConnect bulk copy transfer, you can take advantage of express transfer without modifying your applications.

Availability

This new feature is available for the following ECDA products:

• DirectConnect DB2 UDB

• DirectConnect for Informix

• DirectConnect for Microsoft SQL Server

• DirectConnect for ODBC

To use this feature, you will need one of these products and an ODBC driver from another DirectConnect or Adaptive Server Enterprise (ASE) product. For example, to transfer data between Microsoft SQL Server and ASE requires DirectConnect for Microsoft SQL Server and an ASE ODBC driver.

Functional differences between bulk copy and express transfer

There are functional differences in datatype conversion and error handling between bulk copy and express transfer:

• Datatype conversion

  • In bulk copy transfer, data being transferred is converted into intermediate Open Server datatypes.

  • In express transfer, the ODBC driver converts the datatypes automatically, and no conversion takes place in DirectConnect.

• Error handling

  • If a datatype conversion error occurs, bulk copy transfer supports error handling as defined in the following section “Bulk copy and express transfer errors”.

  • Express transfer does not support error handling: If an error occurs, it aborts the entire transfer.

Using express transfer

To prepare to use express transfer

1. Set the TransferExpress property to yes, which causes the bulk copy transfer statements to be interpreted as express transfer statements.

2. Enter the ODBC data source name as the secondary name in the transfer statement.

Certified express transfer targets

Express transfer is now certified for the following target combinations:

• DB2 UDB OS/390, to and from Microsoft SQL Server 2000, requires the following:

  • DirectConnect for Microsoft SQL Server

  • DirectConnect for DB2 UDB driver

• UDB DB2 OS/390, to and from ASE, requires the following:

  • DirectConnect for DB2 UDB

  • Sybase ASE ODBC driver

• Microsoft SQL Server 2000, to and from ASE, requires the following:

  • DirectConnect for Microsoft SQL Server

  • Sybase ASE ODBC driver

To use express transfer with ASE, a Sybase-supplied ASE ODBC driver is available on the Windows platforms. For DirectConnect on UNIX platforms, a UNIX ASE driver must be purchased from Data Direct Technologies. Additional information regarding a driver can be found.

An express transfer example

This example describes how you can express transfer data from DB2 to Microsoft SQL Server. It uses an isql connection to DB2 through DirectConnect for DB2 UDB (primary server), and an ODBC DSN (on the primary server machine) for Microsoft SQL Server called MSQL-DSN.

To use express transfer from DB2 to Microsoft SQL Server

1. Connect to DB2 through the primary server by entering the following:

   -isql -S dcdb2udb - Uuserid -Ppassword
   

2. Set TransferExpress to yes.

3. Enter the following:

   Transfer to ‘MSQL-DSN userid password’;
   with insert into MSQL-table;
   Select * from db2-table
   

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF