dba_compare_db.sh
         table_list.sql        
         proc_list.sql
         view_list.sql
         dba_compare_tables.sh
             dba_get_ddl.sh
                 help2sql_xe.pl
             ddl_cleanup.pl
         dba_compare_proc.sh
             getprocdef.sh
                 def_cleanup.pl
         dba_compare_grants.sh
             protects.sql
             file_diff.pl
         dba_compare_config.sh
             config.sql
             config.dat
 

 -- table_list.sql
 set nocount on
 go
 select substring(name,1,45) from sysobjects where type = 'U'
   and name not like 'rs%'
   and name not like 'sys%'
   and name not like 'spt%'
   and name not like 'mon%'
   and name not like '%jdbc%'
 order by 1
 go
 

 #!/usr/bin/perl
 #=================================
 #
 # ddl_cleanup.pl
 #
 #=================================
 
 $fname1 = $ARGV[0];
 $fname2 = $ARGV[1];
 
 open(FILE1,"<$fname1") or die "error: file1";
 open(FILE2,">$fname2") or die "error: file2";
 
 while ()
   {
   $line1 = $_ ;
 
   chomp $line1 ;
 
   $line1 =~ s/\t/    /g ;
   $line1 =~ s/ +/ /g ;
 
   print FILE2 $line1 . "\n" ;
 
   }
 
 close(FILE1);
 close(FILE2);
 

 #!/bin/bash
 #============================================
 #  dba_compare_tables.sh
 #
 #
 #============================================
 
 dbsource=$1
 dbsourcedb=$2
 dbtarget=$3
 dbtargetdb=$4
 table=$5
 
 if test $# -lt 5; then
    echo "Options are:  dbsource  dbsourcedb  dbtarget dbtargetdb table"
    exit
 fi
 
 echo "Table compare for $dbsource : $table with $dbtarget"
 
 UTIL=/opt/dba/util/scripts
 TEMP=/opt/dba/util/temp
 
 tfile1=$TEMP/${table}_A.txt
 tfile2=$TEMP/${table}_B.txt
 tfile3=$TEMP/${table}_C.txt
 tfile4=$TEMP/${table}_D.txt
 
 
 cd $TEMP
 
 
 echo "Getting table def from $dbsource .."
 $UTIL/dba_get_ddl.sh $dbsource $dbsourcedb $table $tfile1
 
 
 echo "Getting table def from $dbtarget .."
 $UTIL/dba_get_ddl.sh $dbtarget $dbtargetdb $table $tfile2
 
 $UTIL/ddl_cleanup.pl $tfile1 $tfile3
 $UTIL/ddl_cleanup.pl $tfile2 $tfile4
 
 ls -ltr $tfile3
 ls -ltr $tfile4
 
 
 diff $tfile3 $tfile4
 
 
 if test $? -ne 0; then
    echo "Error: compare failed: $table"
    echo '============================================'
 else
    echo "Compare OK."
    echo '--------------------------------------------'
 fi
 

 #!/bin/bash
 #====================================================
 #  dba_get_ddl.sh
 #
 #
 #====================================================
 
 if test $# -lt 2; then
    echo "Parms are:  SERVER  DBNAME   TNAME  file(optional)"
    exit
 fi
 
 dbserver=$1
 dbname=$2
 tname=$3
 ofile=$4
 tfile=/opt/dba/util/temp/tdef$$.txt
 dfile=/opt/dba/util/temp/tdef$$.sql
 UTIL=/opt/dba/util/scripts
 secret=`cat /opt/dba/util/local/secret.dat`
 
 options="-S$dbserver -D$dbname -Usa -P$secret -I/opt/dba/util/interfaces.test -o$tfile"
 
 isql -w199 $options << finis
 
 sp_help $tname
 go
 
 finis
 
 
 $UTIL/help2sql_xe.pl $tfile $dfile
 
 if test $# -eq 4; then
    cp $dfile $ofile
 fi
 
 
 cat $dfile
 
 
 rm $tfile  2>/dev/null
 rm $dfile  2>/dev/null
 

 #!/usr/bin/perl
 #==================================================
 #  help2sql_xe.pl
 #
 #==================================================
 
 $fname = $ARGV[0];
 $dfile = $ARGV[1];
 
 $tables = 0 ;
 $idx    = 0 ;
 
 open(FILE1,"<$fname");
 open(FILE2,">$dfile");
 
 while ()
   {
   $line1 = $_ ;
   chomp $line1 ;
   $line1 =~ s/ +$// ;
   $line1 =~ s/ +/ / ;
   $line1 =~ s/\t/   /g ;
 
   $print_ind = 0;
 
   if ($line1 =~ /base table/)
      {  $line1 = ' ' ; }
 
 
   if (($line1 =~ /dbo/) && ($line1 =~ /[a-z]/))
      {
      $line1 =~ s/^ +//g ;
      $line1 =~ s/ +.+//g ;
 
      if ($tables > 0)
         {
         $line2 = "\n)\ngo\n\n" . 'create table ' . $line1 . ' (' . "\n" ;
         }
      else
         {
         $line2 = 'create table ' . $line1 . ' (' . "\n" ;
         }
      $tname = $line1;
 
 
      $print_ind = 1;
 
      $tables ++ ;
      }
 
   if ($line1 =~ /clustered/)
      {
      $line1 =~ s/, /,/g ;
 
      @line1 = split ( ' ', $line1) ;
 
      $index_name = shift @line1 ;
      $index_cols = shift @line1 ;
      $index_opt  = shift @line1 ;
 
      $idx ++ ;
      $idx = 0 ;
 
      if ($idx eq 99)
          {
          $line2 = "\n)\ngo\n\n"  ;
          print FILE2 $line2 ;
          }
 
      $line2 = "create $index_opt index $index_name on $tname ( $index_cols )\ngo\n" ;
 
      push @indexes, $line2 ;
 
      $print_ind = 0;
      }
 
   if (($line1 =~ /NULL/) && ($line1 =~ /[a-z]/))
      {
      @line1 = split ( ' ', $line1) ;
 
      $field = shift @line1 ;
      $type  = shift @line1 ;
      $length  = shift @line1 ;
      $prec    = shift @line1 ;
      $scale   = shift @line1 ;
      $nulls   = shift @line1 ;
 
      if ($type =~ /char/)
         {
         $type = $type . '(' . $length . ')' ;
         }
      if ($type =~ /numeric/)
         {
         $type = $type . '(' . $prec . ',' . $scale . ')' ;
         }
 
      if ($nulls =~ /0/)
         { $nulls = 'not null' ;  }
      else
         { $nulls = 'null' ;      }
 
 
      if ($line3 =~ /null/)
         {
         $line2 = ",\n" . $field . "\t\t\t" . $type . "\t\t\t" . $nulls ;
         }
      else
         {
         $line2 = $field . "\t\t\t" . $type . "\t\t\t" . $nulls ;
         }
 
      $print_ind = 1 ;
      }
 
   if ($print_ind eq 1)
        {
        print FILE2 $line2 ;
        $line3 = $line2 ;
        }
   }
 
 if ($idx eq 0)
          {
          $line2 = "\n)\ngo\n\n"  ;
          print FILE2 $line2 ;
          }
 
 # This is it .. where we sort the indexes!
 
 @indexes2 = sort @indexes ;
 
 foreach $index (@indexes2)
    {
    print FILE2 $index ;
    }
 
 close(FILE1);
 close(FILE2);
 

 -- view_list.sql
 
 set nocount on
 go
 select substring(name,1,45) from sysobjects where type = 'V'
   and name not like 'rs%'
   and name not like 'sys%'
 order by 1
 go
 

 -- proc_list.sql
 
 set nocount on
 go
 select substring(name,1,85) from sysobjects where type = 'P'
   and name not like 'rs%'
 order by 1
 go
 

 #!/bin/bash
 #=====================================================
 #
 #  dba_compare_proc.sh
 #
 #=====================================================
 
 server1=$1
 dbname1=$2
 server2=$3
 dbname2=$4
 proc=$5
 
 echo "Proc compare for $server1 : $proc"
 
 UTIL=/opt/dba/util/scripts
 TEMP=/opt/dba/util/ase/procs
 
 proc1=$TEMP/${server1}.${dbname1}.${proc}.sql
 proc2=$TEMP/${server2}.${dbname2}.${proc}.sql
 
 cd $TEMP
 
 echo "Getting proc def from db .."
 $UTIL/getprocdef.sh $server1 $dbname1 $proc
 
 echo "Getting proc def from db .."
 $UTIL/getprocdef.sh $server2 $dbname2 $proc
 
 diff $proc1 $proc2
 
 if test $? -ne 0; then
    echo "Error: compare failed: $proc"
    echo '============================================'
 else
    echo "Compare OK."
    echo '--------------------------------------------'
 fi
 

 -- protect.sql
 
 sp_helprotect
 go
 

 #!/bin/bash
 #==================================================================
 # dba_compare_grants.sh
 #
 # Sample call:   ./dba_compare_grants.sh BISMARK sales_db HAMILTON sales_db
 #
 #==================================================================
 
 dbsource=$1
 dbsourcedb=$2
 dbtarget=$3
 dbtargetdb=$4
 
 if test $# -lt 4; then
    echo "Options are:  dbsource  dbsourcedb  dbtarget dbtargetdb"
    exit
 fi
 
 echo "Grants compare for $dbsource with $dbtarget"
 
 UTIL=/opt/dba/util/scripts
 TEMP=/opt/dba/util/temp
 secret1=`cat /opt/dba/util/local/secret.dat`
 secret2=`cat /opt/dba/util/local/secret.dat_prod`
 
 grants=grants
 
 tfile1=$TEMP/${grants}_A.txt
 tfile2=$TEMP/${grants}_B.txt
 tfile3=$TEMP/${grants}_C.txt
 tfile4=$TEMP/${grants}_D.txt
 tfile5=$TEMP/${dbtarget}_${dbtargetdb}.sql
 
 
 cd $TEMP
 
 
 echo "Getting grants from $dbsource .."
 $UTIL/isql.sh $dbsource -D$dbsourcedb -i${UTIL}/protects.sql -Usa -P${secret1} -o${tfile1}
 sleep 2
 
 echo "Getting grants from $dbtarget .."
 $UTIL/isql.sh $dbtarget -D$dbtargetdb -i${UTIL}/protects.sql -Usa -P${secret2} -o${tfile2}
 sleep 2
 
 
 cat $tfile1 | sed 's/Truncate Table/TRUNC/' | awk '{ print $3 " " $4 " on " $5 " to " $2 ; } ' > $tfile3
 
 cat $tfile2 | sed 's/Truncate Table/TRUNC/' | awk '{ print $3 " " $4 " on " $5 " to " $2 ; } ' > $tfile4
 
 ls -ltr $tfile3
 ls -ltr $tfile4
 
 sleep 2
 
 ${UTIL}/file_diff.pl $tfile3 $tfile4 | egrep -v '\-\-\-\-\-' | awk '{ print $0; print "go" ; }' |           sed 's/TRUNC/Truncate Table/'  > $tfile5
 
 ct=`grep -i grant $tfile5 | wc -l`
 
 if test $ct -ne 0; then
    echo "Error: compare failed: $grants"
    echo "$tfile5 .......... $ct grants needed in $dbtarget"
    echo '============================================'
    echo "Press enter to run the grants against $dbtargetdb .."
    read sel1
    $UTIL/isql.sh $dbtarget -D$dbtargetdb -i${tfile5} -Usa -P${secret2} -e
 else
    echo "Compare OK."
    echo '--------------------------------------------'
 fi
 
 

 -- config.sql
 
 sp_configure
 go
 

 #!/bin/bash
 
 dbsource=$1
 dbtarget=$2
 
 ISQL=/opt/dba/util/scripts/isql.sh
 UTIL=/opt/dba/util/ase/sql
 
 file1=/tmp/part1.txt
 file2=/tmp/part2.txt
 
 option='-I/opt/dba/util/interfaces.test'
 
 $ISQL -S$dbsource -Usa -i${UTIL}/config.sql $option | tee  $file1
 
 $ISQL -S$dbtarget -Usa -i${UTIL}/config.sql $option | tee  $file2
 
 
 echo "========================================="
 echo "Comparing $dbsource to $dbtarget .."
 echo "========================================="
 
 ./dba_compare_config.pl config.dat $file1 $file2  | sort -u
 
 

 #!/usr/bin/perl
 #
 # Program:  dba_compare_config.pl
 #
 # Purpose:  Process config file, show diff
 #
 # Parms:    dat-file (lists all config options),  infile, outfile
 #
 # Modifications:  none required
 
 $cdata = $ARGV[0];
 $file1 = $ARGV[1];
 $file2 = $ARGV[2];
 
 $vcount = 0 ;
 
 # ---------------------------------
 # Step 1: Collect config items
 # ---------------------------------
 
 open(FILE1,"<$cdata") or die "ERROR: fname #1" ;
 
 $print_ind = 0 ;
 
 while ()
   {
   $line1 = $_ ;
   chomp $line1 ;
 
   $line1 =~ s/^ // ;
   $line1 =~ s/ +$// ;
 
   $A_config { $line1 } = 1 ;
 
   push @configs, $line1 ;
   }
 
 close(FILE1);
 
 
 open(FILE1,"<$file1")    or die "ERROR: file1";
 
 while ()
   {
   $line1 = $_ ;
   chomp $line1 ;
 
   foreach $config ( @configs )
    {
    if ($line1 =~ /$config/)
        {
        $line1 =~ s/$config// ;
 
        @line1 = split ' ', $line1 ;
 
        $part1 = shift @line1 ;
        $part2 = shift @line1 ;
        $part3 = shift @line1 ;
        $part4 = shift @line1 ;
 
        $A_config { $config } = $part4 ;
        }
    }
 
   }
 
 
 close(FILE1);
 
 
 
 open(FILE2,"<$file2")    or die "ERROR: file2";
 
 while ()
   {
   $line1 = $_ ;
   chomp $line1 ;
 
   foreach $config ( @configs )
    {
    if ($line1 =~ /$config/)
        {
        $line1 =~ s/$config// ;
 
        @line1 = split ' ', $line1 ;
 
        $part1 = shift @line1 ;
        $part2 = shift @line1 ;
        $part3 = shift @line1 ;
        $part4 = shift @line1 ;
 
        if  ( $A_config { $config } ne $part4  )
               {
               print "Diff detected:  $config \t\t $A_config{$config} \t\t $part4  \n" ;
               }
        }
    }
 
   }
 
 
 close(FILE2);
 
 

 #!/bin/bash
 #=======================================================
 #  dba_compare_db.sh
 #
 #  Compare tables, views, procs across 2 ASE databases
 #
 #=======================================================
 
 server1=$1
 server2=$2
 dbname=$3
 
 dbuser=sa
 secret=`cat /opt/dba/util/local/secret.dat`
 sdir=/opt/dba/util/scripts
 tdir=/opt/dba/util/temp
 isqlx="${sdir}/isql.sh"
 options="-Usa -D${dbname} -P${secret} -i"
 
 cd $sdir
 
 echo "Get tables .."
 $isqlx $server1 $options table_list.sql -o${tdir}/tables.dat
 
 echo "Get procs .."
 $isqlx $server1 $options proc_list.sql -o${tdir}/procs.dat
 
 echo "Get views .."
 $isqlx $server1 $options view_list.sql -o${tdir}/views.dat
 
 wc -l ${tdir}/tables.dat
 wc -l ${tdir}/procs.dat
 wc -l ${tdir}/views.dat
 
 echo "Analyzing .."
 sleep 5
 
 for tname in `egrep '[a-z]' ${tdir}/tables.dat` ; do
 
    echo "---> TABLE: $tname"
    ./dba_compare_tables.sh $server1 $dbname $server2 $dbname $tname > $tdir/compare.log
    ct=`grep OK $tdir/compare.log | wc -l`
    if test $ct -eq 0; then
        cat $tdir/compare.log
        sleep 2
    else
        echo "-- Table compare for $tname OK."
    fi
 
 done
 
 sleep 3
 
 for tname in `egrep '[a-z]' ${tdir}/views.dat` ; do
 
    echo "---> VIEW: $tname"
    ./dba_compare_proc.sh $server1 $dbname $server2 $dbname $tname > $tdir/compare.log
    ct=`grep OK $tdir/compare.log | wc -l`
    if test $ct -eq 0; then
        cat $tdir/compare.log
        sleep 2
    else
        echo "-- View compare for $tname OK."
    fi
 
 done
 
 sleep 3
 
 for tname in `egrep '[a-z]' ${tdir}/procs.dat` ; do
 
    echo "---> PROC: $tname"
    ./dba_compare_proc.sh $server1 $dbname $server2 $dbname $tname > $tdir/compare.log
    ct=`grep OK $tdir/compare.log | wc -l`
    if test $ct -eq 0; then
        cat $tdir/compare.log | tail -3
        sleep 2
    else
        echo "-- Proc compare for $tname OK."
    fi
 
 done
 
 echo "Analyzing .. COMPLETE."
 

 -- config.dat ; feel free to add config options for newer versions of ASE as appropriate.
  abstract plan cache
  abstract plan dump
  abstract plan load
  abstract plan replace
  additional network memory
  allocate max shared memory
  allow backward scans
  allow nested triggers
  allow procedure grouping
  allow remote access
  allow resource limits
  allow sendmsg
  allow sql server async i/o
  allow updates to system tables
  audit queue size
  auditing
  auto query tuning
  average cap size
  caps per ccb
  check password for digit
  cis bulk insert array size
  cis bulk insert batch size
  cis connect timeout
  cis cursor rows
  cis idle connection timeout
  cis packet size
  cis rpc handling
  compression memory size
  configuration file
  cost of a cpu unit
  cost of a logical io
  cost of a physical io
  cpu accounting flush interval
  cpu grace time
  curread change w/ open cursors
  current audit table
  deadlock checking period
  deadlock pipe active
  deadlock pipe max messages
  deadlock retries
  default character set id
  default data cache
  default data cache Active
  default database size
  default exp_row_size percent
  default fill factor percent
  default language id
  default network packet size
  default sortorder id
  default unicode sortorder
  default xml sortorder
  disable character set conversion
  disable disk mirroring
  disk i/o structures
  dtm detach timeout period
  dtm lock timeout period
  dump on conditions
  dynamic allocation on demand
  enable DTM
  enable HA
  enable cis
  enable encrypted columns
  enable file access
  enable full-text search
  enable housekeeper GC
  enable java
  enable job scheduler
  enable ldap user auth
  enable literal autoparam
  enable logins during recovery
  enable merge join
  enable metrics capture
  enable monitoring
  enable pam user auth
  enable query tuning mem limit
  enable query tuning time limit
  enable real time messaging
  enable rep agent threads
  enable row level access
  enable semantic partitioning
  enable snmp
  enable sort-merge join and JTC
  enable sql debugger
  enable ssl
  enable stmt cache monitoring
  enable surrogate processing
  enable unicode conversions
  enable unicode normalization
  enable webservices
  enable xact coordination
  enable xml
  engine memory log size
  errorlog pipe active
  errorlog pipe max messages
  esp execution priority
  esp execution stacksize
  esp unload dll
  event buffers per engine
  event log computer name
  event logging
  executable codesize + overhead
  extended cache size
  global async prefetch limit
  global cache partition number
  heap memory per user
  histogram tuning factor
  housekeeper free write percent
  i/o accounting flush interval
  i/o batch size
  i/o polling process count
  identity burning set factor
  identity grab size
  identity reservation size
  job scheduler interval
  job scheduler tasks
  license information
  lock address spinlock ratio
  lock hashtable size
  lock scheme
  lock shared memory
  lock spinlock ratio
  lock table spinlock ratio
  lock wait period
  log audit logon failure
  log audit logon success
  max SQL text monitored
  max async i/os per engine
  max async i/os per server
  max buffers per lava operator
  max cis remote connections
  max concurrently recovered db
  max memory
  max native threads per engine
  max nesting level
  max network packet size
  max number network listeners
  max online Q engines
  max online engines
  max parallel degree
  max query parallel degree
  max repartition degree
  max resource granularity
  max scan parallel degree
  maximum dump conditions
  maximum failed logins
  maximum job output
  memory alignment boundary
  memory per worker process
  messaging memory
  metrics elap max
  metrics exec max
  metrics lio max
  metrics pio max
  min pages for parallel scan
  minimum password length
  mnc_full_index_filter
  msg confidentiality reqd
  msg integrity reqd
  net password encryption reqd
  number of Q engines at startup
  number of alarms
  number of aux scan descriptors
  number of ccbs
  number of checkpoint tasks
  number of devices
  number of dtx participants
  number of dump threads
  number of engines at startup
  number of histogram steps
  number of index trips
  number of java sockets
  number of large i/o buffers
  number of locks
  number of mailboxes
  number of messages
  number of oam trips
  number of open databases
  number of open indexes
  number of open objects
  number of open partitions
  number of pre-allocated extent
  number of remote connections
  number of remote logins
  number of remote sites
  number of sort buffers
  number of user connections
  number of worker processes
  o/s file descriptors
  object lockwait timing
  open index hash spinlock ratio
  open index spinlock ratio
  open object spinlock ratio
  optimization goal
  optimization timeout limit
  page lock promotion HWM
  page lock promotion LWM
  page lock promotion PCT
  page utilization percent
  partition groups
  partition spinlock ratio
  per object statistics active
  percent database for history
  percent database for output
  percent history free
  percent output free
  performance monitoring option
  permission cache entries
  plan text pipe active
  plan text pipe max messages
  print deadlock information
  print recovery information
  procedure cache size
  process wait events
  prod-consumer overlap factor
  query tuning plan executions
  read committed with lock
  recovery interval in minutes
  remote server pre-read packets
  restricted decrypt permission
  row lock promotion HWM
  row lock promotion LWM
  row lock promotion PCT
  rtm thread idle wait period
  runnable process search count
  sampling percent
  secure default login
  select on syscomments.text
  send doneinproc tokens
  session tempdb log cache size
  shared memory starting address
  size of auto identity column
  size of global fixed heap
  size of process object heap
  size of shared class heap
  size of unilib cache
  sproc optimize timeout limit
  sql server clock tick length
  sql text pipe active
  sql text pipe max messages
  stack guard size
  stack size
  start mail session
  start xp server during boot
  statement cache size
  statement pipe active
  statement pipe max messages
  statement statistics active
  strict dtm enforcement
  suspend audit when device full
  syb_sendmsg port number
  sysstatistics flush interval
  systemwide password expiration
  tape retention in days
  tcp no delay
  text prefetch size
  time slice
  total data cache size
  total logical memory
  total physical memory
  txn to pss ratio
  unified login required
  upgrade version
  use security services
  user log cache size
  user log cache spinlock ratio
  wait event timing
  xact coordination interval
  xp_cmdshell context
 

 #!/usr/bin/perl
 #
 # file_diff.pl
 #
 # Find items in file1 which are not in file2
 #
 # Parms:    infile  outfile
 #
 
 $fname1 = $ARGV[0];
 $fname2 = $ARGV[1];
 
 open(FILE1,"<$fname1") or die "ERROR: fname #1" ;
 open(FILE2,"<$fname2") or die "ERROR: fname #2" ;
 
 while ()
   {
   $line1 = $_ ;
 
   chomp $line1 ;
 
   $A_oname { $line1 } = 1 ;
   }
 
 close(FILE2);
 
 
 while ()
   {
   $line1 = $_ ;
 
   chomp $line1 ;
 
   if (! ( $A_oname { $line1 } ))
       {
       print $line1 . "\n" ;
       }
   }
 
 close(FILE1);