Post-installation check

Do not leave master as the default device, database creates without a device specification will be created in master.

1> sp_diskdefault master, defaultoff
2> go
(return status = 0)
1> sp_diskdefault device26, defaulton
2> go

Routine memory check

dbcc traceon(3604)
go
dbcc memusage
go
dbcc traceoff(3604)
go


/* sample post-install config, for 12.5 ASE */


-- send results, no wait
sp_configure 'tcp no delay',1
go


-- allocate 1.2 gb to sybase
sp_configure 'max memory',600000
go
-- allocate at sybase boot time
sp_configure 'lock shared memory',1
go

-- additional data cache
sp_cacheconfig 'default data cache','600M'
go
-- additional procedure cache
sp_cacheconfig 'procedure cache','50M'
go

-- cache for tempdb
sp_cacheconfig 'cache01','80M'
go

/* reboot ASE */



-- Additional config, for server w/several CPUs


sp_configure "number of user connections",500
go
sp_configure "number of worker processes",100
go
sp_configure "max parallel degree",3
go
sp_configure "max scan parallel degree",3
go
sp_configure "global cache partition number",2
go
sp_configure "number of locks",50000
go
sp_configure "number of open objects",50000
go
sp_configure "number of open databases",32
go
sp_configure "number of devices",50
go

/* reboot ASE */


-- Additional config, for system using text/blob data


sp_configure 'additional network memory',4096
go
sp_configure 'max network packet size',2048
go
sp_configure 'default network packet size',1024
go

sp_configure 'heap memory per user',4096
go


/* 
   UNIX Sybase >= 11.9, allow device buffering in O/S;
   - improves performance
   - increases chance of device corruption during failure
*/

sp_deviceattr "device21","dsync","false"
go


/* LINUX:  may need to set shared memory */

echo 134217728 > /proc/sys/kernel/shmmax

echo 999999999 > /proc/sys/kernel/shmmax

Extend tempdb: size should be about 20% of the main production database's size.

/* configure tempdb to 20 mb ... this command adds an additional
   18 meg to the 2 mb already present on the master device */
1> alter database tempdb on device26 = 18
2> go


/* Add local server name */

sp_addserver  snoopy, local
go

Starting the Sybase process

Data server:

nohup /apps/sybase/install/startserver \
   -f /apps/sybase/install/RUN_sybase1 >> startup.log &

Backup server:

nohup /apps/sybase/install/startserver \
   -f /apps/sybase/install/RUN_SYB_BACKUP & >> startup.log

---

Device initialization

/* create a 2 gig device */
1> disk init name = 'device19',
2>      physname  = '/dev/md/rdsk/d19',
3>      vdevno    = 6,
4>      size      = 1024000
5> go

---

Database creation

/* create a 1 gig database, with a 50 mb transaction log */
/* for load clause allows quick creation when dump is available */
1> create database
2>    dbname
3>    on device18 = 1000
4>    log on device8 = 50
5>    for load
6> go
CREATE DATABASE: allocating 512000 pages on disk 'device18'
CREATE DATABASE: allocating 25600 pages on disk 'device8'
/* change the database owner */
use dbname
go
1> sp_changedbowner 'jmith'
2> go
/* set up automatic log truncate, for development mode */
use master
go
sp_dboption 'dbname','trunc log on chkpt',true
go

---

A backup routine

use master
go
sp_dboption dbname, "single user", true
go
use dbname
go
checkpoint
go
dbcc checkdb (dname,skip_ncindex)
go
dbcc checkcatalog
go
dbcc checkalloc
go
use master
go
sp_dboption dbname, "single user", false
go
use dbname
go
checkpoint
go
dump tran dbname to device1
go
dump database dbname to device1
go

---

Striping Dump Devices

Sybase (prior to version 12) has a 2 GB dump file size limitation for most platforms. Getting around this is easy - simply stripe the dumps across multiple files or devices. The examples below use file names instead of device names.

dump database hr_db to '/usr2/dumps/remote/db_hr05121318.dmp'
  stripe on '/usr2/dumps/remote/db_hr_S1_05121318.dmp'
  stripe on '/usr2/dumps/remote/db_hr_S2_05121318.dmp'
go


load database hr_db from  '/usr2/dumps/remote/db_hr05121318.dmp'
  stripe on '/usr2/dumps/remote/db_hr_S1_05121318.dmp'
  stripe on '/usr2/dumps/remote/db_hr_S2_05121318.dmp'
go
online database hr_db
go

---

Moving the transaction log to another device

1> alter database dbname log on device19 = 10
1> sp_logdevice dbname, device19
The last-chance threshold for database dbname is now 1232 pages.
 ... sql inserts, to fill old log segment ...
1> dump tran dbname with truncate_only

1> sp_helplog dbname
2> go
In database 'dbname', the log starts on device 'device19'.
(return status = 0)
1>

---

Adding a segment to a database

1> use dbname
2> go
1> sp_addsegment 'idx_seg1','dbname','device18'
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Segment created.
1> use dbname
2> go
1> sp_dropsegment 'system','dbname','device18'
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Segment reference to device dropped.
(return status = 0)
1> sp_dropsegment 'default','dbname','device18'
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Segment reference to device dropped.
(return status = 0)

---

Setting the thresholds

Threshold settings allow customized procedures to be run when database segments approach a defined capacity. The "last chance threshold" is set by default, to execute sp_thresholdaction within the current database, when a segment reaches 95% of capacity. The procedure sp_thresholdaction needs to be created by the DBA. Here is a sample:

create proc sp_thresholdaction (
  @dbname varchar(30),
  @segmentname varchar(30),
  @space_left int,
  @status int )  as

  declare @msg    varchar(80),
          @date1  datetime,
          @fname  varchar(80),
          @fdate  varchar(20),

@fpath  varchar(40)

select @fpath = '/usr/dumps/logs/'

select @date1 = getdate()

  select @fdate =
   convert(varchar(2),datepart(MM,@date1)) +
   convert(varchar(2),datepart(DD,@date1)) +
   convert(varchar(2),datepart(HH,@date1)) +
   convert(varchar(2),datepart(MI,@date1))

  select @fname = @fpath + 'log_' + @dbname + @fdate + '.dmp'

  select @msg = '***!! Last Chance Threshold reached, for ' + @dbname + '(' + @segmentname + ')'

  print @msg

  if @segmentname = 'logsegment'
     dump tran @dbname to @fname

return

Other threshold levels can be created, for specific segments. They can be set up to print informational messages to the error log, as a forewarning to the DBA. Here's a sample which reflects the command syntax:

1> sp_addthreshold dbname,logsegment,400,'proc_log_threshold'
2> go
Adding threshold for segment 'logsegment' at '400' pages.
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
(return status = 0)

---

Configuring the cache

Important: for ASE 12.5, the default data cache MUST be configured !

use master
go
sp_cacheconfig 'cache01','4M'
go

Entry in config file looks like this:

[Named Cache:dev_cache1]
        cache size = 4M
        cache status = mixed cache

Next, database objects need to be bound to the cache

use dev_main_db
go
sp_bindcache 'dev_cache01','dev_main_db','customer'
go
sp_helpcache
go



/* see sample post-install config above for more examples */

---

Security Tasks

/* create a super user, along with database ownership */
use silvermaster
go
sp_addlogin 'silveruser','silver','silvermaster'
go
sp_role 'grant','sa_role','silveruser'
go
sp_changedbowner silveruser
go
/* create a developer profile */
sp_addlogin 'jsmith','yankees','silvermaster'
go
use silvermaster
go
sp_addalias 'jsmith','dbo'
go
/* change jsmith password, note how SA/SSO pwd is required here */
sp_password 'sa_pwd','dodgers','jsmith'
go

---

Running SQL within a script

This script accepts a sybase command as a parameter, and executes it.

#!/usr/bin/ksh
#------------------------------------------------------
#  File: sybexec
#  Process Sybase command, output goes to std output
#  Parameter: SQL command, in quotes
#
#  Sample call:  sybexec "sp_helpdb billing_db"
#------------------------------------------------------
intfile=/apps/sybase/interfaces

eval /apps/sybase/bin/isql -Sserver -I$intfile -Ujsmith -Pyankees << finis

$1
go

finis

---

Apply a transaction dump

This script accepts a transaction file and dbname as parameters, and applies the data

#!/usr/bin/ksh
#-------------------------------------
#  Sybase database loader
#  Parms:  database, log dump file
#-------------------------------------

if test $# -lt 2
then
   echo " "
   echo "usage:"
   echo "------"
   echo "syb_applylog  "
   echo " "
   echo " "

   exit
fi

if test ! -f $2 ; then
   echo " "
   echo "Invalid dump file: "
   echo $2
   echo " "
   exit
fi

echo "-----------------------------------------------"
echo "`date`"
echo "**** Loading transaction dump file ..." $2

eval /apps/sybase/bin/isql \
 -SFocal1 -I/apps/sybase/interfaces -Ujsmith -Pyankees << finis

load transaction $1 from '$2'
go

finis

echo "-----------------------------------------------"
echo "`date`"
echo '**** Load complete.'

---

Apply multiple transaction dumps

This script accepts a directory and dbname as parameters, and applies the dumps in the directory, in filename order

#!/usr/bin/ksh
#-------------------------------------------------------
#  Log File Applier
#  Parms:  database name, dump directory containing logs
#-------------------------------------------------------
if test $# -lt 2 ; then
   echo " "
   echo "usage: "
   echo "syb_applylogs dbname sourcedir"
   echo " "
   exit
fi

if test -d $2 ; then
   mstatus="OK"
else
   echo " "
   echo " Invalid path: "
   echo $2
   echo " "

   exit
fi

for fname in $2/log*.dmp ; do
     echo $fname
     if test -f $fname ;  then
       /usr2/dumps/scripts/syb_applylog $1 $fname
     fi
done

---

Database maintenance procedure

This stored proc performs transaction dumps, or database dumps for a specified database. It is used in the script below.

use master
go

create proc sp_syb_maint (@dbname varchar(30),
                         @fpath  varchar(50),
                         @mode   varchar(15)) as

declare @fname1  varchar(50),
        @fname2  varchar(50),
        @fdate   varchar(12),
        @fdate1  varchar(12),
        @fdate2  varchar(12),
        @date1   datetime,
        @msg     varchar(80),
        @char1   char(1),
        @dbprefix char(3)

if (@mode = 'dbcc')
   return

select @fpath = rtrim(@fpath)

select @char1 = right(@fpath,1)

if (@char1 != char(47))
  select @fpath = @fpath + char(47)

select @date1 = getdate()

select @fdate1 = convert(varchar(12),@date1,112),
       @fdate2 = convert(varchar(12),@date1,108)

select @fdate =
   substring(@fdate1,5,4) +
   substring(@fdate2,1,2) +
   substring(@fdate2,4,2)

select @dbprefix = substring(@dbname,1,3)

select @fname1 = @fpath + 'log_' + @dbprefix + @fdate + '.dmp'
select @fname2 = @fpath + 'db_'  + @dbprefix + @fdate + '.dmp'

if ((@mode = 'dump') or (@mode = 'tran_only')) and
   charindex(@dbname,'master-model-tempdb-sybsystemprocs')=0
   begin
   select @msg = '*** Dumping transaction log to ' + @fname1
   print  @msg
   dump tran @dbname to @fname1
   end

if (@mode = 'dump') and
   charindex(@dbname,'model-tempdb-sybsystemprocs')=0
   begin
   select @msg = '*** Dumping database to ' + @fname2
   print  @msg
   dump database @dbname to @fname2
   end

return

---

Database maintenance script

This script performs DBCCs, transaction dumps, or database dumps for a specified database.

#!/usr/bin/ksh
#-------------------------------------
#  syb_maint
#
#  Sybase database maintenance: perform DBCCs / log backups / db backups
#
#  Parms:  database, dump dir, mode (dump | tran_only | dbcc)
#
#  Step 1:  DBCCs    (dbcc mode only)
#  Step 2:  Backup
#
#  Output is routed to backup.log & dbcc.log
#-------------------------------------

if test $# -lt 3
then
   echo " "
   echo "usage:"
   echo "------"
   echo "syb_maint   "
   echo " "
   echo " "

   exit
fi

if test ! -d $2 ; then
   echo " "
   echo " Invalid path: "
   echo $2
   echo " "

   exit
fi

if test ! -f /usr2/dumps/scripts/contact.txt ; then
   echo " contact.txt file not found "
   exit
fi

contact=`cat /usr2/dumps/scripts/contact.txt`
logfile1=/usr2/dumps/cronlogs/syb_maint/dbcc.log
logfile2=/usr2/dumps/cronlogs/syb_maint/backup.log

if test -f /tmp/syb_stop ; then
   echo " ***** db stop detected ***** " >> $logfile1
   exit
fi

echo "=============================" > /dev/null
echo $1                              > /dev/null
echo "=============================" > /dev/null

if test "$3" = "dbcc" ; then

echo "Running dbcc step ..." > /dev/null

eval /apps/sybase/bin/isql -Sserver -I/apps/sybase/interfaces \
  -Ujsmith -Pyankees  << finis >> $logfile1

print '***** DBCC $1 **************************************'
go
use master
go
sp_dboption $1, "single user", true
go
use $1
go
dbcc checkdb ($1,skip_ncindex)
go
dbcc checkcatalog
go
dbcc checkalloc
go
checkpoint
go
use master
go
sp_dboption $1, "single user", false
go

quit

finis

# check output

if egrep "error|corrupt" $logfile1 | egrep -v "printed|TABLE|Checking" > /dev/null
then

echo "*** Errors found in DBCC log file."
rmail $contact@focal.com << endmsg
*** Errors found in DBCC log file
.
endmsg

fi


fi


echo "Running dump step ..." > /dev/null

eval /apps/sybase/bin/isql -Sserver -I/apps/sybase/interfaces \
   -Ujsmith -Pyankees  << finis2 >> $logfile2

print '***** DUMP $1 **************************************'
go

use master
go

exec sp_syb_maint $1, '$2', '$3'
go

quit

finis2

if grep "error|corrupt" $logfile2 > /dev/null
then

echo "*** Errors found in backup log file"
rmail $contact@mycompany.com << endmsg2
*** Errors found in backup log file
.
endmsg2

fi

echo "Sybase maintenance complete" > /dev/null

---

BCP data to/from a flat file

/* export */
/apps/sybase/bin/bcp dbname..tablename out /data/data01.bcp \
  -c -Ujsmith -Pyankees -Sserver -I/apps/sybase/interfaces
/* import */
/apps/sybase/bin/bcp dbname..tablename in /data/data01.bcp \
  -c -Ujsmith -Pyankees -Sserver -I/apps/sybase/interfaces


/* BCP table "employee" to file named test1.txt */

/apps/sybase/bin/bcp dev_db..employee out test1.txt -c -t \\t -r \\n
   -Sserver -Ujsmith -I/apps/sybase/interfaces

/* BCP file named test2.txt into table employee */

/apps/sybase/bin/bcp dev_db..employee in test2.txt -c -t \\t -r \\n
    -Sserver -Ujsmith -I/apps/sybase/interfaces

Parms for each command:

database
table
in/out
character format specified (-c)
tab is the field separator (-t \\t)
newline is the record separator (-r \\n)
server
user
interfaces

---

Server configuration

One of the best enhancements included in System 11 is the addition of the editable configuration text file. This allows you to change the server's configuration using any text editor, and makes switching configuration files a snap. Notable configuration parameters:

• Total memory - memory allocated to SQL Server in 2K pages. This memory includes all memory used by the server process, including: data cache, procedure cache, program memory, and connection memory.
• Procedure cache - percent of cache allocated for stored procs. Decrease this value if stored procedures are not use frequently by your application. The default is 30.
• User connections - user connection take about 60K each. Set this parameter sparingly, as it takes more memory than most of the other config values.
• Sort order - set this parm as soon as possible. A sort order id of 50 is default, which is the case-sensitive type. Recommended: 52, this setting is not case-sensitive.

Dealing with a Corrupted Database
Hardware failures can result in databases that are corrupt and will not open upon restart of the server. In some cases the database is marked suspect, and then cannot be opened. The best way to deal with a database in this state is to nuke it and reload it from a backup. Here's a code snippet which will force the drop to occur, when drop database fails. 
/* note:  X=the dbid of the database (from sysdatabases) */

use master
go
sp_configure "allow updates",1
go
begin tran?go
update sysdatabases set status = 320 where dbid = X
go

/* always make sure the status has been changed to 320 */
select dbid, status from sysdatabases where dbid = X
go
commit tran
go
sp_configure 'allow updates', 0
go
checkpoint
go

/* recycle the server */

dbcc dbrepair (database_name, dropdb)
go

/* now, recycle the server and rebuild the database */



Dealing with a Server Failure
There are rare instances when the server crashes down so hard that it cannot be started again. In the synopsis that follows, the crash was due to extremely high database activity after the transaction log filled up - making it impossbile to clear. The server was brought down, and could not be restarted. The trick here was to bring up the server in "non-recovery" mode, and then clear the transaction log using some tricks from the Sybase support team. 
/* Note:  dbname = the database name, X = the dbid */


/* In the runserver file, add the following flags: */

-m
-T3608   (recover master and nothing else)

     -or-

-T3607   (no recovery)


/* Now, recycle the server */

Then, in isql:

sp_configure 'allow updates',1
go


update sysdatabases set status=-32768
where name = 'dbname'
go

select config_admin(1,102,1,0,null,null)
go


update sysdatabases set status=0 where dbid=X
go


/* recycle again, things should be OK */


DBCC Notes
DBCCs should be run on a regular basis to check for allocation errors, which occur due to hardware issues (in most cases). For 24x7 needs, DBCCs can be run on a separate server that is loaded from a current database dump. 

Here is a script which will perform the basic DBCC functions 
use master
go
sp_dboption invoice_db,'single user', true
go
use invoice_db
go
checkpoint
go



use invoice_db
go
select db_name()
go
checkpoint
go
dbcc checkdb
go
dbcc checkalloc
go
dbcc checkcatalog
go


use master
go
sp_dboption invoice_db,'single user',false
go
use invoice_db
go
checkpoint
go




Table or index allocation errors can be fixed by simply dropping the object and recreating it (using BCP as needed). See below for other repair methods. 



Here is a script which will fix many table allocation errors 
use invoice_db
go
dbcc tablealloc(tablename, full, fix)
go

Here is a script which will fix most page allocation errors 
use master
go
sp_dboption invoice_db,'single user', true
go
use invoice_db
go
checkpoint
go



use invoice_db
go
select db_name()
go
checkpoint
go
dbcc checkalloc(invoice_db,fix)
go


use master
go
sp_dboption invoice_db,'single user',false
go
use invoice_db
go
checkpoint
go




Intferace Files, IP and Port Translation
SUN installations have interface file entries that appear cryptic - see below for a dissection of a typical entry. 
Interfaces file fragment:

\x0002 08fc a825d0b5 0000000000000000


Breakdown appears below:

168.37.208.181,2300

a8   168
25   37
d0   208
b5   181



Explained:


0002 
               Denotes that this entry is a TLI "address family". This is always at
               the start of a TLI address. TCP/IP is family 2. Depending on the
               network vendor and the byte order of the machine, this works out as a
               hexadecimal "0002" (most common) or "0200" (the format is
               dependent on whether the machine is "little endian" or "big endian").
               Take a look at how your current interfaces file is structured to
               confirm your address family number format, and make a change to
               the variable ADDRESS_FAMILY in tli_mapper accordingly.

1E6C 
               This is the hexadecimal equivalent of the port number. In this
               example, the hexadecimal address 1E6C translates to the decimal
               address 7788.

9D0E7D24 
               This 8-digit hexadecimal address is the translation of the decimal IP
               address equivalent. The address is formed by translating each decimal
               portion of the IP address, separated by the period, to its hexidecimal
               equivalent(minus the periods). Single digits are entered with a leading
               zero. 
                 9D  157 
                 0E  14 
                 7D  125 
                 24  36 



Setting Process Priorities
With Sybase 11.9.5 and above, you can set the run class for processes to LOW, MEDIUM, or HIGH. 

Here's a sample call which sets the priority for a specific spid: 

     sp_setpsexe 14, 'priority', 'LOW'


Here are sample calls which define a class, and sets the priority for a login, forever: 

         sp_addexeclass 'rpt_class',LOW,null,'ANYENGINE'
         
         sp_bindexeclass 'bjenner','lg','null','rpt_class'




 discuss this topic to forum
[Print][Close][Bookmark:digg+,reddit+,del.icio.us+,dzone+]