psa-chapter07.txt

perl语言的经典文章

die "Unable to query available DSN's".Win32::ODBC::Error()."\n" 
  unless (%dsnavail = Win32::ODBC::DataSources());
if (!defined $dsnavail{$dsn}) {
    die "unable to create DSN:".Win32::ODBC::Error()."\n"
      unless (Win32::ODBC::ConfigDSN(ODBC_ADD_DSN, 
				  "SQL Server", 
				  ("DSN=$dsn", 
				   "DESCRIPTION=DSN for PerlSysAdm", 
				   "SERVER=mssql.happy.edu",
				   "DATABASE=master",
				   "NETWORK=DBMSSOCN", # TCP/IP Socket Lib
				  )));
}

# connect to the master database
$dbh = new Win32::ODBC("DSN=$dsn;UID=$user;PWD=$pw;");
die "Unable to connect to DSN $dsn:".Win32::ODBC::Error()."\n" 
  unless (defined $dbh);

# find the databases on the server
if (defined $dbh->Sql(q{SELECT name from sysdatabases})){
    die "Unable to query databases:".Win32::ODBC::Error()."\n";
}

while ($dbh->FetchRow()){
    push(@dbs, $dbh->Data("name"));
}
$dbh->DropCursor();

# find the user tables in each database
foreach $db (@dbs) {
    if (defined $dbh->Sql("use $db")){
	die "Unable to change to database $db:" . Win32::ODBC::Error() . "\n";
    }
    print "---$db---\n";
    @tables=();
    if (defined $dbh->Sql(q{SELECT name from sysobjects 
                            WHERE type="U"})){
	die "Unable to query tables in $db:" . Win32::ODBC::Error() . "\n";
    }
    while ($dbh->FetchRow()) {
	push(@tables,$dbh->Data("name"));
    } 
    $dbh->DropCursor();

    # find the column info for each table
    foreach $table (@tables) {
  	    print "\t$table\n";
  	    if (defined $dbh->Sql(" {call sp_columns (\'$table\')} ")){
	        die "Unable to query columns in 
                  $table:".Win32::ODBC::Error() . "\n";
	    }
	    while ($dbh->FetchRow()) {
	        @cols=();
	        @cols=$dbh->Data("COLUMN_NAME","TYPE_NAME","PRECISION");
	        print "\t\t",$cols[0]," [",$cols[1],"(",$cols[2],")]\n";
	    } 
	    $dbh->DropCursor();
	}
}
$dbh->Close();

die "Unable to delete DSN:".Win32::ODBC::Error()."\n"
    unless (Win32::ODBC::ConfigDSN(ODBC_REMOVE_DSN,
                                "SQL Server","DSN=$dsn"));
-------
#*
#* creating logins on a Sybase server using DBI
#*

-------use DBI;

# USAGE: syaccreate <username>

$admin = 'sa';
print "Enter passwd for $admin: ";
chomp($pw = <STDIN>);
$user=$ARGV[0];

# generate a *bogus* password based on user name reversed 
# and padded to at least 6 chars with dashes
$genpass = reverse join('',reverse split(//,$user));
$genpass .= "-" x (6-length($genpass));

# here's a list of the SQL commands we will execute in order
# we: 1) create the database on the USER_DISK device, 
#        with the log on USER_LOG
#     2) add a login to the server for the user, 
#        making the new database the default
#     3) switch to the newly created database
#     4) change its owner to be this user
@commands = ("create database $user on USER_DISK=5 log on USER_LOG=5",
             "sp_addlogin $user,\"$genpass\",$user",
             "use $user",
             "sp_changedbowner $user");

# connect to the server 
$dbh = DBI->connect('dbi:Sybase:',$admin,$pw);
die "Unable to connect: $DBI::errstr\n" 
  unless (defined $dbh);

# loop over the command array, execute each command in turn
for (@commands) {
    $dbh->do($_) or die "Unable to $_: " . $dbh->errstr . "\n";
}

$dbh->disconnect;

#*
#* deleting logins on a Sybase server using DBI
#*

use DBI;

# USAGE: syacdelete <username>

$admin = 'sa';
print "Enter passwd for $admin: ";
chomp($pw = <STDIN>);
$user=$ARGV[0];

# here's a list of the SQL commands we will execute in order
# we: drop the user's database
#     drop the user's server login
@commands = ("drop database $user",
            "sp_droplogin $user");

# connect to the server
$dbh = DBI->connect('dbi:Sybase:',$admin,$pw);
die "Unable to connect: $DBI::errstr\n" 
  unless(defined $dbh);

# loop over the command array, execute each command in turn 
for (@commands) {
    $dbh->do($_) or die "Unable to $_: " . $dbh->errstr . "\n";
}

$dbh->disconnect or
  warn "Unable to disconnect: " . $dbh->errstr . "\n";
-------
#*
#* monitoring space usage on a Sybase server using DBI
#*

use DBI;

$admin = 'sa';
print "Enter passwd for $admin: ";
chomp($pw = <STDIN>);
$pages = 2; # data is stored in 2k pages

# connect to the server
$dbh = DBI->connect('dbi:Sybase:',$admin,$pw);
die "Unable to connect: $DBI::errstr\n" 
  unless (defined $dbh);

# get the name of the databases on the server
$sth = $dbh->prepare(q{SELECT name from sysdatabases}) or
  die "Unable to prepare sysdatabases query: ".$dbh->errstr."\n";
$sth->execute or
  die "Unable to execute sysdatabases query: ".$dbh->errstr."\n";

while ($aref = $sth->fetchrow_arrayref) {
    push(@dbs, $aref->[0]);
}
$sth->finish;

# retrieve stats for each of the databases
foreach $db (@dbs) {
    
    # get and total the size column from all non-log segments
    $size    = &querysum(qq{SELECT size FROM master.dbo.sysusages 
                           WHERE  dbid = db_id(\'$db\') 
                           AND    segmap != 4});
    # get and total the size column for the log segment
    $logsize = &querysum(qq{SELECT size FROM master.dbo.sysusages
                           WHERE  dbid = db_id(\'$db\') 
                           AND    segmap = 4});		      

    # change to the database and retrieve usage stats
    $dbh->do(q{use $db}) or
      die "Unable to change to $db: ".$dbh->errstr."\n";

    # we used the reserved_pgs function to return the number of pages
    # used by both the data (doampg) and index (ioampg) part 
    # of the database
    $used=&querysum(q{SELECT reserved_pgs(id,doampg)+reserved_pgs(id,ioampg) 
                     FROM sysindexes 
                     WHERE id != 8});

    # same, except this time we look at the log usage
    $logused=&querysum(q{SELECT reserved_pgs(id, doampg) 
                        FROM sysindexes 
                        WHERE id=8});
    
    # show this information graphically
    &graph($db,$size,$logsize,$used,$logused);
}
$dbh->disconnect;

# prepare/exec a given single-column SELECT query, return 
# the sum of the results
sub querysum {
    my($query) = shift;
    my($sth,$aref,$sum);

    $sth = $dbh->prepare($query) or
      die "Unable to prepare $query: ".$dbh->errstr."\n";
    $sth->execute or 
      die "Unable to exec $query: ".$dbh->errstr."\n";

    while ($aref=$sth->fetchrow_arrayref) {
        $sum += $aref->[0];
    }
    $sth->finish;

    $sum;
}

# print out nice chart given database name, size, log size, 
# and usage info
sub graph {
    my($dbname,$size,$logsize,$used,$logused) = @_;

    # line for data space usage
    print ' 'x15 . '|'.'d'x (50 *($used/$size)) .
          ' 'x (50-(50*($used/$size))) . '|';

    # percentage used and total M for data space
    printf("%.2f",($used/$size*100));
    print "%/". (($size * $pages)/1024)."MB\n";
    print $dbname.'-'x(14-length($dbname)).'-|'.(' 'x 49)."|\n";

    if (defined $logsize) { # line for log space usage  
        print ' 'x15 . '|' . 'l'x (50 *($logused/$logsize)) .
	      ' 'x (50-(50*($logused/$logsize))) . '|';
        # percentage used and total M for log space
        printf("%.2f",($logused/$logsize*100));
	print "%/". (($logsize * $pages)/1024)."MB\n";
    }
    else { # some databases do not have separate log space
	print ' 'x15 . "|- no log".(' 'x 41)."|\n";
    }
    print "\n";
}
-------
#*
#* show minute-by-minute status of both a Sybase and an MS-SQL server using DBI
#*

use DBI;

$syadmin = "sa";
print "Sybase admin passwd: ";
chomp($sypw = <STDIN>);

$msadmin = "sa";
print "MS-SQL admin passwd: ";
chomp($mspw = <STDIN>);

# connect to Sybase server
$sydbh = DBI->connect("dbi:Sybase:server=SYBASE",$syadmin,$sypw);
die "Unable to connect to sybase server: $DBI::errstr\n" 
  unless (defined $sydbh);
# turn on the ChopBlanks option to remove trailing whitespace in columns
$sydbh->{ChopBlanks} = 1;

# connect to MS-SQL server (handy that we can use DBD::Sybase for this!)
$msdbh = DBI->connect("dbi:Sybase:server=MSSQL",$msadmin,$mspw);
die "Unable to connect to mssql server: $DBI::errstr\n" 
  unless (defined $msdbh);
# turn on the ChopBlanks option to remove trailing whitespace in columns 
$msdbh->{ChopBlanks} = 1;

$|=1; # turn off STDOUT IO buffering

# initialize the signal handler so we can cleanup nicely
$SIG{INT} = sub {$byebye = 1;};

# infinitely loop unless our interrupt flag has been set
while (1) {
    last if ($byebye);
    
    # run the stored procedure sp_monitor
    $systh = $sydbh->prepare(q{sp_monitor}) or
      die "Unable to prepare sy sp_monitor:".$sydbh->errstr."\n";
    $systh->execute or 
      die "Unable to execute sy sp_monitor:".$sydbh->errstr."\n";
    # loop to retrieve the lines from the output we need. 
    # We know we have all of it when we see the cpu_busy information
    while($href = $systh->fetchrow_hashref or 
          $systh->{syb_more_results}) {
	# got what we needed, stop asking
	last if (defined $href->{cpu_busy}); 
    }
    $systh->finish;
    
    # substitute out everything but the % number from 
    # the values we receive 
    for (keys %{$href}) {
	$href->{$_} =~ s/.*-(\d+%)/\1/;
    }

    # collect all the data we need into a single line
    $info = "Sybase: (".$href->{cpu_busy}." CPU), ".
                    "(".$href->{io_busy}." IO), ".
                    "(".$href->{idle}." idle)   ";

    # ok, now let's do it all over again for the second server (MS-SQL)
    $mssth = $msdbh->prepare(q{sp_monitor}) or
      die "Unable to prepare ms sp_monitor:".$msdbh->errstr."\n";
    $mssth->execute or
      die "Unable to execute ms sp_monitor:".$msdbh->errstr."\n";
    while($href = $mssth->fetchrow_hashref or 
	  $mssth->{syb_more_results}) {
	# got what we needed, stop asking
	last if (defined $href->{cpu_busy}); 
    }
    $mssth->finish;

    # substitute out everything but the % number from 
    # the values we receive
    for (keys %{$href}) {	
	$href->{$_} =~ s/.*-(\d+%)/\1/;
    }

    $info .= "MSSQL: (" . $href->{'cpu_busy'}." CPU), ".
                    "(".$href->{'io_busy'}." IO), ".
                    "(".$href->{'idle'}." idle)";
    print " "x78,"\r";
    print $info,"\r";
    
    sleep(5) unless ($byebye);
}

# only end up here if we've broken out of the loop thanks to an interrupt
$sydbh->disconnect;
$msdbh->disconnect;