Sybase Archives - MemoSoup

MS SQL & Sybase: recreate master database

Sybase: before 12.5: buildmaster (bldmatr for NT)

buildmaster -d master_device -s size_in_2k_pages

12.5 and later: dataserver with some options (sqlsvr for NT)

dataserver -d master_device -b size -forcebuild -z page_size -Z size_of_master_db

MS SQL:

before MS 2005: rebuildm utility (GUI)

MS SQL 2005:

Start server in single-user mode (sqlserver.exe -m) setup.exe /qn INSTANCENAME=instance_name REINSTALL=SQL_Engine REBUILDDATABASE=1 […]

Quick calculation of the row count

Warning! It depends on the statistics and could be not accurate!

Sybase

select user_name( obj.uid), obj.name , rowcnt(doampg) from sysindexes idx, sysobjects obj where idx.indid < 2 and idx.id = obj.id […]

Creation and using of Sybase DBCCDB database

Planning:

sp_plan_dbccdb ‘{dbname}’

Creation:

create database dbccdb on … log on … isql -U sa -i $SYBASE/$SYBASE_ASE/scripts/installdbccdb

DBCCDB Configuration:

sp_configure "number of worker processes", {value_from_sp_plan} — create named cache for dbccdb sp_cacheconfig "dbccdb_cache", "{cache_size}M" — Size is at least 40 * 16K * (number of working processes) sp_poolconfig "dbccdb_cache", "{pool_size}M", "16K"

Working space configuration:

[…]

Find actual device numbers in Sybase

select low/16777216 "vdevno", name, phyname from master..sysdevices where status&=2 order by 1

Warning! For vdevno>127 it could generate incorrect results.

Converting timestamp to char

Sometimes it’s necessary to convert Sybase timestamp to something more readable Warning! Timestamp is not date type!

declare @p timestamp declare @s varchar(16) declare @t1, @t2 varbinary(4) //— from char to timestamp @t1 = hextoint(substring(lower(@s),1,8)) @t2 = hextoint(substring(lower(@s),9,8)) @p = @t1 + @t2 //— from timestamp to char @s=(lower(convert(varchar(8),intohex(substring(p,1,4))))+ lower(convert(varchar(8),intohex(substring(p,5,4))))

Sybase sp_freedevice procedure

Only the short info about Sybase database devices. Parameters: min_space – minimum of required space (i.e. 40 will display the devices with >=40M free space) the_name – the template for the device name (i.e. %mast% will display only master device)

Warning! It was not tested for all possible configurations, so the result could be incorrect […]

Sybase simplified sp_who procedure

The procedure displays only the necessary fields from sysprocesses:

create procedure sp_who_all as select PR.spid , PR.fid , substring(suser_name(PR.suid),1,10) Login_name, substring(PR.program_name,1,10) Program , PR.status , cmd , substring(db_name(dbid),1,19) db_name, substring(tran_name,1,22) tran_name, blocked, physical_io from master..sysprocesses PR go

Process all databases in Sybase

#!/usr/bin/ksh echo "Started…" print -u2 -n "Enter password:" stty -echo read PASS stty echo print rm -f databases.lst isql -U sa -S $DSQUERY -w 999<<EOF | awk ‘/DBLABEL/ { print $2 }’ > databases.lst $PASS select ‘DBLABEL’ "XXX", name from master..sysdatabases go exit EOF for DB in $(cat databases.lst) ; do print -u2 "Processing $DB […]