ASA SQL User's Guide
Importing and Exporting Data
Rebuilding a database is a specific type of import and export involving unloading and reloading your entire database. Rebuilding your database takes all the information out of your database and puts it back in, in a uniform fashion, thus filling space and improving performance much like defragmenting your disk drive.
It is good practice to make backups of your database before rebuilding.
Loading and unloading are most useful for improving performance, reclaiming fragmented space, or upgrading your database to a newer version of Adaptive Server Anywhere.
Rebuilding is different from exporting in that rebuilding exports and imports table definitions and schema in addition to the data. The unload portion of the rebuild process produces ASCII format data files and a ' reload.SQL ' file which contains table and other definitions. Running the reload.SQL script recreates the tables and loads the data into them.
You can carry out this operation from Sybase Central or using the dbunload command line utility.
When unloading and reloading a database that has proxy tables, you must create an external login to map the local user to the remote user, even if the user has the same password on both the local and remote databases. If you do not have an external login, the reload may fail because you cannot connect to the remote server.
For more information about external logins, see Working with external logins.
Consider rebuilding your database if you want to upgrade your database, reclaim disk space or improve performance. You might consider extracting a database (creating a new database from an old database) if you are using SQL Remote or MobiLink.
If you need to defragment your database, and a full rebuild is not possible due to requirements for continuous access to the database, consider reorganizing the table instead of rebuilding.
For more information about reorganizing tables, see the REORGANIZE TABLE statement.
If a database is participating in replication, particular care needs to be taken if you wish to rebuild the database.
Replication is based on the offsets in the transaction log. When you rebuild a database, the offsets in the old transaction log are different than the offsets in the new log, making the old log unavailable For this reason, good backup practices are especially important when participating in replication.
There are two ways of rebuilding a database involved in replication. The first method uses the dbunload utility -ar option to make the unload and reload occur in a way that does not interfere with replication. The second method is a manual method of accomplishing the same task.
The rebuild (load/unload) and extract procedures are used to rebuild databases and to create new databases from part of an old one.
With importing and exporting, the destination of the data is either into your database or out of your database. Importing reads data into your database. Exporting writes data out of your database. Often the information is either coming from or going to another non-Adaptive Server Anywhere database.
Rebuilding, however, combines two functions: loading and unloading. Loading and Unloading takes data and schema out of an Adaptive Anywhere database and then places the data and schema back into an Adaptive Server Anywhere database. The unloading procedure produces fixed format data files and a reload.SQL file which contains table definitions required to recreate the table exactly. Running the reload.SQL script recreates the tables and loads the data back into them.
Rebuilding a database can be a time consuming operation, and can require a large amount of disk space. As well, the database is unavailable for use while being unloaded and reloaded. For these reasons, rebuilding a database is not advised in a production environment unless you have a definite goal in mind.
Rebuilding a database involved in replicationThe procedure for rebuilding a database depends on whether the database is involved in replication or not. If the database is involved in replication, you must preserve the transaction log offsets across the operation, as the Message Agent and Replication Agent require this information. If the database is not involved in replication, the process is simpler. |
Rebuild tools
Rebuild file formats
Exporting table data or table schema
Reloading a Database
Rebuilding a database not involved in replication
Rebuilding a database involved in replication
Minimizing downtime during rebuilding