Sybase ADDICT

This is an External End of Life Notice for Sybase IQ Version 12.7, the end of life date is 04/23/10, and the end of engineering support is effective on 04/30/11 and 03/31/12.
See more here

MDA tables are very useful to understand what’s going on within your dataserver, so you can carry out accurate and successful performance and tuning campaigns. In this post I publish few basic Sybase stored procedures to use with MDA tables that focus on databases objects activity only (but I may release more MDA stored procedures in the future).

If you haven’t installed yet MDA tables in your Sybase Adaptive Server, you can refer to my previous post HowTo install MonTables to install them. Read more…

Sybase is back with 3 new technical webcasts about Replication Server 15.5. Sybase webcasts series are very valuable to acquire additional knowledge from Sybase experts. The Replication Server’s webcasts are scheduled from May through June 2010 as follow:

Part 1: Replication Server 15.5 Overview

Tuesday, May 4, 2010
1:00 p.m. – 2:00 p.m. ET
(10:00 a.m. – 11:00 a.m. PT)

Part 2: Real-Time Loading for Sybase IQ

Thursday, May 20, 2010
1:00 pm – 2:00 pm ET
(10:00 am – 11:00 am PT)

Part 3: Replication Server and Performance

Tuesday, June 8, 2010
1:00 pm – 2:00 pm ET
(10:00 am – 11:00 am PT)

You can join Sybase experts webcasts for FREE: Register Here

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.