Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to ensure dump for cross-platform load will actually load on target platform

adaptive server

How to ensure dump for cross-platform load will actually load on target platform

by  JMCraig  Posted    (Edited  )
It can be extremely annoying to attempt a cross-platform load only to have the ONLINE DATABASE command report that it's converted all the system and user tables, but the log indicates that the database was not "quiescent" at the time the dump was made. (It tells you to do the sp_flushstats and to ensure that there were no updates during the dump--which you presumably carefully did. Arrrgh!)

The use of the term "quiescent" is particularly confusing because you may assume you should use the QUIESCE DATABASE command before doing the dump for the cross-platform migration--don't do it: you cannot do a DUMP DATABASE command against a DB that has been forced to be quiescent by QUIESCE DATABASE (presumably because it cannot record the dump operation in the transaction log).

In any case, after much experimenting in a Solaris 64-bit to a Linux 32-bit migration, here's a sequence of commands (beyond what Sybase suggests in their documentation) that does work.

Naturally, throughout these steps, use your own database name rather than testdb and an approprate dump file or device as part of the DUMP DATABASE command.

Code: 
/* in addition to setting to single user mode, set to truncate the log on checkpoint too--just in case. (This is probably not necessary, but after days of experimenting, I'm not interested in finding the absolute minimal set of commands that'll do the job: this works and it takes no additional time.) */
use master
go
sp_dboption testdb, 'trunc', true
go
sp_dboption testdb, 'single', true
go
use testdb
go
checkpoint
go
/* get rid of transaction log entries
--you're going to do a full dump anyway so it presumably doesn't matter; you could probably also dump it to a file/device */
dump transaction testdb with truncate_only
go
/* verify that the log is empty--should be no more than 16 pages of reserved space */
use testdb
go
sp_spaceused syslogs
go
/* now start the steps that Sybase documents */
sp_flushstats
go
/* wait for everything to settle at this point, then: */
checkpoint
go
/* do the actual dump */
dump database testdb to '/data/sybdump/testdb.bkp'
go
A dump created this way will report something similar to the following during the cross-platform conversion steps in ONLINE DATABASE. (Note that there's only one entry in the transaction log [first and last entries are the same ID and it's a checkpoint]):

Code: 
Started estimating recovery log boundaries for database 'xpl_testdb'.
Database 'xpl_testdb', checkpoint=(33135568, 12), first=(33135568, 12),
last=(33135568, 12).

I hope this saves someone some trouble!

As far as I can tell, there's no way to know if the dump will produce a file that will load in cross-platform mode other than by ensuring that the transaction log is empty (via sp_spaceused: on a 2K-page-size DB, it reports no more than 32K of data; if you have a larger page size, the data space would be correspondingly larger).
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top