create_db_sybase.sh

 #!/bin/sh
#
# create sybase device
# Notice: please use sybase sa user to do this.
#

#//////////////////////////////////////////////////////
#create device

create_device()
{
#get current max device number

isql -U$USERNAME -P$PASSWORD -S$SERVER <<! >/tmp/a1
  select  max(convert(tinyint, substring(convert(binary(4), d.low),v.low, 1))) from sysdevices d,spt_values v where v.type="E" and v.number=3
go
!
dbdevicenum=`sed -e '1,2d' -e '4,5d' /tmp/a1`
rm /tmp/a1

#update max device number

isql -U$USERNAME -P$PASSWORD -S$SERVER <<!
sp_configure "number of devices",20
go
!

yn="n"

echo "Now Create Database Device..../n"
echo "Notice:you must create a master device and a log device for a database!!! /n/n"

while [ $yn = "n" ] || [ $yn = "N" ]
do
 subyn="n"
 while [ $subyn = "n" ] || [ $subyn = "N" ]
 do
 echo "please input db logical device_name:/c"
 read dbdevicename

 echo "please input db physical device path&name(eg:/home/sybase/data/test.dat):/c"
 read dbphyname
 
 echo "please input db_device_size(unit:2K):/c"
 read dbdevicesize

   dbdevicenum=`expr $dbdevicenum + 1`

 echo "/ndb logical device_name:"$dbdevicename
 echo "db physical device name:"${dbphyname}
 echo "db device size:"$dbdevicesize
 echo "db device number:"$dbdevicenum
 
 echo " /n All Inputs are Right(Y/n)? /c"
 read subyn
 done

echo "Now creating database device....."

dbphy=/"$dbphyname/"

isql -U$USERNAME -P$PASSWORD -S$SERVER <<!
disk init
name=$dbdevicename,
physname=$dbphy,
vdevno=$dbdevicenum,
size=$dbdevicesize
go
!

 echo "/nDevice Create Finish(Y/y)? /c"
 read yn
done
}

#/////////////////////////////////////////////////////////////
#create database

create_database()
{
echo "/nNow create DataBase...../n"

echo "Please Input database name: /c"
read dbname
echo "Please Input database device name: /c"
read dbdev
echo "Please Input device size(unit:1M): /c"
read dbdev_size
echo "Please Input database log device name: /c"
read dblogdev
echo "Please Input log device size(unit:1M): /c"
read dblogdev_size

isql -U$USERNAME -P$PASSWORD -S$SERVER <<!
create database $dbname on $dbdev=$dbdev_size log on $dblogdev=$dblogdev_size
go
!
}

#/////////////////////////////////////////////////////////////
#create a sybase user

create_user()
{
echo "Now Create User /n"

echo "Please input user name: /c"
read dbuser

echo "Please input user password: /c"
read dbuser_pass

isql -U$USERNAME -P$PASSWORD -S$SERVER <<!
sp_addlogin $dbuser,$dbuser_pass,$dbname
go
use $dbname
go
sp_changedbowner $dbuser,'true'
go
!
}

#////////////////////////////////////////////////////////////
#performance tuning

performance_tune()
{
echo "Now DataBase Performance......"

echo "Please Input Total Memory: /c"
read totalmem
echo "Please Input Connection Number: /c"
read conn
echo "Please Input Total locks: /c"
read locksnum
echo "Please Input procedure cache percent: /c"
read proc

isql -U$USERNAME -P$PASSWORD -S$SERVER <<!
use master
go
sp_dboption $dbname,'trunc log on chkpt',true
go
use $dbname
go
checkpoint
go
use master
go
sp_dboption tempdb,'trunc log on chkpt',true
go
use tempdb
go
checkpoint
go
use master
go
sp_dboption $dbname,'select into/bulkcopy/pllsort',true
go
use $dbname
go
checkpoint
go
use master
go
sp_configure 'total memory',$totalmem
go
use master
go
sp_configure "number of user connections",$conn
go
use master
go
sp_configure "number of locks",$locksnum
go
use master
go
sp_configure "procedure cache percent",$proc
go
!
echo "/n/n*****Notice: Please reboot Sybase System!!!*****"
}

#///////////////////////////////////////////////////////
#
#main
#
clear

USERNAME=$1
PASSWORD=$2
SERVER=$3

if [ $# != 3 ]
then
 echo "Usage:$0 sa_username sa_password server "
 exit 0
fi

chmain="y"
while [ $chmain = "y" ]
do
  echo "/n/n**************************"
  echo "   1 - Create DB Device"
  echo "   2 - Create DataBase"
  echo "   3 - Create User"
  echo "   4 - Performance Tune"
  echo "   0 - Exit"
  echo "--------------------------"
  echo "Please Choose: /c"
  read ch
  case $ch in
  1)    create_device
 ;;
  2)    create_database
 ;;
  3)    create_user
 ;;
  4)    performance_tune
 ;;
  0)    exit
 ;;
 
esac
done