Google
Web tarasdom.com

 

Sybase tricks and tips


Books about Sybase
ASE Quick Reference GuideASE Quick Reference Guide - very usefull book, just MUST HAVE for every Sybase DBA. It contains a short information about Sybase commands, description of the important parameters and a lot of other necessary things
Good book for beginners

Interfaces convertion: Solaris <-> text
Solaris:
Plain text:

Here is simplified sp_who procedure. It displays only the most used columns, like program name, I/O count, transaction name etc

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 , PR.cmd ,
substring(db_name(PR.dbid),1,19) db_name,
substring(PR.tran_name,1,22) tran_name,
PR.blocked, 
PR.physical_io
from master..sysprocesses PR
go

sp_freedevice

This procedure displays the information about Sybase devices, including name, used and free size.
Parameters:
  • min_space - the minimum free space on the device
  • the_name - name template for the device

Examples:
  • sp_freedevice - displays the full list of the devices
  • sp_freedevice 100,’mydata’ - displays the devices like mydata1, mydata2 etc, which have >= 100Mb free space

create procedure sp_freedevice
@min_space int = null,
@the_name varchar(40) = null
as
begin
declare @numpgsmb integer /* Number of 'virtual' Pages per Megabytes */
select @numpgsmb = (1048576. / @@pagesize)
select "phyname"=convert(varchar(40), d.phyname),
"name"=convert(varchar(28),d.name),
"d_size"=convert(varchar(6),(1 + d.high - d.low) / @numpgsmb),
"d_used"=convert(varchar(6), sum(u.size / @numpgsmb)) ,
"d_free"=((1 + d.high - d.low) / @numpgsmb) - sum(u.size / @numpgsmb),
vdevno=d.low/power(2,24) & 255
into #free_device_all_tbl
from master..sysusages u, master..sysdevices d
where u.vstart between d.low and d.high
and d.status & 2 = 2 
group by d.name
select 
vdevno=low/power(2,24) & 255,
"Physical Name"=convert(varchar(40), d.phyname),
"Device Name"=convert(varchar(28),d.name),
"Size"=convert(varchar(6),(1 + d.high - d.low) / @numpgsmb),
"Used"=convert(varchar(6),0),
"Free"=convert(varchar(6),(1 + d.high - d.low) / @numpgsmb)
from master..sysdevices d
where d.name not in (select tmp.name from #free_device_all_tbl tmp)
and d.status & 2 = 2
and ( @min_space is null or ((1 + d.high - d.low) / @numpgsmb) >= @min_space )
and ( @the_name is null or d.phyname like @the_name or d.name like @the_name)
union
select vdevno , phyname , name , d_size , d_used , convert(varchar(6), d_free) 
from #free_device_all_tbl
where ( @min_space is null or d_free >= @min_space )
and ( @the_name is null or phyname like @the_name or name like @the_name)
order by 1
end
go 

See also the article on memosoup.com.

©
Page was modified 14.06.2010