Migrating a 32-bit ASE database to 64-bit ASE

Frequently people upgrade a 32-bit Sybase server to 64-bit. For example when moving from Sybase 11.9.2 on Solaris to Sybase 12.5.0.1 on the same platform. Although the upgrade looks successful, the day following the upgrade the OLTP (mainly user transactions) can cause serious performance problems or freezing of Sybase ASE engines. I have seen occasions when users could not log in to ASE (engine 0 frozen) and ASE had to be killed at the O/S level and brought back on-line. Very messy indeed.

In my opinion,the cause of this problem has something to do with the compiled objects in a 64-bit database. I have seen a number of occasions of this and I am now convinced about it! The 32-bit to 64-bit upgrade process is explained in chapter 8 of Sybase 12.5 "Installation Guide". The bottom line is that if you do not recompile the compiled objects (defaults, procedures, views and triggers" YOURSELF under 64-bit ASE, sybase will try to upgrade them internally using "dbcc upgrade_object" facility when they are called first time. [color red]However, this can fail and in some occasions can cause ASE to hang if the upgrade process fails[/color]. I do not know the reasons for this. Possibly time slice problem?

To avoid these problems, I suggest that DBAs and developers recompile manually the compiled objects in a given database following the successful upgrade of a server to 64-bit. Please note that this applies to all user databases including the replication server RSSD database ( I have seen some replication server commands (rs_?) failing in the RSSD database post upgrade!). The same principle applies whether you are dumping and loading a 32-bit database to 64-bit. Do not take a chance especially in production!

In summary you need to drop and recreate all the views, procedures and triggers. You also need to put permissioning back for views and procedures once they have been created. Do not forget the transaction modes for stored procedures as well. For replicated stored procedures you need to turn on the replication flag using sp_setrepproc etc. This is especially true for RSSD database!

Have a look at the script below that I use for this purpose. If you have problems understanding it, please come back to me. The best place to get the code for database objects is via reverse engineering from the database itself! When you are creating SPs make sure that if the stored procedure makes a reference to a #table, the #table is created beforehand in the same session that creates the stored procedure. This is the new ANSI complience of Sybase 12 etc.

I have a shell routine which does all these in one go, ${SQL_SERVER} is the name of 64-bit ASE and ${DB} is the name of database to be upgraded

#
### These steps assume that a 1192 database is loaded into 1250
#
LOG_FILE=${LOGDIR}/${FILE_NAME}_${SQL_SERVER}_${DB}.log
[ -f ${LOG_FILE} ] && rm -f ${LOG_FILE}
touch ${LOG_FILE}
#
echo `date` " ""======= Started the 1250 upgrade process for ${DB} on ${SQL_SERVER} =======" | tee -a ${LOG_FILE}
#
### save the old stuff
#
echo `date` " ""======= Backing up the old system tables and checking the source code =======" | tee -a ${LOG_FILE}
isql -U${USERNAME} -P${PASSWORD} -S${SQL_SERVER} -w1000 << ! >> ${LOG_FILE}
use master
go
sp_dboption ${DB}, 'select',true
go
sp_dboption ${DB}, 'abort',true
go
sp_dboption ${DB}, 'trunc.',true
go
use ${DB}
go
checkpoint
go
select * into sysobjects_1192 from sysobjects
go
create unique clustered index sysobjects_1192 on sysobjects_1192 (id)
go
create unique index ncsysobjects_1192 on sysobjects_1192 (name,uid)
go
update statistics sysobjects_1192
go
update index statistics sysobjects_1192
go
select * into sysprocedures_1192 from sysprocedures
go
create unique clustered index sysprocedures_1192 on sysprocedures_1192 (id, number, type, sequence)
go
update statistics sysprocedures_1192
go
update index statistics sysprocedures_1192
go
select distinct o.name, o.type, p.version,p.status
from sysobjects_1192 o, sysprocedures_1192 p
where o.id = p.id
order by o.type,o.name
go
dump tran ${DB} with truncate_only
go
sp_checksource
go
exit
!
echo `date` " ""======= Reverse engineering all the view permissions =======" | tee -a ${LOG_FILE}
/apps/sybase/dba/bin/1250_upgrade/reverse_engineering/shells/permissions_view.ksh ${SQL_SERVER} ${DB}
echo `date` " ""======= Reverse engineering all the procedure permissions =======" | tee -a ${LOG_FILE}
/apps/sybase/dba/bin/1250_upgrade/reverse_engineering/shells/permissions_sp.ksh ${SQL_SERVER} ${DB}
echo `date` " ""======= Reverse engineering all the transaction mode for sps =======" | tee -a ${LOG_FILE}
/apps/sybase/dba/bin/1250_upgrade/reverse_engineering/shells/reverse_procxmode.ksh ${SQL_SERVER} ${DB}
#
echo `date` " ""======= Reverse engineering all the triggers =======" | tee -a ${LOG_FILE}
/apps/sybase/dba/bin/1250_upgrade/reverse_engineering/shells/reverse_objects.ksh -S ${SQL_SERVER} -D ${DB} -T TR
#
echo `date` " ""======= Reverse engineering all the views =======" | tee -a ${LOG_FILE}
/apps/sybase/dba/bin/1250_upgrade/reverse_engineering/shells/reverse_objects.ksh -S ${SQL_SERVER} -D ${DB} -T V
#
echo `date` " ""======= Reverse engineering all the procedures =======" | tee -a ${LOG_FILE}
/apps/sybase/dba/bin/1250_upgrade/reverse_engineering/shells/reverse_objects.ksh -S ${SQL_SERVER} -D ${DB} -T P
#
echo `date` " ""======= Putting in the objects from under /apps/sybase/dba/bin/1250_upgrade/scripts/${GROUP} directory =======" | tee -a ${LOG_FILE}
#
### now put back all the missing stuff
#
cd /apps/sybase/dba/bin/1250_upgrade/scripts/${GROUP}/${DB}
./install.ksh $SQL_SERVER $DB
echo `date` " ""======= Granting permissions on views =======" | tee -a ${LOG_FILE}
#
isql -U${USERNAME} -P${PASSWORD} -S${SQL_SERVER} -w1000 -b < ${LOGDIR}/${SQL_SERVER}_permissions_view_${DB}.sql >> ${LOGL_FILE}
#
echo `date` " ""======= Granting permissions on stored procedures =======" | tee -a ${LOG_FILE}
#
isql -U${USERNAME} -P${PASSWORD} -S${SQL_SERVER} -w1000 -b < ${LOGDIR}/${SQL_SERVER}_permissions_sp_${DB}.sql >> ${LOGL_FILE}
#
echo `date` " ""======= Putting back transaction mode on stored procedures =======" | tee -a ${LOG_FILE}
#
isql -U${USERNAME} -P${PASSWORD} -S${SQL_SERVER} -w1000 -b < ${LOGDIR}/${SQL_SERVER}_reverse_procxmode_${DB}.sql >> ${LOGL_FILE}
#
echo `date` " ""======= Doing dbcc upgrade_objects =======" | tee -a ${LOG_FILE}
#
### now do the dbcc upgrade_object
#
isql -U${USERNAME} -P${PASSWORD} -S${SQL_SERVER} -w1000 << ! >> ${LOG_FILE}
use ${DB}
go
dbcc traceon(3604)
go
dbcc upgrade_object
go
--
-- now look for upgraded objects
--
select distinct o.name, o.type, p.version,p.status
from sysobjects o, sysprocedures p
where o.id = p.id
order by o.type,o.name
go
--
-- Now check for missing objects
--
select t.name,t.type from sysobjects_1192 t
where not exists (select 1 from sysobjects o where t.name = o.name and t.type = o.type)
order by t.type, t.name
go
exit
!
#
echo `date` " ""======= Finished the 1250 upgrade process for ${DB} on ${SQL_SERVER} =======" | tee -a ${LOG_FILE}
echo `date` " ""======= Check the file ${LOG_FILE}"