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. Thanks Daz. |
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. |
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. |
Feel free to suggest an extension to the ALTER TABLE statement, such as ALTER TABLE myTable MOVE TO <dbspacename> if that seems useful for your needs and a reload is not feasible.
I'm not sure sybase will add that option to v10, but thanks :-)
@Daz: I'm quite sure they won't, but I'd never hesitate to suggest any useful extension for the next SA release:)