PEPPLER.ORG
Michael Peppler
Sybase Consulting
Menu
Home
Sybase on Linux
Install Guide for Sybase on Linux
General Sybase Resources
General Perl Resources
Freeware
Sybperl
Sybase::Simple
DBD::Sybase
BCP Tool
Bug Tracker
Mailing List Archive
Downloads Directory
FAQs
Sybase on Linux FAQ
Sybperl FAQ
Personal
Michael Peppler's resume

sybperl-l Archive

Up    Prev    Next    

From: "WORENKLEIN, David, GCM" <David dot Worenklein at gcm dot com>
Subject: RE: bcp and select, not the same time?
Date: Jul 29 1999 8:00PM

Issue an "sp_whor" to the server when it locks up.  See if you're blocking
yourself.

-----Original Message-----
From: AGHZHONG@aol.com [mailto:AGHZHONG@aol.com]
Sent: Thursday, July 29, 1999 2:58 PM
To: SybPerl Discussion List
Subject: bcp and select, not the same time?


I am trying to use Sybase::DBlib, bcp related calls to take advantage 
the bcp speed to copy in large amount of data.  However, the input
data I have is not suitable to do direct bcp in. For each line I read
in from the input file,  I need to do a select from other tables and
then assemble a new row.  When I accumulate enough assembled rows,
I do bcp_batch.  Here is the pseudo code:

&BCP_SETL(&Sybase::DBlib::TRUE);
my $dbh = new Sybase::DBlib user, pass, database
my $dbhp = new Sybase::DBlib user, pass, database  #same users are used for 
both
$dbh->bcp_init("table_name", undef, "", DB_IN);  # dbh is for bcp in
$dbh->bcp_meminit($num_of_columns_in_input_table);
while (<>) {
     $count++;
     my $element = get_the_element_from_input_line();
     my @ref = $dbhp->("select ... where element=$element"); # dbhp is for 
select
     my @assembled = combine($element,\@ref);
     my $ret = $dbh->bcp_sendrow(\@assembled);
     if (($count % $batch_size)==0) {
        my $batch_ret = $dbh->bcp_batch;
        .....
     }
}

This works fine for a few hundred line input files.  However, the program
simply hangs when processing large input files.  There is no error of any
kind in log.  Any idea what might be the problem? Memory leak? The same
program can process large files just fine when I comment out all $dbhp
related lines.

The reason I assemble the rows and bcp in when reading the input file,
rather 
than
writing two programs, one assembles and the other bcp in, is because
I thought this way is faster.  Are there other ways to do this and get the
speed I want?


Thank you
Heather