Sybase ADDICT

Embarcadero software and ISUG (International Sybase User Group) offer a free online webinar about query optimization for Sybase ASE. In this particular webinar, you will be able to ask directly your questions to optimize your sql queries with a bunch of top Sybase ASE experts like Peter Dobler and Kevin Sherlock. Query performances, Sybase ASE optimizer and optimizer mode will be discuss. This webinar is a valuable way to acquire or expand your knowledge.

3 sessions of this free webinar are scheduled October 21th from 7:00AM to 7:00PM.
See the schedule here.

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

Surprisingly, I still see some of my customers using Sql BackTrack to backup their Sybase ASE databases, even with simple backup strategy such as "dump database"… Sql BackTrack is a great third tool package to complete the "backup/restore tools" provided by Sybase ASE. But Sql BackTrack license cost can be very expensive. In this hard economic area, you can help your company to cut costs by skipping Sql BackTrack. How ? By using only new backup features Sybase implemented this last decade, and some work !

When I discuss with my customers the reasons why they use Sql BackTrack, they answer:

1°) "on the fly" backup compression capability.
2°) restore objects DDL (tables indexes views etc…) from backups files.
3°) databases are saved directly to TSM thanks to the OBSI module (additional cost).

For each key requirements mentioned above, Sybase has a solution or a workaround for it ! Read more…

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.