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
>
