Sybase ADDICT

To know when the last full backup of your database occurred you can look at the backupserver logfile, of course. But you don’t have to. Indeed you can find this information with Sybase ASE using the command “dbcc dbtable”.
Read more…

I recently had to resync a warm standby database for a customer. When I can’t stop the transactions on primary database I use the “dump marker” clause while recreating the connection to the warm standby database with replication server. Indeed, you can resync a warm standby database anytime with no downtime on the primary database side using the “dump marker” method. Let’s assume the following warm-standy architecture:

- SYBPARFRDEV01_DS as the primary dataserver
- SYBPARFRDEV02_DS as the target dataserver
- SYBPARFRDEV02_RS as the replication server
- projectdb as the primary and replicated database

Read more…

Wonder how to display or delete duplicate records ? Sometimes, under certain circumstances duplicate data may occur. Then it should be deleted.

In this example, we are going to create a dummy table with an identity column as a unique key, and with two varchar columns. Then we are going to insert data and duplicates manually. The script for the test comes as follow:

create table dummytable(
keycolumn   numeric(5,0) identity,
column1     varchar(10),
column2     varchar(10)
)
go

insert dummytable(column1,column2) values("AAA","AAA")
go 10
(1 row affected)
10 xacts:

insert dummytable(column1,column2) values("AAA","BBB")
go
(1 row affected)

insert dummytable(column1,column2) values("BBB","BBB")
go
(1 row affected)

insert dummytable(column1,column2) values("BBB","AAA")
go
(1 row affected)

Read more…

Yes, with Sybase ASE you can get a stored procedure result as rows in a table. The trick is to use proxy tables with an optional clause to the "create existing table" statement to tell ASE the remote object is actually a stored procedure, not a table.

This requires CIS to be enabled on your ASE:

1> sp_configure "enable cis",1
2> go

Make sure to add a local server to the sysservers system table by adding:

1> sp_addserver loopback, null, @@servername
2> go

Columns list and datatypes must match the description of the procedure’s result set. Consider it when you create your proxy table, otherwise you’ll get errors:

1> use tempdb
2> go
1> create existing table DBDATABASE(
2> name varchar(35),
3> db_size varchar(20),
4> owner varchar(20),
5> dbid int,
6> created varchar(35),
7> status varchar(255)
8> )
9> external procedure at "loopback.sybsystemprocs.dbo.sp_helpdb"
10> go

Then you can freely query your proxy table to display the stored procedure result set:

1> select * from DBDATABASE
2> go

Or store the result in a table for further processing later on:

1> select * into #temptable from DBDATABASE
2> go

‘group by’ behaviour has changed in ASE v15.x and now follows the ANSI standard (previous ASE versions did not). Indeed the ANSI standard does not sort the output of a ‘group by’ without an ‘order by’. Thanks to traceflag 450, you can revert back this behaviour like it was with previous ASE versions.

1°) At dataserver startup:

Start ASE with -T450 added to the startup script to enable the traceflag for all client sessions.

2°) At session level you can issue:

1> dbcc traceon(450)
2> go

or

1> set switch on 450 with override
2> go

Note: “set switch” is the replacement command for dbcc traceon.