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 MBAY dot NET>
Subject: Re: running dbcc via Sybperl
Date: Jul 24 1998 7:36PM

Hmm - not sure what the problem is.

Here's a script that I've used with success to run dbcc's on a
database. It writes a log file and an errorlog, and is generally
fairly simple:

#!/usr/bin/perl

#	%W%	%G%
#

use Sybase::DBlib;
require 'getopts.pl';
require 'sybutil.pl';
require 'itf/locklib.pl';

&Lock::lock("/tmp/loglock", 0, 20);

&Getopts('d:');

&dbmsghandle ("msg_hdl"); 

$dbh = new Sybase::DBlib 'sa';

die "$0: -d database_name not specified!\n" if(!defined($opt_d));
$db = $opt_d;
$logfile = "$ENV{HOME}/tmp/$db.dbcc";
$tmpfile = "/tmp/dbcc$$";

open(LOG, ">$logfile") || die "Can't open log file $logfile: $!\n";
open(TMP, ">$tmpfile") || die "Can't open file $tmpfile: $!\n";

&dolog("dbcc.pl for $db started at ", scalar(localtime(time)), "\n");
&dolog("***** checkdb($db) *****\n");
&dbexec("dbcc checkdb('$db')\n");
&dolog("***** checkalloc($db) *****\n");
&dbexec("dbcc traceon (2512)\n");
&dbexec("dbcc checkalloc('$db')\n");
&dbexec("dbcc traceoff (2512)\n");
&dbexec("dbcc tablealloc(syslogs)\n");
&dolog("***** checkcatalog($db) *****\n");
&dbexec("dbcc checkcatalog('$db')\n");
&dolog("dbcc.pl for $db done at ", scalar(localtime(time)), "\n");

close(LOG);
close(TMP);

#system("/usr/ucb/Mail -s 'DBCC $db' admin <$tmpfile");


sub dbexec
{
    my($cmd) = @_;
    my(@dat, $ret);

    $dbh->dbcmd($cmd);
    $dbh->dbsqlexec;
    while(($ret = $dbh->dbresults) != NO_MORE_RESULTS && $ret != FAIL)
    {
	while(@dat = $dbh->dbnextrow())
	{
	    print "@dat\n";
	}
    }
}

sub dolog
{
    my(@strings) =@_;

    print LOG @strings;
    print TMP @strings;
}

sub msg_hdl
{
    my ($db, $message, $state, $severity, $text, $server, $procedure, $line)
	= @_;

    if ($severity > 0)
    {
	print TMP ("Sybase message ", $message, ", Severity ", $severity,
	       ", state ", $state);
	print TMP ("\nServer `", $server, "'") if defined ($server);
	print TMP ("\n    ", $text, "\n\n");
    }
    elsif ($message == 0)
    {
	print STDERR ($text, "\n");
    }
    
    printf LOG ("%4d %d %d $text\n", $message, $state, $severity)
	if($message != 0);
    
    0;
}

Michael


Eli Perl writes:
 > Michael:
 > 
 > I changed my code to set the TRACEON and the message handler to return all message. This is all I
 > get (I cut some of the repetitious stuff)
 > 
 > 
 > > use KOPS01DB
 > Sybase message 2528, Severity 0, state 1
 > Server `PCSHELLDEV'
 > Line 1
 >     DBCC execution completed. If DBCC printed error messages, contact a user with System
 > Administrat
 > or (SA) role.
 > 
 >     1> dbcc traceon(3604)
 > Sybase message 5701, Severity 0, state 2
 > Server `PCSHELLDEV'
 > 
 >    1>  dbcc page (5, 23587)
 > Sybase message 2528, Severity 0, state 1
 > Server `PCSHELLDEV'
 > Line 1
 >     DBCC execution completed. If DBCC printed error messages, contact a user with System
 > Administrat
 > or (SA) role.
 > 
 > 
 > Michael Peppler wrote:
 > 
 > > The default message handler (in sybutil.pl) will *NOT* print messages
 > > from dbcc, because it ignores all messages with severity 10 or lower.
 > >
 > > You should write your own message handler that prints out everything,
 > > and then tailor it to maybe filter out the exact messages that you do
 > > not want to see.
 > >
 > > Michael
 > >
 > > Eli Perl writes:
 > >  > Michael:
 > >  > isql/10.0.4/P-EBF7430/sun_svr4/SPARC Solaris 2.3/1/Wed Jul  9 23:18:54 PDT 1997
 > >  > SQL Server/11.0.3.2/P/Sun_svr4/OS 5.4/SWR 7578 Rollup/OPT/Mon Nov  3 22:19:21 PST 1997
 > >  >
 > >  > The script is a simple isql-like processor. It reads the input file which contains the T-SQL
 > >  > I want to execute and either loops through the result table if there is one or the
 > >  > message_handler takes over.
 > >  > So when  my input.sql file contains 'sp_sysmon 5' it gives me exactly what I want, but 'dbcc
 > >  > page (5, 23587)' returns nothing in the message handler output file. (I know that the page
 > >  > is valid because it gives me what I want when I set TRACEON 3604 and then run it via isql.)
 > >  > Do I have to turn 3604 on first?
 > >  > Thanks for your help
 > >  >
 > >  > Eli Perl
 > >  >
 > >  >
 > >  > Michael Peppler wrote:
 > >  >
 > >  > > Eli Perl writes:
 > >  > >  > Thanks, but I am using the handler included in the sybperl distribution, and it works
 > >  > >  > fine for everything else.
 > >  > >
 > >  > > What does your script do exactly?
 > >  > >
 > >  > > Also, what versions of Sybase (both server and OpenClient) do you use?
 > >  > >
 > >  > > Michael
 > >  > >
 > >  > >  >
 > >  > >  > Raja Sambamurty wrote:
 > >  > >  >
 > >  > >  > > Eli,
 > >  > >  > >
 > >  > >  > > It works fine, except remember that all the non-select statements go the
 > >  > >  > > the mesg/error handlers. So make sure that you have the handlers setup
 > >  > >  > > in your perl script. I have a working version that I could share with
 > >  > >  > > you, if you want.
 > >  > >  > >
 > >  > >  > > Raja
 > >  > >  > > >
 > >  > >  > > >All:
 > >  > >  > > >
 > >  > >  > > >When I try to run a dbcc page in a sybperl script, I get
 > >  > >  > > >this error:
 > >  > >  > > >
 > >  > >  > > >'Sybase error: Attempt to print unknown token.'
 > >  > >  > > >
 > >  > >  > > >If I go in via ISQL and use TRACEON 3604 ro redirect the
 > >  > >  > > >output (of the eact same dbcc page command) to my session,
 > >  > >  > > >everything's fine. How can I do this in Sybperl?
 > >  > >  > > >
 > >  > >  > > >Eli Perl
 > >  > >  > > >
 > >  > >  > > >End
 > >  > >  > > >
 > >  > >  > > >
 > >  > >  > > >
 > >  > >  > > >
 > >  > >  > > >***********************************************************************************
 > >  > >  > > >Bear Stearns is not responsible for any recommendation, solicitation,
 > >  > >  > > offer or
 > >  > >  > > >agreement or any information about any transaction, customer account or
 > >  > >  > > account
 > >  > >  > > >activity contained in this communication.
 > >  > >  > > >***********************************************************************************
 > >  > >  > > >
 > >  > >  > > >
 > >  > >  > > >
 > >  > >  > >
 > >  > >  > > ______________________________________________________
 > >  > >  > > Get Your Private, Free Email at http://www.hotmail.com
 > >  > >  >
 > >  > >  >
 > >  > >  >
 > >  > >  >
 > >  > >  >
 > >  > >  > ***********************************************************************************
 > >  > >  > Bear Stearns is not responsible for any recommendation, solicitation, offer or
 > >  > >  > agreement or any information about any transaction, customer account or account
 > >  > >  > activity contained in this communication.
 > >  > >  > ***********************************************************************************
 > >  > >  >
 > >  > >  >
 > >  > >  >
 > >  > >
 > >  > > --
 > >  > > Michael Peppler         -||-  Data Migrations Inc.
 > >  > > mpeppler@mbay.net       -||-  http://www.mbay.net/~mpeppler
 > >  > > Int. Sybase User Group  -||-  http://www.isug.com
 > >  >
 > >  >
 > >  >
 > >  >
 > >  >
 > >  > ***********************************************************************************
 > >  > Bear Stearns is not responsible for any recommendation, solicitation, offer or
 > >  > agreement or any information about any transaction, customer account or account
 > >  > activity contained in this communication.
 > >  > ***********************************************************************************
 > >  >
 > >  >
 > >  >
 > >
 > > --
 > > Michael Peppler         -||-  Data Migrations Inc.
 > > mpeppler@mbay.net       -||-  http://www.mbay.net/~mpeppler
 > > Int. Sybase User Group  -||-  http://www.isug.com
 > 
 > 
 > 
 > 
 > 
 > ***********************************************************************************
 > Bear Stearns is not responsible for any recommendation, solicitation, offer or
 > agreement or any information about any transaction, customer account or account
 > activity contained in this communication.
 > ***********************************************************************************
 > 
 > 
 > 

-- 
Michael Peppler         -||-  Data Migrations Inc.
mpeppler@mbay.net       -||-  http://www.mbay.net/~mpeppler
Int. Sybase User Group  -||-  http://www.isug.com