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…

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.

First, “bcp OUT” syslogins from your ASE v12.5 dataserver:

bcp tempdb..syslogins out master.v125.syslogins.bcp -c -t"|" -r"\n" -Usa -SSYBPARFRDEV02_DS

Then, connect to your ASE v15 dataserver and create in tempdb a table based on the v12.5 syslogins table (you can reverse the syslogins ddl from Sybase Central or from DDLgen tool):

isql -Usa -SSYBPARFRDEV01_DS -w1000

create table tempdb..ase125logins (
suid        int           not null,
status      smallint      not null,
accdate     datetime      not null,
totcpu      int           not null,
totio       int           not null,
spacelimit  int           not null,
timelimit   int           not null,
resultlimit int           not null,
dbname      sysname(30)   null,
name        sysname(30)   not null,
password    varbinary(30) null,
language    varchar(30)   null,
pwdate      datetime      null,
audflags    int           null,
fullname    varchar(30)   null,
srvname     varchar(30)   null,
logincount  smallint      null,
procid      int           null
)
lock allpages
on 'default'
go

Now you can “bcp IN” the v12.5 syslogins table into the ASE v15 dataserver:

bcp tempdb..ase125logins in master.v125.syslogins.bcp -c -t"|" -r"\n" -Usa -SSYBPARFRDEV01_DS

What’s next ? Connect to the ASE v15 dataserver, and insert the logins from the v12.5 syslogins table into the ASE v15 master..syslogins table. To avoid insert failures because of duplicate logins id, I exclude here the logins ’sa’,'probe’ and ‘guest’:

isql -Usa -SSYBPARFRDEV01_DS -w1000

sp_configure 'allow updates',1
go
insert master..syslogins
select *,null,null,null,null,null
from tempdb..ase125logins
where name not in ('sa','probe','guest')
go
sp_configure 'allow updates',0
go

Character Set convertion issue:

Before to bcp syslogins, consider each dataserver’s character set. If character set are different between dataservers, you’ll need to modify your bcp commands accordingly. Let’s imagine this particular case: SYBPARFRDEV02_DS is roman8 and SYBPARFRDEV01_DS is utf8.

“bcp OUT” then would be:
bcp tempdb..syslogins out master.v125.syslogins.bcp -c -t"|" -r"\n" -Jroman8 -Usa -SSYBPARFRDEV02_DS

“bcp IN” then would be:
bcp tempdb..ase125logins in master.v125.syslogins.bcp -c -t"|" -r"\n" -Jroman8 -Y -Usa -SSYBPARFRDEV01_DS

Option -Y specifies character-set conversion is disabled in the server, and is instead performed by bcp on the client side when using “bcp IN”.

Cross-platform issue:

Your syslogins is coming from a ASE dataserver prior to version 15.0.2 ? Then end-user passwords may not work anymore after a cross-platform bcp. Actually the hash value stored in syslogins..password column is computed natively inside ASE, regarding the byte ordering of the platform (little/big endian). So, depending of platform endianess, you may have to reset all logins passwords. Your syslogins comes from ASE 15.0.2 or above ? Then you should not have this problem. Indeed the hash value stored in syslogins..password column is no longer implemented natively inside ASE. As of ASE 15.0.2, password hash value generation has been improved and is now platform-independent thanks to 3rd-party cryptographic libraries standing outside the ASE executable.