And just to make sure everyone gets credit where it's due, most of the code
for the error handler was taken from Michael Peppler's work on DBD::Sybase.
The async stuff was just a kludge so I could get status messages while the
query is still running.
On Wed, Aug 28, 2002 at 08:40:14AM -0400, Jeff Urlwin <[EMAIL PROTECTED]> wrote:
> Steve -- thanks -- but please credit David L. Good for the async processing
> and the error handler (among other things)! I just incorporated his work.
>
> Nice script and thanks for posting. I've taken the liberty of cross-posting
> to [EMAIL PROTECTED] where that group may find this interesting, also. I
> really like the use of the closure, too.
>
> Regards,
>
> Jeff
>
> >
> > Thanks to the excellent work of Mr. Urlwin in providing us an
> > error checker
> > in DBD::ODBC where we can get back all informational messages
> > from commands
> > (such as dbcc), I can get rid of all those old OSQL scripts for
> > maintenance,
> > and I can overcome another difficulty - multiple "Very Large" databases on
> > servers that all must be maintained in a small maintenance window
> > allowed by
> > our contract. By using a Perl fork, and Mr. Urlwin's error checker, I can
> > turn my maintenance jobs into an organized, multi-threaded process which
> > will greatly reduce the amount of time required to perform the
> > maintenance,
> > and log the results, and condense the logs into our web-page (that our DBA
> > staff looks at to see all the servers, and all the databases' maintenance)
> > database all in one nice step.
> >
> > I know I am not the only DBA that has problems of large databases - small
> > maintenance windows, and single threads on high-performance
> > multi-processor,
> > multi-raid 0+1 machines, so I thought I would post a short example here of
> > how to multi-thread the maintenance, and handle the output. I am posting
> > just in hope that someone will find this helpful.
> >
> > Note, however, that if you shouldn't do this if you use Win32, or
> > any of its
> > sub-components anywhere in your script (unless you know something
> > I don't).
> > I have never been able to make Win32 and fork play nicely
> > together. Whenever
> > I absolutely must use Win32, I can simulate this by arranging the code so
> > that I can open multiple process pipes (I've read the documentation for
> > Win32::Process which sounds like it would operate like pipes with a few
> > extra nice features, but I have never used that module).
> >
> > Anyway, enough talk, here is the example I wanted to post for
> > whomever might
> > find it useful:
> >
> >
> >
> > #! perl -w
> >
> > use strict;
> >
> > # pretend like I'm going to process large databases:
> >
> > my @databases = qw(northwind pubs master);
> >
> > # I need to track what PIDS I fork.
> > my @pids;
> >
> > # spawn a separate thread for each database to be processed:
> >
> > foreach (0 .. $#databases)
> > {
> > my $pid = fork();
> >
> > # using fork, the spun off pid is returned to the parent proccess.
> > # therefore, if pid has a value, this is the parent process
> > # if it does not, it is the child process, and we need it to get to
> > work:
> >
> > CheckDb($databases[$_]) if ! $pid;
> >
> > # child processes should never arrive here. They will exit in the
> > subroutine.
> > # keep track of all pids for whose finish we will need to wait.
> >
> > push (@pids, $pid);
> > }
> >
> > # only the parent process should arrive here.
> > # child processes should exit at the end of the subroutine.
> >
> > # cleanup nicely before exiting:
> >
> > waitpid($_, 0) foreach @pids;
> >
> > # show a finish message:
> >
> > die "Complete";
> >
> >
> > sub CheckDb
> > {
> > # only the child processes should arrive here.
> > use DBI;
> > my $dbh = DBI->connect('DBI:ODBC:northwind', 'sa', 'changed');
> >
> > # initialize the closure with our database name, and it will
> > return the
> > reference
> > # to the error handler subroutine with our database name as
> > part of the
> > error msg.
> >
> > $dbh->{odbc_err_handler} = err_handler($_[0]);
> > $dbh->{odbc_async_exec} = 1;
> >
> > # execute the checkdb on this database:
> >
> > my $sth = $dbh->prepare("DBCC CHECKDB('$_[0]')");
> > $sth->execute();
> >
> >
> > # failure to explicitly disconnect when running multiple threads
> > sometimes
> > # causes a runtime memory read/write error.
> > $dbh->disconnect();
> >
> >
> > # we do not want a child process to return. We want them to exit here.
> >
> > exit (0);
> >
> > }
> >
> >
> > # use a closure to initialize the data such as what database is being
> > processed.
> > # the error_handler wants a reference to a subroutine, and a closure will
> > return
> > # a reference to a subroutine, initialized with the data we passed in
> > calling the
> > # outer subroutine.
> >
> > sub err_handler {
> > my $db = $_[0];
> > use DBI;
> > my $dbh = DBI->connect('DBI:ODBC:WebSite', 'MaintJob', 'changed');
> >
> > # ODBC RPC to a stored procedure which will compare this message
> > # with our normalmessages table, and record it in the
> > logmessages table
> > # if we have not already identified this pattern as a normal or "info
> > only"
> > # message.
> >
> > my $sth = $dbh->prepare(qq/{Call ProcessMessage(?, ?)}/);
> > # we have our data prepared. now return a reference to an anonymous
> > subroutine
> > # which will actually be the error handler.
> > return sub
> > {
> > my ($state, $msg) = @_;
> > # Strip out all of the driver ID stuff
> > $msg =~ s/^(\[[\w\s]*\])+//;
> > # for our use here, display the message that will be logged:
> > print "$db: $msg\n";
> > # for to be condensed onto our website so our DBA's can review
> > # when they arrive in the morning:
> > $sth->execute($db, $msg);
> > return 0;
> > }
> > }
> >
> >
> >
> > _______________________________________________
> > Perl-Win32-Database mailing list
> > [EMAIL PROTECTED]
> > To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
> >
>
--
David Good [EMAIL PROTECTED]
This space intentionally left blank.
