I have a lot of databases using dbspaces, I'd like to consolidate all the dbspaces into one database file for each database, Each database isn't massive (5gb ish) and due to various reasons all the dbspace files for a database have ended up on the same drive anyway so I'd rather each database was just two files (the db and the log). I've had a search and the only tips I can find are for unloading the database and modifying the reload sql. Is there any hidden switches in dbunload, another sybase utility,or some other way that can do this now ?
I'm using v10.0.1.4075, I can patch it up if I have to but can't upgrade to 11.
asked 27 Jul '10, 14:41
The -kd option for dbunload in version 12 should do what you need. It was added for use when creating a database for diagnostic tracing, but should function as described in normal use as well.
Since you can't upgrade to a newer version, modifying the reload.sql script is your only alternative.
answered 27 Jul '10, 17:45
You should be able to automate the editing of the reload script (using something like Windows Grep), so that might not be too painful.
The only way I can see of avoiding an unload / reload (which would be beneficial anyway given the changes involved to the main db file tructure) would be to rename the tables in the other dbspaces, recreate them in the main db space (with indexes, foreign keys, triggers etc etc) copy the data across and then drop the other dbspaces. That could be semi-automated using queries on the system tables, but I would have thought that the unload, modify, reload route would be much easier.
answered 27 Jul '10, 18:34
And I have found the unload/reload does many nice things for me: reorganizing and defragging pages in tables, indexes, etc. which improve performance. I make it an annual maintenance task.
answered 27 Jul '10, 21:46