Difference in Sybase 12 and 15

When one works on database migration, one has to think about many things and test carefully. The things that work in previous version, spectacularly fail in the newer version. Why these software are not backward compatible, is beyond this discussion.

For example, when we try to use isql tool to connect to Sybase database server on Sybase 12 version, everything works well.

For example you can use following syntax on Sybase 12 and everything should work ok.

 

isql -X -Uusername -Ppassword -w2000
// perform database operations and exit

When you use the same syntax while trying to connect to Sybase 15 database server, you will see following error.
 

ct_connect(): protocol specific layer: internal Client Library error: There is a tds login error. The installed encryption handler returned a status that was not CS_SUCCEED.

The usage of -X parameter in this statement is the culprit. One of the simpler way to resolve this issue is to get rid of -X parameter.

isql  -Uusername -Ppassword -Sservername -Ddatabasename -w2000
// perform database operations and exit

DB2 temporary table deletion

I was working on creating relatively simple DB2 stored procedure. In that procedure, I had to create a temporary table, insert some values in it, and in the next call to the procedure, i needed to delete the temporary table. I searched a lot to find right way of deleting the temporary table but nothing worked. Please know that when you use just the following syntax, it does not work. DB2 complains when the first time the procedure is compiled, because the temporary table does not exist yet.

// procedure creation, definition, result, etc etc. 
drop table SESSION.tempresult;
declare global temporary table SESSION.tempresult (
FIRST INTEGER,
SECOND INTEGER)
on commit preserve rows not logged;

//Rest of the logic 

So what can you do? Following worked for me.

// procedure creation, definition, result, etc etc. 

DECLARE SQLSTATE char(5);
DECLARE v_TABLE FOUND INT DEFAULT 0;
DECLARE c_TABLE FOUND CONDITION FOR SQLSTATE '42704';
DECLARE CONTINUE HANDLER FOR c_TABLE_FOUND SET v_TABLE FOUND = 1

DROP TABLE SESSION.tempresult;
declare global temporary table SESSION.tempresult (
FIRST INTEGER,
SECOND INTEGER)
on commit preserve rows not logged;

//Rest of the logic