#!/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