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.

asked 27 Jul '10, 14:41

Daz%20Liquid's gravatar image

Daz Liquid
861182338
accept rate: 28%

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.

(28 Jul '10, 08:32) Volker Barth

I'm not sure sybase will add that option to v10, but thanks :-)

(28 Jul '10, 14:25) Daz Liquid

@Daz: I'm quite sure they won't, but I'd never hesitate to suggest any useful extension for the next SA release:)

(28 Jul '10, 15:48) Volker Barth

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.

permanent link

answered 27 Jul '10, 17:45

Bruce%20Hay's gravatar image

Bruce Hay
2.6k1510
accept rate: 48%

Interestingly v12 developer Edition performs an unload with that option on a v10 database, I haven't got any further with it than just unloading so I'm not sure if the rest of it will work but I'll follow it up later. It's hardly a perfect solution but it might be workable.

(28 Jul '10, 14:29) Daz Liquid

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.

permanent link

answered 27 Jul '10, 18:34

Justin%20Willey's gravatar image

Justin Willey
6.8k111145214
accept rate: 20%

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.

permanent link

answered 27 Jul '10, 21:46

Bill%20Aumen's gravatar image

Bill Aumen
2.1k334674
accept rate: 16%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×41
×18

question asked: 27 Jul '10, 14:41

question was seen: 992 times

last updated: 27 Jul '10, 21:46