About
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
Michael Peppler.
Disclaimer
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.
Installation
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
network placement.
Usage
1) Setup
After starting BCPtool,
click on the "Setup" button to pop up the setup panel. The "About" button
displays a popup that shows some basic about details. The "Quit" button
quits the application. The "Start" button commences processing after the
setup has been configured. The application itself takes no command line
options or parameters. Configuration is done entirely thru the setup panel.
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.
2) Table Selection
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
options chosen.
Clicking on the Start button will commence the processing.
3) Processing
When processing starts it executes the databases options selected (and
unsets them on completion). It then proceeds thru the table list and executes
each table transfer in turn. It timestamps it activity and display an average
number of rows transfered per second summary. For example:
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
batch break.
There are many issues to consider, including network topology, physical disk
layouts and server configuration.
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.