Is it possible to rebuild just one database space

[Former Member]

We have 3 database spaces: central.db, sales.db, invoice.db. The invoice.db has grown quite large and we are looking to drop 50% of the records (90GB of data). Is it possible to rebuild just this database space to reclaim this disk space or will we have to do a full unload/reload of the entire database?

We are using SQL Anywhere v12..01.3942 on Windows Server 2008 R2.

Thanks, Brian

[Breck_Carter]

This is not an actual answer, but...

What are the sizes of the three files?

The delete, unload and reload processes may be quite time consuming, compared with simply creating a new database and doing selective UNLOAD SELECT statements followed by LOAD TABLE on the new database to copy the data you want... rather than struggling with the data you don't want.

If the other two files are small compared with invoice.db, they may not be worth excluding from the process.

Note that the dbunload -nl option can be used to generate a reload.sql file with all the commands required to recreate and reload everything... without actually unloading any data.

AFAIK you would be on your own coding the UNLOAD SELECT statements to create the files used by the reload.sql file.

Also, this discussion assumes you will be using the dbunload command rather than Sybase Central (don't even think of using Sybase Central for any of this :), and that you understand and test the dbunload -nl, dbisql UNLOAD SELECT, dbinit and dbisql reload.sql commands beforehand.

[VolkerBarth]

In addition, how many tables do reside on the larger dbspace? Are they heavily connected via FKs and the like? Are there other database objects residing there - indexes and/or materialized views?

IMHO, the following untested method might work (it's expected you have a working backup!):

1. Unload these tables (say, with DBUNLOAD -t MyTable1, MyTable2, ...), i.e. both structure and data
2. Make sure you can use the according reload.sql script and the extracted data to re-create the according database objects.
3. Drop the existing tables etc. from the old dbspace.
4. Drop the dbspace.
5. Create a new dbspace with the same name and the desired size (ALTER DBSPACE ADD...)
6. Use the script from DBUNLOAD (which will require some modifications, methinks) to re-create the according tables (and indexes) and reload the data.

In case you only have one single table on the dbspace, it would be easier to just rename that table, rename the dbspace, create a new dbspace with the according table and INSERT SELECT from the old table, and then drop the old objects.

---

Aside: I do not claim this is easier than a general reload.

[Former Member]

I am getting the impression that there is no built-in clean way to do this and since our database is involved in replication / synchronization, it probably creates some additional complexitities. I will test standard unload/reload and head down that path. Thanks for your feedback.

[Breck_Carter]

Are you using both SQL Remote and MobiLink? Using SQL Remote implies you must protect the transaction log, and that implies special care must be taken with unload/reload as discussed in the Help.

If it was me, I'd try to live with a *.db with plenty of free space for repeated growth... it will be reused for new rows... rather than deal with the pain of unload/reload. Disk space is cheap. REORGANIZE statements can be used to gain the other performance benefits of unload/reload.

[VolkerBarth]

I guess in both cases (SQL Remote and/or MobiLink), my suggestion to drop and re-create published tables is not recommended at all - that will usually break things... - Even a full unload/reload is something I would try to omit in such cases if possible, and otherwise I'd recommend very thorough testing.

So I'll certainly share Breck's point of view:)

[Breck_Carter]

> in both cases (SQL Remote and/or MobiLink)

AFAIK, in the case of a database that plays no other role than as a MobiLink consolidated database, there are no restrictions on ordinary operations like unload, reload, drop-and-recreate, etc (with stress put on the word ordinary operations, there are certainly other things you can do to destroy synchronization 🙂

In particular, a MobiLink consolidated database has no physical representation of "published table"... think Oracle 🙂

Caveat: Trying to do some of those "ordinary operations" while the MobiLink server is running moves them out of the "ordinary" category into the "death wish" zone 🙂

[VolkerBarth]

Thanks for the clarification, I was more thinking of a ML client...

[VolkerBarth]

You might suggest an enhancement to "move" a table to another dbspace, something like the fictional

ALTER TABLE [owner.]tablename MOVE TO location-string


Apparently, that would be an expensive operation, even more than REORGANIZE TABLE...
That way, you could move a table to a new dbspace and drop the old one afterwards - or could move it "home" to the system dbspace. Like REORGANIZE TABLE, it should not have effect on replication/synchronization as it would just move data physically, but not logically.