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

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

/* 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

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

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

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

/* 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

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

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

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>

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)

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

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)

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

[Named Cache:dev_cache1]
        cache size = 4M
        cache status = mixed 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 */

/* 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

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

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

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

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

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

/* 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

• 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.

/* 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 */

/* 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 */

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

use invoice_db
go
dbcc tablealloc(tablename, full, fix)
go

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

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 

     sp_setpsexe 14, 'priority', 'LOW'

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