BCPtool is intended as a user data migration tool. As such it doesn't handle the migration of database and table definitions and user account details. There are, however, plenty of tools available (both free and commercial) that do provide this level of extraction. See the Sybase FAQ for further details.
BCPtool was designed for the migration of data when normal dumps and loads cannot be used (i.e. as a result of a change in architecture or layout) or when dumps and loads are unsupported (between versions lower than System 10 and those at or greater than System 10 or loading into a server version lower than the dump version) and normal use of the bcp program would be too cumbersome. There are other methods of doing this but they are either too unwieldy or don't provide an easy to use GUI front end. BCPtool supports full data migration or partial transfer thru the use of table and table data selection patterns. It works with all versions of Sybase SQL Server and ASE. Use with SQL Anywhere or ASA is unsupported. Sourcing data from MS SQL Server is unsupported and doesn't work, but loading data into MS SQL Server may work (but is not guaranteed).
BCPtool was developed by Anthony Mandic
with the Open Look look and feel (using XView) and Sybase's DB-Library and the
BCP API. The Linux port was done by
BCPtool is unwarranted and is not guaranteed as suitable for any purpose.
Its use is solely at the discretion of the user. Currently it is an initial
release and, as such, it should be tested throughly before being commited to
any production use. Bug reports are welcome, as are enhancement suggestions.
Unfortunately, no indication can be given as to when and if any action may be
taken. This also applies to ports to other platforms.
BCPtool is provided as a single binary file. It can be installed anywhere and just requires the normal Sybase environment variables to be set to allow it to find the interfaces file and Open Client library.
See the Performance Tuning section for advice on optimal
On the setup panel enter the server name, login id and password for both the source and target servers. The TDS packet size may also be set for either or both servers. An optional where clause may also be entered for the source server (the default is where "dbid > 3". Any valid where clause may be used. The program adds the word "where" itself. An order by statement may also be entered - but after a where clause or a syntax error will be generated). An optional log filename may also be entered. All text displayed in the text panes of the main panel will also be recorded in this log file. The field "Processes Sharing Locks on Target" is designed for multiple instances of BCPtool. It normally defaults to 1. If you intend to run more than one instance in slow bcp mode on a target at any one time, set it to the number of instances you intend to run. This value is used later to calculate the batch size of a table transfer.
Click on the "Connect" button to initiate a connection or click on "Reset" to reset the input fields.
Once connected, a
list of target databases will be listed on the main panel. Click on one of
the database rows to select its details. This pops up the "User Tables" panel
where database options can be set and table selection patterns can be made.
Selecting either database option records it in the database list. The option won't take effect until processing is started. If these options are already in effect in the target database, do not select them within BCPtool or they will be reset when BCPtool completes its processing.
Source data can either be selected from tables or views.
The choice between
tables and views is non-exclusive. The selection pattern can be any where
clause and operates on the sysobjects system table. The selection mechanism
already does an order by on the name field of the sysobjects table, so
an order by cannot be entered. Matching tables can either be marked selected
or deselected when first presented.
Click on the "Make Selection" button to select the table list. Clicking on the "Drop Selection" button clears the table selection parameters and prepares the panel for another selection.
Once tables are listed in the table list, each entry can be clicked on to pop up the "Table Options" panel. This panel allows the user to select/deselect the table, specify an index to force during the data selection (it does this by index number rather than by index name. Be aware that having parallel features turned on does not guarantee any ordering of rows but an order by clause can be added to the where field after any valid where clause. Be aware of space requirements in tempdb when doing this on large tables), set a rowcount, set a batch size (the batch size is initially predetermined), truncate the target table, modify the where clause for the data selection or change the name of the target database and/or table. The batch size can be manipulated to set useful sizing for partitioned tables on the target although BCPtool itself doesn't calculate this.
Below these options is a list of the table's fields. Each of the fields can be selected/deselected. This is useful for eliminating columns such as filler fields from the transfer. BCPtool doesn't validate the data types of the source and target fields, so be careful with this functionality. Repositioning of columns is not supported. Click on the "Apply" button to set the changes made or click "Reset" to undo the entered changes.
When done the
database list will reflect the number of tables selected and the database
Clicking on the Start button will commence the processing.
Started Fri Nov 19 13:26:23 1999 Processing table: wami Processed 1000 rows in 2.951647 seconds Processed 1000 rows in 2.929544 seconds Processed 1000 rows in 2.932581 seconds Processed 1000 rows in 3.145349 seconds Processed 1000 rows in 2.986489 seconds Committed 5922 rows in 17.994706 seconds Averaged 348 rows per second Processing table: basic_details Processed 1000 rows in 9.028303 seconds Processed 1000 rows in 11.117554 seconds Processed 1000 rows in 10.466894 seconds Processed 1000 rows in 10.282467 seconds Processed 1000 rows in 16.955785 seconds Committed 5916 rows in 67.292270 seconds (1 mins 7 secs) Averaged 88 rows per second Completed Fri Nov 19 13:27:50 1999
The start button is changed to a stop button during processing to allow
the processing to be halted. If clicked, processing will stop at the next
For optimal network performance, avoid running BCPtool on a machine with only a single half duplex interface - unless it is either one of a source or target server. While this configuration will work, its preferable to avoid the ensuing network issues. Using a machine with either a full duplex interface or two interfaces or running BCPtool on either the source or target machine will minimise these issues. A 100 Mbit interface will perform better than a 10 Mbit interface. The OS platform it runs on need not be the same as either the source or target. Because data is migrated via the BCP API, the configuration of the servers need not be the same. This is one area where using dumps and loads to move data fails. Character sets may play a part but sort orders are of little consequence. (Differences in sort orders should still be kept in mind, however, as index rebuilds on the target may be affected. The 'with sorted_data' option may fail on character fields.) All datatypes are copied in native mode.
When running multiple instances, ensure that there are as many server engines as instances and that network capacity is sufficient.
Refer to the Sybase System Administration Guide for details on the tuneable parameters "default network packet size", "max network packet size" and "additional network memory" when configuring the packet size.
Physical disks layouts are normally important and are also significant when multiple instances of BCPtool are used. BCPtool reads and writes data seqentially thru each of the selected tables. With multiple instances, ensure that reads and writes won't interfere with each other. Reading from and writing to seperate disks between instances will ensure optimal disk performance. Log devices should be kept distinct from data devices to avoid disk head contention.
See the Sybase technical document on tuning Sybase server for bulk loads for more details on configuring servers for optimal bcp performance. Not all tuneable options apply to all server versions and very few options need be tuned on the source server.
There may be an issue with BCPtool copying into data-only locked tables. Refer to the Sybase technote on this.
After transfering data, index rebulding may be required. Apart from using the
"with sorted_data" option for clustered indexes, parallel features may be used
on partitioned tables, large i/o buffer pools and the configuration parameter
"number of sort buffers". Refer to the System Administation Guide and the
Performance and Tuning Guide for more details.