A good amount of DBA work is Ad-Hoc: users call up and request work, you notice a problem in a database and quickly want to know if any other databases share the same problem, you need a quick database dump of something, and so on. Existing Ad-hoc scripts are essential to quickly satisfying user requests. We satisfy ad-hoc requests/DBA needs via one of three types of ad-hoc programming: bourne shell, sybperl, and Datatools-api scripts:

Bourne shell scripts

A good example of all that is available via this method is run.sh. This is a shell program I continually add ad-hoc commands to. Here's an example of how you might add a user to all databases in a Server:

#!/bin/sh

SYBASE="/export/sybase11"
sapw11=`cat .p11sapw`
P11ISSA="/export/sybase11/bin/isql -Usa -Sprod11"
prod11_dblist="CDB cc_lan cc_web_apps cdr common dtm edison estar hss \
 issc issc_cdr issc_test master mis model prrc sybsecurity \
 sybsyntax sybsystemprocs tempdb"

##############################

all_dbs ()
{
   for name in $prod11_dblist
   do
      echo "*** $name ***"
      $P11ISSA << EOTSQL
$sapw11
use $name
go
sp_adduser tboss
go
EOTSQL
   done
}

#######################

all_dbs

We make liberal use of shell variables in the script, making it very repeatable with other servers and other database lists. Further, by commenting out commands within the SQL section with two dashes (--) you can quickly modify this script to run some other job (say sp_spaceused) on all the databases. Note the convention "$P11ISSA << EOTSQL:" this means literally, 'read lines into the SQL server and execute them until you reach the tag "EOTSQL." You'll note that all the SQL statements are along the left edge of the script; this is necessary for the SQL server to properly read and execute the SQL statements (really only the "go" lines must be along the left edge, but it makes for cleaner programming if all SQL lines are consistent). Also note the $sapw11 line by itself INSIDE the EOTSQL clause; this prevents the sa password from being visible in the process table (a problem inherent w/ isql scripts)

See the run.sh mentioned above for many more examples of Bourne shell scripting. Also, see dbcc_one_time.sh for example one-time dbcc job processing in a Bourne shell script.

Sybperl scripts

Like with Bourne Shell scripts, there are some activities that are required to be done in sybperl, namely any type of array-processing task. Its pretty straightforward to scroll through a pre-typed in list of databases as in the Bourne shell example above, but its much more complicated to scroll through every TABLE of every Database on a server. For tasks like these, we depend on sybperl.

See the script run.perl for a good example of ad-hoc activities run through sybperl. Here's a perl example of how one might run the maintenance command "update statistics" on every table in every database on a server:

#! /usr/local/bin/sybperl 

require "sql.pl";
$ENV{'SYBASE'} = "/export/sybase11";
$ENV{'DSQUERY'} = "prod11";

sub populate_sa_pws {
   open(PWFILE,"../.p11sapw");
   $sapw11 = <PWFILE>;
   chop($sapw11);
   close(PWFILE);
}
   
sub every_table {
   $dbproc = &dblogin("sa", $sapw11);
   &dbuse($dbproc, "master");
   @dbs = &sql($dbproc, "select name from sysdatabases order by name");

   foreach $db (@dbs) {
      &sql($dbproc, "use $db");
      @tables = &sql($dbproc, "select name from sysobjects where type = 'U' 
                                 order by name");
      foreach $table (@tables) {
         &sql($dbproc, "update statistics $table");
      }
   } 
   &dbclose($dbproc);
}

##########
&populate_sa_pws;
&every_table;

Note its not quite as straightforward to populate the $sapw as with the shell script (though perl has great file manipulation capabilities, its actually more code just to read a one-line file). sybperl can read result sets into arrays and then scroll through the array elements with little programming effort, making it ideal to do certain tasks (especially tasks in which you're not REALLY that worried about the standard output and/or standard error coming from the server). The &sql is inherited from sql.pl and serves as a nice front end to the process of actually calling dbsqlexec, and scrolling through dbresults. &sql automatically returns an array of results of a query...its very nice. See the sql.pl file with the standard sybperl distribution for the true DB-Lib API calls.