Sybasehelp

dev usage

2009-06-07T07:35:00.001-07:00

IF OBJECT_ID('dbo.sp_dba_devusage') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.sp_dba_devusage
    IF OBJECT_ID('dbo.sp_dba_devusage') IS NOT NULL
        PRINT '<<<>>>'
    ELSE
        PRINT '<<<>>>'
END
go

create proc sp_dba_devusage
as
set nocount on

select distinct
     name = d.name,
     db_size = str(sum(u.size) / 512, 10, 1) + " MB",
     dbid = d.dbid,
     created = convert(char(14), d.crdate, 107)
     from master.dbo.sysdatabases d, master.dbo.sysusages u
     where d.dbid = u.dbid
     group by d.dbid, u.dbid
     order by d.dbid

declare @vsize int
select @vsize = low
from master.dbo.spt_values
where type="E"
and number = 3

select device = substring(name,1,15),
       vdevno = convert(tinyint,substring(convert(binary(4),low),@vsize,1)),
       "default disk?" = "      " + substring("NY",(status & 1)+1,1),
       "total (MB)" = str(round((high-low)/512.,2),7,2),
       used = str(round(isnull(sum(size),0)/512.,2),7,2),
       free = str(round(abs((high-low-isnull(sum(size),0))/512.),2),7,2)
from master.dbo.sysusages u,
     master.dbo.sysdevices d
where vstart between low and high
and cntrltype=0
group by all name
having cntrltype=0
order by vdevno

go
EXEC sp_procxmode 'dbo.sp_dba_devusage','unchained'
go
IF OBJECT_ID('dbo.sp_dba_devusage') IS NOT NULL
    PRINT '<<<>>>'
ELSE
    PRINT '<<<>>>'
go
USE sybsystemprocs
go
GRANT EXECUTE ON dbo.sp_dba_devusage TO public
go

check for blocks in database

2009-06-07T07:32:00.001-07:00

IF OBJECT_ID('dbo.sp_dba_dblock') IS NOT NULL

BEGIN

DROP PROCEDURE dbo.sp_dba_dblock

IF OBJECT_ID('dbo.sp_dba_dblock') IS NOT NULL

PRINT '<<<>>>'

ELSE

PRINT '<<<>>>'

END

create procedure sp_dba_dblock

declare @PRNTBUFFER varchar(150),

@CURRENTDB varchar(30),

@CURRENTDBID smallint,

@LOCKCOUNT int

select @CURRENTDB = rtrim(db_name())

select @CURRENTDBID = db_id()

select @LOCKCOUNT = count(dbid)

from master.dbo.syslocks

where master.dbo.syslocks.dbid = @CURRENTDBID

if ( @LOCKCOUNT = 0 )

begin

select @PRNTBUFFER = " " + replicate("=",68)

print @PRNTBUFFER

select @PRNTBUFFER = " No Locks In Database : " + rtrim(@CURRENTDB)

print @PRNTBUFFER

select @PRNTBUFFER = " " + replicate("=",68)

print @PRNTBUFFER

end

else

begin

select @PRNTBUFFER = " " + replicate("=",68)

print @PRNTBUFFER

select @PRNTBUFFER = " Locks In Database : " + rtrim(@CURRENTDB)

print @PRNTBUFFER

select @PRNTBUFFER = " " + replicate("=",68)

print @PRNTBUFFER

select Spid = convert(char(4),master.dbo.syslocks.spid),

SybaseUser = convert(char(12),master.dbo.syslogins.name),

LockType = convert(char(14),master.dbo.spt_values.name),

TableName = convert(char(18),object_name(master.dbo.syslocks.id)),

PageNumber = convert(char(14),master.dbo.syslocks.page),

UnixHost = convert(char(8),master.dbo.sysprocesses.hostname),

UnixPid = convert(char(8),master.dbo.sysprocesses.hostprocess)

from master.dbo.syslocks,

master.dbo.spt_values,

master.dbo.syslogins,

master.dbo.sysprocesses

where master.dbo.syslocks.type = master.dbo.spt_values.number

and master.dbo.sysprocesses.suid = master.dbo.syslogins.suid

and master.dbo.spt_values.type = "L"

and master.dbo.syslocks.spid = master.dbo.sysprocesses.spid

and master.dbo.syslocks.dbid = @CURRENTDBID

order by 1,4,5

end

return (0)

EXEC sp_procxmode 'dbo.sp_dba_dblock','unchained'

IF OBJECT_ID('dbo.sp_dba_dblock') IS NOT NULL

PRINT '<<<>>>'

ELSE

PRINT '<<<>>>'

USE sybsystemprocs

GRANT EXECUTE ON dbo.sp_dba_dblock TO public

gen database ddl

2009-06-07T07:31:00.000-07:00

create procedure sp_dba_gendbddl @dbname varchar(32) = null

declare @db_name varchar(30),

@dbid smallint,

@segmap smallint,

@segs varchar(4),

@dev_frag varchar(30),

@frag_size char(10),

@sql_string varchar(255),

@alter_status tinyint,

@counter int,

@seq_no int,

@numdbs int

set nocount on

if @dbname is null select @dbname = "%"

-- Match databases if given else do them all

select @numdbs = count(*) from master.dbo.sysdatabases where name like @dbname

-- DB exists ?

if @numdbs = 0

begin

/* 17590, "The specified database does not exist." */

raiserror 17590

return (1)

end

-- Initialize dbids twmp table from sysdatabases

select dbid into #dbids from master.dbo.sysdatabases where name like @dbname

declare @curdbid smallint /* the one we're currently working on */

declare @dbdesc varchar(102) /* the total description for the db */

declare @bitdesc varchar(30) /* the bit description for the db */

-- Set @curdbid to the first dbid.

select @curdbid = min(dbid) from #dbids

create table #db_info(

seq_no int not null,

sql_string varchar(255) null

)

-- Iterate through by dbid

while @curdbid is not NULL

begin

/************************************************

* Temporary table to hold individual fragments *

* of SQL_STRING, numbered to preserve their *

* correct sequence *

************************************************/

delete #db_info

-- Initialize variables

select @alter_status = 0

select @counter = 0

select @seq_no = 0

select @db_name = db_name(@curdbid)

/* Declare a cursor to allow processing of each row individually */

/* Select statement extracts relevant info from the system tables */

/* ORDER BY clause is critical in order to ensure that all fragments */

/* are processed in the correct sequence */

declare get_db_info cursor for

select db.dbid, usg.segmap, "device fragment"=substring(dev.name,1,15), "size(MB)" = str(usg.size/512.,10,0)

from master.dbo.sysusages usg,

master.dbo.sysdevices dev,

master.dbo.sysdatabases db

where vstart between low and high

and cntrltype = 0

and db.dbid = @curdbid

and db.dbid =usg.dbid

order by db.dbid,usg.lstart,dev.name

-- Prepare initial SQL string

select @sql_string = 'create database ' + @db_name + ' on '

-- Store initial SQL_STRING in temporary table and

-- number it to allow later extraction in the correct

-- sequence

insert into #db_info (seq_no, sql_string) values (@seq_no, @sql_string)

-- Increment the sequence number

select @seq_no = @seq_no + 1

-- Open the cursor and fetch the first row

open get_db_info

fetch get_db_info into @dbid,@segmap,@dev_frag,@frag_size

if (@@sqlstatus=2)

begin

select "No information found for database"

close get_db_info

deallocate cursor get_db_info

return

end

while (@@sqlstatus = 0)

begin

if @alter_status = 0 /* Still part of original 'create database ...' command */

begin

if @segmap = 4 /* Must refer to log device */

begin

select @sql_string = ' log on ' + @dev_frag + ' = ' +

ltrim(convert(varchar(20),@frag_size)) + char(10) + 'go' + char(10)

select @alter_status = 1

end

else /* Must refer to data device */

begin

if @counter = 0 /* 1st data device of "create database ..." */

begin

select @sql_string = ' ' + @dev_frag + ' = ' + ltrim(convert(varchar(20),@frag_size))

select @counter = @counter + 1

end

else /* Additional data device for "create database ..." */

begin

select @sql_string = ' ,' + @dev_frag + ' = ' + ltrim(convert(varchar(20),@frag_size))

end

else /* Must be an alter database command */

begin

select @sql_string = 'alter database ' + @db_name

insert into #db_info (seq_no, sql_string) values (@seq_no, @sql_string)

select @seq_no = @seq_no + 1

-- Log = 4 or data

if @segmap = 4

select @sql_string = ' log on ' + @dev_frag + ' = ' +

ltrim(convert(varchar(20),@frag_size)) + char(10) + 'go' + char(10)

else

select @sql_string = ' on ' + @dev_frag + ' = ' +

ltrim(convert(varchar(20),@frag_size)) + char(10) + 'go' + char(10)

end

insert into #db_info (seq_no, sql_string) values (@seq_no, @sql_string)

select @seq_no = @seq_no + 1

-- Get next row

fetch get_db_info into @dbid,@segmap,@dev_frag,@frag_size

end

close get_db_info

deallocate cursor get_db_info

-- Select each piece of SQL from temporary table ordering by */

-- sequence number */

declare print_createdb_cursor cursor for

select sql_string

from #db_info

order by seq_no

for read only

open print_createdb_cursor

fetch print_createdb_cursor into @sql_string

while (@@sqlstatus = 0)

begin

print @sql_string

fetch print_createdb_cursor into @sql_string

end

close print_createdb_cursor

deallocate cursor print_createdb_cursor

-- Get next dbid.

select @curdbid = min(dbid) from #dbids where dbid > @curdbid

end

drop table #db_info

drop table #dbids

return(0)

check table space

2009-06-07T07:25:00.000-07:00

IF OBJECT_ID('dbo.sp_dba_dbspace') IS NOT NULL

BEGIN

DROP PROCEDURE dbo.sp_dba_dbspace

IF OBJECT_ID('dbo.sp_dba_dbspace') IS NOT NULL

PRINT '<<<>>>'

ELSE

PRINT '<<<>>>'

END

create procedure sp_dba_dbspace

set nocount on

declare @data_size float,

@log_size float,

@psize_kb float,

@log_id int,

@rsrvTTL int,

@log_segment int,

@message char(80)

/* get the segmap mask for log only segments */

select @log_segment = number

from master..spt_values

where name = "log only"

if @@error != 0 return

/* get the id of the syslogs table */

select @log_id = id

from sysobjects where name = "syslogs"

if @@error != 0 return

/* get page size from system proc values table */

select distinct @psize_kb = convert(float, low) / 1024

from master..spt_values

where number = 1 and type = "E"

if @@error != 0 return

/* get the db size excluding the log segment size */

select distinct @data_size = sum(size)

from master..sysusages

where dbid = db_id() and segmap != @log_segment

if @@error != 0 return

/* get the log segment size */

select distinct @log_size = sum(size)

from master..sysusages

where dbid = db_id() and segmap = @log_segment

if @@error != 0 return

if @log_size is NULL

select @log_size = 0 /* case where the db has no log segment */

/* print out the overall database stats */

select @message = " " + replicate("=",68)

print @message

select DbName = rtrim(db_name()),

DbSize = ltrim(str(sum(@data_size + @log_size) * @psize_kb, 12, 0) + " KB"),

DataSegments = ltrim(str(@data_size * @psize_kb, 10, 0) + " KB"),

LogSegments = ltrim(str(@log_size * @psize_kb, 10, 0) + " KB")

if @@error != 0 return

select @message = " " + replicate("=",68)

print @message

/* get page counts for all tables except syslogs */

select

o.name "Tname",

i.name "Iname",

i.segment,

i.id,

i.indid,

Rrows = rowcnt(i.doampg),

rsrv = (reserved_pgs(i.id,i.doampg) + reserved_pgs(i.id,i.ioampg)),

data = data_pgs(i.id,i.doampg),

idx = data_pgs(i.id,i.ioampg),

unused = ((reserved_pgs(i.id,i.doampg) + reserved_pgs(i.id,i.ioampg)) -

(data_pgs(i.id,i.doampg) + data_pgs(i.id,i.ioampg)))

into #tableCount

from sysobjects o, sysindexes i

where o.id = i.id

and o.id != @log_id

if @@error != 0 return

/* print out results

* order by system tables, system tables nonclustered idx

* then user tables with no clustered idx, clustered idx tabs and

* finally non clustered idx

print " SYSTEM TABLES"

select @message = " " + replicate("=",68)

print @message

select rtrim(Tname) "TableName",

rtrim(Iname) "IndexName",

ltrim(str(convert(float,Rrows),12,0)) "RowCount",

ltrim(str(convert(float,rsrv),10,0)) "ReservedPages",

ltrim(str(convert(float,rsrv)*@psize_kb,10,2)+" KB") "Space",

ltrim(str(convert(float,data),10,0)) "DataPages",

ltrim(str(convert(float,idx),10,0)) "IndexPages",

ltrim(str(convert(float,unused),10,0)) "UnusedPages"

from #tableCount tp

where tp.id <>

and ( tp.indid = 0 or tp.indid = 1 )

order by id

select @message = " " + replicate("=",68)

print @message

print " NON CLUSTERED INDEXES ON SYSTEM TABLES"

select @message = " " + replicate("=",68)

print @message

select rtrim(Tname) "TableName",

rtrim(Iname) "IndexName",

ltrim(str(convert(float,rsrv),10,0)) "ReservedPages",

ltrim(str(convert(float,rsrv)*@psize_kb,10,2)+" KB") "Space",

ltrim(str(convert(float,idx),10,0)) "IndexPages",

ltrim(str(convert(float,unused),10,0)) "UnusedPages"

from #tableCount tp

where tp.id <>

and tp.indid > 1

order by id

select @message = " " + replicate("=",68)

print @message

print " USER TABLES WITH NO CLUSTERED INDEXES"

select @message = " " + replicate("=",68)

print @message

select rtrim(Tname) "TableName",

ltrim(str(convert(float,Rrows),12,0)) "RowCount",

ltrim(str(convert(float,rsrv),10,0)) "ReservedPages",

ltrim(str(convert(float,rsrv)*@psize_kb,10,2)+" KB") "Space",

ltrim(str(convert(float,data),10,0)) "DataPages",

ltrim(str(convert(float,unused),10,0)) "UnusedPages"

from #tableCount tp

where tp.id > 100

and tp.indid = 0

order by Tname

select @message = " " + replicate("=",68)

print @message

print " USER TABLES WITH CLUSTERED INDEX"

select @message = " " + replicate("=",68)

print @message

select rtrim(Tname) "TableName",

rtrim(Iname) "IndexName",

ltrim(str(convert(float,Rrows),12,0)) "RowCount",

ltrim(str(convert(float,rsrv),10,0)) "ReservedPages",

ltrim(str(convert(float,rsrv)*@psize_kb,10,2)+" KB") "Space",

ltrim(str(convert(float,data),10,0)) "DataPages",

ltrim(str(convert(float,idx),10,0)) "IndexPages",

ltrim(str(convert(float,unused),10,0)) "UnusedPages"

from #tableCount tp

where tp.id > 100

and tp.indid = 1

order by Tname

select @message = " " + replicate("=",68)

print @message

print " NON CLUSTERED INDEXES ON USER TABLES"

select @message = " " + replicate("=",68)

print @message

select rtrim(Tname) "TableName",

rtrim(Iname) "IndexName",

ltrim(str(convert(float,rsrv),12,0)) "ReservedPages",

ltrim(str(convert(float,rsrv)*@psize_kb,10,2)+" KB") "Space",

ltrim(str(convert(float,idx),10,0)) "IndexPages",

ltrim(str(convert(float,unused),10,0)) "UnusedPages"

from #tableCount tp

where tp.id > 100

and tp.indid > 1

order by Tname

/* now get space for syslogs */

insert into #tableCount

select

o.name,

i.name,

i.segment,

i.id,

i.indid,

rowcnt(i.doampg),

reserved_pgs(i.id, i.doampg),

data_pgs(i.id,i.doampg),

unused = reserved_pgs(i.id, i.doampg) - data_pgs(i.id,i.doampg)

from sysobjects o, sysindexes i

where o.id = i.id

and o.id = @log_id

if @@error != 0 return

/* compute summary results showing Total data seg allocated

* whats been reserved and unused.

* compute a usage as a percentage

select @message = " " + replicate("=",68)

print @message

print " SUMMARY RESULTS "

/* if log on its own segment then use @log_size else use data_size */

if @log_size != 0

begin

select

DataSegTTL = ltrim(str(@data_size * @psize_kb, 10, 0) + " KB"),

Reserved = ltrim(str(convert(float,sum(rsrv))*@psize_kb,10,0)+" KB"),

Objects = ltrim(str(convert(float,sum(data))*@psize_kb,10,0)+" KB"),

Indixes = ltrim(str(convert(float,sum(idx))*@psize_kb,10,0)+" KB"),

FreeSpace =str((@data_size*@psize_kb)-(sum(rsrv)*@psize_kb)) + " KB",

Usage = str((sum(rsrv)*@psize_kb)/(@data_size * @psize_kb) * 100,6,2) + " % Used"

from #tableCount

where id != @log_id

if @@error != 0 return

select

LogSegTTL = str(@log_size * @psize_kb, 10, 0) + " KB",

Reserved = str(rsrv*@psize_kb) + " KB",

FreeSpace = str((@log_size*@psize_kb)-(rsrv*@psize_kb)) + " KB",

Usage = str((((rsrv*@psize_kb)/(@log_size * @psize_kb)) * 100),6,2) + " % Used"

from #tableCount

where id = @log_id

if @@error != 0 return

end

else

begin

select @message = "DATA AND LOG SHARE SAME SEGMENT"

print @message

/* get the total of reserved pages from #tableCount */

select @rsrvTTL = sum(rsrv) from #tableCount

if @@error != 0 return

select

DataSegTTL = ltrim(str(@data_size * @psize_kb, 10, 0) + " KB"),

Reserved = ltrim(str(convert(float,sum(rsrv))*@psize_kb,10,0)+" KB"),

Objects = ltrim(str(convert(float,sum(data))*@psize_kb,10,0)+" KB"),

Indixes = ltrim(str(convert(float,sum(idx))*@psize_kb,10,0)+" KB"),

FreeSpace = str((@data_size*@psize_kb)-(@rsrvTTL*@psize_kb)) + " KB",

Usage = str((@rsrvTTL*@psize_kb)/(@data_size * @psize_kb) * 100,6,2) + " % Used"

from #tableCount

where id != @log_id

select

DataSegTTL = str(@data_size * @psize_kb, 10, 0) + " KB",

LogReserved = str(rsrv*@psize_kb) + " KB",

FreeSpace = str((@data_size*@psize_kb)-(@rsrvTTL*@psize_kb)) + " KB",

Usage = str((((@rsrvTTL*@psize_kb)/(@data_size * @psize_kb)) * 100),6,2) + " % Used"

from #tableCount

where id = @log_id

if @@error != 0 return

end

select @message = " " + replicate("=",68)

print @message

select @message = "TOTAL SPACE RESERVED PER SEGMENT"

print @message

select

s.name "SegName",

Reserved = ltrim(str(convert(float,sum(rsrv))*@psize_kb,10,0)+" KB")

from syssegments s, #tableCount t

where s.segment = t.segment

group by s.name

order by s.segment

select @message = " " + replicate("=",68)

print @message

/* how much space the current db uses on each device */

select size = convert(float, high-low+1) * @psize_kb,

alloc = convert(float, sum(size)) * @psize_kb,

device = name,

total_alloc = convert(float, 0)

into #devSum

from master..sysusages, master..sysdevices

where vstart >= low and vstart <= high and cntrltype = 0

and dbid = db_id()

group by name

if @@error != 0 return

/* update total usage for each device. */

update #devSum

set total_alloc = ( select sum(su.size) from master..sysusages su,

master..sysdevices sd

where vstart >= low and vstart <= high and cntrltype = 0

and #devSum.device = sd.name

)

if @@error != 0 return

update #devSum

set total_alloc = total_alloc * @psize_kb

if @@error != 0 return

/* add summary lines to the device sum table */

insert #devSum

select sum(size), sum(alloc), "TOTAL", sum(total_alloc)

from #devSum

if @@error != 0 return

/* now print stats */

select @message = " DISK ACTUAL USAGE BY DEVICE FOR: " + db_name()

print @message

select @message = " " + replicate("=",68)

print @message

select DeviceName = device,

Size = str(size, 10, 0) + " KB",

ThisDb = str(alloc, 10, 0) + " KB",

OtherDb = str(total_alloc - alloc, 10, 0) + " KB",

UnAlloc = str(size - total_alloc, 10 ,0) + " KB",

Usage = str(total_alloc/size *100,6,2) + " % Used"

from #devSum

where device != "TOTAL"

order by device

if @@error != 0 return

select @message = " " + replicate("=",68)

print @message

select TOTAL = device,

Size = str(size, 10, 0) + " KB",

ThisDb = str(alloc, 10, 0) + " KB",

OtherDb = str(total_alloc - alloc, 10, 0) + " KB",

UnAlloc = str(size - total_alloc, 10 ,0) + " KB",

Usage = str(total_alloc/size *100,6,2) + " % Used"

from #devSum

where device = "TOTAL"

if @@error != 0 return

select @message = " " + replicate("=",68)

print @message

drop table #devSum

drop table #tableCount

return 0

EXEC sp_procxmode 'dbo.sp_dba_dbspace','unchained'

IF OBJECT_ID('dbo.sp_dba_dbspace') IS NOT NULL

PRINT '<<<>>>'

ELSE

PRINT '<<<>>>'

USE sybsystemprocs

GRANT EXECUTE ON dbo.sp_dba_dbspace TO public

Sybase IPG Group White Paper

2009-06-07T07:24:00.002-07:00

Sybase IPG Group White Paper

Series : Sybase IQ 12 Technical Series

Index Selection Criteria

Sybase IQ 12.5

Index Selection Criteria

Richard Soundy
Sybase IPG (Analytics) Group for EMEA
November 2003

Abstract

This document details the basic steps for the determination of the required indexes for a Sybase IQ 12.5 database. It is not considered that this document is in any way a complete blueprint for the required indexes, it is intended more to be a layout or a basis for the layout.

In addition this document only seeks to provide the information for the 12.5 version of the IQ product. It is very likely that future releases will require a different choice of indexes, or maybe provide a different set of index to select from.

Initial Information Requirements

In order to determine the “best” initial fit of indexes for a specific database and application the following information will need to be derived. It is understood that for a new system some if not much of this information will not be available, and in this event the best information should be obtained.

In all cases the monitoring of the performance of the server/database/application will be required to correct any errors in the initial index determination. The tools and techniques used for this monitoring are outside the scope of this document, and are a subject for future documentation.

Table Related Information Required.

For each and every table the following information needs to be obtained:

1. Number of Rows in the table using the following bands:

i. Less than 2,000 rows

ii. Between 2,000 and 1,000,000 rows

iii. Greater than 1,000,000 rows.

The first two bands above (less than 1,000,000 rows) relates, in the majority of data warehouse designs to dimension or look-up tables, the final band to the fact or event tables.

The reasons for the split are two fold. An IQ system will only access a table of less than 2,000 rows with Default or Fast Projection Indexes^{^[1]}. Any enumerated indexes are only of use to the Optimizer, and are not used by the Run-Time Engine at all. This is not to say that the enumerated indexes are of no use, in fact to the contrary, they provide priceless information to the optimizer in planning the query. Also given the small size of the table(s) the indexes are also of similar size.

The banding of 2,000 to 1,000,000 rows is given by the relative sizes of the Low Fast (LF) and High Group (HG) index types. It is considered that these two index types are merely reflections of each other. This is very wrong. They are internally very different, have very different performance capabilities, and have differing load and storage costs. This document is not the place to go in to the structures of the HG or LF indexes, however the following two examples show the differing sizes of the two index types, which leads to the reasons behind the indexing guidelines presented further on in this document.

If we consider a table of 10,000 rows, which has a column that has a cardinality of 1,000[2], we should consider the differing storage sizes for the two index types under discussion (LF and HG).

An LF index will have 1 page of storage for the Index ID page and 1 bitmap for each and every discrete value the column is storing. For a 10,000-row table, given a page size of 218 Kbytes[3], the bitmaps will be 1 page in size. Hence the size of the LF index will be 1,001 pages. It should be noted that this will not use 64 times 1,001 Kbytes of disk space as the compression routines will shrink this to a smaller number. However to access a given value the system will have to read 2 pages (ID + 1 bitmap).

For the HG index the calculation is somewhat different. The HG will still have and ID page (which will also contain the B-Tree component of the index), but the G-Array component will be stored in some 2- 3 pages. This is because a G-Array page can hold some 8,000 row-id values; hence 10,000 row-ids (rows) can be stored in 2 to 3 pages. So the overall size of the HG index will be 4 pages. Also the access to a given value will still only be 2 I/Os (pages).

Given that the optimizer will select (all other thing being equal) the fast search index (LF or HG) that is physically smaller the optimizer will always select the HG index. Hence for a table of 10,000 rows an HG is far more efficient than an LF. There is a question of skewed data - but even this does not effect the overall size of the HG index to any great degree, as this will only add a couple of pages to the overall size of the index. The difference in the load time between an LF and an HG index is for all intents and purposes irrelevant for a table of only 10,000 rows.

The break even point on the relative size of the two index types is easily calculated for each table, but the larger the table, the more the likelihood is that it will be dynamic, and hence favour the use of the LF index for low cardinality columns.

We can continue to provide sizing costs for the two indexes, but the logic is clear, for smaller tables the Hg index should be considered to be the “index of choice”.

So where is the break point, well there is no firm “number-of-rows” at which the LF becomes a “better” index than the HG. But we would consider somewhere in the few million rows this takes place. Of course other questions need be answered, for example should the table have a very low volatility[4] of the size compression is not an issue then you might wish to use the HG for low cardinality columns up to 10 or 20 million row tables.

Column Related Information Required

For each and every column the following information should be obtained:

1. The cardinality of the column

2. The data type of the column

3. The expected use of the column

i. Simple searches (equality/inequality, EXISTS, IN)

ii. Range Searches (<, >, between)

iii. Aggregation functions (SUM(), AVG(), DISTINCT, COUNT)

iv. Intra-table comparison

v. Free Text searches (CONTAINS, LIKE)

vi. Ordering and grouping (ORDER BY, GROUP BY)

This information is required to determine the LF/HG and other index requirements. This is also deemed to be the most difficult information to gather for a new system. If the Sybase IQ database is to accelerate of replace an existing system, there should be no barrier to any of the above information.

As the volume of this information is likely to be high it is suggested that some form of schema design tool is adopted to hold the information. A product such as Sybase Power Designer is recommended for this area as it provides many other features in addition to its documentation functions.

Database Index Selection

The following list needs to be applied to each and every column in each and every table (with the possible exception of tables of less than 2,000 rows) in the database.

For each column the cardinality needs to be applied to the CREATE TABLE IQ UNIQUE constraint. This will provide the best version of the FP index (1-byte FP, 2-byte FP or Flat FP). Note that the MINIMIZE_STORAGE option will auto-generate an IQ UNIQUE value of 255 for all columns for which IQ UNIQUE is not specified.

Note: for a column that has a data type of FLOAT, REAL or DOUBLE then the only indexes that can be applied are flat FP. For a column of data type BIT there is a custom index structure automatically generated under it, hence there is no requirement for any further indexes to be placed upon it. A column of data type VARCHAR() with a length greater than 255 can only have a Flat FP and (if required) a Word (WRD) index placed on it.

If a column is a PRIMARY KEY or UNIQUE then this should be specified within the CREATE TABLE statement, this will auto generate a unique HG index on the column, allowing improved storage, better join processing and more statistics available to the optimizer.
If a column is a FOREIGN KEY then this should also be defined in the CREATE TABLE statement, also allowing automatic construction of an HG index, this is vital for both the optimizer and for join performance.
If a table has a multi-column PRIMARY or FOREIGN KEY, this can be specified, and the system will auto-generate the correct type of Multi-Column HG index on the columns (at this point it should be noted that the individual columns within the key will not have an HG index placed upon them – so it is the responsibility of the designer to ensure that if these columns are used for searches or whatever then the appropriate indexes are placed upon them).
For any and every other join column (column that is used in a join – but not in a PRIMARY KEY/FOREIGN KEY join), then this column should also have an HG index placed upon it.

At this point all of the above information applies to columns of any cardinality and to tables of any size. The following steps may require the column cardinality and the table size to be considered.

For a column that is going to be used for searching (Equality, inequality) for certain aggregations (COUNT, DISTINCT), for grouping and ordering, then either an HG or an LF index is required. For a table of less than around 1 million rows (see discussion above) a HG index is more efficient for all cardinalities. For a table greater in size than this use an LF for columns with a cardinality of less than around 1,500 and an HG of columns that have a cardinality greater than this.
For a column that has a data type of DATE, TIME or DATETIME, then it is suggested that the DATE, TIME and DTTM indexes are applied. This will provide for both range searches and date function searches (DATEPART() etc.).
For a non-date/time column that is involved in range searches and/or SUM() and AVG() aggregation functions it is suggested that an HNG index is applied. This may not be required if the column has a high percentage of NULL values (greater than 10% of the rows). Also the use of HNG columns should be monitored, as HG and LF can provide (in some cases) a faster range search capability.
Any column that is compared with another column in the same table should have a compare (CMP) index applied to the comparison pair of columns.
Any VARCHAR() or CHAR() columns that are likely to be used for word searches should have an word (WRD) index applied to them.

Conclusion

That is really all that is required for the implementation of indexes to an IQ database. Naturally there are always exceptions (for both table sizes cardinality [HG vs. LF]) and the performance of the system must be monitored to determine if further indexes are required or maybe some indexes to be removed.

For further information on the structure and function of the various index types then the Sybase IQ manual set can be consulted, in addition the Sybase Professional Services Groups can be used both as implementers and trainers in some of the more esoteric functionality of the Sybase IQ index sets.

Richard Soundy

November 2003

London.

Sybase, Inc.

Worldwide Headquarters

One Sybase Drive

Dublin, CA 94568-7902 USA

Tel: +800 8 SYBASE

www.sybase.com

[1] In this document I generally refer to Default Indexes as Fast Projection or FP indexes.

[2] The column supports the storage of 1,000 different values.

[3] The default IQ Page Size for an IQ 12.5 Database

[4] A table that is low volatility does not change very often; hence the loading time is not of issue.

Sybase IQ as a Data Warehousing Database Solution

2009-06-07T07:24:00.001-07:00

Sybase IQ as a Data Warehousing Database Solution

There are a number of points to look at when deciding whether to use Sybase IQ as a database platform. It is specialised the target audience is inherently limited by feature set.

Some background - Sybase IQ is the Sybase Data Warehouse product offering. It is not like ASE or Oracle or MS SQL Server in that it has been designed from the ground up for analytic and not OLTP based applications.

Key Business benefits:

Ø Scalability: can provide access for many simultaneous users to terabytes of information.

Ø Speed: delivers query responses to users in seconds rather than minutes or hours.

Ø Flexibility: gives users the ability to create any type of ad-hoc query.

Ø Low TCO: reduces storage with data compression; compresses data from 50 to 80 percent; is far easier to maintain than traditional databases and does not require time and resource-intensive tuning to obtain excellent performance

Key technical advantages:

a) Many times faster for both simple and complex selects (typically a factor of 100) as the optimiser takes more time so is more thorough.

b) Storage efficiency - data is held in a compressed form so take up typically 1/3rd the space.

c) All columns are indexed by default - other types of index can be added for additional performance.

d) Uses less system resources so requiring smaller hardware configurations.

e) Scales well to Terabytes.

f) Large updates performed more efficiently (typically 100K row update <1second).

g) Can operate in a cluster mode by adding additional 'read only' nodes for scaling.

h) Transact and ANSI SQL compatible.

i) Can be fed data by Sybase Replication/CIS or File.

j) Bulk load is very fast.

k) Uses table versioning so no transaction log to fill up, in addition updates are queued so no locking issues.

l) No read locking as ‘selects’ read current version.

m) Inbuilt Index optimiser advises of indexes needed to enhance performance.

n) Little tuning necessary and very forgiving of badly written queries.

o) No index rebuilds or statistics maintenance.

p) Internal event based scheduling.

Main disadvantages:

a) It is not suitable for an OLTP environment.

b) Query optimiser is more thorough and takes longer to compile queries so is not suitable where ultra quick response time is required.

c) Optimised for last row out not first as on OLTP systems

d) Inefficient updating small numbers of records due to large page size (1 record similar time to 10,000 records).

e) Only one writer/updater process per table at the moment (all updaters on one node only).

f) Is 99.9% compatible with Transact SQL so there is likely to be some reworking necessary to migrate ASE SQL.

Other Points:

Ø Data is held in a column based format and no row based as is the case with conventional RDBMS’s.

Ø DR is maintained by Database + either Transactional or Differential dumps.

Ø Database dump time in the order of 200Gb+ per hour (Linux + SAN).

Ø Many different types of indexes for precise data type support + will support time series in the next version.

Ø Data load time in the order of 100k+ records per CPU per second.

creating a ocs client the easy way

2009-03-11T02:25:00.000-07:00

This is an easy script to help you create a ocs client on an apps box.

Terry

!#/bin/bash
PROJ=OCS
VERSION=OCS1251_EBF12655
cd /opt/sybase
cp defaultpath.profile .profile
cp defaultpath.kshrc .kshrc
cp defaultpath.inputrc .inputrc
cp defaultpath.vimrc .vimrc
cp defaultpath.sqshrc .sqshrc
cp defaultpath.forward .forward # ssh-keygen -t dsa <EOF
mkdir .ssh
cat /shared/sybase-build/last/GENERIC/ssh/id_dsa.pub.oishare>>/opt/sybase/.ssh/authorized_keys
cat /shared/sybase-build/last/GENERIC/ssh/gh_sybase.pub>>/opt/sybase/.ssh/authorized_keys
mkdir -p /opt/sybase/PROJ/OCS1251_EBF12655
cd /opt/sybase/PROJ
ln -s OCS1251_EBF12655 openclient
cd /opt/sybase/PROJ/openclient
PATH=/usr/bin:/opt/IB/bin:/usr/sfw/bin:/usr/sbin:/sbin:/usr/ucb:/usr/ccs/bin
export PATH
gtar -xvzf /defaultpath/OCS1251-32bit.tar.gz
gtar -xvzf /defaultpath/OCS1251-32bit_EBF13428.tar.gz
cp /defaultpath/.profile_OCS1251-32bit /opt/sybase/PROJ
cp /defaultpath/goto_PROJ_OCLIENT /opt/sybase/PROJ
cd /opt/sybase/PROJ
cat goto_PROJ_OCLIENT
cat goto_PROJ_OCLIENT | sed 's/???/OCS/g' > goto_PROJ_OCLIENT
rm goto_PROJ_OCLIENT
cd /opt/sybase
ln -s PROJ/goto_PROJ_OCLIENT
cd /opt/sybase/PROJ/openclient
ln -s ../interfaces .

quiescing a replication system

2009-02-24T01:38:00.000-08:00

to do this you will need to first suspend all the log transactions do this by

isql -S server_rep -U repadmin -P password

admin health
go

this well show false of quiese is not right
next run
suspend log transfer from all
go
then run
admin quiesce_force_rsi
go
then run
admin quiesce_check
go

this should now say the server is quiesce
I would recomed doing this 2 to 3 times more before restoring the log transfer as sometime it does not work.

Truncation log is filling up and its to do with replication what should I do?

2009-02-09T21:14:00.000-08:00

Firstly run login to the box
/opt/sybase/TEST/dba/scripts : isql -S servername -Usa -P password1

1> sp_who
2> go

0 1063 recv sleep pwsys pwsys ln2p3310ap 0 ITS_FF_logins AWAITING COMMAND 0
0 1064 recv sleep jupiter_fft jupiter_fft IBLONPSP32 0 ImagineFFProd AWAITING COMMAND 0
0 1074 recv sleep herrnd herrnd 0 ImagineFFProd AWAITING COMMAND 0
0 1075 recv sleep pwsys pwsys ln2p3310ap 0 ITS_FF_logins AWAITING COMMAND 0
0 1091 recv sleep Pmccartm Pmccartm iblongsw22 0 ImagineFFProd AWAITING COMMAND 0
0 1100 recv sleep otc_book otc_book ln7p3371ap 0 MOP AWAITING COMMAND 0
0 1120 recv sleep pwsys pwsys ln2p3310ap 0 EOY_ITS_FF_logins AWAITING COMMAND 0
0 1122 sleeping sysadmin sysadmin 0 master LOG SUSPEND

after that do a select * from syslogshold
1> use master
2> go
1> select * from syslogshold
2> go
dbid reserved spid page xactid masterxactid starttime name xloid
------ ----------- ------ ----------- -------------- -------------- -------------------------- ------------------------------------------------------------------- -----------
12 0 0 102007 0x000000000000 0x000000000000 Feb 10 2009 3:13AM $replication_truncation_point 0
11 0 0 293304 0x000000000000 0x000000000000 Feb 10 2009 3:06AM $replication_truncation_point 0
10 0 0 374671 0x000000000000 0x000000000000 Feb 10 2009 3:13AM $replication_truncation_point 0
9 0 0 102145 0x000000000000 0x000000000000 Feb 10 2009 3:09AM $replication_truncation_point 0
8 0 0 565203 0x000000000000 0x000000000000 Feb 10 2009 3:14AM $replication_truncation_point 0
7 0 0 5465069 0x000000000000 0x000000000000 Feb 10 2009 3:09AM $replication_truncation_point 0
6 0 0 4154341 0x000000000000 0x000000000000 Feb 10 2009 3:06AM $replication_truncation_point 0
5 0 315 2219491 0xe3dd21000100 0x000000000000 Feb 10 2009 3:14AM $dmpxact 630
5 0 0 2279742 0x000000000000 0x000000000000 Feb 10 2009 2:03AM $replication_truncation_point 0
31516 0 12 9571 0x632500000a00 0x000000000000 Feb 10 2009 3:14AM $audit_xact 24

you will see alot of replication_trncations_point you will find that you wont beable to truncated the log, best thing to do is ask the apps team if you can kill the spid causing the troubles and break replications

when trying to truncate you will receve this error
dump tran database with truncate_only
2> go
DUMP TRANSACTION for database 'database' could not truncate the log. Either extend the log using ALTER DATABASE ... LOG ON command or eliminate the oldest active transaction in database 'database' shown in syslogshold table.

Breaking replication

dbcc settrun(ltm, ignore)
go
sp_stop_repagent(database)
go

this should now start to clear the tranlog you can now run a dump tran database with truncate_only
dump tran database with truncate_only
go

you will now need to do a resync

exec sp_addalias replogin, dbo
go
dbcc settrunc (ltm,ignore)

on warmstand by run

exec RSSD_DB..rs_zeroltm Primaryserver,DATABASE
copy over the database load it and the resume connections
log in to primary
dbcc settrunc (ltm,valid)
go
sp_start_repagent (database)
go

log in to repserver
resume connection to STANDBYDS.DATABASE
and so on

How do I move tempdb off of the Master Device

2009-01-28T01:49:00.000-08:00

1.1.3: How do I move tempdb off of the Master Device?
There used to be a section in the FAQ describing how to drop all of tempdb'sdevices physically from the master device. This can make recovery of theserver impossible in case of a serious error and so it strongly recommendedthat you do not do this but simply drop the segments as outlined below.Sybase TS Preferred Method of Moving tempdb off the Master Device.This is the Sybase TS method of removing most activity from the master device: 1. Alter tempdb on another device:
1> alter database tempdb on ...
2> go 2. Use the tempdb:
1> use tempdb
2> go 3. Drop the segments:
1> sp_dropsegment "default", tempdb, master
2> go
1> sp_dropsegment "logsegment", tempdb, master
2> go
1> sp_dropsegment "system", tempdb, master
2> go Note that there is still some activity on the master device. On a three connection test that I ran:
while ( 1 = 1 )
begin
create table #x (col_a int)
drop table #x
end
there was one write per second. Not bad. An Alternative (I recently did some bench marks comparing this method, the previous method and a combination of both. According to sp_sysmon there was no difference
in activity at all. I leave it here just in case it proves useful to
someone.) The idea of this handy script is to simply fill the first 2MB of tempdb thuseffectively blocking anyone else from using it.
The slight gotcha with thisscript, since we're using model, is that all subsequent database creates willalso have tempdb_filler installed.
This is easily remedied by dropping thetable after creating a new database.
This script works because tempdb is rebuilt every time the ASE is rebooted.
Very nice trick!/* this isql script creates a table in the model database.
*//* Since tempdb is created from the model database when the
*//* server is started, this effectively moves the active
*//* portion of tempdb off of the master device.
*/use modelgo/* note: 2k row size */create table tempdb_filler(a char(255) not null,b char(255) not null,c char(255) not null,d char(255) not null,e char(255) not null)go/* insert 1024 rows */declare @i intselect @i = 1while (@i <= 1024)begin insert into tempdb_filler values('a','b','c','d','e')
if (@i % 100 = 0) /* dump the transaction every 100 rows */
dump tran model with truncate_only
select @i=@i+1endgo

how to get the sql query behind a spid in sybase

2009-01-27T23:21:00.000-08:00

The command you need to use to find the text of a spid say one that is blocking is dbcc sqltext.

This can be used like this

dbcc sqltext()
you can also use this command to get see whats is happening in a block you can do this by

use master
go
select * from syslogshold this will find the longest running transaction.
go
dbcc traceon(3604)
go
dbcc sqltext (104)
go
SQL Text: INSERT INTO JMA2_HstYtdSave ( Class, A_B, SUN_DB, Ent_ID, LAcct_ID, CC_ID, PJ_ID, Period, Amount, Type, Cat, LAcct, Acct_ID, CP_ID, LM_Gr, SU_CC_ID, FU_ID, CC, PE_BL_ID, Div_ID, BL_ID, BU_ID )\t\t VALUES ( 'Hst', 'A', 'DB7', '720', '800000001', '3110', ' ', 2008012, -0.050, 'H', 'FU', 'Head count Avg', '0010E', ' ', 'HC', ' ', 'CM30_105_0005', 'Money Market', 'woPE', 'IB', 'CM30', 'CM30_105' )
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

here are a list of repserver commands

2009-01-26T05:32:00.001-08:00

Replication server Command followed by description

abort switch
Aborts the switch active command, unless Replication Server has gone too far in the active switch process to abort it.

activate subscription
For a subscription to a replication definition or a publication, starts the distribution of updates from the primary to the replicate database and sets the subscription status to ACTIVE.

add partition
Makes a partition available to Replication Server. A partition can be a disk partition or an operating system file. See create partition.

admin disk_space
Displays use of each disk partition accessed by the Replication Server.

admin echo
Returns the string entered by the user.

admin get_generation
Retrieves the generation number for a primary database.

admin health
Displays the status of the Replication Server.

admin log_name
Displays the path to the current log file.

admin logical_status
Displays status information for logical connections.

admin pid
Displays the process ID of the Replication Server.

admin quiesce_check
Determines if the queues in the Replication Server have been quiesced.

admin quiesce_force_rsi
Determines whether a Replication Server is quiescent and forces it to deliver and obtain acknowledgments for messages in RSI queues.

admin rssd_name
Displays the names of the data server and database for the RSSD.

admin security_property
Displays information about supported network-based security mechanisms and security services.

admin security_setting
Displays network-based security parameters and values for the Replication Server.

admin set_log_name
Closes the existing Replication Server log file and opens a new log file.

admin show_connections
Displays information about all connections from the Replication Server to data servers and to other Replication Servers.

admin show_function_classes
Displays the names of existing function-string classes and their parent classes, and indicates the number of levels of inheritance.

admin show_route_versions
Displays the version number of routes that originate at the Replication Server and routes that terminate at the Replication Server.

admin show_site_version
Displays the site version of the Replication Server.

admin sqm_readers
Displays the read and delete points of the threads that are reading a stable queue.

admin stats
Displays information and statistics about Replication Server counters.

admin stats, backlog
Reports the current transaction backlog in the stable queues.

admin stats, { md | mem | mem_in_use }
Reports information about memory usage.

admin stats, reset
Resets all counters that can be reset.

admin stats, status
Displays the flushing status for all counters.

admin stats, { tps | cps | bps }
Reports the number of transactions, commands, or bytes of throughput per second.

admin time
Displays the current time of Replication Server.

admin translate
Performs a datatype translation on a value, displaying the results in delimited literal format.

admin version
Displays the version number of the Replication Server software.

admin who
Displays information about threads running in the Replication Server.

admin who_is_down
Displays information about Replication Server threads that are not running.

admin who_is_up
Displays information about Replication Server threads that are running.

allow connections
Places Replication Server in recovery mode for specified databases.

alter connection
Changes the attributes of a database connection.

alter database replication definition
Changes an existing database replication definition.

alter function
Adds parameters to a user-defined function.

alter function replication definition
Changes an existing function replication definition.

alter function string
Replaces an existing function string.

alter function string class
Alters a function-string class, specifying whether it should be a base class or a derived class.

alter logical connection
Disables or enables the Distributor thread for a logical connection, changes attributes of a logical connection, and enables or disables replication of truncate table to the standby database.

alter partition
Alters the size of a partition.

alter queue
Specifies the behavior of the stable queue that encounters a large message of greater than 16K bytes. Applicable only when the Replication Server version is 12.5 or later and the site version is 12.1 or earlier.

alter replication definition
Changes an existing replication definition.

alter route
Changes the attributes of a route from the current Replication Server to a remote Replication Server.

alter user
Changes a user’s password.

assign action
Assigns Replication Server error-handling actions to data server errors received by the DSI thread.

check publication
Finds the status of a publication and the number of articles the publication contains.

check subscription
Finds the materialization status of a subscription to a replication definition or a publication.

configure connection
Changes the attributes of a database connection.

configure logical connection
Changes attributes of a logical connection.

configure replication server
Sets characteristics of the Replication Server, including network-based security.

configure route
Changes the attributes of a route from the current Replication Server to a remote Replication Server.

create article
Creates an article for a table or function replication definition and specifies the publication that is to contain the article.

create connection
Adds a database to the replication system and sets configuration parameters for the connection. To create a connection for an Adaptive Server database, use Sybase Central or rs_init.

create database replication definition
Creates a replication definition for replicating a database or a database object.

create error class
Creates an error class.

create function
Creates a user-defined function.

create function replication definition
Creates a function replication definition and user-defined function for a stored procedure that is to be replicated.

create function string
Adds a function string to a function-string class. Replication Server uses function strings to generate instructions for data servers.

create function string class
Creates a function-string class.

create logical connection
Creates a logical connection. Replication Server uses logical connections to manage warm standby applications.

create partition
Makes a partition available to Replication Server. A partition can be a disk partition or an operating system file.

create publication
Creates a publication for tables or stored procedures that are to be replicated as a group to one or more subscribing replicate databases.

create replication definition
Creates a replication definition for a table that is to be replicated.

create route
Designates the route to use for a connection from the current Replication Server to a remote Replication Server.

create subscription
Creates and initializes a subscription and materializes subscription data. The subscription may be for a database replication definition, a table replication definition, a function replication definition, or a publication.

create user
Adds a new user login name to a Replication Server.

define subscription
Adds a subscription to the Replication Server system tables, but does not materialize or activate the subscription. The subscription may be for a database replication definition, a table replication definition, a function replication definition, or for a publication. This command begins the process of bulk subscription materialization, or the process of refreshing a publication subscription.

drop article
Drops an article and optionally drops its replication definition.

drop connection
Removes a database from the replication system.

drop database replication definition
Drops an existing database replication definition.

drop error class
Drops an error class and any actions associated with it.

drop function
Drops a user-defined function and its function strings.

drop function replication definition
Drops a function replication definition and its user-defined function.

drop function string
Drops a function string for a function-string class.

drop function string class
Drops a function-string class.

drop logical connection
Drops a logical connection. Logical connections are used to manage warm standby applications.

drop partition
Removes a disk partition from the Replication Server.

drop publication
Drops a publication and all of its articles, and optionally drops the replication definitions for the articles.

drop replication definition
Drops a replication definition and its functions.

drop route
Closes the route to another Replication Server.

drop subscription
Drops a subscription to a database replication definition, table replication definition, function replication definition, article, or publication.

drop user
Drops a Replication Server user login name.

grant
Assigns permissions to users.

ignore loss
Allows Replication Server to accept messages after it detects a loss.

move primary
Changes the primary Replication Server for an error class or a function-string class.

rebuild queues
Rebuilds Replication Server stable queues.

resume connection
Resumes a suspended connection.

resume distributor
Resumes a suspended Distributor thread for a connection to a database.s

resume log transfer
Allows the RepAgent to connect to the Replication Server.

resume queue
Restarts a stable queue stopped after being passed a message larger than 16K bytes. Applicable only when the Replication Server version is 12.5 or later and the site version has not been similarly upgraded.

resume route
Resumes a suspended route.

revoke
Revokes permissions from users.

set autocorrection
Prevents failures that would otherwise be caused by missing or duplicate rows in a replicated table.

set log recovery
Specifies databases whose logs are to be recovered from offline dumps.

set proxy
Switches to another user.

shutdown
Shuts down a Replication Server.

suspend connection
Suspends a connection to a database.

suspend distributor
Suspends the Distributor thread for a connection to a primary database.

suspend log transfer
Disconnects a RepAgent from a Replication Server and prevents a RepAgent from connecting.

suspend route
Suspends a route to another Replication Server.

switch active
Changes the active database in a warm standby application.

sysadmin apply_truncate_table
Turns on or off the “subscribe to truncate table” option for all existing subscriptions to a particular table, enabling or disabling replication of truncate table.

sysadmin dropdb
Drops a database from the ID Server.

sysadmin dropldb
Drops a logical database from the ID Server.

sysadmin drop_queue
Deletes a stable queue. Use this command to drop a failed materialization queue.

sysadmin droprs
Drops a Replication Server from the ID Server.

sysadmin dump_file
Specifies an alternative log file name for use when dumping a Replication Server stable queue.

sysadmin dump_queue
Dumps the contents of a Replication Server stable queue.

sysadmin erssd
Displays ERSSD name, schedule, backup directory, and ERSSD file locations. Used with options, this command performs unscheduled backups and moves ERSSD files.

sysadmin fast_route_upgrade
Updates the route version to the site version of the lower of the primary or replicate Replication Server.

sysadmin hibernate_off
Turns off hibernation mode for the Replication Server and returns it to an active state.

sysadmin hibernate_on
Turns on hibernation mode for (or suspends) the Replication Server.

sysadmin log_first_tran
Writes the first transaction in a DSI queue into the exceptions log.

sysadmin purge_all_open
Purges all open transactions from an inbound queue of a Replication Server.

sysadmin purge_first_open
Purges the first open transaction from the inbound queue of a Replication Server.

sysadmin purge_route_at_replicate
Removes all references to a primary Replication Server from a replicate Replication Server.

sysadmin restore_dsi_saved_segments
Restores backlogged transactions.

sysadmin set_dsi_generation
Changes a database generation number in the Replication Server to prevent the application of transactions in the DSI stable queue after a replicate database is restored.

sysadmin site_version
Sets the site version number for the Replication Server. This lets you use the software features in the corresponding release, and prevents you from downgrading to an earlier release.

sysadmin sqm_purge_queue
Purges all messages from a stable queue.

sysadmin sqm_unzap_command
Undeletes a message in a stable queue.

sysadmin sqm_zap_command
Deletes a single message in a stable queue.

sysadmin sqt_dump_queue
Dumps the transaction cache for an inbound queue or a DSI queue.

sysadmin system_version
Displays or sets the system-wide version number for the replication system, allowing you to use the software features in the corresponding release level.

validate publication
Sets the status of a publication to VALID, allowing new subscriptions to be created for the publication.

validate subscription
For a subscription to a replication definition or a publication, sets the subscription status to VALID. This command is part of the bulk materialization process, or part of the process of refreshing a publication subscription.

wait for create standby
A blocking command that allows a client session in the Replication Server to wait for the standby database creation process to complete.

wait for delay
Specifies a time interval at which this command is blocked.

wait for switch
A blocking command that allows a client session in the Replication Server to wait for the switch to the new active database to complete.

wait for time
Specifies a time of day at which to unblock this command.

a little too if you dont use dbaritsan or a tool like that

2009-01-23T19:48:00.000-08:00

This will extract your db's ddl

create procedure sp_gendbddll @dbname varchar(32) = null
as

declare @db_name varchar(30),
@dbid smallint,
@segmap smallint,
@segs varchar(4),
@dev_frag varchar(30),
@frag_size char(10),
@sql_string varchar(255),
@alter_status tinyint,
@counter int,
@seq_no int,
@numdbs int

set nocount on

if @dbname is null select @dbname = "%"

-- Match databases if given else do them all
--
select @numdbs = count(*) from master.dbo.sysdatabases where name like @dbname

-- DB exists ?
--
if @numdbs = 0
begin
/* 17590, "The specified database does not exist." */
raiserror 17590
return (1)
end

-- Initialize dbids twmp table from sysdatabases
--
select dbid into #dbids from master.dbo.sysdatabases where name like @dbname

declare @curdbid smallint /* the one we're currently working on */
declare @dbdesc varchar(102) /* the total description for the db */
declare @bitdesc varchar(30) /* the bit description for the db */

-- Set @curdbid to the first dbid.
--
select @curdbid = min(dbid) from #dbids

create table #db_info(
seq_no int not null,
sql_string varchar(255) null
)

-- Iterate through by dbid
--
while @curdbid is not NULL
begin

/************************************************
* Temporary table to hold individual fragments *
* of SQL_STRING, numbered to preserve their *
* correct sequence *
************************************************/

delete #db_info

-- Initialize variables
select @alter_status = 0
select @counter = 0
select @seq_no = 0
select @db_name = db_name(@curdbid)

/* Declare a cursor to allow processing of each row individually */
/* Select statement extracts relevant info from the system tables */
/* ORDER BY clause is critical in order to ensure that all fragments */
/* are processed in the correct sequence */

declare get_db_info cursor for
select db.dbid, usg.segmap, "device fragment"=substring(dev.name,1,15), "size(MB)" = str(usg.size/512.,10,0)
from master.dbo.sysusages usg,
master.dbo.sysdevices dev,
master.dbo.sysdatabases db
where vstart between low and high
and cntrltype = 0
and db.dbid = @curdbid
and db.dbid =usg.dbid
order by db.dbid,usg.lstart,dev.name

-- Prepare initial SQL string
select @sql_string = 'create database ' + @db_name + ' on '

-- Store initial SQL_STRING in temporary table and
-- number it to allow later extraction in the correct
-- sequence
insert into #db_info (seq_no, sql_string) values (@seq_no, @sql_string)

-- Increment the sequence number
select @seq_no = @seq_no + 1

-- Open the cursor and fetch the first row
open get_db_info

fetch get_db_info into @dbid,@segmap,@dev_frag,@frag_size

if (@@sqlstatus=2)
begin
select "No information found for database"
close get_db_info
deallocate cursor get_db_info
return
end

while (@@sqlstatus = 0)
begin

if @alter_status = 0 /* Still part of original 'create database ...' command */
begin
if @segmap = 4 /* Must refer to log device */
begin
select @sql_string = ' log on ' + @dev_frag + ' = ' +
ltrim(convert(varchar(20),@frag_size)) + char(10) + 'go' + char(10)
select @alter_status = 1
end
else /* Must refer to data device */
begin
if @counter = 0 /* 1st data device of "create database ..." */
begin
select @sql_string = ' ' + @dev_frag + ' = ' + ltrim(convert(varchar(20),@frag_size))
select @counter = @counter + 1
end
else /* Additional data device for "create database ..." */
begin
select @sql_string = ' ,' + @dev_frag + ' = ' + ltrim(convert(varchar(20),@frag_size))
end
end
end
else /* Must be an alter database command */
begin

select @sql_string = 'alter database ' + @db_name

insert into #db_info (seq_no, sql_string) values (@seq_no, @sql_string)

select @seq_no = @seq_no + 1

-- Log = 4 or data
--
if @segmap = 4
select @sql_string = ' log on ' + @dev_frag + ' = ' +
ltrim(convert(varchar(20),@frag_size)) + char(10) + 'go' + char(10)
else
select @sql_string = ' on ' + @dev_frag + ' = ' +
ltrim(convert(varchar(20),@frag_size)) + char(10) + 'go' + char(10)
end

insert into #db_info (seq_no, sql_string) values (@seq_no, @sql_string)

select @seq_no = @seq_no + 1

-- Get next row
fetch get_db_info into @dbid,@segmap,@dev_frag,@frag_size
end

close get_db_info
deallocate cursor get_db_info

-- Select each piece of SQL from temporary table ordering by */
-- sequence number */
--
declare print_createdb_cursor cursor for
select sql_string
from #db_info
order by seq_no
for read only

open print_createdb_cursor

fetch print_createdb_cursor into @sql_string

while (@@sqlstatus = 0)
begin
print @sql_string
fetch print_createdb_cursor into @sql_string
end

close print_createdb_cursor
deallocate cursor print_createdb_cursor

-- Get next dbid.
--
select @curdbid = min(dbid) from #dbids where dbid > @curdbid

end

drop table #db_info
drop table #dbids

return(0)

Fix SUIDs

2009-01-20T16:13:00.000-08:00

In ASE you may face problems with users not being able to login, this is probably down to logind IDS and database IDS getting mismatched this often happens when a database dump is taken from one server and is loaded to another. You can solve this by either recreating the users in the same odered as they were created in the original server or drop and recreate all the IDS in the database. However there is a script that can do this for you.

sp_configure 'allow update',1
go
use database
go
begin tran
update sysusers
set suid=suser_id(user_name(uid))
from sysusers
where uid between 2 and 16000
and suser_id(user_name(uid)) is not null
go
sp_helpuser
go
/* if no mismatch, commit, else rollback */
commit tran
go
use master
go
sp_configure 'allow update',0
go

or create this in a store prod like this
IF OBJECT_ID('dbo.sp_dba_fixallsuid') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_dba_fixallsuid
IF OBJECT_ID('dbo.sp_dba_fixallsuid') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_dba_fixallsuid >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_dba_fixallsuid >>>'
END
go

create proc sp_dba_fixallsuid
as

declare @invalids int
declare @dbid int
declare @dbo int
declare @dbname char(100)
declare @invalid_offset int

select @dbname=db_name()
select @dbid=db_id()

set nocount on

if (@dbname not in ("master","model","sybsystemprocs","tempdb"))
begin

/* Identify the dodgy users */

select invalid_user_names_found=t.name from
sysusers t
where t.name not in (select name from master..syslogins r
where r.suid = t.suid) and t.suid > 0 and t.uid > 1

/* Get spare suid range for invalids */
select @invalid_offset = max(suid) + 10 from master..syslogins

/* Set all suids to be invalid (this prevents duplicates) */
update sysusers
set suid=suid + @invalid_offset where suid > 0 and suid < @invalid_offset and name <> 'dbo'

update sysusers
set suid=(select m.suid from master..syslogins m where sysusers.name=m.name)
where name in (select name from master..syslogins)

/* Fix the dbo in case he has changed */

select @dbo=suid from master..sysdatabases m where m.dbid=@dbid
update sysusers set suid=@dbo where name = 'dbo'

select dbo_set_to = m.name from master..syslogins m where m.suid=@dbo
select still_invalid_after_fix=name from sysusers where suid > @invalid_offset

/* Inform of the number of alaises unprocessed */

select aliases_unprocessed = count(*) from sysalternates

end
else
print "Not allowed in a system database."

return (0)

go
EXEC sp_procxmode 'dbo.sp_dba_fixallsuid','unchained'
go
IF OBJECT_ID('dbo.sp_dba_fixallsuid') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_dba_fixallsuid >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_dba_fixallsuid >>>'
go
USE sybsystemprocs
go
GRANT EXECUTE ON dbo.sp_dba_fixallsuid TO public
go

Adding raw devices to Sybase:

2009-01-19T16:28:00.001-08:00

First you want to link the raw device you can do this by going to the device path
Do this on the primary and then make sure it is the same on the warm standby if not ask the UNIX team to create some more devices.

Example
/opt/sybase/proj/devices/servername

How to link the files example
ln -s /dev/vx/rdsk/sybdg/servername_datadev15 datadev15

then do the same on the warm standby if it is not already done.
As they need to look the same when creating the new database.

Then do a g and then SQL on both the boxes and create the devices to do this use the disk nit command.
This looks like this

disk init name = 'datadev15',
physname = '/opt/sybase/proj/devices/servername/datadev18',
size = '6144M'
go

Adding raw devices to Sybase:

2009-01-19T16:28:00.000-08:00

sybase locks and blocks

2009-01-19T16:14:00.000-08:00

here are some basic tips on clearing a block in a database in sybase

log in to the sybase database

isql -S sybasesever -U dbo_user -P sdsdsrere123

1> sp_who
2> go

0 211 recv sleep zbi_trade_dbo zbi_trade_dbo tk0u2314ap 0 apps_gm AWAITING COMMAND 0
0 213 recv sleep read_genfeed read_genfeed ibtkygsw32 0 Clearing AWAITING COMMAND 0
0 218 sleeping dbo_swiftcentral dbo_swiftcentral tk0d2311ap 0 SwiftCentral LOG SUSPEND 0
0 224 sleeping dbo_swiftcentral dbo_swiftcentral tk0d2311ap 0 SwiftCentral LOG SUSPEND 0

you will see a log suspend or you might not you might see a block the next best thing to do is to use the master and select * from syslogshold to see if there are any long running transactions you can also to a sp_lock in the database. the transaction below is doing an insert in this case I would just truncate the log as this is a log suspend as you can see below this has cleaned up the tranlog. If that does not work then you can kill the spid

use master
go
select * from syslogshold
1> select * from syslogshold
2> go
dbid reserved spid page xactid masterxactid starttime name xloid
------ ----------- ------ ----------- -------------- -------------- -------------------------- ------------------------------------------------------------------- -----------
31516 0 9 6514 0x721900001600 0x000000000000 Jan 20 2009 9:18AM insert 18

(1 row affected)

dump tran database with truncate_only
go

1> use database
2> go
1> Dbcc checktable(syslogs)
2> go
Checking syslogs: Logical pagesize is 2048 bytes
The total number of data pages in this table is 3420.
*** NOTICE: Space used on the log segment is 3470 pages (6.78 MB), 27.11%.
*** NOTICE: Space reserved on the log segment is 0 pages (0.00 MB), 0.00%.
*** NOTICE: Space free on the log segment is 9330 pages (18.22 MB), 72.89%.
Table has 25403 data rows.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

if this does not work and the tranlog is still full
kill 9 where 9 is spid
this will then put the transaction in to rollback. Remember to speak to your apps team or dev team I would not just kill the spid.

Creating a table in Sybase

2009-01-19T16:08:00.000-08:00

Now you have your database and users setup we should look at creating tables.
remember before when I said about creating objects remember to put dbo before your table name otherwise the object will be created with your user ie jagot.table.

CREATE TABLE dbo.table
(
assetManagerCode varchar(30) NOT NULL,
assetManagerName varchar(100) NULL,
assetManagerNameJpy univarchar(100) NULL,
address1 univarchar(100) NULL,
address2 univarchar(100) NULL,
attention univarchar(50) NULL,
telephoneNo varchar(20) NULL,
faxNo varchar(20) NULL,
insertedBy varchar(30) NOT NULL,
insertTimestamp datetime NOT NULL,
updatedBy varchar(30) NULL,
updatedTimestamp datetime NULL,
CONSTRAINT PKAssetManagerDetails
PRIMARY KEY NONCLUSTERED (assetManagerCode)
)
LOCK ALLPAGES
go
CREATE NONCLUSTERED INDEX IndexAssetManagerName
ON dbo.AssetManagerDetails(assetManagerName)
go

Creating a user and aliasing the user to dbo

2009-01-19T15:54:00.000-08:00

I would not recommend logging in and out and doing all the changes with sa, so I would recommend creating and aliasing a dbo user. I would recommend being careful when creating objects with a user always specify dbo before creating tables and so on

to create a user

USE master
go
EXEC sp_addlogin 'dbo_user','PASSWORD','database','us_english',"user Dbo",null,null,null
go

USE database
go
EXEC sp_addalias 'dbo_user','dbo'
go
drop dbo_user
go
EXEC sp_addalias 'dbo_user','dbo'
go

Dumping and loading a database

2009-01-19T15:47:00.001-08:00

Now you know how to create a database maybe you have a DR server you wish to copy the database too.
Or maybe you just wish to back the database up as this is good practise just in case you loose data or your database become corrupt.

dump database database to '/dump/dbname.dmp
go

or if your database is compressed also you might want to strip the database over a number of volumes.

Dump database database to `compress::/fox/dump/dbname/:database.dmp
Strip /dbdump/proj/dump/dbname/:database.dmp　１,
Strip /dbdump/proj/dump/dbname/:databasedmp　２,
Strip /dbdump/proj/dump/dbname/:database.dmp　3,
Strip /dbdump/proj/dump/dbname/:database.dmp　4

This goes for loading too

if you are copying this to a dr site then
scp database hostname:/dump/proj/dbname/

load database dbname from '/dump/dbname.dump
or if you have a compressed database with strips

load database database from `compress::/fox/dump/dbname/:database.dmp
Strip /dbdump/proj/dump/dbname/:database.dmp　１,
Strip /dbdump/proj/dump/dbname/:databasedmp　２,
Strip /dbdump/proj/dump/dbname/:database.dmp　3,
Strip /dbdump/proj/dump/dbname/:database.dmp　4
go
online database database
remember to online the database

Creating a database in Sybase

2009-01-19T15:42:00.000-08:00

Sybase is a little diffrent to mssql in that you need to create sybase on devices.

In sybase you have data devices and log devices.

to create a database use this sytax.
I useually create devices called datadev and logdev as this will distiqush the too.

I would always say to set the dbowner to sa too so you know the jobs will have all the rights to the database

USE master
go
CREATE DATABASE database
ON datadev1=1000
LOG ON logdev1=250
go
ALTER DATABASE database
ON datadev2=1000
go
ALTER DATABASE database
ON datadev2=348
go
USE database
go
EXEC sp_changedbowner 'sa'
go

Intro to me and Sybase

2009-01-08T20:45:00.000-08:00

I have been working with databases now for around 10 years. I have about 4 years experience with Sybase and about 7 years with ms products from Access to SQL server 2008.

If you would like my CV please send me a mail to Terry.Jago@gmail.com

I wont start at installing sybase as if you are looking at my site you would have already installed it for some time now I have being to think of where to start on this site.