Sybase ADDICT

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.

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…

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.