Up Prev Next
From: Stephen dot Sprague at msdw dot com
Subject: CPAN Upload: S/SP/SPRAGST/Sybase-Xfer-0.31.tar.gz (fwd)
Date: Feb 17 2001 6:36PM
Version 0.31 of Sybase::Xfer.pm is now available on CPAN. It still uses
Sybase::DBlib - this I know - so no need to flame me about it. I'll be
trying to convert it over to DBD::Sybase within the next 6 months and
probably applying a little more finesse one the guts to see what I can
to do speed it up.
Constructive feedback always appreciated!
As always with this list a nod goes to Michael Peppler and this time for
the idea of batching rows one at time inside Sybase::BCP when a server
error is detected. I liked it so much I put it in Sybase::Xfer.
The README follows with the change log at the end.
Sybase::Xfer transfers data between two Sybase servers with multiple
options like specifying a where_clause, a smart auto_delete option and
can pump data from a perl subroutine or take a plain flat file. Has
option, similiar to default behaviour in Sybase::BCP, to capture failed
rows in a batch.
Also comes with a command line wrapper, sybxfer.
Also comes with a sister module Sybase::ObjectInfo.pm
INSTALLATION (the usual)
perl Makefile.PL [ LIB= ]
Requires Perl Version 5.005 or beyond
$h = new Sybase::Xfer( %options );
DESCRIPTION (a little bit from the pod)
If you're in an environment with multiple servers and you don't want
to use cross-server joins then this module may be worth a gander. It
transfers data from one server to another server row-by-row in memory
w/o using an intermediate file.
To juice things up it can take data from any set of sql commands as
long as the output of the sql matches the definition of the target
table. And it can take data from a perl subroutine if you're into
It also has some smarts to delete rows in the target table before the
data is transferred by several methods. See the -truncate_flag,
-delete_flag and -auto_delete switches.
Everything is controlled by switch settings sent has a hash to the
module. In essence one describes the from source and the to source and
the module takes it from there.
An attempt was made to build in hooks for robust error reporting via
perl callbacks. By default, it will print to stderr the data, the
column names, and their datatypes upon error. This is especially
useful when sybase reports attempt to load an oversized row warning
More recently the code has been tweaked to handle the condition
where data is bcp'ed into a table but the row already exists and the
desired result to replace the row. Originally, the -delete_flag
option was meant for this condition. ie. clean out the table via the
-where_clause before the bcp in was to occur. If this is action is
too drastic, however, by using the -auto_delete option one can be
more precise and force only those rows about to be inserted to be
deleted before the bcp in begins. It will bcp the 'key' information
to a temp table, run a delete (in a loop so as not to blow any log
space) via a join between the temp table and target table and then
begin the bcp in. It's weird but in the right situation it may be
exactly what you want. Typically used to manually replicate a table.
my e-mail: firstname.lastname@example.org
Version .31 12-feb-2001
* Fixed sloppy bug left in v.30 where if @ARGV is empty it'd print a
usage statment. Duh! That holds for the script sybxfer but it
doesn't hold user written scripts.
Version .30 11-feb-2001
Some promised mods.
* New/modified switches as follows:
added value 'retry'. Will behave like Sybase::BCP upon error
on bcp_batch. In other words, set the batchsize to 1 and
resend the data. Prints failed records to -error_data_file.
(thanks to Michael Peppler for allowing me to raid his code.)
number of times to retry a failed bcp_batch. Default is
number of seconds to sleep between sending the bcp_batch
again iff a deadlock error is detected. Default is 120.
implemented it. Now users can modify the rows in the batch
and request that xfer resend it or not. Default is Xfer's own
routine that implements the most useful behaviour
-error_data_file | -edf
contains records that failed to transfer in addition to
source record number and Sybase erorr message for each record
-from_file | -ff
new source. Take the data from a flat file. Must be a
delimited file. Steals a chapter from Sybase's bcp.
-from_file_delimiter | -ffd
used inconjunction with with -from_file. The delimeter can be
regular expression. (similiar to -t switch in Sybase's bcp)
if switch -from_table specified then this switch appends a
'holdlock' to internally generated select statement.
sets the program_name in master..sysprocesses table. Defaults
to the script name.
modified such that rows are deleted in -batchsize increments
so the log doesn't get blown.
* changed the final summary message slightly to report:
1) num rows read from source
2) num rows resent to target table (only if errors)
3) num rows failed to be transferred
* if no args given it'll exit gracefully with a message to run perldoc
---------- Forwarded message ----------
Date: Tue, 13 Feb 2001 04:04:34 +0100
To: Stephen J. Sprague , email@example.com
Subject: CPAN Upload: S/SP/SPRAGST/Sybase-Xfer-0.31.tar.gz
The uploaded file
has entered CPAN as
size: 28518 bytes
No action is required on your part
Request entered by: SPRAGST (Stephen J. Sprague)
Request entered on: Tue, 13 Feb 2001 03:03:32 GMT
Request completed: Tue, 13 Feb 2001 03:04:33 GMT
Id: paused,v 1.72 2000/12/26 15:12:53 k Exp k