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: Michael Peppler <mpeppler at peppler dot org>
Subject: Re: Running multiple queries simultaneously
Date: Jan 29 2005 11:11PM

On Thu, 2005-01-27 at 22:53, Wechsler, Steven wrote:
> Is there a way to do this with Sybperl without fork()ing separate
> subprocesses?
> 
> I'd like to write a function that can take, say, a list of tables,
> databases, or other objects, and perform a T-SQL function against each
> (let's use update stats as an example). Since the function going
> against each object is not dependent on any other function in the
> list, I'd like to be able to run several of these in parallel to be
> able to take advantage of SMP or a high performance SAN or what have
> you; when the first one completed I'd take the next off the queue and
> continue until the queue was emptied. Presumably I could do this with
> asynch I/O and n (where n is some integer >= 1) DB handles; however,
> the stumbling block is: if I have messages returned (as opposed to a
> result set), how do I know which handle returned the message without
> creating a distinct message handler for each handle?

First - sorry for the delay in answering - it's been a little hectic for
the last week.

You've got a couple of options.

1. Fork child processes with a new connection to execute each task.
2. Use a multi-threaded approach (requires perl 5.8.x, and a recent
sybperl.)
3. Open multiple connections, and then use async CTlib calls to send
requests to the server without blocking. 

Option 1 is the simplest, and most reliable. There are modules on CPAN
that can make this fairly straightforward (I think one is called
Parallel::ForkManager).

Option 2 is similar to 1, but doesn't fork. I've not used perl threads
extensively so I don't know how solid they are.

Option 3 is interesting, but requires some pretty complex programming.
You need to setup a "completion" callback (this is a subroutine similar
to the error handler that gets called with a handle has completed its
work). If you have 10 or 20 connections it might get a little hairy
keeping track of which connections are active, etc.

To keep track of which PRINT messages are sent to what handle is
actually pretty easy. The server message callback is passed the handle
of the connection for which the message is generated - from that you can
easily associate the message with the handle. For example:

my $dbh = new Sybase::CTlib $user, $pwd, $server, $appname,
{PRIVATE_DATA => {}};

....

sub srv_cb {
    my ($dbh, $number, $severity, $state, $line, $server, $proc, $msg) =
@_;

    $dbh->{PRIVATE_DATA}->{error_message} = $msg;
    etc...
}


You need to predefine the PRIVATE_DATA entry in the $dbh hash because
sybperl uses a tied hash interface and validates the keys used against
the keys that it knows about.

Hope this helps!

Michael
-- 
Michael Peppler  -  mpeppler@peppler.org  -  http://www.peppler.org/
Sybase DBA/Developer
Sybase on Linux FAQ: http://www.peppler.org/FAQ/linux.html